Quantcast

About LOB

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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




_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: About LOB

Vernon D. Cole

Chan:
[I hope I picked your familiar name? If not, please correct.]

I think that you have started not one, but two excellent projects. I am excited by your post, and hope that both have a happy ending. (Will explain later, hard to type on this thing) Don't let what I am about to ask make you think you have a bad idea.

Our industry is plagued by rampant TLA usage. Often, a single TLA is used for several different concepts. Before using a TLA or EMLA in a paper or conversation, we should make sure we have defined our TLA in English. For example is SMB Service Message Block, or Small and Medium Business? [TLA here means "Three Letter Acronym", EMLA is Extended Multi-Letter Acronym.]

Please define precisely what LOB support should mean.
--
Vernon
(sent from my 'droid phone)


_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: About LOB

Chris Clark-2
In reply to this post by Kwon, Chan Young
Kwon, Chan Young wrote:
> 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.
>  


BLarge OBject's (BLOBs) are alluded to in pep 249 BUT they really are
not detailed so I think you have spotted a weakness in the spec. CLOBs
(Character Large OBjects) are not covered at all. Locators are not
discussed but implied by references to bufferobjects.

For BLOBs as input bind parameters check out the Binary() constructor
(case sensitive) at http://www.python.org/dev/peps/pep-0249/.
For BLOB results look for bufferobject in
http://www.python.org/dev/peps/pep-0249/

The spec (as I read it) really expects LOBs to be handled as one massive
block. However implementators can choose to implement locators behind
the scene so that data isn't sent across the wire unless the buffer
object is read from.

So, model your LOB access methods on bufferobjects and you should be all
set! Marc-Andre reads/responds to the list regularly so take note of any
suggestions he has :-)

Hope that helps get you started.

Chris

_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: About LOB

Kwon, Chan Young
Hi,

Let me describe more...
1. My suggestion of LOB object is kind of Locator object. It does not contain whole massive data. It just passes small piece of data when its methods are called.
2. buffer object is not enough to handle various kinds of LOB types. For example, character LOB, binary LOB and unicode character LOB.

In my company, this LOB class is already used as extension of DBAPI spec 2.0.
All colleagues like LOB class because it is very simple and easy to use.
So I sent this for your information.


Best wishes,
Chanyoung Kwon

SAP R&D Center Korea



-----Original Message-----
From: Chris Clark [mailto:[hidden email]]
Sent: Thursday, April 28, 2011 2:05 AM
To: Kwon, Chan Young
Cc: [hidden email]
Subject: Re: [DB-SIG] About LOB

Kwon, Chan Young wrote:
> 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.
>  


BLarge OBject's (BLOBs) are alluded to in pep 249 BUT they really are
not detailed so I think you have spotted a weakness in the spec. CLOBs
(Character Large OBjects) are not covered at all. Locators are not
discussed but implied by references to bufferobjects.

For BLOBs as input bind parameters check out the Binary() constructor
(case sensitive) at http://www.python.org/dev/peps/pep-0249/.
For BLOB results look for bufferobject in
http://www.python.org/dev/peps/pep-0249/

The spec (as I read it) really expects LOBs to be handled as one massive
block. However implementators can choose to implement locators behind
the scene so that data isn't sent across the wire unless the buffer
object is read from.

So, model your LOB access methods on bufferobjects and you should be all
set! Marc-Andre reads/responds to the list regularly so take note of any
suggestions he has :-)

Hope that helps get you started.

Chris

_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: About LOB

M.-A. Lemburg
Kwon, Chan Young wrote:
> Hi,
>
> Let me describe more...
> 1. My suggestion of LOB object is kind of Locator object. It does not contain whole massive data. It just passes small piece of data when its methods are called.
> 2. buffer object is not enough to handle various kinds of LOB types. For example, character LOB, binary LOB and unicode character LOB.
>
> In my company, this LOB class is already used as extension of DBAPI spec 2.0.
> All colleagues like LOB class because it is very simple and easy to use.
> So I sent this for your information.

There are various ways large binary/text objects are handled by
databases at the API level.

