• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 178
  • Last Modified:

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.
0
JPNeron
Asked:
JPNeron
  • 7
  • 5
  • 2
  • +1
3 Solutions
 
Phil DavidsonCommented:
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.
0
 
Kim WalkerWeb Programmer/TechnicianCommented:
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

0
 
Julian HansenCommented:
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 ?
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
JPNeronAuthor Commented:
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
0
 
Julian HansenCommented:
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.
0
 
JPNeronAuthor Commented:
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
0
 
Kim WalkerWeb Programmer/TechnicianCommented:
Ignore my suggestion. When I was scrolling up and down to prepare my comment, I failed to scroll up far enough to see your original code. I believe this is neither a PHP nor Postgres issue. I believe it is an MDB2 issue. I can find no MDB2 documentation to support the query function syntax of your original code. Assuming that you are using the PEAR MDB2 extension, the current documentation does provide for a second argument to the query function, but it is for an array of field data types, not field values. What version of MDB2 extension are you using and have you updated it along with PHP and Postgres?
0
 
JPNeronAuthor Commented:
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
0
 
JPNeronAuthor Commented:
FYI: Previously I was using 'DB'. This problem started when I migrated to 'MDB2'.
0
 
Julian HansenCommented:
FYI: Previously I was using 'DB'. This problem started when I migrated to 'MDB2'.
And DB uses the same API interface as MDB2?
If you look at the API docs for query() - (based on a brief look) and as Kim already mentioned, it does not seem to support the calling convention you are using.

Look at the MDB2 API docs for query and then update your code to use the convention listed there.
0
 
JPNeronAuthor Commented:
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.
0
 
Julian HansenCommented:
The mixture of PHP and DB2 is not one commonly catered for on these forums - MySQL, PostGres, MSSQL being the main weapons of choice - so I suspect you are going to get best guess answers here.

Having done a bit of research I will say that I find the API docs below par on this. There does not seem to be anything obviously stating a difference in the query methods - but I did find this article which may be useful.

http://www.phpied.com/db-2-mdb2/
0
 
JPNeronAuthor Commented:
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.
0
 
JPNeronAuthor Commented:
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.
0
 
Julian HansenCommented:
You are welcome.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 7
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now