pydb2-discuss — Python DB2 Discussion List

[PyDB2-discuss] Re: I have a patch to handle SQL_SUCCESS_WITH_INFO

[Preston L. <pla...@jo...>]

On Fri, 16 Jan 2004, ??? wrote:

> > Does anyone have an issue with the solution that I chose to handle the
> > SQL_SUCCESS_WITH_INFO state?  To remind you, that is the case where an
> > operation worked (e.g. returned rows) and yet there is a warning string
> > from the database server.   The solution I chose was just to stuff the
> > warning string into a LastWarningString attribute of the cursor object and
> > rely on the caller to check the string if they care.  Previously, the
> > driver was raising the warning as if it were a fatal error
> 

> Personally, i'm reluctant to add a new ``public'' attribute to cursor
> object for being used by users. Because PyDB2 users get stucked with
> the attribute once it is provided as public attribute.
> 
> And the best solution is to use ``warning'' module to control warning
> situations in standard manner. Could you please consider warning
> module solution? Adding internal attribute is OK and the attribute
> SHOULD be controlled by DB2.py.


Hello, thanks for the response.

I'm not sure I understand what you mean by a 'warning module.'  Perhaps I
don't know what the standard manner is.  Can you please provide an example
of another Python module that does it that way so I can refer to that?

thanks,

---
Preston Landers - pla...@jo...
Senior Software Developer, Journyx, Inc.
Austin, Texas  (512) 225-3929   

[PyDB2-discuss] Re: I have a patch to handle SQL_SUCCESS_WITH_INFO

[Preston L. <pla...@jo...>]

On 15 Jan 2004, Patrick K. O'Brien wrote:

> Preston Landers <pla...@jo...> writes:
>
> > By the way, my Sourceforge account is 'planders'.
>
> You are now a developer.  Feel free to update the source files.  I've
> also given you release privileges, so that you can package up a file
> release.  Thank you very much for your contributions.  They are
> greatly appreciated.

OK.  I will work on that within the next week or so.  I'm a bit tied up
at the moment.

Does anyone have an issue with the solution that I chose to handle the
SQL_SUCCESS_WITH_INFO state?  To remind you, that is the case where an
operation worked (e.g. returned rows) and yet there is a warning string
from the database server.   The solution I chose was just to stuff the
warning string into a LastWarningString attribute of the cursor object and
rely on the caller to check the string if they care.  Previously, the
driver was raising the warning as if it were a fatal error.

The only scenario I can think of where this change may break someone is if
they were for some obscure reason relying on the warnings being raised as
errors.

Re: [PyDB2-discuss] Re: I have a patch to handle SQL_SUCCESS_WITH_INFO

[<po...@or...>]

Preston Landers <pla...@jo...> writes:

> By the way, my Sourceforge account is 'planders'.

You are now a developer.  Feel free to update the source files.  I've
also given you release privileges, so that you can package up a file
release.  Thank you very much for your contributions.  They are
greatly appreciated.

-- 
Patrick K. O'Brien
Orbtech      http://www.orbtech.com/web/pobrien
-----------------------------------------------
"Your source for Python programming expertise."
-----------------------------------------------

[PyDB2-discuss] Re: I have a patch to handle SQL_SUCCESS_WITH_INFO

[Preston L. <pla...@jo...>]

On Wed, 14 Jan 2004, Preston Landers wrote:

> We ran into a situation recently where we wanted to use a certain
> construct in DB2 that was generating the SQL0347W warning about recursion.
> The DB2.py driver was raising this non-fatal warning as a error (and not
> returning the result rows.)
>
> Digging a bit deeper I found that this is the SQL_SUCCESS_WITH_INFO result
> code.  The current driver (_db2_module.c) has a comment to the effect of
> "need to cope with SQL_SUCCESS_WITH_INFO."
>
> I took what seemed to me to be the most simple and direct route to fix
> this problem.  I simply added a 'LastWarningString' attribute to the
> internal cursor object.  This is cleared before every statement execution.
> If SQL_SUCCESS_WITH_INFO is encountered then LastWarningString is updated
> with the warning text, and the result rows are returned.
>
> It is then up to the caller to check LastWarningString after every query
> if they care about that.  I also made a minor change in DB2.py BaseCursor
> class to copy over the LastWarningString in _refresh_info().


