PostgreSQL Discussion - Re: [Re] wrong protocol sequence?

Author

Re: [Re] wrong protocol sequence?

Андрей

2005-09-22, 11:23 am

John DeSoi wrote:

> On Sep 22, 2005, at 10:11 AM, Андрей wrote:
>
>
>
> I think I recall having some problems around this also. But I don't
> think there is any reason to need Parse -> Sync anyway. If you just
> want to prepare, use Parse -> Describe -> Sync. For executing, Parse
> (if not already parsed) -> Bind -> Describe -> Execute -> Sync.
>
> John DeSoi, Ph.D.
> http://pgedit.com/
> Power Tools for PostgreSQL

The reason is to get parameters description (if there are any in
query) before binding them to the backend. For example 'select * from
table_name where column_name = $1'. I would like to get required type of
parameter $1 to bind later. But I can't be sure at prepare time, that
I'll need such description in future.

Thanks for your answer,
Andrei

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

John DeSoi

2005-09-22, 1:23 pm

On Sep 22, 2005, at 11:16 AM, Андрей wrote:

> The reason is to get parameters description (if there are any in
> query) before binding them to the backend. For example 'select *
> from table_name where column_name = $1'. I would like to get
> required type of parameter $1 to bind later. But I can't be sure at
> prepare time, that I'll need such description in future.

It has been a while since I looked at this, but I think what I just
said will give you that (Parse -> Describe -> Sync).

If you are familiar with Lisp, I have included some code below which
might help you. The xp-send function adds a sync message and flushes
the output.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

; parse a statement and return parameter and row info
(defun xp-prepare (connection sql &key (name "") parameter-types)
(declare (type string sql)
(type string name))
(let ((stream (ref connection))
(result nil)
(row-info nil)
(param-info nil)
(err nil)
(param-count (length parameter-types)))
;; parse
(write-byte +parse+ stream)
(write-int32 (+ +int32-length+ (length name) 1 (length sql) 1
+int16-length+ (* +int32-length+ param-count)) stream)
(write-cstring name stream)
(write-cstring sql stream)
(write-int16 param-count stream)
(loop for pt in parameter-types do (write-int32 pt stream))
;; describe the statement
(write-byte +describe+ stream)
(write-int32 (+ +int32-length+ 1 (length name) 1) stream)
(write-byte #.(char-code #\S) stream)
(write-cstring name stream)
(xp-send stream) ;sync
(loop for op = (read-byte stream) do
(case op
(#.+parse-complete+
(read-empty-response stream)
(setf result t))
(#.+parameter-description+
(setf param-info (read-parameter-description stream)))
(#.+row-description+
(setf row-info (read-row-description stream)))
(#.+no-data+ ;not a select statment, get this instead of
row-description
(read-empty-response stream))
(#.+ready-for-query+
(ready-for-query connection)
(return t))
(t
(setf err (common-op-handler connection op)))))
(values result param-info row-info err)))

PGLISP 4 > (xp-prepare *connection* "select * from test where ab = $1")
T
#(25)
#(#<COLSPEC ab (18098 1 25) 100DF68B> #<COLSPEC ac (18098 2 25)
100DF617> #<COLSPEC coolstuff (18098 3 25) 100DF59B> #<COLSPEC vcar
(18098 4 1043) 100DF563> )
NIL

=== psql 3 ===
Table "public.test"
Column | Type | Modifiers
-----------+-----------------------+-----------
ab | text |
ac | text |
coolstuff | text |
vcar | character varying(20) |

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Sponsored Links