Thank you James. Is it possible to set a number to autoincrement field as a starting number?James Bott wrote:Horizon,
Define it with the type "+"How Can I define autoincrement field in DBFCDX.
James
ADO and Lock
Re: ADO and Lock
Regards,
Hakan ONEMLI
Harbour & VS 2019 & FWH 20.12
Hakan ONEMLI
Harbour & VS 2019 & FWH 20.12
- James Bott
- Posts: 4654
- Joined: Fri Nov 18, 2005 4:52 pm
- Location: San Diego, California, USA
- Contact:
Re: ADO and Lock
Hakan,
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.
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.Is it possible to set a number to autoincrement field as a starting number?
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
Thank you James.James Bott wrote:Hakan,
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.Is it possible to set a number to autoincrement field as a starting number?
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.
Regards,
Hakan ONEMLI
Harbour & VS 2019 & FWH 20.12
Hakan ONEMLI
Harbour & VS 2019 & FWH 20.12
- Biel EA6DD
- Posts: 680
- Joined: Tue Feb 14, 2006 9:48 am
- Location: Mallorca
- Contact:
Re: ADO and Lock
Code: Select all
DbFieldInfo( DBS_COUNTER, 1, 10) //Set autoincremt to 10
DbFieldInfo( DBS_STEP, 1, 4) //Set Step to 4
- James Bott
- Posts: 4654
- Joined: Fri Nov 18, 2005 4:52 pm
- Location: San Diego, California, USA
- Contact:
Re: ADO and Lock
Biel,
James
I am guessing that sets the next number to be 10?DbFieldInfo( DBS_COUNTER, 1, 10) //Set autoincremt to 10
James
- Biel EA6DD
- Posts: 680
- Joined: Tue Feb 14, 2006 9:48 am
- Location: Mallorca
- Contact:
Re: ADO and Lock
James,James Bott wrote: I am guessing that sets the next number to be 10?
James
that is, next number will be 10.
- reinaldocrespo
- Posts: 918
- Joined: Thu Nov 17, 2005 5:49 pm
- Location: Fort Lauderdale, FL
Re: ADO and Lock
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:
You might have to translate this code into the SQL flavor being used but you get the idea.
Reinaldo.
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 ;
//-------------------------------------------------
Reinaldo.
- James Bott
- Posts: 4654
- Joined: Fri Nov 18, 2005 4:52 pm
- Location: San Diego, California, USA
- Contact:
Re: ADO and Lock
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
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
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.
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.
Regards
Antonio H Ferreira
Antonio H Ferreira
- James Bott
- Posts: 4654
- Joined: Fri Nov 18, 2005 4:52 pm
- Location: San Diego, California, USA
- Contact:
Re: ADO and Lock
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
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
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
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
Tim Stone
http://www.MasterLinkSoftware.com
timstone@masterlinksoftware.com
Using: FWH 19.06 with Harbour 3.2.0 / Microsoft Visual Studio Community 2019
http://www.MasterLinkSoftware.com
timstone@masterlinksoftware.com
Using: FWH 19.06 with Harbour 3.2.0 / Microsoft Visual Studio Community 2019
Re: ADO and Lock
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
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
Just some ideas.
I would prefer the 2nd one.
Another alternative in MySql is SELECT ... FOR UPDATE this locks the records.
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
Regards
Antonio H Ferreira
Antonio H Ferreira