Attached are the three files that I modified to make this change.

_db2_module.c  -- handle SQL_SUCCESS_WITH_INFO by setting the warning
                  to a cursor variable LastWarningString

DB2.py  -- copy over LastWarningString in _refresh_info().
           Also changed 'x in dict' Python 2.2 syntax to the
           equivelent has_key() so it will work with Py 2.1.

README -- just added a note about checking LastWarningString

By the way, my Sourceforge account is 'planders'.

thanks,

---
Preston Landers - pla...@jo...
Senior Software Developer, Journyx, Inc.
Austin, Texas  (512) 225-3929

Re: [PyDB2-discuss] I have a patch to handle SQL_SUCCESS_WITH_INFO

[<po...@or...>]

Preston Landers <pla...@jo...> writes:

> Greetings to all on the list.

Same to you.  Nice to have you on the list.

> What is the best way to provide a patch such as this?  Should I just
> attach the new versions of the files to an email to this list and
> let a maintainer diff the changes in?

I was hoping Man-Yong Lee would respond.  If we don't hear from him
soon, what I can do is add you as a developer to the project and you
can patch the code and update it yourself.

Do you have a SourceForge username?

-- 
Patrick K. O'Brien
Orbtech      http://www.orbtech.com/web/pobrien
-----------------------------------------------
"Your source for Python programming expertise."
-----------------------------------------------

Re: [PyDB2-discuss] I have a patch to handle SQL_SUCCESS_WITH_INFO

[Yuval T. <yu...@il...>]

Preston Landers wrote:

>Greetings to all on the list.
>
>I'm not seeing much activity on this list or with the driver in general.
>I'm not sure if that means not many people are using it, or maybe it just
>works so well there's not much to discuss or change. ;-)
>
>We here at Journyx, Inc. (www.journyx.com - free timekeeping for 10 users
>or less) have been using this driver with good results for a while now.
>Most of our customers are NOT using DB2 (most are on Postgresql, SQL
>Server, and Oracle) but the few who do are depending on this driver for
>their production Time and Expense data.
>
>We ran into a situation recently where we wanted to use a certain
>construct in DB2 that was generating the SQL0347W warning about recursion.
>The DB2.py driver was raising this non-fatal warning as a error (and not
>returning the result rows.)
>
>Digging a bit deeper I found that this is the SQL_SUCCESS_WITH_INFO result
>code.  The current driver (_db2_module.c) has a comment to the effect of
>"need to cope with SQL_SUCCESS_WITH_INFO."
>
>I took what seemed to me to be the most simple and direct route to fix
>this problem.  I simply added a 'LastWarningString' attribute to the
>internal cursor object.  This is cleared before every statement execution.
>If SQL_SUCCESS_WITH_INFO is encountered then LastWarningString is updated
>with the warning text, and the result rows are returned.
>
>It is then up to the caller to check LastWarningString after every query
>if they care about that.  I also made a minor change in DB2.py BaseCursor
>class to copy over the LastWarningString in _refresh_info().
>
>I thought these changes would be generally useful.  Unless there is
>disagreement about the design (LastWarningString) the changes should be
>ready to patch into the latest version.  I'm open to suggestions for a
>better design to handle SQL_SUCCESS_WITH_INFO.
>
>What is the best way to provide a patch such as this?  Should I just
>attach the new versions of the files to an email to this list and let a
>maintainer diff the changes in?
>
>thanks,
>
>Preston Landers - pla...@jo...
>Senior Software Developer, Journyx, Inc.
>Austin, Texas  (512) 225-3929
>  
>
Could you post your patch here ?
Thanks!

-- 
Yuval Turgeman
Content Technology, Aduva LTD.

[PyDB2-discuss] I have a patch to handle SQL_SUCCESS_WITH_INFO

[Preston L. <pla...@jo...>]

