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
88 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
  • 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 21

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 51

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
 

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 51

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 21

Assisted Solution

by:Kim Walker
Kim Walker earned 250 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 51

Assisted Solution

by:Julian Hansen
Julian Hansen earned 250 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 51

Accepted Solution

by:
Julian Hansen earned 250 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 51

Expert Comment

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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article discusses how to create an extensible mechanism for linked drop downs.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now