VisualBasic-ODBC-Oracle, Limitations?!!

In article <1994May19.183143.5789_at_verifone> sanjay_p1_at_verifone.com writes:

> I am working on this setup since past couple of months, and found
> a lot of limitations. There are a lot of good features provided by Oracle,
> but over my set up VisualBasic-ODBC-Oracle these features are of no use.

I'm interested in this. What kinds of features are not available to VB but possible in Oracle? I'm more experienced in VB but less knowledgeable in Oracle.

> Very simple function call but effective in Visual Basic ie.e
> ExecuteSQL just does not work! I dont know if I could execute the
> stored procedure from VB. Also when I have to Insert-Update-Delete
> records, I create Dynasets. And they take noticably long time.
> This case is just with few records arround 20 in the table, I wonder
> whats going to be the case when my number of records grow to say 10000!
>
> Has anybody faced this kind of situation? If so lets share it.

I have recently tested out VB 3.0 Professional against Oracle 7 running on Netware. I've encountered some of the problems you mentioned. For starter, using dynaset to perform insert, update, delete is slow. It is much faster to use ExecuteSQL( "insert into atable ..." ) etc but the problem is this will incur one extra connection and the SQL update commands will be automatically commited.

When your recordset is big, lets say 50,000 records, I found that using a snapshot was much faster than creating a dynaset. This is contrary to information in VB manual but it's true. Finally I concluded that the quickest approach for Oracle access is to use snapshots and ExecuteSQL for update commands.

ExecuteSQL() worked fine for me. It could be used to call stored procedures as well. Can you provide me an example in code that ExecuteSQL() did not work properly?

Another possible way out is to call ODBC call level interface directly. The performance for this is quite adequate but then this approach will render data controls, data objects and many other third-party add-on *.VBX useless.

--
Email : hkchan_at_hkchan.pc.my