Re^2: last_insert_id() in sybase

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]

Comment on Re^2: last_insert_id() in sybase Download Code

Replies are listed 'Best First'.
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
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^5: last_insert_id() in sybase by boftx (Deacon) on Jul 12, 2014 at 07:07 UTC
What are the error messages you get when you execute that script? And what is the code using `do` that works? You must always remember that the primary goal is to *drain the swamp* even when you are hip-deep in alligators.	[reply] [d/l]