[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


ADO, MySQL, and VB6 Record Locking

Posted on 2007-07-24
Medium Priority
Last Modified: 2013-12-25
So, here's the story:

I am writting a VB6 program that accesses a MySQL server to get info and write info.  I am using ADO to make the connections.

The program gets a recordset and calls a phone number from that recordset.  If the caller answers, it sets the status of the call to accepted.  

This program works mostly fine.  The problem I need to run multiple instances of the program.  When I try that, every instance of the program tries to call the first record at the same time.  I need to find a way for the program to check if a record is already being accessed from another program.  I can lock the write function, but how do I stop it from trying to read the record as well?

I saw a stored procedure for SQL on this site, but it doesn't work (or so it seems) for MySQL.  I am open to doing it with ADO and VB6 or with MySQL stored procedures that I can access from VB6.  Any ideas are welcome.  Thanks.
Question by:Gregg Battaglia
  • 2
  • 2
LVL 35

Expert Comment

ID: 19561176

What table types are you using? And what version of mysql?

Depending on these the stored procedures may / may not work and there may not be row level locking enabled.

However if you are using ado then you can execute sql strings directly so should be able to do anything you would do with a stored procedure if you are using the correct versions.

can you have one program (server side) that returns the next row, then  you will nto get any contenion issues.
LVL 29

Accepted Solution

leonstryker earned 1500 total points
ID: 19565679
Seems to me that the easiest way to control this would be for a store procedure to grab only a subset of data say 20 records and mark those records as in use. I would setup a separate column with a value of 0, then as the data is being processed by the store proc, change it to 1.  The next instance would then not grab any rows that are marked 1.


Author Comment

by:Gregg Battaglia
ID: 19566353
I would be okay just using ADO without stored procedures (I am not that familiar with SQL commands as it is)  I would like to use pessimistic locking and not let any other program read the record as well as write to it.

I am using MySQL 5.0.41 on the Server side.
The table engine is InnoDB.
LVL 29

Expert Comment

ID: 19566402
>I would be okay just using ADO without stored procedures

I think you will get yourself into trouble here.  How do you ensure that once an instance finishes and releases the locks, the next one will not grab the same numbers and trys to process them again?


Author Comment

by:Gregg Battaglia
ID: 19566458
Well, it uses the record and if there is sucess, it chages the status from 0 to 1.  The program knows not to use records set with a 1. It is okay if the next program tries a failed record again.  As long as it is not trying that same record at the same time.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Simple Linear Regression

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question