FAQ error

On 10 Oct 2001 at 17:12 (-0400), Bruce Momjian wrote:
|
| Our FAQ, item 4.16.2 has:
|
| $newSerialID = nextval('person_id_seq');
| INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal');
|
| Is this correct Perl? I don't see a nextval() function in Perl. Can
| you call SQL server-side functions natively from Perl?

no. The proper perl code would be more like...

use DBI;
my ($lastid,$nextid,$sql,$rv);
my $dbh = DBI->connect("perldoc DBD::Pg");

# to use the nextval
$sql = "SELECT nextval('person_id_seq')";
$nextid = ($dbh->selectrow_array($sql))[0];
$sql = "INSERT INTO person (id, name) VALUES ($nextid, 'Blaise Pascal');
$rv = $dbh->do($sql);

# or to get the currval
$sql = "INSERT INTO person (name) VALUES ('Blaise Pascal');
$rv = $dbh->do($sql);
$sql = "SELECT currval('person_id_seq')";
$lastid = ($dbh->selectrow_array($sql))[0];

| --
| Bruce Momjian | http://candle.pha.pa.us
| pgman@candle.pha.pa.us | (610) 853-3000
| + If your life is a hard drive, | 830 Blythe Avenue
| + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
|
| ---------------------------(end of broadcast)---------------------------
| TIP 6: Have you searched our list archives?
|
| http://archives.postgresql.org

--
"Develop your talent, man, and leave the world something. Records are
really gifts from people. To think that an artist would love you enough
to share his music with anyone is a beautiful thing." -- Duane Allman

Bruce Momjian wrote:

$newSerialID = nextval('person_id_seq');
INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal');

Is this correct Perl? I don't see a nextval() function in Perl. Can
you call SQL server-side functions natively from Perl?

Ofcourse not. This can be counted as 'pseudo-code'...

A correct implementation using DBI (and DBD::Pg) would be

$newSerialID = $dbh->selectrow_array (q{select
nextval('person_id_seq')});
$dbh->do (qq{INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise
Pascal')});

--
Alessio F. Bragadini alessio@albourne.com
APL Financial Services http://village.albourne.com
Nicosia, Cyprus phone: +357-2-755750

"It is more complicated than you think"
-- The Eighth Networking Truth from RFC 1925

Bruce Momjian writes:

Our FAQ, item 4.16.2 has:

$newSerialID = nextval('person_id_seq');
INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal');

Is this correct Perl?

No. I always thought it was pseudo code. I think it's fine.

--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter

Bruce Momjian writes:

Our FAQ, item 4.16.2 has:

$newSerialID = nextval('person_id_seq');
INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal');

Is this correct Perl?

No. I always thought it was pseudo code. I think it's fine.

It is psaudo-code, but the assignment for nextval() is just wrong:

$newSerialID = nextval('person_id_seq');

I am going to flesh this out with the SELECT but not the rest.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

On 10 Oct 2001 at 17:12 (-0400), Bruce Momjian wrote:
|
| Our FAQ, item 4.16.2 has:
|
| $newSerialID = nextval('person_id_seq');
| INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal');
|
| Is this correct Perl? I don't see a nextval() function in Perl. Can
| you call SQL server-side functions natively from Perl?

no. The proper perl code would be more like...

use DBI;
my ($lastid,$nextid,$sql,$rv);
my $dbh = DBI->connect("perldoc DBD::Pg");

# to use the nextval
$sql = "SELECT nextval('person_id_seq')";
$nextid = ($dbh->selectrow_array($sql))[0];
$sql = "INSERT INTO person (id, name) VALUES ($nextid, 'Blaise Pascal');
$rv = $dbh->do($sql);

OK, new FAQ code is:

$sql = "SELECT nextval('person_id_seq')";
$newSerialID = ($conn->selectrow_array($sql))[0];
INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal');
$res = $dbh->do($sql);

# or to get the currval
$sql = "INSERT INTO person (name) VALUES ('Blaise Pascal');
$rv = $dbh->do($sql);
$sql = "SELECT currval('person_id_seq')";
$lastid = ($dbh->selectrow_array($sql))[0];

and:

INSERT INTO person (name) VALUES ('Blaise Pascal');
$res = $conn->do($sql);
$sql = "SELECT currval('person_id_seq')";
$newSerialID = ($conn->selectrow_array($sql))[0];

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian writes:

OK, new FAQ code is:

$sql = "SELECT nextval('person_id_seq')";
$newSerialID = ($conn->selectrow_array($sql))[0];
INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal');
$res = $dbh->do($sql);

This code is still incorrect for any known programming language and it's
even less clear to a person that doesn't know the programming language
it's probably trying to imitate.

--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter

Bruce Momjian writes:

OK, new FAQ code is:

$sql = "SELECT nextval('person_id_seq')";
$newSerialID = ($conn->selectrow_array($sql))[0];
INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal');
$res = $dbh->do($sql);

This code is still incorrect for any known programming language and it's
even less clear to a person that doesn't know the programming language
it's probably trying to imitate.

OK, what suggestions do you have?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian writes:

Bruce Momjian writes:

OK, new FAQ code is:

$sql = "SELECT nextval('person_id_seq')";
$newSerialID = ($conn->selectrow_array($sql))[0];
INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal');
$res = $dbh->do($sql);

This code is still incorrect for any known programming language and it's
even less clear to a person that doesn't know the programming language
it's probably trying to imitate.

OK, what suggestions do you have?

I didn't have a problem with the original version. It conveyed clearly
(to me), "read the nextval and insert it yourself".

--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter

Bruce Momjian writes:

Bruce Momjian writes:

OK, new FAQ code is:

$sql = "SELECT nextval('person_id_seq')";
$newSerialID = ($conn->selectrow_array($sql))[0];
INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal');
$res = $dbh->do($sql);

This code is still incorrect for any known programming language and it's
even less clear to a person that doesn't know the programming language
it's probably trying to imitate.

OK, what suggestions do you have?

I didn't have a problem with the original version. It conveyed clearly
(to me), "read the nextval and insert it yourself".

Obviously, someone did because they tried the code and it didn't work.
At least the new code is closer to valid, though less clear. It is at
least a valid snippet, which the previous version was not.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Obviously, someone did because they tried the code and it didn't work.
At least the new code is closer to valid, though less clear. It is at
least a valid snippet, which the previous version was not.

OK, I changed it to more pseudocode:

new_id = output of "SELECT nextval('person_id_seq')"
INSERT INTO person (id, name) VALUES (new_id, 'Blaise Pascal');

and

INSERT INTO person (name) VALUES ('Blaise Pascal');
new_id = output of "SELECT currval('person_id_seq')";

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026