Thread: deallocating prepared statements
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
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,
-- DanielPostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org
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@jabber.org Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!
>From: Michael Meskes [mailto:meskes@postgresql.org] >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
>From: pgsql-interfaces-owner@postgresql.org >[mailto:pgsql-interfaces-owner@postgresql.org] 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
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, -- DanielPostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
>-----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
[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@jabber.org Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL!