Re: [Re] wrong protocol sequence? - Mailing list pgsql-general

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) |