Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

last_insert_id did not work

by bizactuator (Sexton)
on Mar 27, 2021 at 07:17 UTC ( [id://11130432]=perlquestion: print w/replies, xml ) Need Help??

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

I have code that inserts data into a table, after someone orders something.

I needed to get the last inserted id, to store in another table, for if they refund it later.

here is my code for it:
$sth = $dbh->prepare(qq{INSERT INTO `coacttran` SET $_pstmt"}); $sth->execute(@_placeholders); $sth->fetchrow_hashref(); $sth->finish(); $_chinsert = $sth->last_insert_id;

I tested it with a real transaction and this error happened:

Can't locate object method "last_insert_id" via package "DBI::st" at / +path/to/file... line 256.

Line 256 is the last_insert_id I showed above.

So that did not work. someone on here suggested it be done that way instead of with $dbh->do...

Can you tell me why it gave me this error?

Thanks,
-Richard

Replies are listed 'Best First'.
Re: last_insert_id did not work
by haukex (Archbishop) on Mar 27, 2021 at 07:36 UTC

    Corion is right that the method should be called on $dbh (though in my test it worked on $sth too, so I'm not sure that's the problem here). Note the documentation: "The last_insert_id method was added in DBI 1.38." - what version do you have?

    use warnings; use strict; use feature 'say'; use DBI; my $dbh = DBI->connect( "DBI:mysql:database=testing;host=127.0.0.1", $ENV{USER}, 'barfoo', { RaiseError => 1, AutoCommit => 1 }); $dbh->do('DROP TABLE IF EXISTS coacttran'); $dbh->do(<<'ENDSQL'); CREATE TABLE coacttran ( id INT PRIMARY KEY AUTO_INCREMENT, foo VARCHAR(256) ); ENDSQL for my $i (1..10) { my $req = qq{ INSERT INTO `coacttran` SET foo=? }; my $sth = $dbh->prepare($req); my $row = $sth->execute("bar$i"); say $dbh->last_insert_id; }

    I spun up the test database with Docker the same way I showed here.

    Also note that I don't know how you're generating $_pstmt, but you really should use placeholders! See Bobby Tables.

      I am using placeholders like this:
      $_pstmt .= ',' if $_pstmt; $_pstmt .= '`fsb_details` = ?'; push(@placeholder,"$_fsbdotnot");
      then I put $_pstmt for the places ???
      then the @placeholder for the things to put on it...

        Ok, that's good to know, and the code you've shown so far is fine, so if you're doing it that way everywhere it's good. Anytime you interpolate strings into SQL is a place for potential quoting and similar issues, which is why I made that comment. Note that there are also modules to help you generate SQL, like SQL::Abstract.

Re: last_insert_id did not work
by Corion (Patriarch) on Mar 27, 2021 at 07:30 UTC

    ->last_insert_id is a method of the database handle, not of the statement handle (see last_insert_id). So you want to call it on the database handle:

    $dbh->last_insert_id()
      It depends on the driver. DBD::MariaDB supports is as a method of the statement handle, too. I've recently recommended it.

      map{substr$_->[0],$_->[1]||0,1}[\*||{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^*ARGV,3]
      Got it. Thank you!!

      I am trying this right now: $sth->{mysql_insertid};
      I'll try that if this does not work.

      Thanks for the help.

      -Richard
Re: last_insert_id did not work
by Bod (Parson) on Mar 27, 2021 at 13:07 UTC

    Note that last_insert_id has quite a few caveats including:
    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.

    For this reason I prefer to get the database do provide the information rather than the driver.

    my ($_cinsert) = $dbh->selectrow_array("SELECT LAST_INSERT_ID()");

      Doing it that way, would I have to tell it which table? or does it already know?

      Example:
      $_chinsert = $dbh->selectrow_array("SELECT LAST_INSERT_ID()");
      that does not specify which table, if the site is busy, could that not get something else from someone else?

      Thanks,
      -Richard
        Doing it that way, would I have to tell it which table? or does it already know?

        No need to specify the table. LAST_INSERT_ID() returns the ID of the last insert made on a table with an auto-increment column by the session calling the function.

        could that not get something else from someone else?

        No because the function only returns the information from the current session.

Re: last_insert_id did not work
by bizactuator (Sexton) on Mar 27, 2021 at 08:35 UTC
    I am so dumb. OMG. I just realized, I used a code for reading a database table... lol. $sth->fetchrow_hashref(); DUH, my bad. I cannot believe I messed up that so bad. errrgh. How do I use a statement like that to do it that way?
    $sth = $dbh->prepare(qq{INSERT INTO `coacttran` SET $_pstmt"}); $sth->execute(@_placeholders); $sth->fetchrow_hashref(); $sth->finish(); $_chinsert = $sth->{mysql_insertid};
    how do I make that work?
    My bad. I'm totally exhausted, been working for 20 hours today. errgh. Should have slept first. lol. Sorry.
      Should have slept first. lol. Sorry.

      No worries, it happens :-) Update: We missed it too!

      I am trying this right now: $sth->{mysql_insertid};
      then later message:
      I just realized, I used a code for reading a database table... lol. $sth->fetchrow_hashref();
      From DBI docs ... last_insert_id of DBI
      * For some drivers the value may only available immediately after the insert statement has executed (e.g., mysql, Informix).

      Ron

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others about the Monastery: (5)
As of 2024-03-29 10:52 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found