ADO and Lock

[Horizon]

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?

[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.

[Horizon]

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.

[Biel EA6DD]

Code: Select all  Expand view

DbFieldInfo( DBS_COUNTER, 1, 10) //Set autoincremt to 10
DbFieldInfo( DBS_STEP, 1, 4) //Set Step to 4

[James Bott]

Biel,

DbFieldInfo( DBS_COUNTER, 1, 10) //Set autoincremt to 10

I am guessing that sets the next number to be 10?

James

[Biel EA6DD]

I am guessing that sets the next number to be 10?

James

James,
that is, next number will be 10.

[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  Expand view

//-------------------------------------------------
//
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.

[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

[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.

[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

[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

[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

[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  Expand view

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  Expand view

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