Some databases provide APIs which allow reading/writing such
data in chunks, others provide file descriptors which can be use
used for this, yet others maintain the files outside the database
and provide "locator" objects for these.

Since all of these approaches use a file-like interface in one
way or another, perhaps we could agree on a common DB-API extension
that defines the minimum interface of such objects and a constructor
that turns an existing file into an object which can then be passed
to the database (much like the Binary() constructor we have for
binary data).

Do you have documentation for the LOB object you are using available
on the web ?

Regards,
--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, May 02 2011)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
2011-06-20: EuroPython 2011, Florence, Italy               49 days to go

::: Try our new mxODBC.Connect Python Database Interface for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611
               http://www.egenix.com/company/contact/


> Best wishes,
> Chanyoung Kwon
>
> SAP R&D Center Korea
>
>
>
> -----Original Message-----
> From: Chris Clark [mailto:[hidden email]]
> Sent: Thursday, April 28, 2011 2:05 AM
> To: Kwon, Chan Young
> Cc: [hidden email]
> Subject: Re: [DB-SIG] About LOB
>
> Kwon, Chan Young wrote:
>> 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.
>>  
>
>
> BLarge OBject's (BLOBs) are alluded to in pep 249 BUT they really are
> not detailed so I think you have spotted a weakness in the spec. CLOBs
> (Character Large OBjects) are not covered at all. Locators are not
> discussed but implied by references to bufferobjects.
>
> For BLOBs as input bind parameters check out the Binary() constructor
> (case sensitive) at http://www.python.org/dev/peps/pep-0249/.
> For BLOB results look for bufferobject in
> http://www.python.org/dev/peps/pep-0249/
>
> The spec (as I read it) really expects LOBs to be handled as one massive
> block. However implementators can choose to implement locators behind
> the scene so that data isn't sent across the wire unless the buffer
> object is read from.
>
> So, model your LOB access methods on bufferobjects and you should be all
> set! Marc-Andre reads/responds to the list regularly so take note of any
> suggestions he has :-)
>
> Hope that helps get you started.
>
> Chris
>
> _______________________________________________
> DB-SIG maillist  -  [hidden email]
> http://mail.python.org/mailman/listinfo/db-sig

_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: About LOB

Chris Clark-2
M.-A. Lemburg wrote:

> Kwon, Chan Young wrote:
>  
>> Hi,
>>
>> Let me describe more...
>> 1. My suggestion of LOB object is kind of Locator object. It does not contain whole massive data. It just passes small piece of data when its methods are called.
>> 2. buffer object is not enough to handle various kinds of LOB types. For example, character LOB, binary LOB and unicode character LOB.
>>
>> In my company, this LOB class is already used as extension of DBAPI spec 2.0.
>> All colleagues like LOB class because it is very simple and easy to use.
>> So I sent this for your information.
>>    
>
> There are various ways large binary/text objects are handled by
> databases at the API level.
>
> Some databases provide APIs which allow reading/writing such
> data in chunks, others provide file descriptors which can be use
> used for this, yet others maintain the files outside the database
> and provide "locator" objects for these.
>
> Since all of these approaches use a file-like interface in one
> way or another, perhaps we could agree on a common DB-API extension
> that defines the minimum interface of such objects and a constructor
> that turns an existing file into an object which can then be passed
> to the database (much like the Binary() constructor we have for
> binary data).
>
> Do you have documentation for the LOB object you are using available
> on the web ?
>  

Excellent idea, here are Java Docs for the JDBC clob/blob locator classes:

http://download.oracle.com/javase/1.4.2/docs/api/java/sql/Clob.html
http://download.oracle.com/javase/1.4.2/docs/api/java/sql/Blob.html

If we add this, I think we need clearly separate classes for the 2
types. In the same way Python 2.x has str/unicode and Python 3.x as
byte/str.

Chris

_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: About LOB

Kwon, Chan Young
In reply to this post by M.-A. Lemburg
Hi Lemburg,

There is no documentation on the web open to external access.
Our new DB project is little bit confidential.

