Re: 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 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(at)jabber(dot)org
Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!

Michael Meskes <meskes(at)postgresql(dot)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:
> Michael Meskes <meskes(at)postgresql(dot)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(at)postgresql(dot)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(at)jabber(dot)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(at)jabber(dot)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(at)jabber(dot)org
Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!