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

[Greg Stein]

Oracle has a similar problem: you must bind the column appropriately.
This means that you must know before binding whether you'll bind as a
regular string (against, say, a VARCHAR column) or as a LONG (RAW)
column. Oracle does allow you, however, to bind the LONG value directly
for an insert/update.

Note: the existing modules return LONG columns (and their equivalents)
in dbiRaw instances. resultcolumn.value contains a Python string with
the actual data.

Handling of large blobs has been the trickiest part of any of the DBAPI
modules. Given the unique constraints of Sybase (this hasn't been seen
so far), then I think your solution sounds "best." In other words, move
the problem back to the application.

The DBAPI isn't meant to make clients of it truly portable across all
databases. That simply can't be done. There are too many differences in
SQL text, bindings, capabilities, restrictions, etc. Instead, it is
meant to provide a uniform method of interaction across the databases to
as great an extent as possible. An application that requires more
portability can code that at the Python level given their data handling
requirements and the set of target databases. This policy is close to
that of Python's policy towards C APIs: expose it and do no more. We
actually do more, but we can't do it all. Long and short: I think you
should be fine with your suggested approach.

Regarding the options: simply make them attributes of the connection or
cursor (whichever is appropriate), rather than dealing with an option
dictionary. Again, a client will know it is dealing with the Sybase
module and that those attributes will be present.

Whatever options that you *do* add, though, it would be nice to reflect
those in the DBAPI spec itself. We can label them all as Sybase
specific, but other module maintainers might see a handy parallel with
their modules and we could move some of the attributes towards
commonality. Having them doc'd, whether specific or not, will be a great
boon.

-g

	-----Original Message-----
	From:	Peter Godman [SMTP:pgodman@vgi.com]
	Sent:	Tuesday, April 15, 1997 11:11 PM
	To:	db-sig@python.org
	Subject:	[PYTHON DB-SIG] TEXT and IMAGE datatypes in
sybase ct module

	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
	_______________

_______________
DB-SIG  - SIG on Tabular Databases in Python

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