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. )

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.

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

Did you also do a $sth->execute after your prepare statement? A do combines both of those into one operation.

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.

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.

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.

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.

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]

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.

i also used SELECT @@IDENTITY but no luck same error.

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]

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?)