Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

Re^3: last_insert_id() in sybase

by Shreyak (Novice)
on Jul 12, 2014 at 07:45 UTC ( [id://1093348]=note: print w/replies, xml ) Need Help??


in reply to Re^2: last_insert_id() in sybase
in thread last_insert_id() in sybase

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

Replies are listed 'Best First'.
Re^4: last_insert_id() in sybase
by poj (Abbot) on Jul 12, 2014 at 08:44 UTC
    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).
    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); }
    poj

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://1093348]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others admiring the Monastery: (7)
As of 2024-03-28 11:34 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found