[python-sybase] Re: Question about Sybase Module 0.36 Output Parameter Handling

[Dave Cole]

Ooops - forgot to respond to the list as well...

Bradley Feldman wrote:

 > Hi Dave:
 >
 > Thanks so much for providing your great Sybase module for Python!  I've
 > installed it and it works great so far.


Thanks for the response.

 > I have a question about the handling of Output Parms which is a new 
feature
 > of 0.36.  I understand how to pass the output parm syntax to the database
 > within the callproc as given in the release notes, but how to I see the
 > value passed back?  This seems simple, but there is no example of 
evaluating
 > the resulting parameter and I can't seem to figure it out.


The problem is due to a shortcoming in the Sybase CT library.  There is 
no way to ask the library whether parameters to a query are input or 
output.  If you get it wrong then the query fails.  So in the absence of 
a way to automatically determine the direction of an argument, the user 
must tell the module.

The OUTPUT() function uses the passed value to construct a buffer that 
will accept a value of the same type from the Sybase CT library in 
response to a query.

 > For example, you give:
 >
 > C.callproc ('testproc', {'@parm1': 'value1', '@outputparm':
 > Sybase.OUTPUT(1)})
 >
 > If I print the callproc object after it executes I get back something 
like:
 > {'@parm1':'value1','@outputparm': <DataBufType object at 0x2a394999>}


This is showing you the parameters that will be passed to the CT 
library.  The results will not be available until you try to fetch 
results from the cursor.

 > So how to I examine the contents of the DataBufType object, using 
python db
 > api syntax?


The result should come back as a row via the fetchone() method on the 
cursor.

 > Finally, what does the "1" in Sybase.OUTPUT(1) mean?   A buffer size of 1
 > byte?  A value of 1 contained in one byte?  Can I simply pass a variable
 > reference instead, such as Sybase.OUTPUT(thisVar)?


This is not explained in the documentation.  It probably should be I 
suppose.

Communication with the Sybase server is via the CT library.  The CT 
library uses two objects for passing values to, and accepting values 
from the server; a CS_DATAFMT structure, and a data buffer.  The 
CS_DATAFMT structure describes (among other things) the type, size, and 
direction of a related data buffer.  The data buffer is simply a pointer 
to an area of memory that matches the description in the CS_DATAFMT.

For passing values to Sybase all you need to do is place the value in a 
data buffer and describe it using a CS_DATAFMT.  You can see this in 
operation in the Cursor.callproc() method.  It takes plain values and 
places them in a data buffer via DataBuf() constructor.

      buf = DataBuf(value)

The DataBuf object contains an embedded CS_DATAFMT structure that is 
initialised using the type and size of the Python object that was passed 
to the constructor.  It also contains an allocated area of memory into 
which the value passed to the constructor is copied.

      buf.name = name

After constructing The Cursor.callproc() method then assigns the 
specified parameter name to the .name member of the DataBuf.  This is 
sufficient to create the CS_DATAFMT and data buffer to pass a parameter 
to Sybase.

      cmd.ct_param(buf)


As mentioned above, Sybase does not provide any mechanism to query the 
CT Library about whether a parameter to a stored procedure is for input 
or output.  If you get it wrong then the query will fail.  To get around 
this, the OUTPUT() function establishes a DataBuf object in the same way 
as the Cursor.callproc() method, but then in addition, sets the .status 
attribute of the embedded CS_DATAFMT to CS_RETURN.  This tells Sybase 
that the buffer is intended to receive a return value from the query 
rather than pass a value to the query.


For column values returned by plain queries the Sybase module asks the 
Sybase CT Library the format of each column and then constructs a 
DataBuf to receive that value.  The _row_bind() function in the Sybase 
module shows this:

     cmd.ct_res_info(CS_NUMDATA)

Asks CT how many columns are in the query result.

     cmd.ct_describe(i + 1)

Asks CT to describe one of the columns.  The second return value is a 
CS_DATAFMT object.

     cmd.ct_bind(i + 1, fmt)

This constructs a DataBuf from the CS_DATAFMT and tells CT to bind the 
specified column to the area of memory in the DataBuf.  The second 
return value is the constructed DataBuf object.


So after all of that, if you want to be a bit more direct in 
constructing output parameter buffers to stored procedures you do use 
something like this:

fmt = Sybase.CS_DATAFMT()
fmt.datatype = Sybase.CS_VARCHAR_TYPE
fmt.maxlength = 1024
fmt.count = 1
buf = Sybase.DataBuf(fmt)
buf.status = Sybase.CS_RETURN

The buf can then be passed like this:

  C.callproc ('testproc', {'@parm1': 'value1', '@outputparm': buf})

As explained above, the return parameter should be available as a normal 
row returned using one of the Cursor.fetch*() methods.

Note that when you fiddle with DataBuf objects you must hold a reference 
to that buffer until the CT library has finished using the memory in the 
buffer.  This is transparently done for you by the code in Sybase.py.

If you start programming directly to the sybasect extension module then 
you will need to manage your own DataBuf references.

 > Thanks so much for your help, in advance.


No problem.  Is a distraction from the work I am supposed to be doing :-) .

- Dave

-- 
http://www.object-craft.com.au