topic-30923

Posts: 300

Joined: Wed Jul 11, 2007 11:06 AM

ADO and Lock
Posted: Thu Jun 18, 2015 06:30 AM

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

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: ADO and Lock
Posted: Thu Jun 18, 2015 08:03 AM

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

Regards

G. N. Rao.

Hyderabad, India

Jack

Posts: 300

Joined: Wed Jul 11, 2007 11:06 AM

Re: ADO and Lock
Posted: Thu Jun 18, 2015 08:17 AM

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

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: ADO and Lock
Posted: Thu Jun 18, 2015 08:26 AM

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

Regards

G. N. Rao.

Hyderabad, India

avista

Posts: 301

Joined: Fri Jun 01, 2007 09:07 AM

Re: ADO and Lock
Posted: Thu Jun 18, 2015 09:00 AM

Hi,

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

Best regards,

Rick Lipkin

Posts: 2706

Joined: Fri Oct 07, 2005 01:50 PM

Re: ADO and Lock
Posted: Thu Jun 18, 2015 12:55 PM

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

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: ADO and Lock
Posted: Thu Jun 18, 2015 02:55 PM

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

Posts: 300

Joined: Wed Jul 11, 2007 11:06 AM

Re: ADO and Lock
Posted: Thu Jun 18, 2015 06:53 PM

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

Posts: 990

Joined: Thu Nov 17, 2005 05:49 PM

Re: ADO and Lock
Posted: Thu Jun 18, 2015 09:09 PM

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

Posts: 300

Joined: Wed Jul 11, 2007 11:06 AM

Re: ADO and Lock
Posted: Tue Aug 04, 2015 06:33 PM

I am back with this problem .

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

Thanks

Maurizio

Posts: 842

Joined: Mon Oct 10, 2005 01:29 PM

Re: ADO and Lock
Posted: Wed Aug 05, 2015 06:34 AM

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

Maurizio
www.nipeservice.com

Horizon

Posts: 1387

Joined: Fri May 23, 2008 01:33 PM

Re: ADO and Lock
Posted: Wed Aug 05, 2015 07:07 AM

nageswaragunupudi wrote: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.

Regards,

Hakan ONEMLI

Harbour & MSVC 2022 & FWH 23.06

russimicro

Posts: 410

Joined: Sun Jan 31, 2010 03:30 PM

Re: ADO and Lock
Posted: Wed Aug 05, 2015 01:30 PM

Code (fw): Select all Collapse

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

Posts: 990

Joined: Thu Nov 17, 2005 05:49 PM

Re: ADO and Lock
Posted: Wed Aug 05, 2015 02:11 PM

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

Posts: 4840

Joined: Fri Nov 18, 2005 04:52 PM

Re: ADO and Lock
Posted: Wed Aug 05, 2015 02:53 PM

Horizon,

How Can I define autoincrement field in DBFCDX.

Define it with the type "+"

James

FWH 18.05/xHarbour 1.2.3/BCC7/Windows 10

FiveTech Support Forums