Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw
 
PerlMonks  

last_insert_id() in sybase

by Shreyak (Novice)
on Jul 11, 2014 at 19:04 UTC ( [id://1093289]=perlquestion: print w/replies, xml ) Need Help??

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

i am not able to get value in last_insert_id() after insert operation in sybase database. I had to use do statement instead of prepare statement for both insert and update operations to get value from last_insert_id(). Does anyone know the reason of this behavior?

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

    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"; }

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

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.

      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.
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?";

    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!

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

        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);
Re: last_insert_id() in sybase
by Anonymous Monk on Jul 11, 2014 at 23:54 UTC
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?)

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others cooling their heels in the Monastery: (3)
As of 2024-04-16 20:18 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found