I can just send you a copy of wiki page inside my company's intranet.
My LOB is also file-like object.
So it has just two interfaces.
One is read()
The other is write()
Different from JDBC, this python LOB object does not need different functions for UNICODE,STRING and BINARY.
Inside read/write function, there must be some routine for various input data type.

In my humble opinion, LOB object’s extension should be like this.

*       LOB
o       read(data[,position])
o       write(data[,position])
o       find(data[,position])

Here is my DB API’s documentation inside intranet of my company.

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


Best wishes,
Chanyoung Kwon

SAP R&D Center Korea



-----Original Message-----
From: M.-A. Lemburg [mailto:[hidden email]]
Sent: Monday, May 02, 2011 8:39 PM
To: Kwon, Chan Young
Cc: Chris Clark; [hidden email]
Subject: Re: [DB-SIG] About LOB

Kwon, Chan Young wrote:
> Hi,
>
> Let me describe more...
> 1. My suggestion of LOB object is kind of Locator object. It does not contain whole massive data. It just passes small piece of data when its methods are called.
> 2. buffer object is not enough to handle various kinds of LOB types. For example, character LOB, binary LOB and unicode character LOB.
>
> In my company, this LOB class is already used as extension of DBAPI spec 2.0.
> All colleagues like LOB class because it is very simple and easy to use.
> So I sent this for your information.

There are various ways large binary/text objects are handled by
databases at the API level.

Some databases provide APIs which allow reading/writing such
data in chunks, others provide file descriptors which can be use
used for this, yet others maintain the files outside the database
and provide "locator" objects for these.

Since all of these approaches use a file-like interface in one
way or another, perhaps we could agree on a common DB-API extension
that defines the minimum interface of such objects and a constructor
that turns an existing file into an object which can then be passed
to the database (much like the Binary() constructor we have for
binary data).

Do you have documentation for the LOB object you are using available
on the web ?

Regards,
--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, May 02 2011)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
2011-06-20: EuroPython 2011, Florence, Italy               49 days to go

::: Try our new mxODBC.Connect Python Database Interface for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611
               http://www.egenix.com/company/contact/


> Best wishes,
> Chanyoung Kwon
>
> SAP R&D Center Korea
>
>
>
> -----Original Message-----
> From: Chris Clark [mailto:[hidden email]]
> Sent: Thursday, April 28, 2011 2:05 AM
> To: Kwon, Chan Young
> Cc: [hidden email]
> Subject: Re: [DB-SIG] About LOB
>
> Kwon, Chan Young wrote:
>> 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.
>>
>
>
> BLarge OBject's (BLOBs) are alluded to in pep 249 BUT they really are
> not detailed so I think you have spotted a weakness in the spec. CLOBs
> (Character Large OBjects) are not covered at all. Locators are not
> discussed but implied by references to bufferobjects.
>
> For BLOBs as input bind parameters check out the Binary() constructor
> (case sensitive) at http://www.python.org/dev/peps/pep-0249/.
> For BLOB results look for bufferobject in
> http://www.python.org/dev/peps/pep-0249/
>
> The spec (as I read it) really expects LOBs to be handled as one massive
> block. However implementators can choose to implement locators behind
> the scene so that data isn't sent across the wire unless the buffer
> object is read from.
>
> So, model your LOB access methods on bufferobjects and you should be all
> set! Marc-Andre reads/responds to the list regularly so take note of any
> suggestions he has :-)
>
> Hope that helps get you started.
>
> Chris
>
> _______________________________________________
> DB-SIG maillist  -  [hidden email]
> http://mail.python.org/mailman/listinfo/db-sig


_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: About LOB

Kwon, Chan Young
In reply to this post by M.-A. Lemburg
*       LOB
o       read(size[,position])
o       write(data[,position])
o       find(data[,position])



Best wishes,
Chanyoung Kwon

SAP R&D Center Korea



_____________________________________________
From: Kwon, Chan Young
Sent: Tuesday, May 03, 2011 3:45 PM
To: 'M.-A. Lemburg'
Cc: Chris Clark; [hidden email]
Subject: RE: [DB-SIG] About LOB


Hi Lemburg,

There is no documentation on the web open to external access.
Our new DB project is little bit confidential.

