Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2016-08-30
15
Medium Priority
?
168 Views
Last Modified: 2016-09-06
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
Comment
Question by:JPNeron
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 2
  • +1
15 Comments
 
LVL 7

Expert Comment

by:Phil Davidson
ID: 41777347
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
 
LVL 22

Expert Comment

by:Kim Walker
ID: 41777504
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
 
LVL 59

Expert Comment

by:Julian Hansen
ID: 41777873
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:JPNeron
ID: 41778542
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
 
LVL 59

Expert Comment

by:Julian Hansen
ID: 41778630
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
 

Author Comment

by:JPNeron
ID: 41778734
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
 
LVL 22

Assisted Solution

by:Kim Walker
Kim Walker earned 1000 total points
ID: 41779199
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
 

Author Comment

by:JPNeron
ID: 41780074
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
 

Author Comment

by:JPNeron
ID: 41780097
FYI: Previously I was using 'DB'. This problem started when I migrated to 'MDB2'.
0
 
LVL 59

Assisted Solution

by:Julian Hansen
Julian Hansen earned 1000 total points
ID: 41780348
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
 

Author Comment

by:JPNeron
ID: 41780668
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
 
LVL 59

Accepted Solution

by:
Julian Hansen earned 1000 total points
ID: 41780708
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
 

Author Comment

by:JPNeron
ID: 41785222
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
 

Author Comment

by:JPNeron
ID: 41786607
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
 
LVL 59

Expert Comment

by:Julian Hansen
ID: 41786768
You are welcome.
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question