Thread: Suggestion for parameterized queries

Suggestion for parameterized queries

From
Sebastien FLAESCH
Date:
Hi,

Parameterized queries (PREPARE/EXECUTE), is a great thing, but it
would even be better if the DECLARE CURSOR could use a PREPAREd
statement, to declare a "server-side" cursor with a parameterized
query, to use the benefits of DECLAREd cursors (no fetching of all the
result set on the client, use the binary mode, use the scrollable
option or the FOR UPDATE option).

Something like:

     PREPARE s1 (type,..) AS SELECT ....
     DECLARE c1 (value,...) CURSOR FROM s1
     FETCH c1

or have a new OPEN instruction to pass parameters:

     PREPARE s1 (type,..) AS SELECT ....
     DECLARE c1 CURSOR FROM s1
     OPEN c1 USING (value,...)
     FETCH c1

That would be an incredible improvement to make PostgreSQL compete
with other databases like Informix, Oracle, Db2 UDB, SQL Server (I
wrote some drivers for these database servers).

For now we cannot use DECLARE CURSOR in our PostgreSQL driver because
of this limitation.... I could build the SQL statement with literal
values, but since you have now parametrized queries I would prefer to
use that...

Thank you!

Sebastien FLAESCH
Database driver writer at www.4js.com