I can just send you a copy of wiki page inside my company's intranet.
My LOB is also file-like object.
So it has just two interfaces.
One is read()
The other is write()
Different from JDBC, this python LOB object does not need different functions for UNICODE,STRING and BINARY.
Inside read/write function, there must be some routine for various input data type.

In my humble opinion, LOB object’s extension should be like this.

*       LOB
o       read(data[,position])
o       write(data[,position])
o       find(data[,position])

Here is my DB API’s documentation inside intranet of my company.

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


Best wishes,
Chanyoung Kwon

SAP R&D Center Korea



-----Original Message-----
From: M.-A. Lemburg [mailto:[hidden email]]
Sent: Monday, May 02, 2011 8:39 PM
To: Kwon, Chan Young
Cc: Chris Clark; [hidden email]
Subject: Re: [DB-SIG] About LOB

Kwon, Chan Young wrote:
> Hi,
>
> Let me describe more...
> 1. My suggestion of LOB object is kind of Locator object. It does not contain whole massive data. It just passes small piece of data when its methods are called.
> 2. buffer object is not enough to handle various kinds of LOB types. For example, character LOB, binary LOB and unicode character LOB.
>
> In my company, this LOB class is already used as extension of DBAPI spec 2.0.
> All colleagues like LOB class because it is very simple and easy to use.
> So I sent this for your information.

There are various ways large binary/text objects are handled by
databases at the API level.

Some databases provide APIs which allow reading/writing such
data in chunks, others provide file descriptors which can be use
used for this, yet others maintain the files outside the database
and provide "locator" objects for these.

Since all of these approaches use a file-like interface in one
way or another, perhaps we could agree on a common DB-API extension
that defines the minimum interface of such objects and a constructor
that turns an existing file into an object which can then be passed
to the database (much like the Binary() constructor we have for
binary data).

Do you have documentation for the LOB object you are using available
on the web ?

Regards,
--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, May 02 2011)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
2011-06-20: EuroPython 2011, Florence, Italy               49 days to go

::: Try our new mxODBC.Connect Python Database Interface for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611
               http://www.egenix.com/company/contact/


> Best wishes,
> Chanyoung Kwon
>
> SAP R&D Center Korea
>
>
>
> -----Original Message-----
> From: Chris Clark [mailto:[hidden email]]
> Sent: Thursday, April 28, 2011 2:05 AM
> To: Kwon, Chan Young
> Cc: [hidden email]
> Subject: Re: [DB-SIG] About LOB
>
> Kwon, Chan Young wrote:
>> 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.
>>
>
>
> BLarge OBject's (BLOBs) are alluded to in pep 249 BUT they really are
> not detailed so I think you have spotted a weakness in the spec. CLOBs
> (Character Large OBjects) are not covered at all. Locators are not
> discussed but implied by references to bufferobjects.
>
> For BLOBs as input bind parameters check out the Binary() constructor
> (case sensitive) at http://www.python.org/dev/peps/pep-0249/.
> For BLOB results look for bufferobject in
> http://www.python.org/dev/peps/pep-0249/
>
> The spec (as I read it) really expects LOBs to be handled as one massive
> block. However implementators can choose to implement locators behind
> the scene so that data isn't sent across the wire unless the buffer
> object is read from.
>
> So, model your LOB access methods on bufferobjects and you should be all
> set! Marc-Andre reads/responds to the list regularly so take note of any
> suggestions he has :-)
>
> Hope that helps get you started.
>
> Chris
>
> _______________________________________________
> DB-SIG maillist  -  [hidden email]
> http://mail.python.org/mailman/listinfo/db-sig


_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: About LOB

Kwon, Chan Young
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



_____________________________________________
From: Kwon, Chan Young
Sent: Tuesday, May 03, 2011 4:09 PM
To: Kwon, Chan Young; M.-A. Lemburg
Cc: Chris Clark; [hidden email]
Subject: RE: [DB-SIG] About LOB


*       LOB
o       read(size[,position])
o       write(data[,position])
o       find(data[,position])



Best wishes,
Chanyoung Kwon

SAP R&D Center Korea



