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. | [reply] [Watch: Dir/Any] [d/l] [select] |
|
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...
| [reply] [Watch: Dir/Any] [d/l] |
|
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.
| [reply] [Watch: Dir/Any] |
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()
| [reply] [Watch: Dir/Any] [d/l] [select] |
|
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]
| [reply] [Watch: Dir/Any] [d/l] |
|
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
| [reply] [Watch: Dir/Any] |
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()");
| [reply] [Watch: Dir/Any] [d/l] [select] |
|
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
| [reply] [Watch: Dir/Any] [d/l] |
|
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.
| [reply] [Watch: Dir/Any] [d/l] |
|
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. | [reply] [Watch: Dir/Any] [d/l] |
|
| [reply] [Watch: Dir/Any] [d/l] |
|
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).
| [reply] [Watch: Dir/Any] |