Autoincrement in SB11

[Mirko]

 Posted by:  Mirko BONANNO 

Hi all,

We ware asked how we would like them to enhance the autoincrement
functionality in SB11 and particularly how to get back the value of the just
created primary key in order to insert children.

The solution provided today uses a @LASTINCVAL (I do not remember any more
how it was called exactly) to insert child records. How could this work for
example in a self-referencing relation? Ron asked if we wanted to add a
parameter to that function in order to specify the table name. IMHO this
would be a solution but not for all scenarios; how could we cope with a
multiple parent's relationship (like humans usually have) where a child
record has two parent records?

Well a 'state of the art' solution would "bind-back" the value! I imagine we
could do something like that:

insert into MyTable( MyPk, Col1, col2, ...

values( ;nMyReceivePk, :sCol1, :sCol2, ...

Note that I put a ';' instead of ':' (for the PK) so that the server would
know that I need the value back and that the autoincrement functionality
must be used. On the other hand the server would not use the autoincrement
if I would have used a ':' and would have behaved normally (= inserting the
value I'm passing). And maybe we could use that feature even for not null
with defaults columns.

Ok, assuming that I have no idea on the way inserts are implemented, I would
even appreciate another solution like...

Set nMyPkValue = SqlPrepareAndExecuteInsert( hSql, 'insert ......)

or maybe

Set bOk = bOk AND SqlPrepareAndExecuteInsert( hSql, nMyReceivePk, 'insert
......)

Any other suggestion ?

Mirko

[Mirko]

 Posted by:  Mirko BONANNO 

wow, it looks like nobody gives a ... about this "new fantastic" feature !

I do remember lot of people asking for autoincrement columns. Hey guys ! Are
you out-there ?

[Clifford W. Bass]

 Posted by:  Clifford W. Bass 

Hi Mirko,

Just been exploring/reading about this feature and I like that they
added the feature. Thank you Unify! And I think I like how they
implemented it. Including the ability to override the feature and specify
your own value, which will automatically reset the next value to use if
appropriate. It allows incrementing up or down. It allows incrementing in
amounts other than 1 (i.e. 2, 10, etc.). And it allows (re)setting of the
next value to use. Admittedly I long ago had to create my own work-around.
So to start with, it will probably only get used with new tables. But they
set it up so that it is possible to add the feature to an existing table, so
existing work-arounds can be migrated at some point. I have not tested that
yet.

The last value is gotten by the @AUTOINCREMENTLAST function(? -- ?
because it does not use parentheses) within the current transaction. It
could probably be improved by the ability to specify the table (i.e.
@AUTOINCREMENTLAST('CREATOR', 'TABLE'). To do your parents and children,
one would have to do something like this (in English):

begin transaction
insert father into parent table
get the last increment value with a select statement and store it in a
variable (select @AUTOINCREMENTLAST from dual into :myvariable)
insert mother into parent table
get the last increment value and store it in a variable
insert child 1 into child table using stored values
insert child 2 into child table using stored values
etc.
commit transaction

One parent and multiple children, as long as the child table does not
use the autoincrement feature are easier:

begin transaction
insert row into parent table
insert row 1 into child table using @AUTOINCREMENTLAST directly
insert row 2 into child table using @AUTOINCREMENTLAST directly
etc.
commit transaction

It is designed so that if someone else inserts a row into an
autoincrement table during your transaction, that it will not mess you
up--it will only give you the last value from your transaction. Note that
the "within the current transaction" is an important thing to know. Once
there is a commit, it loses the value. For examples and other information,
see the version 11 database administrator's and SQL language books.

Clifford Bass