Using cursor.callproc with zxJDBC

[Gerhard had problems with Jython's stored procedure support]

'''
First, stored procedure support in Jython 2.1 is far from
perfect.  Since I rarely used stored procedures I've only
recently gotten around to implementing them in current
CVS. Jython 2.2 will have a much better implementation of
.callproc.

Secondly, it's very difficult to abstract stored
procedure handling among the countless JDBC drivers
zxJDBC supports since each vendor does stored procedures
somewhat differently (especially how data is returned).
Current CVS has a number of changes to the DataHandler
interface in order to help invoke procedures properly.

That said we'll carry on.  zxJDBC attempts to construct
the JDBC call syntax on your behalf and does so by making
use of a JDBC DatabaseMetaData's .getProcedureColumns()
[1] method. Without a successful result from gPC() it
cannot construct the proper calling syntax.  Part of the
problem with getting the correct data back is how
different backends interpret the schema and owner
parameters.  This might be part the problem you are
seeing with zxJDBC thinking you have the incorrect number
of parameters and such.

So the final outcome from this is Jython 2.1 is kinda
crippled wrt stored procedures.  I would suggest wrapping
up the call if possible so that when upgrading to Jython
2.2 your application will not have to change and the new
functionality of Jython 2.2 can be used.  I'll also have
a far better tutorial on stored procedure usage in the
documentation.

In the interim, if you wish to test the CVS version of
Jython's new stored procedure support please let me know
and I will help you out in any way possible to test it
for your environment.  I'd really like to make stored
procedures more reliable but I need help testing out all
the different scenarios. I'm also open to api suggestions
so if you have opinions on that I'd be happy to hear them.

In CVS, the following works:
'''

from com.ziclix.python.sql import zxJDBC
from com.ziclix.python.sql.handler import OracleDataHandler

db = zxJDBC.connect(
	"jdbc:oracle:thin:@localhost:1521:ORA817",
	"divineuser",
	"divinedba",
	"oracle.jdbc.driver.OracleDriver"
)
c = db.cursor()

# it's very important to use the appropriate datahandler so the
# proper casing of names is handle correctly
c.datahandler = OracleDataHandler(c.datahandler)

try:
	c.execute("create table sptest (x varchar2(20))")
except:
	pass

sp = "create or replace procedure procin (y in varchar2) is"
sp += " begin insert into sptest values (y); end;"
c.execute(sp)

sp = "create or replace procedure procinout (y out varchar2,"
sp += " z in varchar2) is begin insert into sptest values (z)"
sp += "; y := 'tested'; end;"
c.execute(sp)

c.execute("delete from sptest")
db.commit()

params = ['testProcIn']
c.callproc("procin", params)

params = [None, "testing"]
c.callproc("procinout", params)
assert params[0] == "tested"

print "*" * 10
c.execute("select * from sptest")
for a in c: print a

db.commit()
c.close()
db.close()

'''
thanks,

brian

[1] http://java.sun.com/j2se/1.3/docs/api/java/sql/DatabaseMetaData.html#getProcedureColumns(java.lang.String,
java.lang.String, java.lang.String, java.lang.String)
'''