[Sqlrelay-discussion] PostgreSQL, bytea and stored procedures

[Ville S. <vs...@eb...>]

Hi,

I've been trying to use PostgreSQL bytea column type with SQL Relay.
I think PostgreSQL functions support bytea input parameters and return
type:

http://www.postgresql.org/docs/8.0/static/plpgsql.html#PLPGSQL-ARGS-RESULTS

(Although it doesn't clearly say if bytea is supported or not.)

I did notice that SQL Relay documentation says:
"Blob/Clob bind variables are only supported in Oracle 8i or higher."

Why are they not supported with PostgreSQL?

I tried anyway! :) I installed "psycopg" module so I could quote the
binary data, which is generated with cPickle.dumps(data, 2). This is
how blobs are used in Python Cookbook, 2nd ed. chapter 7.11.

I tried to call the PostgreSQL function like this (slightly simplified):

cur.prepareQuery("select * from sesmgr.create_session(:sid,:sdata,:suser)")
cur.inputBind('sid', '7e5578193932c84fb66c8d848d150d33')
cur.inputBind('sdata', psycopg.Binary(cPickle.dumps(data, 2)))
cur.inputBind('suser', 'vsi')

This returns an error (cur.getErrorMessage):

ERROR:  syntax error at or near ":" at character 72

I *think* this is because SQL Relay doesn't actually bind the "sdata"
input variable. If I turn connection debug on, I can see in the log file:

06/29/2005 16:02:04 BST connection [26828] : select * from sesmgr.create_se=
ssion(:sid,:sdata,:suser)
06/29/2005 16:02:04 BST connection [26828] :            getting query succe=
eded
06/29/2005 16:02:04 BST connection [26828] :            getting input binds=
=2E..
06/29/2005 16:02:04 BST connection [26828] :                            :si=
d
06/29/2005 16:02:04 BST connection [26828] :                            STR=
ING
06/29/2005 16:02:04 BST connection [26828] :                            7e5=
578193932c84fb66c8d848d150d33
06/29/2005 16:02:04 BST connection [26828] :                            :su=
ser
06/29/2005 16:02:04 BST connection [26828] :                            STR=
ING
06/29/2005 16:02:04 BST connection [26828] :                            vsi
06/29/2005 16:02:04 BST connection [26828] :            done getting input =
binds


If I remove the psycopg.Binary call and pass the cPickle.dumps value
as it is, SQL Relay binds the "sdata" as a STRING:

06/29/2005 15:58:50 BST connection [26828] : select * from sesmgr.create_se=
ssion(:sid,:sdata,:suser)
06/29/2005 15:58:50 BST connection [26828] :            getting query succe=
eded
06/29/2005 15:58:50 BST connection [26828] :            getting input binds=
=2E..
06/29/2005 15:58:50 BST connection [26828] :                            :si=
d
06/29/2005 15:58:50 BST connection [26828] :                            STR=
ING
06/29/2005 15:58:50 BST connection [26828] :                            c0d=
b86a43be8f9c6ae47af9dadb980a1
06/29/2005 15:58:50 BST connection [26828] :                            :sd=
ata
06/29/2005 15:58:50 BST connection [26828] :                            STR=
ING
06/29/2005 15:58:50 BST connection [26828] :                            =C3=
=AF=C2=BF=C2=BD}qUmydataq]q(U1U2U3es.
06/29/2005 15:58:50 BST connection [26828] :                            :su=
ser
06/29/2005 15:58:50 BST connection [26828] :                            STR=
ING
06/29/2005 15:58:50 BST connection [26828] :                            vsi
06/29/2005 15:58:50 BST connection [26828] :            done getting input =
binds

But then cPickle.loads cannot read the value anymore:

cPickle.UnpicklingError: invalid load key, '\'.


Is there a way to use bytea datatype with SQL Relay and PostgreSQL
functions? Or do I have to use normal INSERT statements instead?

Thanks for any advice,

Ville