MySQL, DBIx::Recordset and is there a better way?

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

<blantant grovel> Oh great prevayers of truth, justice, and the Perl way,

I beg for your guidence on a matter of modest concern. </blantant grovel> I am doing two inserts into two seperate tables with the primary key for the second table is created by the insert into the first. Here is the code:

    # insert into table
    if (
        DBIx::Recordset->Insert (
                     {
                      '!DataSource'   =>  $dbh,
                      '!Table'        =>  "settings",
                      %$settings
                     }
                    )
        )
      {
        # get the league id from the insert above
        my $stmt = "select ID from settings where ID = last_insert_id(
+)";
        my $sth = $dbh->prepare($stmt);
        $sth->execute();
        # put it into the positions hash
        $positions->{'league_id'} = $sth->fetchrow_array();

        if (
        DBIx::Recordset->Insert (    
                     {
                      '!DataSource'   =>  $dbh,
                      '!Table'        =>  "nba_position",
                      %$positions
                     }
                    )
           )
          {
        return 0;
          }
[download]

As I said in the subject line, is there a better way to do this? I would like to combine the DBIx insert into the same conditional, and I would like a cleaner way to get the ID created from the first insert.

Any ideas?

Comment on MySQL, DBIx::Recordset and is there a better way? Download Code

Replies are listed 'Best First'.
Re: MySQL, DBIx::Recordset and is there a better way? by runrig (Abbot) on Jan 03, 2002 at 02:16 UTC
Every database has its own way of getting a sequence number or id number from the last insert, so you are stuck with whatever your database offers in that regard. You might simplify the whole thing with something like this, though, using eval to eliminate the other conditionals (and also checks the status of getting the last id inserted, which you are not doing): `$dbh->{RaiseError}=1; eval { insert... get last id... insert... }; if ($@) { insert failed... } insert succeeded...` [download]	[reply] [d/l]
Re: MySQL, DBIx::Recordset and is there a better way? by lachoy (Parson) on Jan 03, 2002 at 02:28 UTC
As long as you're using mysql, you can almost certainly do after the first insert: `$positions->{'league_id'} = $dbh->{mysql_insertid};` [download] This is part of the DBD::mysql API. Obligatory SPOPS plug: SPOPS takes care of this issue (auto-incrementing fields) for you transparently, even for different databases. /msg me for info if you're interested. Chris M-x auto-bs-mode	[reply] [d/l]
Re: Re: MySQL, DBIx::Recordset and is there a better way? by jonjacobmoon (Pilgrim) on Jan 03, 2002 at 03:54 UTC
Thanks for the help. That line was indeed what I was hoping for. I actually would like to learn more about SPOPS which has been mentioned in response to my posts more than once. However, in this case, MySQL has an auto-increment on the primary key which I have set. HOWEVER, I do see the advantage of having the code do the autoincrement because then you don't have to rely on the db or the dba to do it for you.	[reply]
SPOPS and auto-incrementing by lachoy (Parson) on Jan 03, 2002 at 07:42 UTC
SPOPS still relies on the database to do the auto-incrementing for you, it just provides a consistent interface on how to retrieve the incremented value. IME, database-generated index fields break down cleanly into two types: those that expect you to trigger something before the INSERT so you can get the incremented value, and those that create a unique value and make it available after the INSERT. Oracle, PostgreSQL, mSQL and other databases that use sequences fall in the first category -- even if you embed the sequence select in the INSERT. In the second category we have Transact-SQL databases (Sybase ASA/ASE, Microsoft SQL) and MySQL. So all SPOPS does is ask you to tell it if you're using an auto-increment field and tell it what type of database you're using. It's generally smart enough to figure out the rest. And that was probably me bringing up SPOPS those times you saw it. One of my resolutions is to become an annoying tubthumper for my work and get other people interested enough to contribute :-) Chris M-x auto-bs-mode	[reply]