ADO, MySQL, and VB6 Record Locking

Posted on 2007-07-24
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
    LVL 35

    Expert Comment


    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

    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
    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

    >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
    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    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…
    An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now