in reply to last_insert_id() in sybase

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!

Replies are listed 'Best First'.
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]

      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]

        sorry for not writing execute there . I've created test scenario where I'm facing problem as shown below.

        my $dbh = DBI->connect(
          "dbi:Sybase:server=$dbServer;hostname=$hostname;  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(),GetDat
+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::errstr $!";
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^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]

      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]
        From DBD::Sybase 
        The last_insert_id() call is simply a wrapper around a "select @@ident
+ity" query. To be successful (i.e. to return the correct value) this 
+must be executed on the same connection as the INSERT that generated 
+the new IDENTITY value. Therefore the statement handle that was used 
+to perform the insert must have been closed/freed before last_insert_
+id() can be called. Otherwise last_insert_id() will be forced to open
+ a different connection to perform the query, and will return an inva
+lid value (usually in this case it will return 0).

        [download]
        Try adding ->finish() 
        for (my $x=0;$x < 5;$x++){ 
  $insertStatement->execute() or die($DBI::errstr); 
  $insertStatement->finish();
  my $id = $dbh->last_insert_id(undef,undef,undef,undef) 
           or die "no insert id $DBI::errstr $!"; 
  print "ID :",$id; 
  update1($id); 
}

        [download]
        poj
[reply]
[d/l]
[select]

In Section Seekers of Perl Wisdom