deallocating prepared statements

[erh]

I've been having some trouble trying to use the libpq api.  Among other
problems (such as '?' style query parameters not working), I can't
figure out how to deallocate prepared statements.  I tried using esql to
do it (since the docs say that "it is only for use in embedded sql"):

exec sql begin declare section;
    const char *stmtName = "exec_stmt";
exec sql end declare section;
exec sql deallocate :stmtName;

but that didn't do anything useful.  Doing a subsequent PQprepare call
still complained that the statement was still present.

So, instead I tried using the libpq interface:

struct connection *ecpg_conn = ECPGget_connection(NULL);
PGconn *conn = ecpg_conn->connection;
Oid deallocTypes[] = { VARCHAROID };
const char *deallocValues[] = { stmtName };
int deallocLengths[] = { strlen(stmtName) } ;
int deallocFormats[] = { 0 } ;

PGresult *dealloc_result;
dealloc_result = PQexecParams(conn, "DEALLOCATE $1", 1, deallocTypes,
deallocValues, deallocLengths, deallocFormats, 1);

But I get this error:
 syntax error at or near "$1" at character 12

What am I doing wrong?

eric

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

[Daniel Verite]

        Haszlakiewicz, Eric wrote:

> So, instead I tried using the libpq interface:
>
> struct connection *ecpg_conn = ECPGget_connection(NULL);
> PGconn *conn = ecpg_conn->connection;
> Oid deallocTypes[] = { VARCHAROID };
> const char *deallocValues[] = { stmtName };
> int deallocLengths[] = { strlen(stmtName) } ;
> int deallocFormats[] = { 0 } ;
>
> PGresult *dealloc_result;
> dealloc_result = PQexecParams(conn, "DEALLOCATE $1", 1, deallocTypes,
> deallocValues, deallocLengths, deallocFormats, 1);
>
> But I get this error:
>  syntax error at or near "$1" at character 12

The prepared statement's name is not a literal, and in general
parameterized queries don't accept parameters beyond literals. So you
need to insert the name directly into the SQL command text.

 Best regards,
--
 Daniel
 PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org

--
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 Tue, Dec 02, 2008 at 01:22:46PM -0600, Haszlakiewicz, Eric wrote:
> figure out how to deallocate prepared statements.  I tried using esql to
> do it (since the docs say that "it is only for use in embedded sql"):
>
> exec sql begin declare section;
>     const char *stmtName = "exec_stmt";
> exec sql end declare section;
> exec sql deallocate :stmtName;

This will not work unless you do more in ECPG. Without preparing a statement,
having a connection etc. there is no way to deallocate the statement.

If you mix libpq and ecpg you should know what you do.

Michael
--
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot 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

[erh]

>From: Michael Meskes [mailto:meskes@...]
>On Tue, Dec 02, 2008 at 01:22:46PM -0600, Haszlakiewicz, Eric wrote:
>> figure out how to deallocate prepared statements.  I tried
>using esql to
>> do it (since the docs say that "it is only for use in embedded sql"):
>>
>> exec sql begin declare section;
>>     const char *stmtName = "exec_stmt";
>> exec sql end declare section;
>> exec sql deallocate :stmtName;
>
>This will not work unless you do more in ECPG. Without
>preparing a statement,
>having a connection etc. there is no way to deallocate the statement.
>
>If you mix libpq and ecpg you should know what you do.

So why is preparing a statement in ecpg different from preparing one
with libpq?  Isn't the actual prepare done on the server?

eric


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

[erh]

>From: pgsql-interfaces-owner@...
>[mailto:pgsql-interfaces-owner@...] On Behalf Of
>Daniel Verite
> Haszlakiewicz, Eric wrote:
>> dealloc_result = PQexecParams(conn, "DEALLOCATE $1", 1, deallocTypes,
>> deallocValues, deallocLengths, deallocFormats, 1);
>>
>> But I get this error:
>>  syntax error at or near "$1" at character 12
>
>The prepared statement's name is not a literal, and in general
>parameterized queries don't accept parameters beyond literals. So you
>need to insert the name directly into the SQL command text.
>

Well, that sucks.  Now I need to worry about quoting and escaping. :(
Is there a limit of what characters can be used in statement names?
Neither the docs for PQprepare(), nor the docs for the prepare statement
say anything about that.

eric

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

[Daniel Verite]

        Haszlakiewicz, Eric wrote:

> >The prepared statement's name is not a literal, and in general
> >parameterized queries don't accept parameters beyond literals. So
you
> >need to insert the name directly into the SQL command text.
> >
>
> Well, that sucks.  Now I need to worry about quoting and escaping. :(
> Is there a limit of what characters can be used in statement names?
> Neither the docs for PQprepare(), nor the docs for the prepare
statement
> say anything about that.

I think the statement's name is an "identifier", just like a table or
column name.
So it has to follow the rules defined for identifiers in the "SQL
syntax" chapter.


 Best regards,
--
 Daniel
 PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org

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

[erh]

>-----Original Message-----
>Daniel Verite
> Haszlakiewicz, Eric wrote:
>
>> >The prepared statement's name is not a literal, and in general
>> >parameterized queries don't accept parameters beyond literals. So
>you
>> >need to insert the name directly into the SQL command text.
>> >
>>
>> Well, that sucks.  Now I need to worry about quoting and escaping. :(
>> Is there a limit of what characters can be used in statement names?
>> Neither the docs for PQprepare(), nor the docs for the prepare
>statement
>> say anything about that.
>
>I think the statement's name is an "identifier", just like a table or
>column name.
>So it has to follow the rules defined for identifiers in the "SQL
>syntax" chapter.
>
>
> Best regards,
>--
> Daniel

ah.. yes, that makes sense.   The syntax for identifiers is relatively
simple
(http://www.postgresql.org/docs/8.2/interactive/sql-syntax-lexical.html#
SQL-SYNTAX-IDENTIFIERS), so I _don't_ need to worry about quoting all
that much, just checking for invalid characters.  That's much easier.
Thanks for the pointer!

eric

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

[Michael Meskes-3]

[Sorry, I seem to not have answered yet, I thought I had. ]

On Wed, Dec 03, 2008 at 11:38:27AM -0600, Haszlakiewicz, Eric wrote:
> So why is preparing a statement in ecpg different from preparing one
> with libpq?  Isn't the actual prepare done on the server?

The actual prepare yes, but there are internal ecpg structures involved too,
e.g. it needs the connection involved. Without getting the connection
information there is no way to deallocate the statement.

Michael

--
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot 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