_____________________________________________
From: Kwon, Chan Young
Sent: Tuesday, May 03, 2011 3:45 PM
To: 'M.-A. Lemburg'
Cc: Chris Clark; [hidden email]
Subject: RE: [DB-SIG] About LOB


Hi Lemburg,

There is no documentation on the web open to external access.
Our new DB project is little bit confidential.

I can just send you a copy of wiki page inside my company's intranet.
My LOB is also file-like object.
So it has just two interfaces.
One is read()
The other is write()
Different from JDBC, this python LOB object does not need different functions for UNICODE,STRING and BINARY.
Inside read/write function, there must be some routine for various input data type.

In my humble opinion, LOB object’s extension should be like this.

*       LOB
o       read(data[,position])
o       write(data[,position])
o       find(data[,position])

Here is my DB API’s documentation inside intranet of my company.

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


Best wishes,
Chanyoung Kwon

SAP R&D Center Korea



-----Original Message-----
From: M.-A. Lemburg [mailto:[hidden email]]
Sent: Monday, May 02, 2011 8:39 PM
To: Kwon, Chan Young
Cc: Chris Clark; [hidden email]
Subject: Re: [DB-SIG] About LOB

Kwon, Chan Young wrote:
> Hi,
>
> Let me describe more...
> 1. My suggestion of LOB object is kind of Locator object. It does not contain whole massive data. It just passes small piece of data when its methods are called.
> 2. buffer object is not enough to handle various kinds of LOB types. For example, character LOB, binary LOB and unicode character LOB.
>
> In my company, this LOB class is already used as extension of DBAPI spec 2.0.
> All colleagues like LOB class because it is very simple and easy to use.
> So I sent this for your information.

There are various ways large binary/text objects are handled by
databases at the API level.

Some databases provide APIs which allow reading/writing such
data in chunks, others provide file descriptors which can be use
used for this, yet others maintain the files outside the database
and provide "locator" objects for these.

Since all of these approaches use a file-like interface in one
way or another, perhaps we could agree on a common DB-API extension
that defines the minimum interface of such objects and a constructor
that turns an existing file into an object which can then be passed
to the database (much like the Binary() constructor we have for
binary data).

Do you have documentation for the LOB object you are using available
on the web ?

Regards,
--
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, May 02 2011)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
2011-06-20: EuroPython 2011, Florence, Italy               49 days to go

::: Try our new mxODBC.Connect Python Database Interface for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611
               http://www.egenix.com/company/contact/


> Best wishes,
> Chanyoung Kwon
>
> SAP R&D Center Korea
>
>
>
> -----Original Message-----
> From: Chris Clark [mailto:[hidden email]]
> Sent: Thursday, April 28, 2011 2:05 AM
> To: Kwon, Chan Young
> Cc: [hidden email]
> Subject: Re: [DB-SIG] About LOB
>
> Kwon, Chan Young wrote:
>> 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.
>>
>
>
> BLarge OBject's (BLOBs) are alluded to in pep 249 BUT they really are
> not detailed so I think you have spotted a weakness in the spec. CLOBs
> (Character Large OBjects) are not covered at all. Locators are not
> discussed but implied by references to bufferobjects.
>
> For BLOBs as input bind parameters check out the Binary() constructor
> (case sensitive) at http://www.python.org/dev/peps/pep-0249/.
> For BLOB results look for bufferobject in
> http://www.python.org/dev/peps/pep-0249/
>
> The spec (as I read it) really expects LOBs to be handled as one massive
> block. However implementators can choose to implement locators behind
> the scene so that data isn't sent across the wire unless the buffer
> object is read from.
>
> So, model your LOB access methods on bufferobjects and you should be all
> set! Marc-Andre reads/responds to the list regularly so take note of any
> suggestions he has :-)
>
> Hope that helps get you started.
>
> Chris
>
> _______________________________________________
> DB-SIG maillist  -  [hidden email]
> http://mail.python.org/mailman/listinfo/db-sig


_______________________________________________
DB-SIG maillist  -  [hidden email]
http://mail.python.org/mailman/listinfo/db-sig
Loading...