Help with table locking 1

[Griffyn]

I have a software package with it's own dataflex database. I use the Connx ODBC driver to access the database tables in Excel, Access and Delphi.

There is a database table (SYSNUMS) with a single record that stores the next transaction number for different types of transactions, eg. invoice, payment, receipt, etc. Whenever a new invoice is created in the software package, a new record with the number showing in the INVOICE_NO field in SYSNUMS is created in the invoice header table, and then the INVOICE_NO field in SYSNUMS is incremented.

The software package must use locking of some sort, because it won't create duplicate invoice numbers, even though we've got 40+ users using it.

The problem is I want to use a Delphi app to create an invoice, and it must read and increment the INVOICE_NO field, without allowing anyone else to jump in and read or increment it in the middle. That would be disaster.

What should my approach be here? The ODBC driver doesn't support transactions. I'm only vaguely aware of what all the properties of TADOConnection and TADOCommand do.

If I could return the field value from TADOCommand as a result of an UPDATE SQL command, that would be dandy.

 

[majlumbo]

You could build another application whose function is nothing more than accepting requests and providing INVOICE_NO. That single app can run on the same machine your database is on. Each instance of your user's application would request an invoice_no from that app, not from the database directly.

That way, only one thread can access the SYSNUMS table at a time, no matter how many users are requesting invoice numbers.

or

on a single computer, this could easily be solved by using a mutex, but since you are dealing with networked applications, there's no network mutex equivalent.

However, you could set up a file in a shared location. Before requesting a invoice number, lock the file (by opening it)

var
   fs:  TFileStream;
   fn:  String;
   successful:  Boolean;
begin
   successful := False;
   fn := <your file location and name>;
   while not successful do
   begin
      fs := TFileStream.Create(fn, fmOpenReadWrite or fmShareExclusive) 
      try
         try
            //should be able to safely get invoice number here
            //since file is locked - no one else can enter 
            //this section to get invoice numbers
            successful := True;
         except
            successful := False;
         end;
      finally
         fs.free;
      end;
   end;
end;

you may want to also limit the amount of time in the while loop to prevent an infinite loop just in case

 

[Griffyn]

Thanks for your thoughts. Sorry I wasn't clear - the software package using the dataflex database is a commercial package that I cannot modify.

The app I'm writing needs to work alongside it.

 

[majlumbo]

You may be better served by going to the tech support for the application you are working with -

Does the application provide a COM interface so you can automate those processes from Delphi? If not, can the application create a stub entry - getting the invoice number, then completing the process through Delphi?

 

[DjangMan]

You can try reading the record and then updating it with a where clause. Something like:

Read NextInvoiceNo From TheTable

returns, for example, 12345

Update TheTable Set NextInvoiceNo=12346 Where NextInvoiceNo=12345

If you don't get a response back from the database that you updated one record then the update failed and someone else got that invoice number. Repeat the loop x times.

 

[majlumbo]

What DjangMan is recommending may work, but it can cause horrible problems if the database is using cached updates (both your app and the commercial app)...

 

[Griffyn]

Thanks DjangMan, I like the approach.