asked on

Migrating from Postgres 8.4.11/PHP 5.3.3 to Postgres 9.2.5/PHP 5.4.16 - syntax error on INSERT

I'm migrating some small php pages from Postgress 8.4.11/PHP 5.3.3 to Postgres 9.2.5/PHP 5.4.16, and I've fixed most of the issues relating to changing from DB to MDB2, but I can't get any database updates to work, I get a syntax error.

Here's a sample of the code:

$howmany=$mdb2->query('insert into sup_sup_response (
		response_problem_no,
		response_response_date,
		response_date,
		response_resolved_by,
		response_description,
		response_follow_up_dte,
		response_time_spent) values (?,?,?,?,?,?,?)', $response);

Open in new window

I get this syntax error:

The error was: MDB2 Error: syntax error
More Details: _doQuery: [Error message: Could not execute statement] [Last executed query: insert into sup_sup_response ( response_problem_no, response_response_date, response_date, response_resolved_by, response_description, response_follow_up_dte, response_time_spent) values (?,?,?,?,?,?,?)] [Native message: ERROR: syntax error at or near "," LINE 8: response_time_spent) values (?,?,?,?,?,?,?) ^]

I tried:

$howmany=$mdb2->query('insert into sup_sup_response (
		response_problem_no,
		response_response_date,
		response_date,
		response_resolved_by,
		response_description,
		response_follow_up_dte,
		response_time_spent) values ($response[0], $response[1]..etc)';

Open in new window

but that didn't work either.

It's been years since I've done anything in php, so I'm stumped.

What am I missing?

Thanks.

Phil Davidson

One difference I see is the closing apostrophe on the second statement is after (to the right) of the closing parens. Are you sure your versions of Postgres and PHP are compatible? Can you reboot the database server to see if that would help? Sometimes a zombie process or lock could cause a temporary problem.

Kim Walker

I have to assume there is another right parenthesis in the ..etc placeholder. But unless all your fields are numerical fields, I would expect the values to be quoted and in order for the PHP variables to be translated, the insert statement would need to be double-quoted.

$howmany=$mdb2->query("insert into sup_sup_response (
		response_problem_no,
		response_response_date,
		response_date,
		response_resolved_by,
		response_description,
		response_follow_up_dte,
		response_time_spent) values ('$response[0]', '$response[1]'..etc)";

Open in new window

Julian Hansen

What is $mdb2 defined as - I am assuming this is some wrapper for the database query?

You are using a parameterised query (?,?,? ...) - which hints at a prepared statement ?

JPNeron

ASKER

Phil:

I can only assume they are compatible. I'm able to retrieve data, I just can't update it. A reboot didn't change anything.

Julian:

$mdb2 is the object I get back from opening the connection:

require_once('MDB2.php');
$mdb2=MDB2::connect("pgsql://user@/database");
if (MDB2::isError($mdb2)){
dberror($mdb2);
}

The connection works, I am able to retrieve data. There is no 'prepare' statement for the query. The code I pasted runs on the old system just as you see it.

Kim:

Your suggested change works, but why doesn't the original code work? It works on the old system, I don't understand why I need to change it. There's a lot of typing to do if I have to make this type of change to all the update statements.

Thanks.

Jean

Julian Hansen

ERROR: syntax error at or near "," LINE 8: response_time_spent) values (?,?,?,?,?,?,?) ^]

The error is saying there is something wrong with your query.
It is not a PHP error - it is an error from the Database - it does not like what you are sending it.

The message says the error is near a ',' on line 8 - the only comma's on line 8 are in the (?,?,....)

Therefore I am saying that you need to look at your query that is being sent to the server.

JPNeron

ASKER

Julian,

This same query works in Postgres 8.4, so you're saying the command syntax is different in 9.2? If so, what is the correct syntax (other than what Kim already suggested)?

Jean

SOLUTION

Kim Walker

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JPNeron

ASKER

I'm using 2.4.1:

$ pear install MDB2
pear/MDB2 is already installed and is the same as the released version 2.4.1
install failed

JPNeron

ASKER

FYI: Previously I was using 'DB'. This problem started when I migrated to 'MDB2'.

SOLUTION

Julian Hansen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JPNeron

ASKER

So what you are telling me is there is no equivalent in MDB2 to the DB API as documented at https://pear.php.net/package/DB/docs/latest/DB/DB_common.html#methodquery ?

It seems odd that the DB replacement would completely skip one of it's methods, and not a little used one at that.

ASKER CERTIFIED SOLUTION

Julian Hansen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JPNeron

ASKER

Sorry for the delay, I got tied up on something else.

I saw that article, and it was helpful for working out what to change for extracting data, but is pretty silent on how to convert the DB 'query' method to whatever MDB2 uses.

I guess I'll just have to rewrite it using prepare/execute if I can't find anything else.

JPNeron

ASKER

In the end, I had to rewrite the update queries using the prepare/execute model. There doesn't appear to be a direct equivalent in MDB2 for the DB method I was using.

Thanks everyone.

Julian Hansen

You are welcome.