Page 2 of 2
Re: ADO and Lock
Posted: Thu Aug 06, 2015 11:47 am
by Horizon
James Bott wrote:Horizon,
How Can I define autoincrement field in DBFCDX.
Define it with the type "+"
James
Thank you James. Is it possible to set a number to autoincrement field as a starting number?
Re: ADO and Lock
Posted: Thu Aug 06, 2015 3:13 pm
by James Bott
Hakan,
Is it possible to set a number to autoincrement field as a starting number?
I don't believe there is a command for that. You could try adding one less records than the starting number you want, then doing a table TRUNCATE command (or ZAP if you are using the ADORDD) and see if that works. I suspect it will just start at 1 again. Maybe you could delete all but the last record.
Keep in mind the things that Reinaldo mentioned previously in this thread about all the different issues with using autoincrement fields as IDs. The table can end up with new numbers in the field in several circumstances.
James
Update: Sorry, I was thinking you were asking about SQL instead of DBFCDX. I don't know the answer for DBFCDX as I just discovered there was an autoincrement fieldtype recently myself, so I have not worked with them.
Re: ADO and Lock
Posted: Thu Aug 06, 2015 6:04 pm
by Horizon
James Bott wrote:Hakan,
Is it possible to set a number to autoincrement field as a starting number?
I don't believe there is a command for that. You could try adding one less records than the starting number you want, then doing a table TRUNCATE command (or ZAP if you are using the ADORDD) and see if that works. I suspect it will just start at 1 again. Maybe you could delete all but the last record.
Keep in mind the things that Reinaldo mentioned previously in this thread about all the different issues with using autoincrement fields as IDs. The table can end up with new numbers in the field in several circumstances.
James
Update: Sorry, I was thinking you were asking about SQL instead of DBFCDX. I don't know the answer for DBFCDX as I just discovered there was an autoincrement fieldtype recently myself, so I have not worked with them.
Thank you James.
Re: ADO and Lock
Posted: Fri Aug 07, 2015 6:06 am
by Biel EA6DD
Code: Select all
DbFieldInfo( DBS_COUNTER, 1, 10) //Set autoincremt to 10
DbFieldInfo( DBS_STEP, 1, 4) //Set Step to 4
Re: ADO and Lock
Posted: Fri Aug 07, 2015 3:46 pm
by James Bott
Biel,
DbFieldInfo( DBS_COUNTER, 1, 10) //Set autoincremt to 10
I am guessing that sets the next number to be 10?
James
Re: ADO and Lock
Posted: Mon Aug 10, 2015 8:24 am
by Biel EA6DD
James Bott wrote:
I am guessing that sets the next number to be 10?
James
James,
that is, next number will be 10.
Re: ADO and Lock
Posted: Mon Aug 24, 2015 7:57 pm
by reinaldocrespo
Hello everyone;
As you see from my previous posts, I do not like auto-increment fields as unique invoice numbers and I have explained why. I've also explained how a unique index on the invoice number can easily and safely solve the problem.
On this post I offer some SQL code (ADS compatible) that relies on a RowVersion field type to accomplish the same. This is how I use it on my applications. The idea here is to keep a single sequences table where all sequences are kept. The table fields are "TableName", "FieldName", "Sequence" and "RowVersion". This last field is of type row version. Whenever a new sequences for any given field on a table is needed, simply update the sequence by adding 1 as long as the rowversion field hasn't changed, we can guarantee no one else has changed the field on a race condition.
Just like auto-inc fields rowversion fields are maintained by the SQL engine and incremented anytime a record is updated.
Here is the code which explains itself a lot better than words:
Code: Select all
//-------------------------------------------------
//
DECLARE @num INTEGER ;
DECLARE @rv INTEGER ;
DECLARE @numrows INTEGER ;
DECLARE @c CUROSR AS SELECT [sequence], [rowversion] FROM sequences WHERE table = :table AND field = :field;
@numrows = 0 ;
WHILE @numrows=0 DO
OPEN @c;
FETCH @c;
@rv = @c.rowversion ;
@num = @c.rowversion + 1 ;
CLOSE @c;
//if rowversion has changed then someone else has updated the record.
UPDATE Sequences SET [Sequence] = @num WHERE [rowversion] = @rv;
@numrows = ::stmt.UpdateCount ;
END;
SELECT @num FROM system.iota ;
//-------------------------------------------------
You might have to translate this code into the SQL flavor being used but you get the idea.
Reinaldo.
Re: ADO and Lock
Posted: Tue Aug 25, 2015 3:35 pm
by James Bott
I have a similar table that I use to track and generate new ID numbers that I use with DBFs. Since we can use both SQL tables and DBFs in the same app, I am thinking I can continue to use the same DBF which allows locking and thus is a simple solution.
I also have a database class that has auto-incrementing using the above table, so I can solve the SQL auto-increment issue too. I think I can implement both of these with the new ADORDD by simply making one change--adding the VIA clause to the sequencing DBF, so that it is uses the DBFCDX RDD.
James
Re: ADO and Lock
Posted: Wed Aug 26, 2015 8:49 am
by AHF
James,
With adordd with SET ADO FORCE LOCK ON no other user or other app (using clipper compatible locking) can alter the table lock or exclusive or record locked hold by you.
With this set ON the FILE LOCK, RECORD LOCK, EXCLUSIVE USE are guaranteed.
Re: ADO and Lock
Posted: Wed Aug 26, 2015 2:21 pm
by James Bott
Antonio F.
Thanks for confirming that. I have been meaning to ask about it.
So, it seems, that my original auto-incrementing system should still work with the ADORDD. I will do some testing to confirm it.
Using the ADORDD is too easy, where's the challenge?
Keep working your magic, Antonio.
Regards,
James
Re: ADO and Lock
Posted: Wed Aug 26, 2015 3:58 pm
by TimStone
Interesting.
I use a single file on the server that contains all unique counters ( ie. invoice numbers, client account numbers, etc ). When someone wants to create a new invoice, a very simple call grabs the last number used, increments it, saves it, and gives it to the client machine. This takes a fraction of a second.
Over all the years I've used the system, with probably millions of invoices generated on systems from1 to 20 users, I've never had a single duplicate number generated.
Tim
Re: ADO and Lock
Posted: Thu Aug 27, 2015 9:51 am
by Jack
In a Sql concept,you will do :
oRs:Open("select ID FROM TABLE1",oCon)
wid:=oRs:Fields("ID"):Value
wid:=wid+1
UPDATE TABLE1 SET ID=wid
*
How can you lock, how are you sure that this code is not execute at the same time from another computer using the same application .
=======
In DBF concept , you can open the TABLE1 in EXCLUSIVE and nobody else can modify the record .
Any idea ?
Thanks
Re: ADO and Lock
Posted: Thu Aug 27, 2015 11:03 am
by AHF
Just some ideas.
I would prefer the 2nd one.
Another alternative in MySql is SELECT ... FOR UPDATE this locks the records.
Code: Select all
oRs:Open("select ID FROM TABLE1",oCon)
wid:=oRs:Fields("ID"):Value +1
oRs:Fields("ID"):Value :=wid
try
oRs:update //if it has been changed in underlying data by others it will fail
catch
if oRs:Fields("ID"):Value <> wid
//update fail
endif
end
Code: Select all
oRs:Open("select ID FROM TABLE1",oCon)
wid:=oRs:Fields("ID"):Value+1
UPDATE TABLE1 SET ID=widwhere ID = wid -1
oRs:Resync //in this table no one can delete records otherwise this might fail
if oRs:Fields("ID"):Value <> wid
//update fail
endif