The obvious and simple fix is simply to perform a select right after the insert, calling up the auto-increment value that way. This mostly works, though I am concerned that it is going to result in my code executing twice as many SQL statements, and there is the further danger that the saved row is not unique.

This method is not safe in multi-process environments at all (CGI, mod_perl, etc) as the INSERT followed by the SELECT is not an atomic action. I would stay away from this kludge as it will only get you in trouble down the road.

On the other hand, I wonder what kind of overhead already exists as the result of the mysql_insertid's availability. Is DBD::mysql already transparently doing something along these lines, or is it somehow optimized?

No, there is no extra SQL call by DBD::Mysql (as best I know without looking at the source that is). I believe that in the client library for MySQL (which DBD::MySQL uses) the 'last_insert_id' is a function/variable/environmental-parameter of some kind. So its actually optimized in MySQL itself.

*snip a bunch of stuff* This still leaves a bitter taste in my mouth, but at least it would avoid the superfluity of executing a second statement that wasn't going to be of any value.

That whole idea leaves a bitter taste in my mouth as well. Anything that is assuming you don't have duplicate data (aside from the primary key) will get you in trouble.

So, what I would really like to know... Does some better, less hackish way exist to accomplish that which I have described, or am I stuck dealing with it in some way along the lines of what I have delineated herein?

Subclassing, subclassing, subclassing. There is no such thing as truely "vanilla" SQL (at least not that I have seen and which is usable in a real world situation), so the idea of complete DBD (therefore database) agnosticism is a fantasy. There really exists only one elegant solution, which is to subclass.

Also, keep in mind that all DBDs are not alike. Just because the DBI specification says it should do "A", doesn't mean the driver author has to actually implement that (or even better, have time to implement it). You would be surprised how minimal a DBD driver can be, and still "work" with DBI.

-stvn

In reply to Re: Auto-Increment and DBD Agnosticism by stvn
in thread Auto-Increment and DBD Agnosticism by skyknight

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.