Thread: using PREPAREd statements in CURSOR
Hello!
I'm connecting via libpq and want to
use prepared statements in a cursor.
Is there a sample somewhere, since I cannot get
it to work.
sebjlun=# \d ssignal
Table "public.ssignal"
Column | Type | Modifiers
---------+---------------+-----------
ssignam | character(12) | not null
ssigdes | character(30) | not null
ssopcid | character(40) |
sstatyp | integer | not null
sstatid | integer | not null
ssigtyp | integer | not null
ssigadd | integer | not null
ssigran | integer | not null
Indexes:
"ssignalp1" PRIMARY KEY, btree (ssignam)
"ssignali3" btree (sstatyp, sstatid)
sebjlun=# prepare test as
sebjlun-# select * from SSIGNAL where SSTATYP=$1 and SSTATID=$2 order by SSIGNAM
sebjlun-# ;
PREPARE
sebjlun=# execute test(4,6);
ssignam | ssigdes | ssopcid
| sstatyp | sstatid | ssigtyp | ssigadd | ssigran
--------------+--------------------------------+--------------------------------
----------+---------+---------+---------+---------+---------
CD_PLC_SS | Counter delay output PLC |
| 4 | 6 | 2 | 2103 | 16
CD_SS_PLC | Counter delay input SS |
| 4 | 6 | 2 | 2003 | 16
CN_PLC_SS | Counter number output PLC |
| 4 | 6 | 2 | 2102 | 16
CN_SS_PLC | Counter Number input SS |
| 4 | 6 | 2 | 2002 | 16
....
so far so good
sebjlun=# declare cursor ctest for test(4,6);
ERROR: syntax error at or near "ctest"
LINE 1: declare cursor ctest for test(4,6);
^
sebjlun=#
How would I express that?
Combining cursors with parameter seems not to be the way either
sebjlun=# Declare C240 cursor for select * from SSIGNAL
where SSTATYP=$1 and SSTATID=$2 order by SSIGNAM;
ERROR: there is no parameter $1
sebjlun=#
/Björn
=?ISO-8859-1?Q?Bj=F6rn_Lundin?= <b.f.lundin@gmail.com> writes:
> I'm connecting via libpq and want to
> use prepared statements in a cursor.
You can't.
If you're just interested in fetching a large query result in sections,
there is protocol-level support for doing that without an explicit
cursor, but libpq doesn't expose that feature because it doesn't fit
into its API very well. (I think JDBC does expose it, but that doesn't
help you if you want to code in C...) A well-thought-out API proposal
would probably be favorably received.
If you wanted some other cursor feature like scrollability, the whole
thing is a bit problematic, because the prepared statement's plan was
not made with the intention of using it that way (yes, DECLARE CURSOR
is planned differently than a plain select).
regards, tom lane
28 jun 2007 kl. 16.45 skrev Tom Lane:
Björn Lundin <b.f.lundin@gmail.com> writes:I'm connecting via libpq and want touse prepared statements in a cursor.You can't.
That explains why I could not find an example...
If you're just interested in fetching a large query result in sections,
No, I'm writing a sql-binding to libpq, for use with a current
system, that is written with Oracle as db.
The processes all use a sql-binding to Oracle,
and in order not to rewrite them, I want to keep the cursor handling.
The processes are filled with code snippets like this:
prepare(Statement1,"select col3,col4 from table_a where col1 = :COL_A and col2 = :COL_B");
set(Statement1,"COLA_A",10);
set(Statement1,"COLA_B","ABC");
open_cursor(Statement1)
loop
fetch(Statement1, end_of_set);
exit when end_of_set;
get(Statement1,"col3",var3);
get(Statement1,"col4",var4);
end loop;
close_cursor(Statement);
--use var3 and var4 here
So I redesigned, and use plain strings, that I pass to
libpq. They are build on the fly.
Since I prefer keeping the cursor, over the prepared statements,
is there any performance gain I can do,
besides fetching say 100 rowa at a time, rather than 1.
(The fetch above is a wrapper to libpq's fetch)
I'm thinking, is it better to explicitly cast the bind variables in the statement string?
The above statement would be sent to libpq as
declare cursor xyz as select col3,col4 from table_a where col1 = 10 and col2 = 'ABC'
Would it be better to send it as
declare cursor xyz as select col3,col4 from table_a where col1 = 10::integer and col2 = 'ABC::text'
I will use integer, float, character(n), date, time w/o tz
(should perhaps be 'ABC::character(3)')
Or should I use say int4 instead of integer?
The character(3) are constrained by the host language,
ie Ada.
A well-thought-out API proposalwould probably be favorably received.
Hmm, I would think that would be over my head...
But, in a way I'm glad that the PQPrepare is not an option,
passing variables in an array from Ada to C would
give at least some headache.
The 'set' approach would be easier, when interfacing from other languages, I think.
(yes, DECLARE CURSORis planned differently than a plain select).
And which way is to be preferred?
/Björn