Greetings to all on the list.

I'm not seeing much activity on this list or with the driver in general.
I'm not sure if that means not many people are using it, or maybe it just
works so well there's not much to discuss or change. ;-)

We here at Journyx, Inc. (www.journyx.com - free timekeeping for 10 users
or less) have been using this driver with good results for a while now.
Most of our customers are NOT using DB2 (most are on Postgresql, SQL
Server, and Oracle) but the few who do are depending on this driver for
their production Time and Expense data.

We ran into a situation recently where we wanted to use a certain
construct in DB2 that was generating the SQL0347W warning about recursion.
The DB2.py driver was raising this non-fatal warning as a error (and not
returning the result rows.)

Digging a bit deeper I found that this is the SQL_SUCCESS_WITH_INFO result
code.  The current driver (_db2_module.c) has a comment to the effect of
"need to cope with SQL_SUCCESS_WITH_INFO."

I took what seemed to me to be the most simple and direct route to fix
this problem.  I simply added a 'LastWarningString' attribute to the
internal cursor object.  This is cleared before every statement execution.
If SQL_SUCCESS_WITH_INFO is encountered then LastWarningString is updated
with the warning text, and the result rows are returned.

It is then up to the caller to check LastWarningString after every query
if they care about that.  I also made a minor change in DB2.py BaseCursor
class to copy over the LastWarningString in _refresh_info().

I thought these changes would be generally useful.  Unless there is
disagreement about the design (LastWarningString) the changes should be
ready to patch into the latest version.  I'm open to suggestions for a
better design to handle SQL_SUCCESS_WITH_INFO.

What is the best way to provide a patch such as this?  Should I just
attach the new versions of the files to an email to this list and let a
maintainer diff the changes in?

thanks,

Preston Landers - pla...@jo...
Senior Software Developer, Journyx, Inc.
Austin, Texas  (512) 225-3929

Re: [PyDB2-discuss] Re: prebuilt pydb2

[Yuval T. <yu...@il...>]

Patrick K. O'Brien wrote:

>"Jon Franz" <jf...@ne...> writes:
>
>  
>
>>I'm attempting to install the pydb2 module on a windows server, and
>>now I find that I need a copy of visual studio on the machine to
>>compile the extension.
>>
>>Is there a place I can get an alternate distribution of pydb2 that
>>is prebuilt for windows?
>>    
>>
>
>Jon,
>
>I'm copying your question to the discussion list to see if one of the
>developers could compile the module for Windows.  Anyone?
>
>  
>
I never compiled it for windows but I do have a copy of visual studio - 
if anyone tells me how to do it, I'll build it.

-- 
Yuval Turgeman
Content Technology, Aduva LTD.

[PyDB2-discuss] Re: prebuilt pydb2

[<po...@or...>]

"Jon Franz" <jf...@ne...> writes:

> I'm attempting to install the pydb2 module on a windows server, and
> now I find that I need a copy of visual studio on the machine to
> compile the extension.
> 
> Is there a place I can get an alternate distribution of pydb2 that
> is prebuilt for windows?

Jon,

I'm copying your question to the discussion list to see if one of the
developers could compile the module for Windows.  Anyone?

-- 
Patrick K. O'Brien
Orbtech      http://www.orbtech.com/web/pobrien
-----------------------------------------------
"Your source for Python programming expertise."
-----------------------------------------------

Re: [PyDB2-discuss] Re: Db2 module issues.

[Yuval T. <yu...@il...>]

??? wrote:

