My project team and are in the midst of converting our current Oracle 8i database (220.127.116.11) to 9i (18.104.22.168.0) and have come across an issue when trying to retieve data from the database using stored procedures. Using ADO we attempt to retieve a result set with the lock type set to adLockBatchOptimistic and are recieving a catastropic error. We have tested creating dynamic sql statements and retrieve result sets that way, and there is no problem. The issue seems to be when we set the ado recordset locktype to adlockbatchoptimistic. We have changed the locktype to adLockOptimistic and have been successful. This is not a acceptable solution though as we have many components which use the adLockBatchOptimistic locktype. Our data provider is the Oracle ODBC driver. Any body know of any issues with this driver and adlockbatchoptimistic? Any body have any ideas? Thanks in advance
Have you tried using any other type of cursor ( adUseClient, etc.. ) to see if it success with one ? I would think the provider doesn't support the pretended lock used. Try using yourrs.supports( adUpdateBatch ) to see wether or not it supports it. Also, if you are using global modules for this, you could change that constant value ( adLockBatchOptimistic ) for something else to fix the problem.
We tried your suggestion but when we try to open the record set after seting the locktype to adLockBatchOptimistic we the a VB error "Unsecified Err" so we can't then check to see if the recordset supports the locktype using the rs.supports(adbatchupdate) method.
We are thinking it could be something wrong in our connection string? Our connection string is currently "DSN=XXX;UID=XXX;PWD=XXX" where XXX represents the actual values. Could something be missing from the string. The odd this is that we had no problems when using the oracle 8i driver, and even no problems using the 8i driver with the 9i database, Its just the Oracle 9i (believe the driver version is 9.2 ) that is causing the problems.