| Subject | RE: [firebird-support] retrieve unique id |
|---|---|
| Author | Nigel Weeks |
| Post date | 2006-03-08T02:25:06Z |
If you're using generators for unique identifiers for a record(which you
should be), you need to obtain the unique key from the generator BEFORE
inserting the record.
You can leave it up to a trigger to obtain it for you, but you cannot fetch
the ID it used after the insert has happened.
It's very easy to do. Here's a snippet of PHP to explain it.
It's just a mind shift from MySQL's "getting the ID AFTER the insert".
<?php
$conn = ibase_connect("server:/path/to/database.fdb","sysdba","masterkey");
// Get the next generator value from the 'gen_tbl_blah' generator
$gensql = "select gen_id(gen_tbl_blah,1) from rdb\$database";
$genrec = ibase_query($gensql);
$genrow = ibase_fetch_row($genrec);
$newval = $genrow[0];
// Now do the main insert
$inssql = "insert into tbl_blah (int_id,str_name,str_phone,str_email) values
($newval,'Bob Bobson','1234 5678','bob@...')";
$insrec = ibase_query($inssql);
?>
You could even write a tiny function to make it easier to obtain generator
values:
<?php
function newid($gen_name){
if($gen_name == ""){
$sql = "select gen_id(".$gen_name.",1) from rdb\$database";
$rec = ibase_query($sql);
$row = ibase_fetch_row($rec);
return $row[0];
}
?>
Now, you can use it like this:
<?php
$newval = newid("gen_tbl_blah");
// Now do the main insert
$inssql = "insert into tbl_blah (int_id,str_name,str_phone,str_email) values
($newval,'Bob Bobson','1234 5678','bob@...')";
$insrec = ibase_query($inssql);
?>
Easy, eh!
N
should be), you need to obtain the unique key from the generator BEFORE
inserting the record.
You can leave it up to a trigger to obtain it for you, but you cannot fetch
the ID it used after the insert has happened.
It's very easy to do. Here's a snippet of PHP to explain it.
It's just a mind shift from MySQL's "getting the ID AFTER the insert".
<?php
$conn = ibase_connect("server:/path/to/database.fdb","sysdba","masterkey");
// Get the next generator value from the 'gen_tbl_blah' generator
$gensql = "select gen_id(gen_tbl_blah,1) from rdb\$database";
$genrec = ibase_query($gensql);
$genrow = ibase_fetch_row($genrec);
$newval = $genrow[0];
// Now do the main insert
$inssql = "insert into tbl_blah (int_id,str_name,str_phone,str_email) values
($newval,'Bob Bobson','1234 5678','bob@...')";
$insrec = ibase_query($inssql);
?>
You could even write a tiny function to make it easier to obtain generator
values:
<?php
function newid($gen_name){
if($gen_name == ""){
$sql = "select gen_id(".$gen_name.",1) from rdb\$database";
$rec = ibase_query($sql);
$row = ibase_fetch_row($rec);
return $row[0];
}
?>
Now, you can use it like this:
<?php
$newval = newid("gen_tbl_blah");
// Now do the main insert
$inssql = "insert into tbl_blah (int_id,str_name,str_phone,str_email) values
($newval,'Bob Bobson','1234 5678','bob@...')";
$insrec = ibase_query($inssql);
?>
Easy, eh!
N
> -----Original Message-----
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com]On Behalf Of Graham
> Sent: Wednesday, 8 March 2006 12:59 PM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] retrieve unique id
>
>
> Is there an example of a stored procedure to retrieve the unique key
> created by a trigger on insert at the same time as the insertion is
> done? ( hope this is the correct list now .. )
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>
> __________ NOD32 1.1433 (20060307) Information __________
>
> This message was checked by NOD32 antivirus system.
> http://www.eset.com
>
>