proposal for callproc implementation

[Joshua L]

Hey Michael (and others),

The group I work with is currently using pyodbc in a few of our services to read/write our MSSQL Server databases with great success. (Unfortunately) All of our database interaction is done via stored procedures.
As such we'd like to optimize stored procedure calls as much as possible. The current method of using pyodbc.execute() to call stored procedures does seem to incur some unnecessary overhead due to the sp_prepare call made
to the db when preparing the SQL statement containing the stored procedure for execution.

Long story short, we've forked pyodbc and implemented callproc with the requirement that the caller indicate the INTPUT_OUTPUT and OUTPUT parameters by wrapping the argument with a new SQLParameter object.
Here are some usage examples, including the stored procedure definitions:

=====

(SQL Server 2008)

create procedure f_move_binary

@in varbinary(50),

@out varbinary(50) out

as

select @out = (select @in)

return;

go

(python 2)

>>> v = pyodbc.SQLParameter(bytearray(), pyodbc.SQL_PARAM_OUTPUT)

>>> cursor.callproc('f_move_binary', bytearray(b'hello, world'), v)

(bytearray(b'hello, world'), bytearray(b'hello, world'))

=====

create procedure p

@a varchar(max) output,

@b int output

as

begin

select @a = (select @@version),

  @b = @b+1;

return;

end

(python 2)

>>> ver_p = pyodbc.SQLParameter('', pyodbc.SQL_PARAM_OUTPUT)

>>> v_p = pyodbc.SQLParameter(41, pyodbc.SQL_PARAM_INPUT_OUTPUT)

>>> cursor.callproc('p', ver_p, v_p)

('Microsoft SQL Server 2008 (SP3) - 10.0.5828.0 (X64) \n\tNov  1 2012 22:54:10 \n\tCopyright (c) 1988-2008 Microsoft Corporation\n\tEnterprise Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)\n', 42)

>>> ver_p = pyodbc.SQLParameter('', pyodbc.SQL_PARAM_OUTPUT, 50)

>>> r = cursor.callproc('p', ver_p, v_p)

>>> print r

('Microsoft SQL Server 2008 (SP3) - 10.0.5828.0 (X64', 42)

>>> len(r[0])

50

>>> ver_p.value

''

>>> v_p.value

41

(for python 3 it's the same)

===

(SQL Server 2008)

create table S(s nvarchar(30));

go

create procedure insert_s

@n nvarchar(30)

as

insert into S values(@n)

go

(python 2 & 3)

>>> r = cursor.callproc('insert_s', u'金坷垃好处都有啥')

>>> cursor.commit()

(inserted into the table as expected)

We'd be interested in getting feedback from Michael K (pyodbc's maintainer) and others about there thoughts about our proposed solution for this.

Ideally we'd like to have the maintained, stable branch of pyodbc support callproc and not have to manage our own fork of pyodbc.

Thanks,

-j

[cory....@gmail.com]

I have been using pyodbc to connect to our iSeries and it works great for running normal SQL or stored procedures that return a result set using "execute" but in order to call stored procedures that have in/out parameters I have had to use PHP and bounce requests around (mainly because they are externally defined procedures in CL/RPG).

I like the idea of having a simple wrapper but only needing to use it when it is IN/OUT or OUT parameter.  If this feature (callproc) was added it sure would make things easier for the things I use pyodbc for.

Thanks
Cory L