[thelist] Free Tip about SQL

[SBeam]

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.