last_insert_id() in sybase

Shreyak has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: last_insert_id() in sybase by erix (Prior) on Jul 12, 2014 at 11:00 UTC
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. )	[reply] [d/l]
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]
Re: last_insert_id() in sybase by boftx (Deacon) on Jul 11, 2014 at 19:08 UTC
Did you also do a `$sth->execute` after your `prepare` statement? A `do` combines both of those into one operation. You must always remember that the primary goal is to *drain the swamp* even when you are hip-deep in alligators.	[reply] [d/l] [select]
Re^2: last_insert_id() in sybase by Shreyak (Novice) on Jul 12, 2014 at 03:59 UTC
Yes i did $sth->execute(@bindValues) after preparing the statement at start of the database connection and executing with bind variables whenever required in code ,and then did $sth->finish() just before disconnecting database. Also i used different statement handlers for insert and update statement.	[reply]
Re^3: last_insert_id() in sybase by boftx (Deacon) on Jul 12, 2014 at 04:28 UTC
Then all I can say at this point is what others have mentioned below, show us what you have that failed and what worked. :) You must always remember that the primary goal is to *drain the swamp* even when you are hip-deep in alligators.	[reply]
Re: last_insert_id() in sybase by thanos1983 (Parson) on Jul 12, 2014 at 02:25 UTC
Hello Shreyak, As everybody suggested, I think there might be a really small syntax mistake at your code. Provide us with a short script to take a closer look. By the way I found this piece of code: `$dbh->do('INSERT INTO a_table ...'); my $id = $dbh->last_insert_id(undef, undef, qw(a_table a_table_id)) or die "no insert id?";` [download] Taken from MySQL Perl DBI last_insert_id. Take a look, maybe you will see a difference on your syntax. Update: Forgot to mention that also `SELECT LAST_INSERT_ID()` the MySQL query will also do the job. You can read more about it here LAST_INSERT_ID(), LAST_INSERT_ID(expr). Hope this helps. Seeking for Perl wisdom...on the process...not there...yet!	[reply] [d/l] [select]
Re^2: last_insert_id() in sybase by Shreyak (Novice) on Jul 12, 2014 at 04:47 UTC
This a test script in sybase database using perl DBI module my $dbh = DBI->connect( "dbi:Sybase:server=$dbServer;hostname=$hostname;database=cle +aringdb", $dbUser, $dbPasswd->[1],{AutoCommit => 1}); my $insertStatement = $dbh->prepare("INSERT INTO BatchFile (FileName,F +ileStatusCode,FileDate,FileFormatCode,TotalRecords,LoadStartTime,Modi +fiedDate,ModifiedBy) VALUES ('Akash',4,GetDate(),0,3,GetDate(),GetDat +e(),'FIX2MQ')") or $DBI::err and die($DBI::errstr); my $id = $dbh->last_insert_id(undef,undef,undef,undef) or die "no inse +rt id $DBI::errstr $!"; print "ID : ",$id; my $updateStatement = $dbh->prepare("UPDATE BatchFile SET FileStatusC +ode = ? ,ModifiedDate = GETDATE(),TotalRecords = ? WHERE BatchFileID += ?"); $updateStatement->execute(6,1,$id) or die($DBI::errstr); $insertStatement->finish(); $updateStatement->finish(); my $rc = $dbh->disconnect or die($DBI::errstr); [download]	[reply] [d/l]
Re^3: last_insert_id() in sybase by boftx (Deacon) on Jul 12, 2014 at 05:24 UTC
It would appear that you do not execute the insert statement after preparing it. For what it is worth, I prefer using a `do` statement for both insert and update unless I will be doing several at one time in a loop. You must always remember that the primary goal is to *drain the swamp* even when you are hip-deep in alligators.	[reply] [d/l]
Re^4: last_insert_id() in sybase by Shreyak (Novice) on Jul 12, 2014 at 07:01 UTC
Re^5: last_insert_id() in sybase by boftx (Deacon) on Jul 12, 2014 at 07:07 UTC
Re^2: last_insert_id() in sybase by Shreyak (Novice) on Jul 12, 2014 at 05:24 UTC
i also used `SELECT @@IDENTITY` but no luck same error.	[reply] [d/l]
Re^3: last_insert_id() in sybase by Shreyak (Novice) on Jul 12, 2014 at 07:45 UTC
sorry for not writing execute there . I've created test scenario where I'm facing problem as shown below.I was able to resolve the problem by using do statement rather than prepare statement but could not figure out the reason behind it. my $dbh = DBI->connect( "dbi:Sybase:server=$dbServer;hostname=$hostnam +e; database=$ +database",$dbUser, $dbPasswd->[1],{AutoCommit => 1}); my $insertStatement = $dbh->prepare("INSERT INTO BatchFile (FileName,F +ileStatusCode,FileDate,FileFormatCode,TotalRecords,LoadStartTime,Modi + +fiedDate,ModifiedBy) VALUES ('Akash',4,GetDate(),0,3,GetDate(),GetD +at +e(),'FIX2MQ')") or $DBI::err and die($DBI::errstr); my $updateStatement = $dbh->prepare("UPDATE BatchFile SET FileStatusC ++ode = ? ,ModifiedDate = GETDATE(),TotalRecords = ? WHERE BatchFileID + += ?"); for(my $x=0;$x < 5;$x++){ $insertStatement->execute() or die($DBI::errstr); my $id = $dbh->last_ +insert_id(undef,undef,undef,undef) or die "no inse +rt id $DBI::errst +r $!"; print "ID : ",$id; update1($id); } sub update1{ my $id = shift; $updateStatement->execute(6,1,$id) or die($DBI::errstr +); } $insertStatement->finish(); $updateStatement->finish(); my $rc = $dbh- +>disconnect or die($DBI::errstr); [download]	[reply] [d/l]
Re^4: last_insert_id() in sybase by poj (Abbot) on Jul 12, 2014 at 08:44 UTC
Re: last_insert_id() in sybase by Anonymous Monk on Jul 11, 2014 at 23:54 UTC
Maybe you didn't commit or some such :) https://metacpan.org/pod/DBD::Sybase#Behavior-of-dbh-last_insert_id seems fairly straightforward to use	[reply]
Re: last_insert_id() in sybase by sundialsvc4 (Abbot) on Jul 12, 2014 at 00:41 UTC
Please show a short, sanitized, excerpt of your actual code. Show us how you attempt to fetch the ID, and also what (if anything) you do get in return. (You are, naturally, entirely certain that the insert did succeed?)	[reply]


Keep It Simple, Stupid
	PerlMonks