Re: AutoIncrement Database Fields for MySQL and PostGreSQL

[toolscripts]

SELECT MAX(id)+1 as lastid FROM table
(It's not +1 if you already said INSERT etc)

I'm not sure what normalized relations are, but I think this will help
anyway.

--t


- Original Message - 
From: Siegfried Heintze [EMAIL PROTECTED]
To: 'Perl Beginners' beginners@perl.org
Sent: Monday, April 11, 2005 4:18 PM
Subject: AutoIncrement Database Fields for MySQL and PostGreSQL


 If I use the auto-increment feature for a couple of normalized relations,
 how do I insert into them?

 Specifically, when I insert into a relation with the autoincrement feature
 on the primary key, how do I get the value of the index on the newly
created
 row so I can use that the value of a foreign key in another relation?

 I'm using DBI for a multithreaded application, sometimes for MySQL and
 sometimes for PostGreSQL. In MSAccess, I know, I can only say SELECT
MAX(ID)
 which does not work well for a multithreaded application.

 Thanks,
 Siegfried



 -- 
 To unsubscribe, e-mail: [EMAIL PROTECTED]
 For additional commands, e-mail: [EMAIL PROTECTED]
 http://learn.perl.org/ http://learn.perl.org/first-response








-- 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/ http://learn.perl.org/first-response

Re: AutoIncrement Database Fields for MySQL and PostGreSQL

[Bob Showalter]

Siegfried Heintze wrote:
If I use the auto-increment feature for a couple of normalized
relations, how do I insert into them?
Specifically, when I insert into a relation with the autoincrement
feature on the primary key, how do I get the value of the index on
the newly created row so I can use that the value of a foreign key in
another relation?
You can use either the last_insert_id() sql function:
  insert into parent (...) values (...);
  insert into child (id, blah) values (last_insert_id(), 'text');
Or the $dbh-{mysql_insertid} database handle attribute:
  $dbh-do(insert into parent (...) values (...));
  my $lastid = $dbh-{mysql_insertid};
  $dbh-do(insert into child (id, blah) values (?, 'text'), undef, 
$lastid);

--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/ http://learn.perl.org/first-response

RE: AutoIncrement Database Fields for MySQL and PostGreSQL

[Siegfried Heintze]

Thanks. I see that works for mysql. 

my $lastid = $dbh-{mysql_insertid};

Is this a feature of MySQL or DBI? 

If it is not a feature of DBI, will it work for PostGreSQL?

Thanks,
Siegfried


-- 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/ http://learn.perl.org/first-response

Re: AutoIncrement Database Fields for MySQL and PostGreSQL

[Bob Showalter]

Siegfried Heintze wrote:
Thanks. I see that works for mysql.
my $lastid = $dbh-{mysql_insertid};
Is this a feature of MySQL or DBI?
It's a feature of MySQL, via the DBD::mysql driver.
If it is not a feature of DBI, will it work for PostGreSQL?
No, but there is a similar feature. Read the docs for DBD::Pg.
Thanks,
Siegfried
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/ http://learn.perl.org/first-response