ADO and Lock

[Jack]

Hello,
I have an invoice file with a field InvoiceNumber, when the user add a record, the invoice number must be the last one + 1

In a SQL network environment, how can i be shure that dont have 2 times the same number .

With DBF, when i want to do the same, i open an other file (LOCK.DBF) in EXCLUSIVE MODE and. if neterr() return .T. ,
i select the INVOICE file, go bottom , place last invoicenumber in memory, i add a new record in INVOICE file , replace InvoiceNumber with last one+1 and than i close the LOCK file .
If an other user try to add a record at same time, the EXCLUSIVE MODE (LOCK.DBF) return FALSE and the user is invited to try again .

How to do this with SQL code ?

Thanks

I

[nageswaragunupudi]

The easiest and the most reliable way is to create the invoiceno field as an autoincrement field.
Note: Even for DBFs (using DBFCDX) this is the current practice to use autoincrement field.
FW functions for ADO make it very easy to implement

[Jack]

I agree with you BUTthe custumer ask me to reset the counter to 1 each month.

There is a prefix in front of the invoice number who is YYYYMM-invoice number ==> 201506-0001 .....

Thanks for your help .

[nageswaragunupudi]

Understand.
I'll be back with a proposal in a while

[avista]

Hi,

You can define a field as unique and there is no chance to have 2 same values

Best regards,

[Rick Lipkin]

Jack

To my knowledge there is not a ( good ) way to lock a Sql table ... and the Ole provider usually handles all the 'opportunistic' locks for you.

With that said .. I have come up with a table called [InvoiceNumbers] and when a person wishes to create a new Invoice, I open the [InvoiceNumber] table, go to the bottom in Ascending order and add a new record +1 and immediately assign the new transaction the next Invoice Number oRs:Update() .. and out, ready for the next transaction, which works quite will in a multi-user application.

If the User wishes to cancel the transaction before I commit the detail .. so be it. There is just a sequence gap in the committed Invoice transactions.

Rao does have a GOOD point about using an AutoNumber on the InvoiceNumber field letting the database issue the next number, however using AutoNumber especially on a primary key can EASILY lead to 'Sql Injection' from a malicious attacker that may ( covertly ) gain access to your system, as 'unlikely' as that may be .. Database Security is a BIG issue today and creating your Own Invoices Numbers under program control ( not database control ) is a good ( security ) selling point.

Rick Lipkin

[Marc Vanzegbroeck]

Jack,

I put a unique index on the invoice-number.
Each time a customer add a new invoice I check for the last invoice number (Query with a desc order on invoice-number and a limit of 1).
Then I add directly an empty record with that invoice number to the table. So, if someone else whant to add an invoice, he will get a higher number. In an other table I record that someone is using that invoice, so nobody else can modify than invoice. Only when it is finished...

[Jack]

Marc,
Is it possible to give a sample of code .

I read in the past that it is possible to use a SQL table in exclusive mode , how to ?

Thanks.

[reinaldocrespo]

An Auto-increment field is a double edge sword that I would avoid as invoice numbers. One of the potential problems that can arise with auto-increment fields is that if the table is later merged o copied to another table, append from another table, or transported somewhere else, or even if restructured -these numbers will change. i.e... you don't have any control over auto-increment fields.

Restarting sequences every month will generate duplicated invoice numbers. I think you'd want to keep monthly sequences a part from invoice numbers which must be unique.

In this case I would declare the field as "unique" -that will ensure that even if badly coded, the invoice number will not be duplicated. It wouldn't be too hard to obtain the last invoice number used with a sql sentence using max. Then inside a transaction I would -try-and-retry to create the record with max(invoice#) + 1. The trying-and-re-trying inside a transaction will help solve the problem of other users racing to create an invoice # with the same number.

I hope that helps.


Reinaldo.

[Jack]

I am back with this problem .

Could someone share some sample of code of transaction with fivewin .

Thanks

[Maurizio]

Hello

I have DBF file
AADD (aDbf, {"XXXXX", "L", 1 , 0 })

when I print the Invoice I open the file in in exclusive mode
Dbusearea(.F. ,"DBFCDX" , m->E7 + ::cFile ,::oDbfMov:cAlias ,.T. ,.F.)

I use the same method with SQL.

Regards Maurizio
www.nipeservice.com

[Horizon]

The easiest and the most reliable way is to create the invoiceno field as an autoincrement field.
Note: Even for DBFs (using DBFCDX) this is the current practice to use autoincrement field.
FW functions for ADO make it very easy to implement

Hi Mr. Rao,

How Can I define autoincrement field in DBFCDX.

Thanks.

[russimicro]

Code: Select all  Expand view

Hi...

In addition to controlling the row, I check if the new invoice number exists in the transaction table, since it is feasible that someone consecutive alter table, for which use the following SQL with php :

function bloqueaDesbloqueaTablaConsecutivo($conn,$lBloTab) {

    $cSenEje  = "UNLOCK TABLES;";
    if ( $lBloTab == 1 ) {
       $cSenEje  = "LOCK TABLES consecutivos AS sec WRITE, transainventarios  WRITE, conceptosinventarios AS con WRITE;";
    };  
  $stmt=$conn->prepare($cSenEje);
  $stmt->setFetchMode(PDO::FETCH_ASSOC);
    $stmt->execute();    
    $registros = $stmt->rowCount();
    unset($stmt);
   
}   

Johnson Russi

 

[reinaldocrespo]

Instead of using an AutoIncrement field -for the reasons I have argued above on this thread- one way to solve this problem is to create a unique index based on the Invoice No field. When you try to insert a new record with an existing invoice number it will fail. After recovering, have the code continue to iterate until successful.

Like I said above, IMHO auto increment fields do not make good invoice numbers. If you ever merge data, replicate, res-structure, move records, you will not have any control over these auto-incremented values and they may reset creating havoc with child-relationships.

If you still prefer to use auto-increment field as invoice numbers here is sample code similar to clipper's syntax:
http://www.dbase.com/Knowledgebase/dbulletin/bu02_c.htm

Hope that helps.


Reinaldo.

[James Bott]

Horizon,

How Can I define autoincrement field in DBFCDX.

Define it with the type "+"

James