[thelist] Free Tip about SQL
SBeam
sbeam at syxyz.net
Fri Jul 18 15:43:45 CDT 2003
On Friday 18 July 2003 01:41 pm, Seb Potter wrote:
> Stick to whatever mechanism your DB provides.
agreed.
> It might not be as 100%
> portable, but it is guaranteed to work every time, which all of these other
> methods are not.
>
for portability, use sequences (or an emulation thereof), instead of the
last_insert_id() call or whatever.
http://www.postgresql.org/docs/aw_pgsql_book/node75.html
if you are using PHP4+ to access mySQL, your PEAR/DB library (yes it comes
with php) has an emulation built in:
$nextId = $db->nextId('foo_table');
$res = $db->query("INSERT INTO foo_table VALUES ($nextId, '$foovalue');
thats it. you dont even have to create the sequence, PEAR/DB will do it for
you if needed. And the same code will work on any DB.
http://pear.php.net/manual/en/package.database.db.intro-sequences.php
Also in favor of sequences - there are situations, rarely, where it is nice to
have the nextId _before_ you do the INSERT. last_insert_id() and friends, for
obvious reasons, cant do that.
More information about the thelist
mailing list