[PYTHON DB-SIG] TEXT and IMAGE datatypes in sybase ct module

[Peter Godman]

Hi Folks.  

For some time I've been working on a DBAPI-compliant module for Sybase
ct-library.  ct-library is the more modern of Sybase's API's (the other
being db-library).  ct-library has the very nice feature that Sybase
provides an unsupported linux version of the library free of charge, hence
I can work on it in my free time. 

I have a question regarding my treatment of TEXT and IMAGE types in the
module (equivalent, I believe, to Oracle LONG and LONG RAW columns).  As I
understand the specification, fetching these datatypes should return
Python strings, whereas supplying them as a parameter should be done using
dbi wrappers or just a string for TEXT.  This is fine.  However, Sybase
has a special interface for getting data into and out of long columns.  In
essence, if one wishes to insert a long value into a long column, one must
first fetch the CS_IODESC structure (similar to a file descriptor) using a
SELECT statement, and then send data down this channel to fill in the long
column.

Imagining, then, that a user wishes to add a row to the database, and
supplies an input variable corresponding to a TEXT column.  In order to
fulfill this request, the sybase module would have to add the row, somehow
figure out how to fetch the row again (which can't be done if there's no
primary key (there are no row ids in Sybase)), obtain the CS_IODESC, and
send the contents of the variable. I believe such an interface would be
very difficult to implement (Updates are similarly difficult), and be
unclean. This leaves me with two alternatives.  I can either provide an
optional file-like interface to CS_IODESCs, so that fetching a row with a
TEXT or IMAGE can return a file-like object suitable for reading and
writing, or I can stipulate that users specify the contents of long
columns in-line in the SQL (which limits long contents to 100K (maximum
query length)).  I would prefer the former.  From what I know of
Postgres, it seems it may be appropriate there also.

For the file solution I'm imagining something like

>>> cursor.execute('SELECT phone, description FROM phones')
>>> result = cursor.fetchone()
>>> print result
['5551212', <iodesc for read/write at 8108ca8>]
>>> print result[1].read()
This is the description
>>> result[1].write('information')

Does anyone have any thoughts on this?  Do the other databases' APIs not
have this limitation?

While I'm asking questions, I should also mention that in Sybase, there
are lots of behaviours one may configure on a per-connection basis.  For
example, one may specify whether COMMIT/ROLLBACK closes open cursors, and 
specify which ANSI isolation level the connection is operating in. I'd
like to include support for these options, but from the spec don't see a
portable way of doing it.  Anyone have any suggestions about this?  I
could imagine something like

>>> import ctsybase
>>> connection = ctsybase.ctsybase()
>>> connection.options['CLOSE_CURSOR_ON_XACT'] = 1
>>> connection.options['ISOLATION_LEVEL'] = 3
>>> connection.options['FOO'] = 1
SybaseError:  'FOO' is not a valid option for connection
>>> print connection.options.keys()
['CLOSE_CURSOR_ON_XACT', 'MAX_COMMANDS', 'MAX_TEXTLEN', 'ISOLATION_LEVEL']


Thanks in advance for any ideas, suggestions, etc.

Peter Godman
pjg@vgi.com


_______________
DB-SIG  - SIG on Tabular Databases in Python

send messages to: db-sig@python.org
administrivia to: db-sig-request@python.org
_______________