>>3.  In DB2_connection_object_cursor (line 666 :-), there's a commented 
>>out call to Py_INCREF(self) right after the assignment of cursor->conn = 
>>self.  If we want to make sure that the connection object stays alive 
>>while the cursor is alive, we must call incref ( and decref upon 
>>destruction ).
>>    
>>
>
>I didn't find the clean way to avoid cyclic reference situation.
>In case of db.close(), users expect cursors from that ``db'' also close,
>I think.  How about your idea?
>
Ok - I saw that we're holding a reference to the connection object 
inside the python cursor object.  So there's really no need to hold it 
in the C module as well.  Also, I saw that the only function that uses 
this connection object from within the C cursor is 'callproc'.  So what 
I did is removed the C connection reference from the C cursor (thus no 
need to play with ref. counting at all), and in 
DB2_cursor_object_callproc I added/changed the following lines:

    DB2ConnectionObject *conn = NULL;

    /* start parsing */
    if ( !PyArg_ParseTuple(args, "Os|O", conn, &proc_name, &p_args) ) {
        return NULL;
    }

    if ( ! conn )
    {
        PyErr_SetString(DB2_Error, "Connection object not found.");
        return NULL;
    }

And in the callproc method of the python cursor I changed the following 
lines:

    def callproc(self, procname, *args):
        t_args = args[:]
   
        r = self._cs.callproc(self._conn, procname.upper(), t_args)
        self._refresh_info()

This way, we're holding the ref. of the connection object only once - in 
the python code, and we send this reference to the C module only when we 
need to use it (only in callproc at the moment).

What do you say ?

-- 
Yuval Turgeman
Content Technology, Aduva LTD.

Re: [PyDB2-discuss] Re: Db2 module issues.

[<po...@or...>]

yu...@ad... writes:

> Quoting ??? <yo...@li...>:
> 
> > Python DB2 module owes a lot to Yuval.
> > I think he should have write access to PyDB2 CVS in sf.net.
> 
> Cool - thanks! 

Done.  I also gave him release privileges, in case he wanted to help
put together a file release with a new version of the package.

-- 
Patrick K. O'Brien
Orbtech      http://www.orbtech.com/web/pobrien
-----------------------------------------------
"Your source for Python programming expertise."
-----------------------------------------------

Re: [PyDB2-discuss] Re: Db2 module issues.

[<yu...@ad...>]

Quoting ??? <yo...@li...>:

> Hi~
> 
> Python DB2 module owes a lot to Yuval.
> I think he should have write access to PyDB2 CVS in sf.net.
Cool - thanks! 

> You mean the line number 414 in _db2_module.c?
> I didn't know about the memory consumption in case of no autocommit.
> But "No" auto commit is the default mode the most people expect.
> They don't want they INSERT or UPDATE operation committed
> immediately.
But this affects the SELECT queries as well - I mean if you don't call commit 
after your SELECT statements, the module keeps consuming memory without bounds.
I dont know about other ppl, but as a user of this module it seems silly that i 
have to commit after SELECT.  For now, I just set autocommit(1) after creation 
and it solves the problem.
 
> I didn't find the clean way to avoid cyclic reference situation.
> In case of db.close(), users expect cursors from that ``db'' also close,
> I think.  How about your idea?

I didn't think of that - but for this case, I think I read somewhere that 
there's an object type that is allowed to have cyclic ref. in it.

--- Python C API ---
TYPE* PyObject_GC_New( TYPE, PyTypeObject *type) 

Analogous to PyObject_New() but for container objects with the 
Py_TPFLAGS_HAVE_GC flag set. 
---------------------

And GC can handle the situation you mentioned (I think... :-) )

Another solution would be to remove the db.close method... ;-)

Btw, regarding the fetching functions - why don't we use the 
DB2_cursor_object_fetchall and fetchmany functions ?  In DB2.py there's a loop 
in fetchall and fetchmany that calls self.fetchone().  Is there a special 
reason for not using the C functions ?

Thanks,
Yuval.

-------------------------------------------------
This mail sent through IMP: http://horde.org/imp/

Re: [PyDB2-discuss] Db2 module issues.

[Yuval T. <yu...@il...>]

Yuval Turgeman wrote:

