Обсуждение: 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_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.

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
confirmthis?
 

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
anyoneconfirm 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:
> 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
anyoneconfirm 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
anyoneconfirm 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
thatthe 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!

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!