Re: last_insert_id() in sybase

DBI says:

For some drivers the value may only be available if placeholders have not been used (e.g., Sybase, MS SQL). In this case the value returned would be from the last non-placeholder insert statement.

... and DBD::Sybase repeats:

last_insert_id() ignores any parameters passed to it, and will NOT return the last @@identity value generated in the case where placeholders were used, or where the insert was encapsulated in a stored procedure.

So apparently, for DBD::Sybase it's just not possible in combination with placeholders. Alas, I have no sybase instance to try it out.

You could give it a try without placeholders to make sure.

(FWIW, I work mostly with postgres, and its INSERT statement has the excellent RETURNING option, which makes it return the inserted row(s), or some columns thereof. But alas, that INSERT RETURNING option is a postgres addition, not found in other RDBMS (or SQL Standards)

$ psql
psql (9.5devel_HEAD_20140710_2144_59efda3e50ca)
Type "help" for help.

testdb=# create table t(id integer);
CREATE TABLE

testdb=# insert into t values ( 42  ) returning *;
 id
----
 42
(1 row)

INSERT 0 1
[download]

Very handy. )

Comment on Re: last_insert_id() in sybase Download Code

Replies are listed 'Best First'.
Re^2: last_insert_id() in sybase by roboticus (Chancellor) on Jul 12, 2014 at 11:34 UTC
If I recall correctly, when I used Sybase (about 12 years ago) I used to do it like this: `my $SQL = <<EOSQL; begin insert into foo (col1, uniquecol) values (?, ?); select id from foo where uniquecol=?; end EOSQL my $ST = $DB->prepare($SQL); $ST->execute('abacus', 'S/N5842', 'S/N5842'); while (my $hr = $ST->fetchrow_hasharray) { print "ID is $$hr{ID}\n"; }` [download] Since it's executed in a transaction, there isn't a race condition. However, you do need to be able to uniquely identify the row without using it's ID--by using a unique (group of) column(s). ...roboticus When your only tool is a hammer, all problems look like your thumb.	[reply] [d/l]
Re^2: last_insert_id() in sybase by mpeppler (Vicar) on Jul 23, 2014 at 19:21 UTC
Indeed. To explain a bit further - the @@identity value is local to the execution context in Sybase ASE. In this case an execution context is the current "batch" (i.e. set of raw SQL statements to be executed as a single operation), or the current SQL proc. In case of nested proc calls each proc gets its own copy of @@identity. When you insert data into a table using placeholders DBD::Sybase uses the Sybase ct_dynamic() API - this has the side effect of creating a temporary stored proc on the server, and then executing this proc however many times you call the execute() method. At one point Tim Bunce suggested doing a `select max()` [download] to get the most recent value from the table, but there is absolutely no guarantee that this would be your insert - in a high volume environment you could very well have several inserts from different clients that happen between the insert and the select. Michael	[reply] [d/l]


Problems? Is your data what you think it is?
	PerlMonks