adLockOptimistic/Pessimistic and Stored Procedure (from SQL) 1

[aas1611]

Hi all,

Does anybody know the difference between adLockOptimistic and adLockPessimistic? I've read, both lock rows at the data source while being edited, but on adLockOptimistic the book says it has less consistency because multiple users may have changed data. How can multiple users change data if the rows are locked? What does it actually mean?(unfortunately the book doesn't explain that).

The second question is, is it possible to call a stored procedure (in SQL Server) from VB6? If yes, is it considerably faster (in database performance) than writing the SQL code on VB itself? Examples would be great!

Of course any useful info will be given a star.

Thank you very much.

 

[HarleyQuinn]

With regard to the locking question. As far as I remember when using optimistic locking other users are only locked out of the record(s) when the Update method is called, probably only for just a split second. In pessimistic locking other users are locked out for the entire period that the records are being accessed and modified.

Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations

 

[bjd4jc]

I would reccommend using stored procedures where possible. They run compiled code on the server and return only the requested results. Other queries have the potential of returning large amounts of data that will actually never be used, thus making them slower. You may want to take a quick look at the faq 'Using Parameters in ADO Stored Procedures' faq222-2067

 

[aas1611]

Thanks for all the answers.

Regarding the stored procedures, I will try further.

About the locking, if I use adLockPessimistic, what would happen programmatically if, for example, in the middle of modifying process I fail to call Update? Is the data source locked the whole time? How to unlock it?

Thanks again!

 

[bjd4jc]

If you are worried about that, I would use an UPDATE SQL statement via a stored proc using parameters.