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

Hi everyone,

I'm puzzled why Example 1 below where I obtain the last insert id from a single insert works but Example 2 where I try to obtain two last insert ids from two different inserts doesn't:

Example 1: 
    $dbh->{RaiseError} = 1;
    $dbh->begin_work;
    eval {
        $dbh->do(qq~INSERT INTO table1 VALUES(?, ?)~, undef, undef, "s
+omevalue");
        my $last_id = $dbh->last_insert_id(undef, undef, qw(table1 id)
+);
        $dbh->do(qq~INSERT INTO table3 VALUES(?, ?)~, undef, undef, $l
+ast_id);

    };
    if ($@) {
        eval { 
            $dbh->rollback();
        };
    }
    $dbh->commit();

[download]
Example 2: 
    $dbh->{RaiseError} = 1;
    $dbh->begin_work;
    eval {
        $dbh->do(qq~INSERT INTO table1 VALUES(?, ?)~, undef, undef, "s
+omevalue});
        my $last_id = $dbh->last_insert_id(undef, undef, qw(table1 id)
+);
        $dbh->do(qq~INSERT INTO table2 VALUES(?, ?)~, undef, undef, $s
+omevalue);
        my $last_id2 = $dbh->last_insert_id(undef, undef, qw(table2 id
+));
        $dbh->do(qq~INSERT INTO table3 VALUES(?, ?, ?)~, undef, undef,
+ $last_id, $last_id2);

    };
    if ($@) {
        eval { 
            $dbh->rollback();
        };
    }
    $dbh->commit();

[download]

In Example 1, the mysql transaction is commited but in Example 2, it's rolled backed. Is it possible to obtain more than one last insert id?

Replies are listed 'Best First'.
Re: Perl msyql last_insert_id works once?
by Corion (Patriarch) on Dec 04, 2015 at 07:25 UTC

    The availability of ->last_insert_id very much depends on your DBD.

    I'm not sure what MySQL states what should happen for insertid within a transaction, so maybe there is a hint in the MySQL documentation.

    Maybe one of your insert statements fails and you don't log the error output? You seem to have two/three placeholders but only pass in one/two values to the do statement?

[reply]
[d/l]
[select]
Re: Perl msyql last_insert_id works once?
by Apero (Scribe) on Dec 04, 2015 at 18:33 UTC

    last_insert_id() is generally only useful directly after a (successful) INSERT, and even then the exact semantics are riddled with potential pitfalls, especially if you're writing a portable application (see the DBI docs for details.) For MySQL (and incidentally SQLite too) you can list all 4 params to last_insert_id as undef, or more commonly the blank string for brevity, and it should do what you expect, even in transactions. (PostgreSQL gives you the RETURNING clause, which is even better if you can design for that RDBMS.)

    Remember to put the rollback inside an eval block too as it may also fail, especially if the handle is now defunct for some reason (connection error or the like.)

    I'd also suggest use of Carp::confess() or Carp::croak() so that you can point to a the specific Perl line that caused the error to be thrown inside the eval block.

    If you're still having problems, posting a minimal reproduction, including the most basic SQL schema you can design to show the problem, is likely required.

    This seems to work as expected for me, printing incrementing PK values for the id column:

    Read more... (2 kB)
[reply]
[d/l]
[select]
Re: Perl msyql last_insert_id works once?
by Anonymous Monk on Dec 04, 2015 at 07:33 UTC
[reply]

Back to Seekers of Perl Wisdom