Perl DBI and Mysql LAST_INSERT_ID() Problem 
Author Message
 Perl DBI and Mysql LAST_INSERT_ID() Problem

This works when I use the command line interface for MySQL, but not when I
send instructions through DBI...

In my simplified example, I have two tables...

create table onetb (pkey int not null primary key auto_increment,
description text null);
create table twotb (pkey int not null, fkey int not null, primary key (pkey,
fkey) );

I want to insert a value into onetb, but then grab the primary key to use as
the fkey value of twotb. Here's my Perl:

use DBI;
$dbh = DBI -> connect ("DBI:mysql:fabdb","user","password");
$sql = "insert into onetb (pkey, description) values (NULL,'Description goes
here')";
$sth = $dbh -> prepare($sql);
$sth -> execute; #fine

$sql = "insert into twotb (pkey, fkey) values (2,LAST_INSERT_ID())";
$sth = $dbh -> prepare($sql);
$sth -> execute; #error

I get an error in my Apache logfile from MySQL saying I'm there's an error
in my SQL near LAST_INSERT_ID());.

Can anyone offer a solution?

Alex



Wed, 20 Jul 2005 14:39:33 GMT  
 Perl DBI and Mysql LAST_INSERT_ID() Problem



Quote:
> I want to insert a value into onetb, but then grab the primary key to use
as
> the fkey value of twotb. Here's my Perl:

> use DBI;
> $dbh = DBI -> connect ("DBI:mysql:fabdb","user","password");
> $sql = "insert into onetb (pkey, description) values (NULL,'Description
goes
> here')";
> $sth = $dbh -> prepare($sql);
> $sth -> execute; #fine

my $lii=$dbh->{'mysql_insertid'}
Quote:

> $sql = "insert into twotb (pkey, fkey) values (2,LAST_INSERT_ID())";

                                                   ^^^^^^^^^^^^^^^^ $lii

Quote:
> $sth = $dbh -> prepare($sql);
> $sth -> execute; #error
> Can anyone offer a solution?

Here's what works for me:

use DBI;
my $dbh = DBI -> connect ("DBI:mysql:fabdb","user","password");
my $sql = "insert into onetb (pkey, description) values
(NULL,'Description...')";
$dbh->do($sql);
my $lii=$dbh->{'mysql_insertid'};
$sql = "insert into twotb (pkey, fkey) values (2,$lii)";
$dbh->do($sql);

--Frank



Wed, 20 Jul 2005 21:24:03 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. DBI, MySQL and LAST_INSERT_ID()

2. MySQL statement last_insert_id() and mod_perl

3. DBI: LAST_INSERT_ID() getting the value of

4. Using perl's modules DBD:Mysql and DBI and mysql

5. Using perl's modules DBD:Mysql and DBI and mysql

6. PERL-DBI-mySQL - how to not DIE on mySQL error

7. perl DBI / Mysql / Apache / ikonboard problem

8. Perl DBI or mysql problem

9. Weird Problem With Perl DBI and MySQL

10. perl/dbi/mysql problem

11. problem by using PERL & MySQL via DBI

12. DBI (DBD:mysql) and Mysql ENUM Datatype question

 

 
Powered by phpBB® Forum Software