> 4.  We clear the cursor data prior to fetching instead of after 
> fetching - this can cause leaks.  After we converted the data to 
> pyobjects, we need to clear the data.  So, to fix this (potential) 
> problem I did the following:
> A.  I changed the name of DB2_cursor_object_fetchone to 
> DB2_cursor_object_do_fetch, and created the following new wrapper 
> function:
>
> static PyObject *
> DB2_cursor_object_fetchone(DB2CursorObject *self, PyObject *args)
> {
>        PyObject *pRet = DB2_cursor_object_do_fetch(self, args);
>        DB2_cursor_object_clear_data(self);
>        return pRet;
> }
>
> B.  In fetchall and fetchmany, I changed the calls from fetchone to 
> do_fetch, and added a calls to DB2_cursor_object_clear_data(self); at 
> the end of the loops.

Disregard this specific issue - I just saw that in DB2.py, we call 
several times to self._cs.fetchone() and  never call to the C 
functions.  Why is that by the way?
Anyway, this makes the fetching issue irrelevant (and the suggested fix 
above doesn't work as well)

-- 
Yuval Turgeman
Content Technology, Aduva LTD.

[PyDB2-discuss] Db2 module issues.

[Yuval T. <yu...@il...>]

Hello everyone -
I've been testing the module extensively in the past few days using the 
following code:

import DB2,sys,random

for x in range(5000):
    conn = DB2.Connect('uvrerch','db2rerch','db2rerch')
    curs = conn.cursor()
    id = random.randint(0, 500)
    curs.execute("SELECT * FROM MYTABLE WHERE ID=%d" % id)

Here are some comments:
1.  In line 414 - default autocommit mode was changed to "no auto 
commit" - this change causes the select query to consume plenty of 
memory.  Of course, if I add a conn.commit() after the execute statement 
(or call conn.autocommit(1) after creating the connection object), the 
memory is stable.  Is this the kind of behaviour that we want?  I mean 
"commit" is meant  to be called only after inserts, no ?

2.  In the dealloc functions (for both cursor, and connection objects) 
we call PyMem_DEL after we initialized those objects with PyObject_NEW.  
According to the python api, objects should be deleted using PyObject_DEL.

3.  In DB2_connection_object_cursor (line 666 :-), there's a commented 
out call to Py_INCREF(self) right after the assignment of cursor->conn = 
self.  If we want to make sure that the connection object stays alive 
while the cursor is alive, we must call incref ( and decref upon 
destruction ).

4.  We clear the cursor data prior to fetching instead of after fetching 
- this can cause leaks.  After we converted the data to pyobjects, we 
need to clear the data.  So, to fix this (potential) problem I did the 
following:
A.  I changed the name of DB2_cursor_object_fetchone to 
DB2_cursor_object_do_fetch, and created the following new wrapper function:

static PyObject *
DB2_cursor_object_fetchone(DB2CursorObject *self, PyObject *args)
{
        PyObject *pRet = DB2_cursor_object_do_fetch(self, args);
        DB2_cursor_object_clear_data(self);
        return pRet;
}

B.  In fetchall and fetchmany, I changed the calls from fetchone to 
do_fetch, and added a calls to DB2_cursor_object_clear_data(self); at 
the end of the loops.

That's what I got till now.
I have those code changes - and I'm currently testing them.  They're looking good.
If you want me to commit those changes to the CVS, then drop me a note.

-- 
Yuval Turgeman
Content Technology, Aduva LTD.

[PyDB2-discuss] IBM Tutorial

[Patrick K. O'B. <po...@or...>]

My Python and DB2 for Linux tutorial was recently published by IBM:

https://www6.software.ibm.com/reg/devworks/dw-db2pylnx-i?S_TACT=102B7W91&S_CMP=DB2DD

Python is a great tool to use with DB2. It combines the ability to quickly 
and simply access a DB2 database with the power to perform more complex 
tasks that require a general purpose programming language. This tutorial 
shows you everything you need to know to interact with DB2 V8.1 using the 
Python programming language. In particular, you'll learn about the Python 
DB2 module, which allows you to connect to a DB2 database, execute queries, 
fetch results, and commit or rollback transactions. By the end of the 
tutorial you will have received a thorough introduction to the entire 
Python DB2 interface.

-- 
Patrick K. O'Brien
Orbtech      http://www.orbtech.com/web/pobrien
-----------------------------------------------
"Your source for Python programming expertise."
-----------------------------------------------