[DB-SIG] About LOB

[Kwon, Chan Young]

Hi,

I am implementing new DB API for new Database according to DB API 2.0. (as C-Extension)
There is no specification about LOB in DB API 2.0
Especially, I need LOB object for piecewise LOB writing/reading after query execution.
So I defined my own LOB object.
I tried to make simple and easy interface for LOB.

Let me introduce my DB API.(red lines are about LOB handling)
This is used in my company.
I hope this document and example could help you to define LOB class in NEXT DB API 3.0 SPEC.

API
Module Interface
Attribute       Description
connect(parameters...)  Constructor for creating a connection to the database.Returns a Connection Object. It takes a number of parameters which are database dependent.
-Example
*

dbapi.connect(address='localhost', port=30415, user='system', password='manager') → common usage
LOB()   Return LOB type object.
Date(year,month,day)    Return datetime type object holding a date value.
Time(hour,minute,second,millisecond=0)  Return datetime type object holding a time value.
Timestamp(year,month,day,hour,minute,second,millisecond=0)      Return datetime type object holding a date+time value.
DateFromTicks(ticks)    Return datetime type object holding a date value.
TimeFromTicks(ticks)    Return datetime type object holding a time value.
TimestampFromTicks(ticks)       Return datetime type object holding a date+time value.
Binary(string)  Return an buffer type object holding a binary string value.
Exceptions
Attribute       Description
Warning         Exception raised for important warnings.
Error   Exception that is the base class of all other error exceptions.
-error information is saved as tuple
*

errobject[0] → contains error code
*       errobject[1] → contains error message
InterfaceError  Exception raised for errors that are related to the database interface rather than the database itself.
DatabaseError   Exception raised for errors that are related to the database.
DataError       Exception raised for errors that are due to problems with the processed data like division by zero, numeric value out of range, etc.
OperationError  Exception raised for errors that are related to the database's operation and not necessarily under the control of the programmer, e.g. an unexpected disconnect occurs, the data source name is not found, a transaction could not be processed, a memory allocation error occurred during processing, etc.
IntegrityError  Exception raised when the relational integrity of the database is affected, e.g. a foreign key check fails.
InternalError   Exception raised when the database encounters an internal error, e.g. the cursor is not valid anymore, the transaction is out of sync, etc.
ProgrammingError        Exception raised for programming errors, e.g. table not found or already exists, syntax error in the SQL statement, wrong number of parameters specified, etc.
NotSupportedError       Exception raised in case a method or database API was used which is not supported by the database, e.g. requesting a .rollback() on a connection that does not support transaction or has transactions turned off.
Connection Object
Attribute       Description
close()         Close the cursor now (rather than whenever _del_ is called).
The cursor will be unusable from this point  forward; an Error (or subclass) exception will be raised if any operation is attempted with the cursor.
commit()        Commit any pending transactions to the database.
rollback()      Rollback any pending transactions.
cursor()        Return a new Cursor object using the connection.
setautocommit(auto=True)        Set auto-commit mode.
getautocommit()         Get auto-commit mode.
cancel()        Cancel the running database request that is executed on the connection.
isconnected()   Return True if the connection is valid(connected to DB).
setclientinfo(key, value=None)  Set client info. If the value is None, the key is removed.
getclientinfo(key=None)         Get client info. If the key is None, All key:value sets are returned.
Cursor Object
Attribute       Description
description     Sequence of column's information; The information contains 7 items : (name, type_code, display_size, internal_size, precision, scale, null_ok).
rowcount        Updated column count.
callproc(procname[,parameters])         Call a stored database procedure with the given name.
close()         Close the cursor now.
nextset()       Skip to the next result set, closing current result set. (for multiple resultsets)
execute(operation[,parameters])         Prepare and execute a database operation (query or command).
-Steps included
1.

prepare operation(statement)
2.      bind paramters
3.      execute prepared-statement
executemany(operation,seq_of_parameters)        Prepare a database operation (query or command) and then execute it against all parameter sequences or mappings found in the sequence seq_of_parameters.
fetchone(uselob=False)  Fetch the next row of a query result set, returning a single sequence, or None when no more data is available.
fetchmany([size=cursor.arraysize])      Fetch the next set of rows of a query result, returning a sequence of sequences (e.g. a list of tuples). An empty sequence is returned when no more rows are available.
fetchall()      Fetch all (remaining) rows of a query result, returning them as a sequence of sequences (e.g. a list of tuples). Note that the cursor's arraysize attribute can affect the performance of this operation.
LOB Object
Attribute       Description
read(size[,position])   Return a portion (or all) of the data in the LOB object
write(data)     Write the data to the LOB object
close()         Close the LOB object


Source code sample
    def test_LobInsertWithPieceWiseLOB(self):
        """Piece-wise LOB INSERT"""
        old_mode = self.conn.getautocommit()
        self.conn.setautocommit(False)
        cur = self.conn.cursor()
        try:
            cur.execute("DROP TABLE PIECEWISE_LOB_TEST")
        except dbapi.Error, err:
            pass
        cur.execute("CREATE ROW TABLE PIECEWISE_LOB_TEST (key int, blob BLOB, clob CLOB, nclob NCLOB)")

        blob = dbapi.LOB()
        try:
            blob.read()
        except dbapi.Error, err:
            print err

        clob = dbapi.LOB()
        nclob = dbapi.LOB()
        cur.execute("INSERT INTO PIECEWISE_LOB_TEST VALUES (?,?,?,?)", (1, blob, clob, nclob))

        blob.write(data = "blob"*1024)
        blob.write("2blob"*512)
        blob.write(u"UNICODE"*32)
        blob.close()
        clob.write("clob"*1024)
        clob.write(buffer("binary"*32))
        clob.write(u"UNICODE"*32)
        clob.write(None)
        clob.close()
        chanyoung = codecs.utf_8_decode("\xEC\xB0\xAC")[0] + u"YOUNG"
        nclob.write(u"CHANYOUNG's UNICODE")
        nclob.write(chanyoung*256)
        nclob.close()

        self.conn.commit()

        cur.execute('select key, blob, clob, nclob from PIECEWISE_LOB_TEST')

        r = cur.fetchone(True) # uselob == True
        for c in r:
            if isinstance(c,dbapi.LOB) :
                c.read(0)
                c.read()
                c.read(999999999, 1)
                c.read(size=10,position=1)
                c.read(size=4,position=1)
                while True:
                    data = c.read(1000)
                    if data is None:
                        break
                    else:
                        #print data,
                        pass
            else:
                #print c
                pass

        cur.execute('select nclob from PIECEWISE_LOB_TEST')
        row = cur.fetchone()
        self.assertEqual(u"CHANYOUNG's UNICODE" + chanyoung*256, row[0])

        try:
            cur.execute("DROP TABLE PIECEWISE_LOB_TEST")
        except dbapi.Error, err:




Best wishes,
Chanyoung Kwon

SAP R&D Center Korea