ecpg 'set' failure using host vars

[Bosco Rama]

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_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
                             ^

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.

--
Sent via pgsql-interfaces mailing list (pgsql-interfaces@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-interfaces

[Michael Meskes-3]

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@...
Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!

--
Sent via pgsql-interfaces mailing list (pgsql-interfaces@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-interfaces

[Tom Lane-2]

Michael Meskes <meskes@...> 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

--
Sent via pgsql-interfaces mailing list (pgsql-interfaces@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-interfaces

[Bosco Rama]

Tom Lane wrote:
> Michael Meskes <meskes@...> 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
>

--
Sent via pgsql-interfaces mailing list (pgsql-interfaces@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-interfaces

[Bosco Rama]

Sorry about the 'false start' before. :-(

Tom Lane wrote:
> Michael Meskes <meskes@...> 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.

--
Sent via pgsql-interfaces mailing list (pgsql-interfaces@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-interfaces

[Michael Meskes-3]

>>> 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@...
Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!

--
Sent via pgsql-interfaces mailing list (pgsql-interfaces@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-interfaces

[Michael Meskes-3]

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@...
Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!

---

--- 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; }
  ;
 

---

--
Sent via pgsql-interfaces mailing list (pgsql-interfaces@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-interfaces

[Bosco Rama]

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.

--
Sent via pgsql-interfaces mailing list (pgsql-interfaces@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-interfaces

[Michael Meskes-3]

> 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@...
Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!

--
Sent via pgsql-interfaces mailing list (pgsql-interfaces@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-interfaces