Stored Procedures and output parameters in Python

[Ehrich]

Hi,

I am having trouble getting output parameter values using
ibm_db.execute. I am binding the parameters but I believe the issue
may be caused by the immutability of strings and integers in Python. I
have tried variations of dictionaries and lists without success. The
database is DB2 and I am writing the code on a Mac (the database is on
an AIX system).

Thanks,

Ehrich

[Tarun Pasrija]

Hi Ehrich

Thank you for choosing our driver.
I would request you to kindly send me a repro script that is causing
an error for you to let me investigate this issue further. Also,
kindly send me the following details about the environment you are
using.

$ python --version
$ db2level

Thanks and Regards
Tarun Pasrija

[Ehrich]

Hi Tarun

python --version
Python 2.5.1

db2level
DB21085I Instance "XXXXX" uses "64" bits and DB2 code release
"SQL09052"
with level identifier "03030107".
Informational tokens are "DB2 v9.5.0.2", "s081205", "DARWIN64", and
Fix Pack
"2".
Product is installed at "/Users/XXXXX/sqllib".

[Tarun Pasrija]

Hi Ehrich

As mentioned on a mail to you, This would be fixed in the next
release. We have come to the resolution for this issue and working on
the necessary code changes.

Thanks and Regards
Tarun Pasrija

[Tarun Pasrija]

Hi Ehrich

We have learnt from the responses on comp.lang.python that module
dictionary is stored temporarily by Python and it is impossible in
Python to return the values in the same variable as we bind the
parameters. Thus we have decided to come up with a new API which
solves our problem. I am attaching a sample testcase with the same.
Please let me know if this would solve your purpose. I would soon send
you a patched driver for you to test in your environment before making
a release.

ibm_db.bind_param(stmt, 1, name, ibm_db.SQL_PARAM_INPUT)
ibm_db.bind_param(stmt, 2, second_name, ibm_db.SQL_PARAM_INPUT_OUTPUT)
ibm_db.bind_param(stmt, 3, weight, ibm_db.SQL_PARAM_OUTPUT)

print
".............................................................."
print "Values of bound parameters _before_ CALL:"
print " 1: %s 2: %s 3: %d\n" % (name, second_name, weight)

# We have introduced an API called callproc instead specifically for
handling stored prodecures
# This would return us the tuple of out parameters in the same order
as written in the stored proc.

second_name,weight=ibm_db.callproc(stmt)
print "............................................................"
print "Values of bound parameters _after_ CALL:"
print " 1: %s 2: %s 3: %d\n" % (name, second_name, weight)
print "............................................................"

row = ibm_db.fetch_tuple(stmt)
while ( row ):
print " %s, %s, %s" % (row[0].strip(), row[1].strip(), row[2])
row = ibm_db.fetch_tuple(stmt)

On Apr 2, 10:43 pm, Ehrich <csg.a...@gmail.com> wrote:

[Tarun Pasrija]

Hi Ehrich

Thanks for your patience. I would like to update you with a good news
that we have created a patch for the driver which includes the
solution to the above problem.

I would like to request you to please tell me your system information
i.e Linux/Windows/Mac and I would test it appropriately and send you a
patch to test it in your environment.

Thanks and Regards
Tarun Pasrija

[Ehrich]

Hi Turan,

My apologies if this is a duplicate post, I have not seen my
previous post yet. I am running OS X 10.5.7, Python 2.5.1, and
DB2Level info as follows:

uses "64" bits and DB2 code release "SQL09052"
with level identifier "03030107".
Informational tokens are "DB2 v9.5.0.2", "s081205", "DARWIN64", and
Fix Pack
"2".

Please contact me reagarding the patch.

Thank you,

Ehrich

[Ehrich]

Hi Tarun,

Thank you. I will install the new version, test, and post my
results.

Regards,

Ehrich

[Ehrich]

Hi Tarun,

I am using a Mac, OS X 10.5.7.

python --version
Python 2.5.1

db2level

DB21085I Instance "XXXXXXXX" uses "64" bits and DB2 code release

"SQL09052"
with level identifier "03030107".
Informational tokens are "DB2 v9.5.0.2", "s081205", "DARWIN64", and
Fix Pack
"2".

Product is installed at "/Users/XXXXXXXX/sqllib".

Thank you,

Ehrich

[Tarun Pasrija]

Hi Ehrich

Thanks for your reply. Please go ahead and download the patch from the
following link

http://01175241965258921893-a-g.googlegroups.com/web/ibm_db-storedproc.zip?gda=QTQKOEcAAAAt6zNXfXZwqsroxsbdXxiids2MNeB8I4aqBwQrTJuxmN2LxBcgWYeCQY_nBY4FqJobzHe87USdioT9uNiA7PHaeV4duv6pDMGhhhZdjQlNAw

If the above link does not work for you, alternatively you can visit
the files section - http://groups.google.com/group/ibm_db/files and
download the ibm_db-storedproc.zip.

We have not documented the new API that we included for stored proc
(would update the docs before release). But you can take a look at the
following test cases to get an idea about the usage.

test_146_CallSPINAndOUTParams.py
test_148_CallSPDiffBindPattern_01.py
test_52949_TestSPIntVarcharXml.py

Please let us know your thoughts on this or if you require any further
help.

Thanks and Regards
Tarun Pasrija

Open Source Application Development
IBM India Software Labs

[Ehrich]

Hi Tarun,

Thank you for the patch. I installed (with warnings) and am having
trouble with the call. I have a simple procedure that takes two in and
returns three out params. The code I have is as follows:

#!/usr/bin/python
import ibm_db

conn = ibm_db.connect('ew_db', 'ew', 'ew_password')
server = ibm_db.server_info(conn)

if conn:

sql = 'CALL ew_db.prc_sel_data(?, ?, ?, ?, ?)'

stmnt = ibm_db.prepare(conn,sql)

field1='123456789'
field2='XID'
field3='c'
field4=0
field5='None'

