Basically I need to know the autoincremented "id" value from row that I am inserting into a table. How can I get this?

I have tried the "last_insert_id" function, but I really don't trust it. I could get into a race condition if a second row were inserted before I could retrieve the id from the row that I just inserted.

Here is what I have tried. Perhaps I just don't have last_insert_id working properly. (it is returning null at the moment.) 

 my $query;
        $query=qq(insert into MasterEpisode set \
                ShowId          ="$ShowID",\
                title           ="$p->{'ep_title'}",\
                description     ="$p->{'ep_description'}",\
                uploaddate      =localtime
                );
                print p($query);
                $dbh->do("$query");
                # I see a risk of a race condition here. I should see 
+if I can get the ID while I insert the line.
        my $episodeid=$dbh->last_insert_id(undef,undef,"MasterEpisode"
+,undef);
        print p("Episode id is $episodeid.");

[download]

Ok, looks like I can make it work in MySQL by doing this 


        # $episodeid=$dbh->last_insert_id(undef,undef,'MasterEpisode',
+undef);
        $sth = $dbh->prepare(qq(select LAST_INSERT_ID() from MasterEpi
+sode));
        $sth->execute();
        @episodeid=$sth->fetchrow_array();
        $episodeid=$episodeid[0];
        print p("Episode id is $episodeid.");

[download]

But it looks ugly and non portable to me.

Skip

In reply to DBI insert: Need to retrieve an autoincremented column from a table after an insert. by SkipHuffman

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.