1

I've recently taken over support of a system which uses Advantage Database Server as its back end. For some background, I have years of database experience but have never used ADS until now, so my question is purely about how to implement a standard pattern in this specific DBMS.

There's a stored procedure which has been previously developed which manages an ID column in this manner:

@ID = (SELECT ISNULL(MAX(ID), 0) FROM ExampleTable);
@ID = @ID + 1;

INSERT INTO Example_Table (ID, OtherStuff)
VALUES (@ID, 'Things');

--Do some other stuff.

UPDATE ExampleTable
SET AnotherColumn = 'FOO'
WHERE ID = @ID;

My problem is that I now need to run this stored procedure multiple times in parallel. As you can imagine, when I do this, the same ID value is getting grabbed multiple times.

What I need is a way to consistently create a unique value which I can be sure will be unique even if I run the stored procedure multiple times at the same moment. In SQL Server I could create an IDENTITY column called ID, and then do the following:

INSERT INTO ExampleTable (OtherStuff)
VALUES ('Things');

SET @ID = SCOPE_IDENTITY();

ADS has autoinc which seems similar, but I can't find anything conclusively telling me how to return the value of the newly created value in a way that I can be 100% sure will be correct under concurrent usage. The ADS Developer's Guide actually warns me against using autoinc, and the online help files offer functions which seem to retrieve the last generated autoinc ID (which isn't what I want - I want the one created by the previous statement, not the last one created across all sessions). The help files also list these functions with a caveat that they might not work correctly in situations involving concurrency.

How can I implement this in ADS? Should I use autoinc, some other built-in method that I'm unaware of, or do I genuinely need to do as the developer's guide suggests, and generate my unique identifiers before trying to insert into the table in the first place? If I should use autoinc, how can I obtain the value that has just been inserted into the table?

Jo Douglass
  • 2,055
  • 1
  • 19
  • 30

2 Answers2

2

You use LastAutoInc(STATEMENT) with autoinc.

From the documentation (under Advantage SQL->Supported SQL Grammar->Supported Scalar Functions->Miscellaneous):

LASTAUTOINC(CONNECTION|STATEMENT)

Returns the last used autoinc value from an insert or append. Specifying CONNECTION will return the last used value for the entire connection. Specifying STATEMENT returns the last used value for only the current SQL statement. If no autoinc value has been updated yet, a NULL value is returned.

Note: Triggers that operate on tables with autoinc fields may affect the last autoinc value.

Note: SQL script triggers run on their own SQL statement. Therefore, calling LASTAUTOINC(STATEMENT) inside a SQL script trigger would return the lastautoinc value used by the trigger's SQL statement, not the original SQL statement which caused the trigger to fire. To obtain the last original SQL statement's lastautoinc value, use LASTAUTOINC(CONNECTION) instead.

Example: SELECT LASTAUTOINC(STATEMENT) FROM System.Iota

Ken White
  • 123,280
  • 14
  • 225
  • 444
  • Thanks very much for the quick response! I suspect the information I found about autoinc and LastAutoInc was out of date. I had also run across usage of LastAutoInc with CONNECTION and STATEMENT but without any information about whether those would resolve the concurrency issue. LastAutoInc(STATEMENT) now sounds like exactly what I'm after. – Jo Douglass Aug 25 '16 at 13:13
0

Another option is to use GUIDs.

(I wasn't sure but you may have already been alluding to this when you say "or do I genuinely need to do as the developer's guide suggests, and generate my unique identifiers before trying to insert into the table in the first place." - apologies if so, but still this info might be useful for others :) )

The use of GUIDs as a surrogate key allows either the application or the database to create a unique identifier, with a guarantee of no clashes.

Advantage 12 has built-in support for a GUID datatype:

GUID and 64-bit Integer Field Types

Advantage server and clients now support GUID and Long Integer (64-bit) data types in all table formats. The 64-bit integer type can be used to store integer values between -9,223,372,036,854,775,807 and 9,223,372,036,854,775,807 with no loss of precision. The GUID (Global Unique Identifier) field type is a 16-byte data structure. A new scalar function NewID() is available in the expression engine and SQL engine to generate new GUID. See ADT Field Types and Specifications and DBF Field Types and Specifications for more information.

http://scn.sap.com/docs/DOC-68484

For earlier versions, you could store the GUIDs as a char(36). (Think about your performance requirements here of course.) You will then need to do some conversion back and forth in your application layer between GUIDs and strings. If you're using some intermediary data access layer, e.g. NHibernate or Entity Framework, you should be able to at least localise the conversions to one place.

If some part of your logic is in a stored procedure, you should be able to use the newid() or newidstring() function, depending on the type of the backing column:

INSERT INTO Example_Table (newid(), OtherStuff)
Community
  • 1
  • 1
ngm
  • 7,277
  • 1
  • 53
  • 62