I am working on an SQlite project now.

From my code... You can call last_insert_id with no args (all undef) which probably would be fine in your app. Or in my code I asked specifically about the ScoreCard Table.

my $scoreCardrow = $dbh->last_insert_id(undef,undef,"ScoreCard",undef)
+; # last_insert_id($catalog, $schema, $table, $field);

[download]
On another point, from your code:
CREATE TABLE Songs (id        INTEGER PRIMARY KEY,

[download]
You do not need this line. SQLite cannot generate a table without a unique row id. If that line is missing, SQLite generates:
rowid        INTEGER PRIMARY KEY on its own. What you have is ok, but it generates an alias for the PRIMARY KEY - id is an alias for rowid.

Update: Complications can occur with multiple writers, but I don't think that is an issue with your code.

my $rowid = $sths{$table}{SELECT}->execute($value) || die;
I think we covered how to get last rowid. You do not need "|| die" because you have RaiseError =1 which is what I recommend.

Another fine point, 0E0 this is the DB's way of saying logical "true", but numeric "zero".

$dbh->do("CREATE INDEX ix_song ON Songs (song)");
I would leave the index off completely to start with and see how the performance is. You may be surprised at how well SQLite does on its own. The index adds considerably to the time for each insert. There are weird things that can happen in complex tables if you "overindex" them. This confuses the execution strategy engine and you can wind up with slower code! Anyway be careful.

In reply to Re: SQLite: INSERT into a unique column and retrieve rowid by Marshall
in thread SQLite: INSERT into a unique column and retrieve rowid by ibm1620

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.