Postgres query failing, not enough info for debugging...

--Apple-Mail-3-569065135
Content-Type: text/plain;
charset=US-ASCII;
format=flowed;
delsp=yes
Content-Transfer-Encoding: 7bit

Hello,

I'm using PHP 5 and PostgreSQL 8.3.6. I have a query that is failing
and I don't know how to troubleshoot the problem. The error message
that it is giving is quite vague. The error message is as follows:


Warning: pg_query_params() [function.pg-query-params]: Query failed:
ERROR: syntax error at end of input at character 156 in /home/walterc/
ssl/PHP/km_input_test2c.php on line 631
ERROR: syntax error at end of input at character 156

The query that is failing looks like this...

$pg_pres_ins6 = pg_query_params("INSERT INTO \"brdgMediaCallsEvents
\" (\"mediumId\", \"eventId\", rank) VALUES
((currval('\"tblMedia_mediumId_seq\"'),
(currval('\"tblCallsEvents_eventId_seq\"'), $1)", array($ev_rank));
echo pg_last_error($pg_connection);

It's quite possible this is something very simple and I'm just not
seeing it. I commented out this query because I thought perhaps it
was failing because it was the last query in the program. The
previous query did not fail when it became the last one.

Thanks in advance for your help.

Carol
--Apple-Mail-3-569065135--

Re: Postgres query failing, not enough info for debugging...

Carol Walter wrote:
> Hello,
>
> I'm using PHP 5 and PostgreSQL 8.3.6. I have a query that is failing
> and I don't know how to troubleshoot the problem. The error message
> that it is giving is quite vague. The error message is as follows:
>
>
> Warning: pg_query_params() [function.pg-query-params]: Query failed:
> ERROR: syntax error at end of input at character 156 in
> /home/walterc/ssl/PHP/km_input_test2c.php on line 631
> ERROR: syntax error at end of input at character 156
>
> The query that is failing looks like this...
>
> $pg_pres_ins6 = pg_query_params("INSERT INTO \"brdgMediaCallsEvents\"
> (\"mediumId\", \"eventId\", rank) VALUES
> ((currval('\"tblMedia_mediumId_seq\"'),
> (currval('\"tblCallsEvents_eventId_seq\"'), $1)", array($ev_rank));
> echo pg_last_error($pg_connection);

Perhaps try:
$pg_pres_ins6 = pg_query_params( $pg_connection, 'INSERT INTO
`brdgMediaCallEvents` ( `mediumId`, `eventId`, `rank` ) VALUES (
\"val1\", \"val2\", \"val3\" ), array( "ev_rank" ) );

You need to read the document for this function because it looks like
you were mis-using it.

http://us3.php.net/manual/en/function.pg-query-params.php

>
> It's quite possible this is something very simple and I'm just not
> seeing it. I commented out this query because I thought perhaps it was
> failing because it was the last query in the program. The previous
> query did not fail when it became the last one.
>
> Thanks in advance for your help.
>
> Carol


--
Jason Gerfen
Systems Administration/Web application development
jason.gerfen@scl.utah.edu

Marriott Library
Lab Systems PC
295 South 1500 East
Salt Lake City, Utah 84112-0806
Ext 5-9810

"Tomorrow isn't promised so we live for today"

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Postgres query failing, not enough info for debugging...

Jason Gerfen wrote:
> Carol Walter wrote:
>> Hello,
>>
>> I'm using PHP 5 and PostgreSQL 8.3.6. I have a query that is failing
>> and I don't know how to troubleshoot the problem. The error message
>> that it is giving is quite vague. The error message is as follows:
>>
>>
>> Warning: pg_query_params() [function.pg-query-params]: Query failed:
>> ERROR: syntax error at end of input at character 156 in
>> /home/walterc/ssl/PHP/km_input_test2c.php on line 631
>> ERROR: syntax error at end of input at character 156
>>
>> The query that is failing looks like this...
>>
>> $pg_pres_ins6 = pg_query_params("INSERT INTO \"brdgMediaCallsEvents\"
>> (\"mediumId\", \"eventId\", rank) VALUES
>> ((currval('\"tblMedia_mediumId_seq\"'),
>> (currval('\"tblCallsEvents_eventId_seq\"'), $1)", array($ev_rank));
>> echo pg_last_error($pg_connection);
>

Whoops, ignore the last one I forget a single quote after the actual sql
string

> Perhaps try:
> $pg_pres_ins6 = pg_query_params( $pg_connection, 'INSERT INTO
> `brdgMediaCallEvents` ( `mediumId`, `eventId`, `rank` ) VALUES (
> \"val1\", \"val2\", \"val3\" )', array( "ev_rank" ) );
>
> You need to read the document for this function because it looks like
> you were mis-using it.
>
> http://us3.php.net/manual/en/function.pg-query-params.php
>
>>
>> It's quite possible this is something very simple and I'm just not
>> seeing it. I commented out this query because I thought perhaps it
>> was failing because it was the last query in the program. The
>> previous query did not fail when it became the last one.
>>
>> Thanks in advance for your help.
>>
>> Carol
>
>


--
Jason Gerfen
Systems Administration/Web application development
jason.gerfen@scl.utah.edu

Marriott Library
Lab Systems PC
295 South 1500 East
Salt Lake City, Utah 84112-0806
Ext 5-9810

"Tomorrow isn't promised so we live for today"

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Postgres query failing, not enough info for debugging...

--Apple-Mail-5-577368078
Content-Type: text/plain;
charset=US-ASCII;
format=flowed;
delsp=yes
Content-Transfer-Encoding: 7bit

Yes, I have read that documentation. I have other queries in this
same program with a similar structure that do work. One of these is
as follows:

$pg_pres_ins4 = pg_query_params("INSERT INTO \"brdgPresenters
\" (\"peopleId\",\"presentationEventId\") VALUES ($1,
currval('\"brdgPresentationsEvents_presentationEventId_seq
\"'))",array($choose_name));

The above query works fine.

Part of the problem is that the currval function requires that the
parameter be enclosed in single quotes. Because the person who created
this database used the upper-lower case naming convention, postgres
requires the datanames be inclosed in double quotes. I can either
enclose the whole query in single or double quotes, but either way one
of them will have to be escaped when using the currval function. I
don't know if that's where the problem lies, but it certainly
complicates the query more that it needs to be.

According to /www.postgresql.org/docs/8.3, the syntax is as follows:
nextval('foo') operates on sequence foo
nextval('FOO') operates on sequence foo
nextval('"Foo"') operates on sequence Foo

currval is analogous to nextval but works on the current value of the
sequence and not the next value of the sequence. The third form is
what I have to use because of the upper-lower case thing. I also
tried the "returning" clause on my insert query, but that doesn't seem
to work either.

Thanks,

Carol


On Jun 16, 2009, at 12:08 PM, Jason Gerfen wrote:

> Jason Gerfen wrote:
>> Carol Walter wrote:
>>> Hello,
>>>
>>> I'm using PHP 5 and PostgreSQL 8.3.6. I have a query that is
>>> failing and I don't know how to troubleshoot the problem. The
>>> error message that it is giving is quite vague. The error message
>>> is as follows:
>>>
>>>
>>> Warning: pg_query_params() [function.pg-query-params]: Query
>>> failed: ERROR: syntax error at end of input at character 156 in /
>>> home/walterc/ssl/PHP/km_input_test2c.php on line 631
>>> ERROR: syntax error at end of input at character 156
>>>
>>> The query that is failing looks like this...
>>>
>>> $pg_pres_ins6 = pg_query_params("INSERT INTO \"brdgMediaCallsEvents
>>> \" (\"mediumId\", \"eventId\", rank) VALUES
>>> ((currval('\"tblMedia_mediumId_seq\"'),
>>> (currval('\"tblCallsEvents_eventId_seq\"'), $1)", array($ev_rank));
>>> echo pg_last_error($pg_connection);
>
> Whoops, ignore the last one I forget a single quote after the actual
> sql string
>
>> Perhaps try:
>> $pg_pres_ins6 = pg_query_params( $pg_connection, 'INSERT INTO
>> `brdgMediaCallEvents` ( `mediumId`, `eventId`, `rank` ) VALUES
>> ( \"val1\", \"val2\", \"val3\" )', array( "ev_rank" ) );
>> You need to read the document for this function because it looks
>> like you were mis-using it.
>> http://us3.php.net/manual/en/function.pg-query-params.php
>>>
>>> It's quite possible this is something very simple and I'm just not
>>> seeing it. I commented out this query because I thought perhaps
>>> it was failing because it was the last query in the program. The
>>> previous query did not fail when it became the last one.
>>>
>>> Thanks in advance for your help.
>>>
>>> Carol
>
>
> --
> Jason Gerfen
> Systems Administration/Web application development
> jason.gerfen@scl.utah.edu
>
> Marriott Library
> Lab Systems PC
> 295 South 1500 East
> Salt Lake City, Utah 84112-0806
> Ext 5-9810
>
> "Tomorrow isn't promised so we live for today"
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>


--Apple-Mail-5-577368078--

RE: Postgres query failing, not enough info for debugging...

On 16 June 2009 15:57, Carol Walter advised:

> Hello,
>=20
> I'm using PHP 5 and PostgreSQL 8.3.6. I have a query that is failing
> and I don't know how to troubleshoot the problem. The error message
> that it is giving is quite vague. The error message is as follows:
>=20
>=20
> Warning: pg_query_params() [function.pg-query-params]: Query failed:
> ERROR: syntax error at end of input at character 156 in /home/walterc/
> ssl/PHP/km_input_test2c.php on line 631
> ERROR: syntax error at end of input at character 156
>=20
> The query that is failing looks like this...
>=20
> $pg_pres_ins6 =3D pg_query_params("INSERT INTO \"brdgMediaCallsEvents
> \" (\"mediumId\", \"eventId\", rank) VALUES
> ((currval('\"tblMedia_mediumId_seq\"'),
> (currval('\"tblCallsEvents_eventId_seq\"'), $1)", array($ev_rank));
> echo pg_last_error($pg_connection);

You have 2 more opening parentheses than close parentheses in that SQL
-- in both cases, the parenthesis immediately preceding currval should
be omitted.

Also, to ameliorate your quote hell, you might consider using a heredoc
(http://php.net/manual/language.types.string.php#language.ty pes.string.s
yntax.heredoc) for this:

$pg_pres_ins6 =3D pg_query_params(<< INSERT INTO "brdgMediaCallsEvents" ("mediumId", "eventId", rank)
VALUES (currval('"tblMedia_mediumId_seq"'),
currval('"tblCallsEvents_eventId_seq"'), $1)
SQL
, array($ev_rank));

Cheers!

Mike

--
Mike Ford, Electronic Information Developer,
C507, Leeds Metropolitan University, Civic Quarter Campus,=20
Woodhouse Lane, LEEDS, LS1 3HE, United Kingdom
Email: m.ford@leedsmet.ac.uk
Tel: +44 113 812 4730


To view the terms under which this email is distributed, please go to http:=
//disclaimer.leedsmet.ac.uk/email.htm

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php