ibm_db.bind_param(stmnt, 1, field1, ibm_db.SQL_PARAM_INPUT)
ibm_db.bind_param(stmnt, 2, field2, ibm_db.SQL_PARAM_INPUT)
ibm_db.bind_param(stmnt, 3, field3, ibm_db.SQL_PARAM_OUTPUT)
ibm_db.bind_param(stmnt, 4, field4, ibm_db.SQL_PARAM_OUTPUT)
ibm_db.bind_param(stmnt, 5, field5, ibm_db.SQL_PARAM_OUTPUT)

stmt, field3, field4, field5 = ibm_db.callproc(conn,
'ttndbo.prc_sel_data', (field1, field2, field3, field4, field5))
print field1,field2,field3,field4,field5


And I get the following error:

stmt, field3, field4, field5 = ibm_db.callproc(conn, 'ew_db.
prc_sel_data', (field1, field2, field3, field4, field5))
Exception: Parameters not bound

Any help would be greatly appreciated.

Thank you,

Ehrich

On Aug 3, 12:01 am, Tarun Pasrija <tarun.pasr...@in.ibm.com> wrote:
> Hi Ehrich
>

> Thanks for your reply. Please go ahead and download the patch from the
> following link
>

> http://01175241965258921893-a-g.googlegroups.com/web/ibm_db-storedpro...

[Tarun Pasrija]

Ehrich,

Please see my comments below

Thanks and Regards
Tarun Pasrija
Open Source Application Development
IBM India Software Labs

On Aug 5, 9:43 pm, Ehrich <csg.a...@gmail.com> wrote:
> Hi Tarun,
>
>     Thank you for the patch. I installed (with warnings) and am having
> trouble with the call. I have a simple procedure that takes two in and
> returns three out params. The code I have is as follows:
>
> #!/usr/bin/python
> import ibm_db
>
> conn = ibm_db.connect('ew_db', 'ew', 'ew_password')
> server = ibm_db.server_info(conn)
>
> if conn:
>
>     sql = 'CALL ew_db.prc_sel_data(?, ?, ?, ?, ?)'
>

We do not require this statement

>     stmnt = ibm_db.prepare(conn,sql)

Prepare is not required since callproc internally does the prepare
too.

>     field1='123456789'
>     field2='XID'
>     field3='c'
>     field4=0
>     field5='None'
>
>     ibm_db.bind_param(stmnt, 1, field1, ibm_db.SQL_PARAM_INPUT)
>     ibm_db.bind_param(stmnt, 2, field2, ibm_db.SQL_PARAM_INPUT)
>     ibm_db.bind_param(stmnt, 3, field3, ibm_db.SQL_PARAM_OUTPUT)
>     ibm_db.bind_param(stmnt, 4, field4, ibm_db.SQL_PARAM_OUTPUT)
>     ibm_db.bind_param(stmnt, 5, field5, ibm_db.SQL_PARAM_OUTPUT)

Bind Params are not required like I mentioned before callproc does the
binding.

>     stmt, field3, field4, field5 = ibm_db.callproc(conn,
> 'ttndbo.prc_sel_data', (field1, field2, field3, field4, field5))
>     print field1,field2,field3,field4,field5

I see you have something schemaname.prc_sel_data or something like
that. Once you have connected to the database, you would not require
this.

> And I get the following error:
>
> stmt, field3, field4, field5 = ibm_db.callproc(conn, 'ew_db.
> prc_sel_data', (field1, field2, field3, field4, field5))
> Exception: Parameters not bound
>
> Any help would be greatly appreciated.

How I would write the above test would be something like this:

#!/usr/bin/python
import ibm_db
conn = ibm_db.connect('ew_db', 'ew', 'ew_password')
server = ibm_db.server_info(conn)
if conn:

field1='123456789'
field2='XID'
field3='c'
field4=0
field5='None'

stmt, field3, field4, field5 = ibm_db.callproc(conn,

'prc_sel_data', (field1, field2, field3, field4, field5))
print field1,field2,field3,field4,field5

Please let me know if this works for you

[Ehrich]

Hello Tarun,

Thank you for your continued help. Unfortunately, I am still
unsuccessful. Below is my code (identical to yours, but included just
in case I missed something)

#!/usr/bin/python
import ibm_db

conn = ibm_db.connect('ew_db', 'ew', 'ew_password')
server = ibm_db.server_info(conn)

if conn:

field1='123456789'
field2='XID'
field3='c'
field4=0
field5='None'

stmt, field3, field4, field5 = ibm_db.callproc(conn,
'prc_sel_data', (field1, field2, field3, field4, field5))
print field1,field2,field3,field4,field5

And I am still receiving the "parameters not bound' error message.


Thank you,

Ehrich

[Tarun Pasrija]

Ehrich,

I dont see actually if something is missing. Can you please send me
your stored proc definition and CLITrace, it would be easier to debug
the exact problem. You can send the CLI trace to me at my IBM ID
directly.

Thanks and Regards
Tarun Pasrija
Open Source Application Development
IBM India Software Labs

[Ehrich]

Hi Tarun,

I cannot send the stored proc or the CLI trace (company rules).
But, what I can say is this:

The proc is defined like follows:

CREATE PROCEDURE "SCHEMA_NAME"."PROC_NAME" (
IN ip_id VARCHAR(100),
IN ip_id_ty CHAR(6),
OUT op_sqlstate CHAR(5),
OUT op_sqlcode INT,
OUT op_msg VARCHAR(255)
)
SPECIFIC proc_name
DYNAMIC RESULT SETS 3
LANGUAGE SQL

and there are no errors in the CLI output.

I was getting a SQL0440N error untill I added the following to the
code:

ibm_db.exec_immediate(conn, "SET SCHEMA TTNDBO")

Thanks,

Ehrich

[Tarun Pasrija]

Ehrich,

This was a very valuable bug report since we found out that giving
schema name with the procName was not handled properly. Guess this use-
case was missed. Give me 2-3 days time, I would upload a fresh patch
for you with this issue fixed.

Thanks for giving us this use-case.

Thanks and Regards
Tarun Pasrija
Open Source Application Development
IBM India Software Labs

[Ehrich]

Hello Tarun,

I'm glad I was able to help find it before it was officially
released. I will continue to check back here for another post from you
with a new link to test.

Thank you,

Ehrich

> ...
>
> read more »

[Tarun Pasrija]

Hi Ehrich,
Thank you again for reporting bug. Please go ahead and download

the patch from the following link

http://01175241965258921893-a-g.googlegroups.com/web/ibm_db-storedproc.tar.gz?gda=eT3wTkoAAACB-FVi9onll463rmKGika0yJE2VlFXoxW_cc4rHdMkDt2LxBcgWYeCQY_nBY4FqJqt3VEdmFjbmcm72jFIfEic_e3Wg0GnqfdKOwDqUih1tA

if the above link does not work for you, alternatively you can visit

the files section - http://groups.google.com/group/ibm_db/files and

download the ibm_db-storedproc.tar.gz.

this fixes the issues identified by you w.r.t. SP execution.

In earlier drop we asked you to look at the test cases to figure out
how to use the API. In addition now we are providing explicit
documentation

ibm_db.callproc(connection,procname[,parameters])


Call a stored database procedure with the given name. The
sequence of parameters must contain one entry for each
argument that the procedure expects. The result of the
call is returned as modified copy of the input
sequence. Input parameters are left untouched, output and
input/output parameters replaced with possibly new values.

The procedure may also provide a result set as
output. This must then be made available through the
standard .fetch*() methods.

Sample usage
Assumed SP defintion as below
create procedure match_animal(first_name VARCHAR(128), INOUT
second_name VARCHAR(128), OUT animal_weight DOUBLE PRECISION )
.... SP also returns a resultset

Sample python code to execute above SP can be as follows

import sys, ibm_db
conn = ibm_db.connect('dbname', 'username', 'password' )
if not conn:
print 'connection failed'
sys.exit()

name = "Peaches"
second_name = "Rickety Ride"
weight = 0

print "Values of parameters _before_ CALL:"

print " 1: %s 2: %s 3: %d\n" % (name, second_name,
weight)

stmt, name, second_name, weight = ibm_db.callproc(conn,
'schemaName.match_animal', (name, second_name, weight))
#schemaName is optional if schema name not specified than it uses
current default schema

if stmt is not None:
print "Values of parameters _after_ CALL:"

print " 1: %s 2: %s 3: %d\n" % (name, second_name,
weight)

print "Returned resultset rows:"

row = ibm_db.fetch_tuple(stmt)
while ( row ):
print " %s, %s, %s" % (row[0].strip(), row
[1].strip(), row[2])
row = ibm_db.fetch_tuple(stmt)

Please give it a try and let us know how it goes

Thanks,
Tarun

> ...
>
> read more »

[Dave Hughes]

Hi Tarun,

On Wed, 12 Aug 2009 05:06:14 -0700 (PDT)
Tarun Pasrija <tarun....@in.ibm.com> wrote:

[snip]

> stmt, name, second_name, weight = ibm_db.callproc(conn,
> 'schemaName.match_animal', (name, second_name, weight))
> #schemaName is optional if schema name not specified than it uses
> current default schema

[snip]

If the schema name isn't specified, the current path should be searched
to qualify the procedure name. See the entries for the "CURRENT
PATH" [1] special register and the "SET PATH" [2] statement in the
InfoCenter.

I haven't checked the code, but I'm assuming it doesn't *really*
qualify the procedure name and then call it - but simply leaves the
qualification to the database, in which case this is just a
documentation bug.

[1]
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0005877.html
[2]
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0001014.html

Cheers,

Dave.

[Ehrich]

Hi Tarun,

Thank you for the update. Unfortunately this version appears to be
crashing Python. Below is the error information collected by OS X. I
have tried the code both specifying the table and not. Also, I have
run both variations preceded with an ibm_db.exec_immediate(conn, "SET
SCHEMA SchemaName").

Thanks again for all the help.

Regards,

Ehrich


Problem Details:
-------------------------

Process: Python [2844]
Path: /System/Library/Frameworks/Python.framework/Versions/
2.5/Resources/Python.app/Contents/MacOS/Python
Identifier: Python
Version: ??? (???)
Code Type: X86 (Native)
Parent Process: bash [2726]

Interval Since Last Report: 72086 sec
Crashes Since Last Report: 4
Per-App Interval Since Last Report: 0 sec
Per-App Crashes Since Last Report: 4

Date/Time: 2009-08-13 09:29:16.933 -0400
OS Version: Mac OS X 10.5.8 (9L30)
Report Version: 6
Anonymous UUID: XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX

Exception Type: EXC_BAD_ACCESS (SIGSEGV)
Exception Codes: KERN_INVALID_ADDRESS at 0x00000000c0000003
Crashed Thread: 0

Thread 0 Crashed:
0 org.python.python 0x0015ce5a PyTuple_Pack + 152
1 org.python.python 0x0018e09b PyEval_EvalFrameEx +
11865
2 org.python.python 0x00191173 PyEval_EvalCodeEx + 1638
3 org.python.python 0x00191260 PyEval_EvalCode + 87
4 org.python.python 0x001a883c PyErr_Display + 1896
5 org.python.python 0x001a8e66 PyRun_FileExFlags + 135
6 org.python.python 0x001aa7d2 PyRun_SimpleFileExFlags
+ 421
7 org.python.python 0x001b5a57 Py_Main + 3095
8 org.python.pythonapp 0x00001fca 0x1000 + 4042

Thread 0 crashed with X86 Thread State (32-bit):
eax: 0x0003f9d0 ebx: 0x0015ce18 ecx: 0x00000006 edx: 0xc0000003
edi: 0x00000000 esi: 0x0003f9f4 ebp: 0xbfffee18 esp: 0xbfffede0
ss: 0x0000001f efl: 0x00010286 eip: 0x0015ce5a cs: 0x00000017
ds: 0x0000001f es: 0x0000001f fs: 0x00000000 gs: 0x00000037
cr2: 0xc0000003

Binary Images:
0x1000 - 0x1ffe org.python.pythonapp 2.5.0 (2.5.0a0)
<5aa9f0cc36fda395f965e08c96613cf5> /System/Library/Frameworks/
Python.framework/Versions/2.5/Resources/Python.app/Contents/MacOS/
Python
0x49000 - 0x4bfff +libdb2install.dylib ??? (???)
<42da5a3a90733004601b4619ab6bdf10> /Users/ehrich/sqllib/lib32/
libdb2install.dylib
0xbb000 - 0xbffff +libdb2trcapi.dylib ??? (???)
<6957a33c9cf0a2cc7459f5ebc6086e04> /Users/ehrich/sqllib/lib32/
libdb2trcapi.dylib
0xc4000 - 0xc4ffd libmx.A.dylib ??? (???) /usr/lib/
libmx.A.dylib
0x119000 - 0x1e5feb org.python.python 2.5 (2.5)
<523ba54c654eeed6bc670db2f58a73ab> /System/Library/Frameworks/
Python.framework/Versions/2.5/Python
0x232000 - 0x240ffc +ibm_db.so ??? (???)
<c51507c26fefd5340240d601641ed87b> /Library/Python/2.5/site-packages/
ibm_db-0.7.2.5-py2.5-macosx-10.5-i386.egg/ibm_db.so
0x247000 - 0x263fe7 +libdb2dascmn.dylib ??? (???)
<05f27aeac95764812015f56978674434> /Users/ehrich/sqllib/lib32/
libdb2dascmn.dylib
0x26b000 - 0x29bfe3 +libdb2genreg.dylib ??? (???)
<8c7ab9cd7e038ee0f30c4781982018bd> /Users/ehrich/sqllib/lib32/
libdb2genreg.dylib
0x2a4000 - 0x2a9fff +libdb2locale.dylib ??? (???)
<4dcca4c86c093efa507b761d5f980b9c> /Users/ehrich/sqllib/lib32/
libdb2locale.dylib
0x2dd000 - 0x2edfef +libicclib.dylib ??? (???)
<36a88edfa1cad248f89e3ce70db38d1f> /Users/ehrich/sqllib/lib32/icc/
icclib/libicclib.dylib
0x2f9000 - 0x2fbfff +IBMOSauthclient.dylib ??? (???)
<5a69df09088408ce9d2b9c893ca17708> /Users/ehrich/sqllib/security32/
plugin/IBM/client/IBMOSauthclient.dylib
0x400000 - 0x446fff +libdb2osse_db2.dylib ??? (???)
<d3a8851539c7351585e31c78583f7a0a> /Users/ehrich/sqllib/lib32/
libdb2osse_db2.dylib
0x522000 - 0x5dffeb +libcryptoIBM.0.9.7.dylib ??? (???)
<298f0f68e0774f5d914648e59d9f58c2> /Users/ehrich/sqllib/lib32/icc/
osslib/libcryptoIBM.0.9.7.dylib
0x1000000 - 0x1b56fe7 +libdb2.dylib ??? (???)
<86faa316ad1fddf92322e88d8551edb8> /Users/ehrich/sqllib/lib32/
libdb2.dylib
0x211b000 - 0x2159fe7 +libdb2g11n.dylib ??? (???)
<8a82d5ef10568847be51c31d1fdd86c1> /Users/ehrich/sqllib/lib32/
libdb2g11n.dylib
0x277d000 - 0x2960fe3 +libdb2osse.dylib ??? (???)
<254501f3b0d3fe124357d7ff4c8b5d99> /Users/ehrich/sqllib/lib32/
libdb2osse.dylib
0x8fe00000 - 0x8fe2db43 dyld 97.1 (???)
<458eed38a009e5658a79579e7bc26603> /usr/lib/dyld
0x90d7d000 - 0x90d9bff3 com.apple.DirectoryService.Framework 3.5.6
(3.5.6) <daa1307737197c7757f44f16370249dc> /System/Library/Frameworks/
DirectoryService.framework/Versions/A/DirectoryService
0x917eb000 - 0x919bcffb com.apple.security 5.0.5 (36371)
<1f7f48b36bc90d114220cc81e4e4694f> /System/Library/Frameworks/
Security.framework/Versions/A/Security
0x91a9f000 - 0x91aa6fe9 libgcc_s.1.dylib ??? (???)
<f53c808e87d1184c0f9df63aef53ce0b> /usr/lib/libgcc_s.1.dylib
0x91aa7000 - 0x91b04ffb libstdc++.6.dylib ??? (???)
<04b812dcec670daa8b7d2852ab14be60> /usr/lib/libstdc++.6.dylib
0x93432000 - 0x93436fff libmathCommon.A.dylib ??? (???) /usr/lib/
system/libmathCommon.A.dylib
0x93444000 - 0x93462fff libresolv.9.dylib ??? (???)
<a8018c42930596593ddf27f7c20fe7af> /usr/lib/libresolv.9.dylib
0x93516000 - 0x935f6fff libobjc.A.dylib ??? (???)
<7b92613fdf804fd9a0a3733a0674c30b> /usr/lib/libobjc.A.dylib
0x94807000 - 0x94832fe7 libauto.dylib ??? (???)
<42d8422dc23a18071869fdf7b5d8fab5> /usr/lib/libauto.dylib
0x94e0a000 - 0x94f43ff7 libicucore.A.dylib ??? (???)
<dd8aa51c356e79ef8cdfa341a0d69f5b> /usr/lib/libicucore.A.dylib
0x94f44000 - 0x95077fe7 com.apple.CoreFoundation 6.5.7 (476.19)
<a332c8f45529ee26d2e9c36d0c723bad> /System/Library/Frameworks/
CoreFoundation.framework/Versions/A/CoreFoundation
0x9522b000 - 0x95392ff3 libSystem.B.dylib ??? (???)
<ae47ca9b1686b065f8ac4d2de09cc432> /usr/lib/libSystem.B.dylib
0xfffe8000 - 0xfffebfff libobjc.A.dylib ??? (???) /usr/lib/
libobjc.A.dylib
0xffff0000 - 0xffff1780 libSystem.B.dylib ??? (???) /usr/lib/
libSystem.B.dylib

System Configuration:
----------------------------------

Model: MacPro3,1, BootROM MP31.006C.B05, 8 processors, Quad-Core Intel
Xeon, 2.8 GHz, 6 GB
Graphics: kHW_ATIrv630Item, ATI Radeon HD 2600,
spdisplays_pcie_device, 256 MB
Graphics: kHW_ATIrv630Item, ATI Radeon HD 2600,
spdisplays_pcie_device, 256 MB
Memory Module: global_name
Bluetooth: Version 2.1.8f2, 2 service, 0 devices, 1 incoming serial
ports
Network Service: Ethernet 1, Ethernet, en0
Network Service: Ethernet 2, Ethernet, en1
PCI Card: ATI Radeon HD 2600, display, Slot-4
PCI Card: ATI Radeon HD 2600, display, Slot-1
Serial ATA Device: WDC WD3200AAJS-41VWA1, 298.09 GB
Serial ATA Device: WDC WD5000AAKS-00A7B0, 465.76 GB
Parallel ATA Device: OPTIARC DVD RW AD-7170A
USB Device: Keyboard Hub, (null) mA
USB Device: Apple Optical USB Mouse, (null) mA
USB Device: Apple Keyboard, (null) mA
USB Device: Bluetooth USB Host Controller, (null) mA
USB Device: Back-UPS CS 500 FW:806.q8.D USB FW:q8, (null) mA
FireWire Device: built-in_hub, unknown_value, unknown_speed

On Aug 12, 8:06 am, Tarun Pasrija <tarun.pasr...@in.ibm.com> wrote:
> Hi Ehrich,
>     Thank you again for reporting bug. Please go ahead and download
> the patch from the following link
>

> http://01175241965258921893-a-g.googlegroups.com/web/ibm_db-storedpro...

> ...
>
> read more »

[Tarun Pasrija]

Dave/ Ehrich

Thanks for your updates and patience. I have not tested on MacOS but
it worked fine on Linux/Windows. In the next patch I would make sure
to try this on Mac as well. Below are our findings presently in
accordance with Dave's post.

Dave is right about the Current path settings as mentioned in
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.db2.luw.sql.ref.doc/doc/r0005877.html

SET SCHEMA does not effect stored procedure calls, but only does for
table names. Schema to be used when an unqualified procedure name is
given follows the following rule as mentioned in the Infocenter above.
Ehrich, using "SET CURRENT SCHEMA X" to get the procedure from schema
X would not work unless we explicitly mention X.procname. How
procedures are searched in DB2 is according to PATH.

The CURRENT PATH (or CURRENT_PATH) special register specifies a VARCHAR
(2048) value that identifies the SQL path used when resolving
unqualified function names, procedure names, data type names, global
variable names, and module object names in dynamically prepared SQL
statements.

Ehrich, thus we are changing our current implementation given the
above constraints and have come up with the following use cases:-

1. If call has been made to a procedure as X.ProcName where X is the
schema name, then we search the procedure in the schema X. If not
found, we throw an error.
2. If call has unqualified ProcName, then we search the current path
and return the procedure found in the first schema in the path. Like
Dave correctly mentioned, the order of the path to search for if a
procedureName is present in more than one schema can be explicitly
defined by the user according to
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0001014.html

We appreciate your patience and are working on this. It is good to
have such feedbacks before the release since it gives us a lot of in-
depth use cases to test on. We would provide a test case with the next
patch in a couple of days!!

Thanks and Regards
Tarun Pasrija
Open Source Application Development
IBM India Software Labs

On Aug 13, 6:43 pm, Ehrich <csg.a...@gmail.com> wrote:
> Hi Tarun,
>

> ...
>
> read more »

[Ehrich]

Hello Tarun,

Thank you. I thought Dave was correct, which is why I included in
my email all the permutations of the test code. I will look for the
next patch and I will test as soon as possible.

Regards,

Ehrich

On Aug 13, 10:19 am, Tarun Pasrija <tarun.pasr...@in.ibm.com> wrote:
> Dave/ Ehrich
>
> Thanks for your updates and patience. I have not tested on MacOS but
> it worked fine on Linux/Windows. In the next patch I would make sure
> to try this on Mac as well. Below are our findings presently in
> accordance with Dave's post.
>

> Dave is right about the Current path settings as mentioned inhttp://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=...

>
> SET SCHEMA does not effect stored procedure calls, but only does for
> table names. Schema to be used when an unqualified procedure name is
> given follows the following rule as mentioned in the Infocenter above.
> Ehrich, using "SET CURRENT SCHEMA X" to get the procedure from schema
> X would not work unless we explicitly mention X.procname. How
> procedures are searched in DB2 is according to PATH.
>
> The CURRENT PATH (or CURRENT_PATH) special register specifies a VARCHAR
> (2048) value that identifies the SQL path used when resolving
> unqualified function names, procedure names, data type names, global
> variable names, and module object names in dynamically prepared SQL
> statements.
>
> Ehrich, thus we are changing our current implementation given the
> above constraints and have come up with the following use cases:-
>
> 1. If call has been made to a procedure as X.ProcName where X is the
> schema name, then we search the procedure in the schema X. If not
> found, we throw an error.
> 2. If call has unqualified ProcName, then we search the current path
> and return the procedure found in the first schema in the path. Like
> Dave correctly mentioned, the order of the path to search for if a
> procedureName is present in more than one schema can be explicitly

> defined by the user according tohttp://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db...

[Mario]

Just to clarify the real reason for all this complex stuff...

the core of the issue is the new signature of the callProc method. If
you look at it, removes the need for the developer to do prepare/
bindParams. What this means is now the implementation has to do this
i.e. bindParams. So how does it know how to bind the parameters ? how
many, what types etc - it uses the catalog API and more specifically
SQLProcedureColumns to figure out # of columns and their types etc.

Now comes the issue - the catalog API's do not honor CURRENT PATH (or
CURRENT SCHEMA). Thus if the developer never explicitly qualified the
procedurename with the schema in callproc(), we could end up
a - looking up the metadata of one SP, binding to that spec and
then
b - in execute function, the database engine tries to execute a
totally different SP
(possible since one can have SP's with same name in different schemas)

Easiest way would be to force the developer to qualify the procName,
but an application developer should never hardcode schema names in a
SQL statement.

Tarun/team has been gracious to still keep the procCall signature and
as well not force you to bad SQL programming practices. Thus writing
extra code to implement #1 and #2 below.

thanks.

On Aug 13, 7:19 pm, Tarun Pasrija <tarun.pasr...@in.ibm.com> wrote:
> Dave/ Ehrich
>
> Thanks for your updates and patience. I have not tested on MacOS but
> it worked fine on Linux/Windows. In the next patch I would make sure
> to try this on Mac as well. Below are our findings presently in
> accordance with Dave's post.
>

> Dave is right about the Current path settings as mentioned inhttp://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=...

>
> SET SCHEMA does not effect stored procedure calls, but only does for
> table names. Schema to be used when an unqualified procedure name is
> given follows the following rule as mentioned in the Infocenter above.
> Ehrich, using "SET CURRENT SCHEMA X" to get the procedure from schema
> X would not work unless we explicitly mention X.procname. How
> procedures are searched in DB2 is according to PATH.
>
> The CURRENT PATH (or CURRENT_PATH) special register specifies a VARCHAR
> (2048) value that identifies the SQL path used when resolving
> unqualified function names, procedure names, data type names, global
> variable names, and module object names in dynamically prepared SQL
> statements.
>
> Ehrich, thus we are changing our current implementation given the
> above constraints and have come up with the following use cases:-
>
> 1. If call has been made to a procedure as X.ProcName where X is the
> schema name, then we search the procedure in the schema X. If not
> found, we throw an error.
> 2. If call has unqualified ProcName, then we search the current path
> and return the procedure found in the first schema in the path. Like
> Dave correctly mentioned, the order of the path to search for if a
> procedureName is present in more than one schema can be explicitly

> defined by the user according tohttp://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db...

[Ehrich]

Hello Mario,

Completely understood. I hope that my previous posts did not come
off as not understanding the complexities or unappreciative or Tarun/
team's efforts. I am very grateful and very impressed with the speed
in which updates are created and the fact that, through this forum, we
are given such great access to the developers. Most companies do not
provide such access and interaction, not to mention the fact that
Tarun/team have been providing me with pre-release versions to test in
order to resolve the issue.

Again, I hope that I have not appeared as anything other that
understanding and greatly appreciative of everyone's efforts.

Regards,

Ehrich

[Dave Hughes]

On Fri, 14 Aug 2009 03:45:44 -0700 (PDT)
Mario <mario....@in.ibm.com> wrote:

>
> Just to clarify the real reason for all this complex stuff...
>
> the core of the issue is the new signature of the callProc method. If
> you look at it, removes the need for the developer to do prepare/
> bindParams. What this means is now the implementation has to do this
> i.e. bindParams. So how does it know how to bind the parameters ?

Typically, by asking the database to describe the type of each
parameter with the SQLDescribeParam() [1] and SQLNumParams() [2] API
calls after performing SQLPrepare() [3] on a CALL statement.

How does one construct the CALL statement with the right number of
markers in the first place? Look at the number of elements in the tuple
of parameters passed to callproc() at a guess (if a parameter is a pure
OUT parameter instead of INOUT, then the value can simply be ignored;
it will simply serve to inform the method of the number of parameters
to utilize).

[1]
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.apdv.cli.doc/doc/r0000582.html

[2]
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.apdv.cli.doc/doc/r0000631.html

[3]
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.apdv.cli.doc/doc/r0000635.html

> how
> many, what types etc - it uses the catalog API and more specifically
> SQLProcedureColumns to figure out # of columns and their types etc.

There should never be any need to query the catalog at all. The process
of preparing the statement (with SQLPrepare) will cause the optimizer
to determine the type and direction of all parameters at which point
one can simply ask the database about them (with the aforementioned
calls).

Incidentally, I'd strongly discourage any such catalog querying method
anyway on the grounds that getting the name parsing correct is *hard*.
Again, I haven't looked at the code but I wonder how it would handle a
pathologically silly, but still perfectly valid example like:

callproc(conn, '"My Silly.""Schema""\nName".SOME_PROC', (
foo, bar, baz))

Would the code correctly ignore the period in the schema name,
maintain the mixed case, strip the surrounding double quotes, replace
the doubled double-quotes with single double-quotes and then use the
result in the query? If it would then I'm most impressed, although it's
still the wrong tactic here...

> Now comes the issue - the catalog API's do not honor CURRENT PATH (or
> CURRENT SCHEMA).

And with good reason, because while the catalog APIs are standard (in as
much as they're defined by ODBC which is the basis of DB2's CLI), the
CURRENT SCHEMA and CURRENT PATH special registers are DB2 specific (I'm
not sure if they're part of the SQL standard in general, but if they
are they're another largely ignored part - also with good reason ;-).

Hence, the catalog APIs have no means of representing these concepts
(obviously a straight query against the catalog views like
SYSCAT.ROUTINEPARMS would, but SYSCAT is DB2 for LUW specific, and
you're targetting several other platforms too, so this isn't a
solution either).

Cheers,

Dave.

[Mario]

Not at all. The reason i jumped in is i thought it would be helpful if
some light was thrown on the reasoning and approach.

I am even more pleased that i did that, since now we are having
alternate approaches being openly discussed, thanks to Dave. As long
as we are open, the best approach/implementation will get in.

"Accepting feedback through a web page and providing mechanisms for
people to report defects is one thing. Allowing everyone else to see
that feedback and all those defects is another, much more
uncomfortable, step. Allowing everyone to see the source code so they
can review it and try it is also an act of faith. Providing a public
forum where people can openly criticize and contribute to the design
and implementation of the software is another act of faith."
[http://wiki.pentaho.com/display/BEEKEEPER/2.+The+Principles+of+Open
+Source]

I am happy we have been able to take those leaps of faith :-)

Mario

[Mario]

Dave,

i appreciate you taking time and effort on this

The very start part of your algo was tangentially different. I agree
its better.

Mario

On Aug 14, 5:42 pm, Dave Hughes <d...@waveform.org.uk> wrote:
> On Fri, 14 Aug 2009 03:45:44 -0700 (PDT)
>

> Mario <mario.bri...@in.ibm.com> wrote:
>
> > Just to clarify the real reason for all this complex stuff...
>
> > the core of the issue is the new signature of the callProc method. If
> > you look at it, removes the need for the developer to do prepare/
> > bindParams. What this means is now the implementation has to do this
> > i.e. bindParams. So how does it know how to bind the parameters ?
>
> Typically, by asking the database to describe the type of each
> parameter with the SQLDescribeParam() [1] and SQLNumParams() [2] API
> calls after performing SQLPrepare() [3] on a CALL statement.
>
> How does one construct the CALL statement with the right number of
> markers in the first place? Look at the number of elements in the tuple
> of parameters passed to callproc() at a guess (if a parameter is a pure
> OUT parameter instead of INOUT, then the value can simply be ignored;
> it will simply serve to inform the method of the number of parameters
> to utilize).
>

> [1]http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db...
>
> [2]http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db...
>
> [3]http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db...

[Tarun Pasrija]

Dave,

Checking back the infocenter for SQLDescribeParam
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.apdv.cli.doc/doc/r0000582.html

Excerpt:-
SQLDescribeParam() does not return the type (input, input/output, or
output) of a parameter in an SQL statement. Except in calls to stored
procedures, all parameters in SQL statements are input parameters. To
determine the type of each parameter in a call to a stored procedure,
call SQLProcedureColumns().

However, having a solution using SQLProcedureColumns would be a
problem like you correctly mentioned that the naming convention
defining the procedure name and schema name can have multiple special
characters enclosed inside quotes.

Thus, we have come to a following workaroung, which works fine(though
we are verifying its correctness).
Instead of calling catalogue for extracting information, we bind the
params for all IN/OUT/INOUT as INOUT which I believe is a superset of
all.

Ehrich, I have tested the implementation on Mac and it is not crashing
for me. Can you help me reproduce the test case that is failing for
you.? I would also like to know the db2level you are on and the client
and server platforms to be able to reproduce.

Thanks and Regards
Tarun Pasrija

On Aug 14, 5:42 pm, Dave Hughes <d...@waveform.org.uk> wrote:

> On Fri, 14 Aug 2009 03:45:44 -0700 (PDT)
>

> Mario <mario.bri...@in.ibm.com> wrote:
>
> > Just to clarify the real reason for all this complex stuff...
>
> > the core of the issue is the new signature of the callProc method. If
> > you look at it, removes the need for the developer to do prepare/
> > bindParams. What this means is now the implementation has to do this
> > i.e. bindParams. So how does it know how to bind the parameters ?
>
> Typically, by asking the database to describe the type of each
> parameter with the SQLDescribeParam() [1] and SQLNumParams() [2] API
> calls after performing SQLPrepare() [3] on a CALL statement.
>
> How does one construct the CALL statement with the right number of
> markers in the first place? Look at the number of elements in the tuple
> of parameters passed to callproc() at a guess (if a parameter is a pure
> OUT parameter instead of INOUT, then the value can simply be ignored;
> it will simply serve to inform the method of the number of parameters
> to utilize).
>

> [1]http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db...
>
> [2]http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db...
>
> [3]http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db...
>

[Dave Hughes]

Hi Tarun,

On Mon, 17 Aug 2009 10:01:02 -0700 (PDT)
Tarun Pasrija <tarun....@in.ibm.com> wrote:

> Checking back the infocenter for SQLDescribeParam
> http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.apdv.cli.doc/doc/r0000582.html
>
> Excerpt:-
> SQLDescribeParam() does not return the type (input, input/output, or
> output) of a parameter in an SQL statement. Except in calls to stored
> procedures, all parameters in SQL statements are input parameters. To
> determine the type of each parameter in a call to a stored procedure,
> call SQLProcedureColumns().

Ah, I'd missed that bit. How annoying.

> However, having a solution using SQLProcedureColumns would be a
> problem like you correctly mentioned that the naming convention
> defining the procedure name and schema name can have multiple special
> characters enclosed inside quotes.

Indeed. DB2's particularly "generous" with delimited identifiers even
allowing things like newline characters within them (I vaguely recall
testing it once and finding the only thing I couldn't reliably include
in a delimited identifier was a NULL (\0) character!).

Thankfully there's rarely any case for a driver to perform name parsing
(there are exceptions to that, but they're generally only for cases of
databases or drivers which are dealing with badly designed interfaces;
not the case here).

> Thus, we have come to a following workaroung, which works fine(though
> we are verifying its correctness).
> Instead of calling catalogue for extracting information, we bind the
> params for all IN/OUT/INOUT as INOUT which I believe is a superset of
> all.

Interesting. Personally, in such a circumstance I would've been tempted
to alter the prototype of the callproc function so that the direction
would be included with each parameter value, e.g.:

# Somewhere in the driver definition
(IN, OUT, INOUT) = range(3)

# Example client code
(stmt, b, c) = callproc(conn, 'FOO', (
('a', IN), ('b', INOUT), (None, OUT)))

However, if binding all parameters as INOUT works in all scenarios
(INOUT as IN, OUT, and INOUT) then it's a nice solution which keeps the
callproc prototype pleasantly simple.

Cheers,

Dave.

[Tarun Pasrija]

Ehrich,

As I mentioned in my previous post, can you please provide me the
scenario on Mac OS that crashes python? Would request you to help me
to reproduce the crash. We have finished the implementation though
according to the discussions involved and testing various scenarios.

Thanks and Regards
Tarun Pasrija

On Aug 17, 11:06 pm, Dave Hughes <d...@waveform.org.uk> wrote:
> Hi Tarun,
>
> On Mon, 17 Aug 2009 10:01:02 -0700 (PDT)
>

> Tarun Pasrija <tarun.pasr...@in.ibm.com> wrote:
> > Checking back the infocenter for SQLDescribeParam

> >http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db...

[Ehrich]

Hello Tarun,

I am using the same code as before. The method being called is
defined as follows:

The proc is defined like follows:
CREATE PROCEDURE "SCHEMA_NAME"."PROC_NAME" (
IN ip_id VARCHAR(100),
IN ip_id_ty CHAR(6),
OUT op_sqlstate CHAR(5),
OUT op_sqlcode INT,
OUT op_msg VARCHAR(255)
)
SPECIFIC proc_name
DYNAMIC RESULT SETS 3
LANGUAGE SQL

And the python code is :

#!/usr/bin/python
import sys
import ibm_db


conn = ibm_db.connect('SCHEMA_NAME', 'USERNAME','PASSWORD')

if conn:

field1='123456789'
field2='XXX'
field3=''
field4=int(0)
field5='None'
stmt = ''

stmt, field3, field4, field5 = ibm_db.callproc(conn,'SCHEMA_NAME.
PROC_NAME',(field1, field2, field3, field4, field5))
print field1,field2,field3,field4,field5

The error report is:
Process: Python [22315]

Path: /System/Library/Frameworks/Python.framework/Versions/
2.5/Resources/Python.app/Contents/MacOS/Python
Identifier: Python
Version: ??? (???)
Code Type: X86 (Native)

Parent Process: java [18145]

Interval Since Last Report: 457222 sec
Crashes Since Last Report: 9

Per-App Interval Since Last Report: 0 sec

Per-App Crashes Since Last Report: 8

Date/Time: 2009-08-18 16:30:03.905 -0400

OS Version: Mac OS X 10.5.8 (9L30)
Report Version: 6

Anonymous UUID: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Exception Type: EXC_BAD_ACCESS (SIGSEGV)
Exception Codes: KERN_INVALID_ADDRESS at 0x00000000c0000003
Crashed Thread: 0

Thread 0 Crashed:
0 org.python.python 0x0015ce5a PyTuple_Pack + 152
1 org.python.python 0x0018e09b PyEval_EvalFrameEx +
11865
2 org.python.python 0x00191173 PyEval_EvalCodeEx + 1638
3 org.python.python 0x00191260 PyEval_EvalCode + 87
4 org.python.python 0x001a883c PyErr_Display + 1896
5 org.python.python 0x001a8e66 PyRun_FileExFlags + 135
6 org.python.python 0x001aa7d2 PyRun_SimpleFileExFlags
+ 421
7 org.python.python 0x001b5a57 Py_Main + 3095
8 org.python.pythonapp 0x00001fca 0x1000 + 4042

Thread 0 crashed with X86 Thread State (32-bit):

eax: 0x0003fab0 ebx: 0x0015ce18 ecx: 0x00000006 edx: 0xc0000003
edi: 0x00000000 esi: 0x0003fad4 ebp: 0xbfffe798 esp: 0xbfffe760

ss: 0x0000001f efl: 0x00010286 eip: 0x0015ce5a cs: 0x00000017
ds: 0x0000001f es: 0x0000001f fs: 0x00000000 gs: 0x00000037
cr2: 0xc0000003

Binary Images:
0x1000 - 0x1ffe org.python.pythonapp 2.5.0 (2.5.0a0)
<5aa9f0cc36fda395f965e08c96613cf5> /System/Library/Frameworks/
Python.framework/Versions/2.5/Resources/Python.app/Contents/MacOS/
Python
0x49000 - 0x4bfff +libdb2install.dylib ??? (???)
<42da5a3a90733004601b4619ab6bdf10> /Users/ehrich/sqllib/lib32/
libdb2install.dylib

0xa7000 - 0xb5ffc +ibm_db.so ??? (???)
<c51507c26fefd5340240d601641ed87b> /Library/Python/2.5/site-packages/
ibm_db-0.7.2.5-py2.5-macosx-10.5-i386.egg/ibm_db.so
0xbc000 - 0xc0fff +libdb2trcapi.dylib ??? (???)
<6957a33c9cf0a2cc7459f5ebc6086e04> /Users/ehrich/sqllib/lib32/
libdb2trcapi.dylib
0xc5000 - 0xc5ffd libmx.A.dylib ??? (???) /usr/lib/

libmx.A.dylib
0x119000 - 0x1e5feb org.python.python 2.5 (2.5)
<523ba54c654eeed6bc670db2f58a73ab> /System/Library/Frameworks/
Python.framework/Versions/2.5/Python

0x232000 - 0x24efe7 +libdb2dascmn.dylib ??? (???)
<05f27aeac95764812015f56978674434> /Users/ehrich/sqllib/lib32/
libdb2dascmn.dylib
0x256000 - 0x286fe3 +libdb2genreg.dylib ??? (???)
<8c7ab9cd7e038ee0f30c4781982018bd> /Users/ehrich/sqllib/lib32/
libdb2genreg.dylib
0x28f000 - 0x294fff +libdb2locale.dylib ??? (???)
<4dcca4c86c093efa507b761d5f980b9c> /Users/ehrich/sqllib/lib32/
libdb2locale.dylib
0x2c8000 - 0x2d8fef +libicclib.dylib ??? (???)
<36a88edfa1cad248f89e3ce70db38d1f> /Users/ehrich/sqllib/lib32/icc/
icclib/libicclib.dylib
0x2e4000 - 0x2e6fff +IBMOSauthclient.dylib ??? (???)

Please let me know what other information you need. But, basically,
nothing has changed other than updating the versions when you provide
a new one.

Thanks,

Ehrich

[Ehrich]

Hi Tarun,

I am not sure if this is connected, but a program that was
working, and has not had any code changed, is now failing with the
following errors:

File "/Library/Python/2.5/site-packages/ibm_db_dbi.py", line 671, in
execute
return self._set_rowcount(operation)
File "/Library/Python/2.5/site-packages/ibm_db_dbi.py", line 639, in
_set_rowcount
raise _get_exception(inst)
ibm_db_dbi.ProgrammingError: '[IBM][CLI Driver] CLI0100E Wrong number
of parameters. SQLSTATE=07001 SQLCODE=-99999'

Regards,

Ehrich

[Mario]

Just closing the loop on this thread (for the sake of completeness).
The crash was due to the situation where-in the Stored Proc leaves an
out parameter uninitialized/unassigned after execution. This has been
handled in the driver and Ehrich & James Ji have verified all their SP
test scenario pass. This is scheduled to be delivered in the next
refresh of the driver i.e version > 0.7.2.5

thanks
Mario Briggs