RDBMS NextID

[wgcs]

How do you generate a primary key on an RDBMS (Sql Server or MySql) other than using an "autoincrement field".

I'm trying to write code so that it will function using either .DBF's or with an RDBMS backend... however, it seems they have complete opposite "best ways" of generating PK's.

(I know VFP8 (or is it the up-and-coming 9?) has auto-inc fields built in, but I don't have it...)

in VFP DBF's a good way is to keep a table just for tracking the next ID, and do this to get an ID:
[ol][li]Lock that NextID table,
[li]increment the record for the DataTable having a record added,
[li]Unlock that NextID table
[li]Insert the new record, with the good PK value, into the dataTable[/ol]

However, with an RDBMS, it goes something like:
[ol][li]Insert the new record into the DataTable
[li]Query the DataTable for that new record
[li]One field on that query is the auto-filled PK (is there a way, other than with Stored Procedures, to get the new PK value back at the same time as inserting the record?)[/ol]

Essentially, the problem is: When do you get the PK value, and where do you pass it as a parameter? (to be compatible, code-structure-wise, with either)

 

[jimoo]

I have a makenewkey method I have used for a long time and it seems to work fine. You are welcome to use it. It uses the SYS(2015) function. This returns a unique character name, but there is no guarantee that other users will create-one at the exact same time in a multi-user environment. Therefore, I remove the underscore (always the first character) and replace it with a random A thru Z. This reduces the risk of error. I have used this simple snippet of code many times with great success.




* the SYS(2015) creates a unique 10 character name.
* it always begins with an underscore. Therefore, I am randomly
* picking a number between 1 to 26 and converting the leading underscoret to a
* character. Since 2015 is random, this may be overkill, but programmers are
* paranoid and this provides a greater comfort level.

RETURN CHR(INT((26 - 1 + 1) * RAND( ) + 1)+64) + RIGHT(SYS(2015),9)

Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[badukist]

I use Firebird and it has a nice thing named generator.
I only need to execute SELECT GEN_ID('key_name',1) and get the next value of a primary key.
But, because I need to work disconnected (insert records directly in remote views) and I need the key for one-to-many relations, doesn't work so well.
So I use something like RIGHT(SYS(2015),9)+ WS_ID where WS_ID is a combination of 2 or 3 letters, saved in a local configuration table or windows registry. I only need to check if this value exists and, if not, generate one from the number_of_workstations+1 retrieved from server.
And I can use the key value as a timestamp

 

[Mike Lewis]

Wgcs,

In SQL Server, you can use @@IDENTITY to find the PK of the last record that you inserted. I'm not sure if that will meet your requirement (it is more typically used when you need to store the value as a foreign key in a child record), but might be worth your while to check further.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[georg59]

I'm using the following code since some years, and it works very fine for me, perhaps it helps you. "Id" is a text field I use in every table. This function is called after every "append blank" statement.

*--- Automated id-counter 06.09.00 GN
*--- 20.11.2001 len for Id variabel
FUNCTION Auto_id
LOCAL nTemp, cTemp, nLenId
SET DELETED OFF
cSelect=ALIAS()
SELECT Id FROM ALIAS() INTO CURSOR cId
CALCULATE MAX(val(id)) ALL TO nTemp
nLenId=LEN(id)
USE IN cId
SET DELETED ON
cTemp=ALLT(STR(nTemp+1))
cTemp=PADL(cTemp,nLenId,'0')
SELECT &cSelect
RETURN cTemp
ENDFUNC

 

[wgcs]

up to now I've used something similar to Georg59's, though somewhat longer and including table locking to prevent multiple users from getting the same PK at the same time.

For performance, I had be switching all my tables from using text PK's (like SSN, or something), to using Integer (Field Type I) PK's that are completely arbitrary.... I had overlooked the possibility of generating unique strings (though the MS GUID code briefly held my interest: Isn't using long character PK's pretty bad for performance? or is it insignificant?)

I think I'm finding my first real need for a meta-sql language.... I'll implement something in MyDataClass.SqlExec( cSql, cCursorName, P1, P2, P3, P4 ) that can handle:

  MyDataClass.SqlExec( 'INSERT INTO myTable ( RecID, Name ) ';
   +' VALUES ( ?PK(myTable), ?P1 )', 'OutCursor', 'new name' )

Something like that. but where does the resulting PK value get passed out? I guess, in an INSERT call, the result will only ever be 0 or 1, so maybe the result will be either 0 or the PK value.


Mike: Thanks: So @@IDENTITY gets the last added record's new PK; Is this variable only available inside a stored procedure, or can it be included something like this:

nConn = GetSqlServerConnection()
nRes = SqlExec( nConn, 'INSERT INTO MyTable ( AutoIncPrimaryKey, Name ) VALUES ( [what do you put here?? or do you leave the AutoIncPrimaryKey out of the INSERT field list?], "New Name" ); ' + chr(13) ;
+' RETURN @@IDENTITY; ', 'OutTable' )

I don't know what the SQLServer version of "RETURN" is, but I think you'll understand.... Essentially, can two T-SQL commands be sent in a single SqlExec so that @@IDENTITY actually means something...

 

[craigsboyd]

I believe the performance difference would be next to nothing even in worse-case scenario. If you don't want to use auto increment field (with good reason, i agree) then continue using string... check out NEWID()

Slighthaze = NULL

[ul][li]FAQ184-2483
An excellent guide to getting a fast and accurate response to your questions in this forum.[/li][/ul]

 

[wgcs]

( See also thread184-652952 for a more general discussion of RDBMS switching )

 

[Mike Lewis]

Wgcs,

I don't know what the SQLServer version of "RETURN" is

SQL Server does have a RETURN statement, but it is pretty useless as far as VFP is concerned. iT return a numeric value from a stored procedure, but there is no way of passing that back to VFP.

A more usuual way of doing this would be to call a stored procedure that has an 'output parameter' (which is nothing more or less than a parameter passed by reference). The stored proc can store @@IDENTITY (or any other value) in that parameter, where it becomes available to the calling program.

can two T-SQL commands be sent in a single SqlExec so that @@IDENTITY actually means something...[

Yes, that should work as well. Something like this:

lcCmd = &quot;INSERT INTO MyTable <whatever> ; SELECT @@IDENTITY&quot;
SQLEXEC(lnConnHandle, lcCmd)

After the above sequence, you should have a cursor with one record and one field, containing the required value.

(Note the semi-colon is optional in the above code.)

One other thing you might like to know about is my SQLSend class. It is probably nothing like as sophisticated as your own data class is (or will be). In fact, it does little more than some essential SQL pass-through house-keeping, but it might give you some ideas. You can find it at:

http://www.ml-consult.demon.co.uk/foxst-25.htm

Mike



Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[wgcs]

Thanks, Mike: I had thought about the need for this, but hadn't written the code yet since I'm still developing using VFP tables. When I write my own connection management part of the DataAccess class, seeing how you've done it will make things alot simpler!

(The project I'm working on is going to be the platform for all our future applications, but the first application to use it doesn't need the RDBMS capability, So I'm researching and fleshing out support for it, but not relying on it yet.

I'm also working on a couple pages at the wiki

http://fox.wikis.com/wc.dll?Wiki~RdbmsAgnostic

http://fox.wikis.com/wc.dll?Wiki~sql

that attempt to summarize what I learn.

Thanks everybody! )