PEAR DB 1.5 RC1 + PostgreSQL 7.3.3

cliffyman

I have a table in PostgreSQL that was created using this statement:

CREATE TABLE cms_page 
(
id serial NOT NULL,
parent_id int4 NOT NULL DEFAULT 0, 
title varchar(255) NOT NULL, 
rank int4 NOT NULL DEFAULT 1,
active boolean DEFAULT true,
PRIMARY KEY (id)
);

-- create special root node
INSERT INTO cms_page VALUES (0,0,'Home',1,true);

-- set up integrity keys
ALTER TABLE cms_page
ADD FOREIGN KEY (parent_id)
REFERENCES cms_page (id)
ON UPDATE CASCADE
ON DELETE CASCADE;

CREATE UNIQUE INDEX cms_page_u1
ON cms_page (parent_id, title);

CREATE UNIQUE INDEX cms_page_u2
ON cms_page (parent_id, rank);

Then I have a query as simple as this:

INSERT INTO cms_page (parent_id, title, rank) VALUES (0, 'Test 1', 1);

Which fails with: "DB Error: Unknown Error"

However, if I execute this exact same query through the psql terminal, it works fine. Can anyone offer a suggestion? Here's the PHP code for the page:

$db_connection = DB::connect($db_hosttype."://".$db_username.":".$db_password."@".$db_hostname."/".$db_database);

if (DB::isError($db_connection)) die($db_connection->getMessage());

$sql1 = "INSERT INTO cms_page (parent_id, title, rank) VALUES (0,'test',6)";

$q_doadd = $db_connection->query($sql1);

if(DB::isError($q_doadd)) die($q_doadd->getMessage());

Thanks very much... I'm pulling my hair out over this one and totally baffled!

-Cliff

cliffyman

All right, looks like I figured this out... that you need to do is explicitly specify any fields that are of "serial" datatype (ie, sequences) and input the next value.

You can accomplish this with PEAR's nextID method:

$id=$db->nextID("mySequenceName");

Then do your insert...

$sql = "INSERT INTO tablename (id, parent_id, test) VALUES ($id, $parent_id, $test);";

Best of luck!

ultraslacker

Check that the user (db_username) has INSERT permissions for that table.

ultraslacker

Hmm...shouldnt be required if db_username has EXECUTE permissions on that sequence.

cliffyman

The user definitely had insert permissions on the table... as I said before, the simple version of the insert worked fine in the shell but failed when executed through PHP with PEAR DB.

I was able to get it work as I specified above, using the nextID() method to get the next number in the sequence and then inserting that value manually.

Any idea why the other (simpler) insert would fail? I'm coming from a MySQL background, where auto_increment would allow you to omit that field and it would automatically populate the value for me.

Thanks!

-Cliff

chriskl

This is totally naff code and you are showing your MySQL background: 🙂

INSERT INTO cms_page (parent_id, title, rank) VALUES (0, 'Test 1', 1);

In PostgreSQL, you just go:

INSERT INTO cms_page (title, rank) VALUES ('Test 1', 1);

INSERT INTO cms_page (parent_id, title, rank) VALUES (DEFAULT, 'Test 1', 1);

Please keep in mind that most of MySQL's syntax is just 'made up' and is non-standard.

Chris

cliffyman

Well, this was just a test case, but in some instances the parent_id is going to be something other than 0 (the DEFAULT) since this table holds a tree-like data structure in it. I am aware of how to use the default values for columns, thanks 🙂

Still, any idea why I have to manually insert the value for the "id" field, even though its datatype is serial? Is this some kind of special case given the fact that it creates a sequence, ie, it doesn't do the MySQL style auto_increment?

I know MySQL isn't a very good DB for ANSI standards compliance, but hey, we all have to start somewhere 🙂

Thanks again... I'd really appreciate an explanation, if there is one, and why the heck the query works fine in the psql terminal but seems to fail with PEAR DB.

-Cliff