|
A solution for SA 11 : 1) Create a DUMMY Table with a NOT NULL UNSIGNED (BIG)INT DEFAULT AUTOINCREMENT column. 2) To get the next key use the Get_Identity() function with the name of the DUMMY table as first parameter. 3) The value of the increment is visible in the max_identity column from the SYSTABCOL system view. If you have to unload / reload the database be carreful to reset the value with the good number. You can use the sa_reset_identity() procedure to do this job. 1
This is an interesting solution - so to simulate a "default shared autoincrement", one might create a before insert trigger for those tables that are to share the same autoincrement values, and in these triggers call get_identity() on the dummy table to reserve and store the next value. I haven't tested this, but as get_identity() was introduced in 8.0.1, it should work in this version and above...
(20 Jul '11, 16:54)
Volker Barth
We use this solution since 3 years with 2 SA 9 databases and a syntax very similar to Sequence. We don't use before insert trigger... If we need to know the key after the insert, we write a function / procedure that first call get_identity(), second execute the insert statement and third return the key if success and 0 in other case. If we don't need to know the key we embed the get_identity() call directly in the insert statement.
(21 Jul '11, 12:18)
Costa
Replies hidden
I really would like to accept more than 1 answer!
(22 Jul '11, 03:08)
Martin
Replies hidden
Well, I had suggested the trigger based approach as an alternative for a DEFAULT value - so you would just omit the column when inserting rows (as one usually would do with a DEFAULT AUTOINCREMENT). Then users of this table would not need to make the get_identity() call (and might even not need to know about this "hand-made sequence" solution). But if there's no need to "hide" this approach, then your way to use get_identity() is more obvious and possibly easier:) For the LOAD/UNLOAD problem, one could also use a DatabaseStart event to select the maximum value of the "shared" columns and insert this into the dummy table. That should retain the maximum value when unloading the database and will omit the need for the sa_reset_identity() call.
(22 Jul '11, 03:28)
Volker Barth
I agree - feel free to make a SQLA suggestion:)
(22 Jul '11, 03:30)
Volker Barth
|
Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.
Follow this question
By Email:Once you sign in you will be able to subscribe for any updates here
By RSS:Markdown Basics
- *italic* or _italic_
- **bold** or __bold__
- link:[text](http://url.com/ "title")
- image?
- numbered list: 1. Foo 2. Bar
- to add a line break simply add two spaces to where you would like the new line to be.
- basic HTML tags are also supported
Question tags:
question asked: 13 Jul '11, 02:51
question was seen: 3,366 times
last updated: 22 Jul '11, 03:30
Which version of ASA is in use ?