ecpg 'set' failure using host vars
Hi,
I've been using the following set statement just fine under versions
prior to 8.3.3:
exec sql char *sm;
...
sm = "myschema"; // This may not always be constant
...
exec sql set search_path to :sm;
However, a recent upgrade from 8.2.7 to 8.3.3 (on FC9) caused the
'set' statement to suddenly start throwing the dreaded error:
ERROR: syntax error at or near "$1" in line 13256
Running in debug output mode we are seeing the following:
[22245]: ecpg_check_PQresult line 13256: Error: ERROR: syntax error at or near "$1" LINE 1: set search_path to $1 ^
[22245]: ecpg_check_PQresult line 13256: Error: ERROR: syntax error at or near "$1" LINE 1: set search_path to $1 ^
[22245]: ecpg_check_PQresult line 13256: Error: ERROR: syntax error at or near "$1" LINE 1: set search_path to $1 ^
[22245]: ecpg_check_PQresult line 13256: Error: ERROR: syntax error at or near "$1" LINE 1: set search_path to $1 ^
LINE 1: set search_path to $1
^
When the schema is replaced by a literal it works just fine, thusly:
exec sql set search_path to myschema;
Did we miss something in the porting between versions?
Any help would be greatly appreciated.
Bosco.
On Fri, Aug 15, 2008 at 08:47:49PM -0700, Bosco Rama wrote:
[22245]: ecpg_execute line 13256: QUERY: set search_path to $1 with 1 parameter on connection ecpgconn
[22245]: ecpg_execute line 13256: using PQexecParams
[22245]: free_params line 13256: parameter 1 = myschema
[22245]: ecpg_check_PQresult line 13256: Error: ERROR: syntax error at or near "$1"
LINE 1: set search_path to $1
Without checking the sources it seems as if PQexecParams is not able to handle a parameter in a set command. Can anyone confirm this?
Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org
Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!
Michael Meskes <meskes@postgresql.org> writes:
Without checking the sources it seems as if PQexecParams is not able to handle a parameter in a set command. Can anyone confirm this?
The backend only supports parameters in plannable statements, ie
SELECT/INSERT/UPDATE/DELETE. (Possibly DECLARE CURSOR, I'm too
lazy to check.)
regards, tom lane
Tom Lane wrote:
Show quoted text
Michael Meskes <meskes@postgresql.org> writes:
Without checking the sources it seems as if PQexecParams is not able to handle a parameter in a set command. Can anyone confirm this?
The backend only supports parameters in plannable statements, ie
SELECT/INSERT/UPDATE/DELETE. (Possibly DECLARE CURSOR, I'm too
lazy to check.)regards, tom lane
Sorry about the 'false start' before. :-(
Tom Lane wrote:
Michael Meskes <meskes@postgresql.org> writes:
Without checking the sources it seems as if PQexecParams is not able
to handle a parameter in a set command. Can anyone confirm this?
The backend only supports parameters in plannable statements, ie
SELECT/INSERT/UPDATE/DELETE. (Possibly DECLARE CURSOR, I'm too
lazy to check.)
Thanks for the hint guys. I used a prepare/execute pair instead and
it worked a treat. More cumbersome but it gets the job done.
Thanks again.
Bosco.
Without checking the sources it seems as if PQexecParams is not able
to handle a parameter in a set command. Can anyone confirm this?
The backend only supports parameters in plannable statements, ie
SELECT/INSERT/UPDATE/DELETE. (Possibly DECLARE CURSOR, I'm too
lazy to check.)Thanks for the hint guys. I used a prepare/execute pair instead and
it worked a treat. More cumbersome but it gets the job done.
I will fix this as soon as my time permits. There is logic there in ecpg to
handle parameters on the client side.
Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org
Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!
On Fri, Aug 15, 2008 at 08:47:49PM -0700, Bosco Rama wrote:
exec sql char *sm;
...
sm = "myschema"; // This may not always be constant
...
exec sql set search_path to :sm;
Could you please try the attached patch and tell me whether it works for you? I will then check this in into 8.3 so that the next stable release works again as it should.
Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org
Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!
Attachments:
ecpg_set.difftext/x-diff; charset=us-asciiDownload
--- pgsql-ecpg/preproc/preproc.y 2008-08-18 16:56:11.000000000 +0200
+++ pgsql/src/interfaces/ecpg/preproc/preproc.y 2008-08-19 12:29:28.000000000 +0200
@@ -1247,7 +1247,16 @@
;
var_value: opt_boolean { $$ = $1; }
- | AllConst { $$ = $1; }
+ | AllConst { /* we have to check for a variable here because it has to be
+ replaced with its value on the client side */
+ if ($1[1] == '$')
+ {
+ $$ = make_str("$0");
+ free($1);
+ }
+ else
+ $$ = $1;
+ }
| ColId { $$ = $1; }
;
Hi Michael,
Michael Meskes wrote:
On Fri, Aug 15, 2008 at 08:47:49PM -0700, Bosco Rama wrote:
exec sql char *sm;
...
sm = "myschema"; // This may not always be constant
...
exec sql set search_path to :sm;Could you please try the attached patch and tell me whether it works
for you? I will then check this in into 8.3 so that the next stable
release works again as it should.
Thanks for working on this.
Unfortunately I'm just an applications person and our systems use the
FC9 distribution's pre-compiled packages only (it's a sysadmin thing).
I asked them about it and they said (and I quote): "When it becomes
part of the FC9 distribution they will 'consider' (my emphasis)
upgrading the packages." *sigh*
I really appreciate the effort and the fix but for now I have to be
satisfied with the work-around you guys suggested previously. :-(
Bosco.
I really appreciate the effort and the fix but for now I have to be
satisfied with the work-around you guys suggested previously. :-(
I see. 8.3.4 will have the fix anyway. :-)
Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes@jabber.org
Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!