SqlAlchemy and Stored Procedures with variables

[Mike]

Hi,

We're trying to use SQLAlchemy with MS SQL Server 2000 using stored
procedures. We connect to the server doing something like this:

engine = sqlalchemy.create_engine('mssql://
user:p...@ntsql.ourWebsite.com/db')
cur = engine.connect()
print cur.execute("execute stored_proc @query='gra%';").fetchall()


This gives us the following traceback:

Traceback (most recent call last):
File "\\ubuntuwww\mcisroot\opt\Plone-2.5\sqltest.py", line 7, in
<module>
print cur.execute("execute sp_EDEN_Vendors (@query='gra
%');").fetchall()
File "c:\python25\lib\site-packages\sqlalchemy-0.4.5-py2.5.egg
\sqlalchemy\engine\base.py", line 844, in execute
return Connection.executors[c](self, object, multiparams, params)
File "c:\python25\lib\site-packages\sqlalchemy-0.4.5-py2.5.egg
\sqlalchemy\engine\base.py", line 854, in _execute_text
self.__execute_raw(context)
File "c:\python25\lib\site-packages\sqlalchemy-0.4.5-py2.5.egg
\sqlalchemy\engine\base.py", line 916, in __execute_raw
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File "c:\python25\lib\site-packages\sqlalchemy-0.4.5-py2.5.egg
\sqlalchemy\engine\base.py", line 960, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor)
File "c:\python25\lib\site-packages\sqlalchemy-0.4.5-py2.5.egg
\sqlalchemy\engine\base.py", line 942, in _handle_dbapi_exception
raise exceptions.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
DatabaseError: (DatabaseError) internal error: None "execute
sp_EDEN_Vendors (@query='gra%');" {}


If we take out the "@query='gra%';" part, it runs fine. However, we
need to be able to pass arguments to the stored procedure. How do we
do this?

Thanks!

Mike

[Rick Morrison]

Does the same statement work in an interactive query window, complete with the embedded semicolon you're using?

Also, you should be able to use positional parameters instead of named parameters in your call:
      cur.execute("execute stored_proc 'gra%' ")

Note that as of yet there is no SQLAlchemy support for OUT or IN/OUT parameters; currently you can return a single set of results via a SELECT in the stored procedure.

Rick

[Michael Bayer]

theres OUT param support for Oracle - MS-SQL dialect could follow the
same approach, provided pyodbc allows it.

[Mike]

Rick,

The SQL works in our SQL analyzer with or without the named parameter
and with the semicolon. We tried it the way you mentioned too, using
positional parameters, and got the same error. I apologize for
forgetting to mention that in my first post.

Any other ideas? I'll look into the OUT parameter that Bayer mentions
in his post.

Mike

[Rick Morrison]

The SQL works in our SQL analyzer with or without the named parameter
and with the semicolon. We tried it the way you mentioned too, using
positional parameters, and got the same error. I apologize for
forgetting to mention that in my first post.

Then perhaps you're not connected to the correct database, or it's a permissions issue? The way you're using the DB connection directly, SQLAlchemy is not issuing any SQL of it's own: it's a straight pass-through.

Positional parameters work with pymssql, so I assume you're using pyodbc, correct? I haven't tried calling a stored procedure using pyodbc, anybody on the list have that working?

 

Any other ideas? I'll look into the OUT parameter that Bayer mentions
in his post.

Well if IN parameters don't work, OUT parameters probably aren't going to work either. If you're using pyodbc, I would try bringing the issue up on the pyodbc list. You're pretty much using the DB-API cursor directly, so it's not interference from SQLA.....



 

[Mike]

On May 20, 10:13 am, "Rick Morrison" <rickmorri...@gmail.com> wrote:
> > The SQL works in our SQL analyzer with or without the named parameter
> > and with the semicolon. We tried it the way you mentioned too, using
> > positional parameters, and got the same error. I apologize for
> > forgetting to mention that in my first post.
>
> Then perhaps you're not connected to the correct database, or it's a
> permissions issue? The way you're using the DB connection directly,
> SQLAlchemy is not issuing any SQL of it's own: it's a straight pass-through.
>

I don't think it's a permissions thing...see below...

> Positional parameters work with pymssql, so I assume you're using pyodbc,
> correct? I haven't tried calling a stored procedure using pyodbc, anybody on
> the list have that working?

I think we're using pymssql from a Linux box. Is there a way to tell
which Python module SQLAlchemy is using? We tried running it with
straight pymssql instead and it works in there:

<code>
import pymssql
db =
pymssql.connect(host="ntsql.ourSite.com",user="user",password="pw",database="ourDB")
cur = db.cursor()
cur.execute("execute sp_EDEN_Vendors @query='gra%';")
print cur.fetchall()
</code>

>
> > Any other ideas? I'll look into the OUT parameter that Bayer mentions
> > in his post.
>
> Well if IN parameters don't work, OUT parameters probably aren't going to
> work either. If you're using pyodbc, I would try bringing the issue up on
> the pyodbc list. You're pretty much using the DB-API cursor directly, so
> it's not interference from SQLA.....

Crumb. Thanks.

Mike

[Paul Johnston]

Hi,

> > The SQL works in our SQL analyzer with or without the named parameter
> > and with the semicolon. We tried it the way you mentioned too, using
> > positional parameters, and got the same error. I apologize for
> > forgetting to mention that in my first post.

Dunno if this is related, but pyodbc and adodbapi execute each statement in a separate context. This caused a problem with scope_identity, as in the original implementation with pyodbc, scope_identity always returned null.

Paul

[Rick Morrison]

I think we're using pymssql from a Linux box. Is there a way to tell
which Python module SQLAlchemy is using? We tried running it with
straight pymssql instead and it works in there:

The MSSQL module does an auto-detect of the supported DB-API modules and uses the first one that imports without error. The sequence for the 0.4 series is [pyodbc, pymssql, adodbapi]. You can force module selection by using a 'module=' keyword argument to the create_engine call.

Crumb. Thanks.

Here's another:
   http://rcrumb.com/

[Rick Morrison]

We should really be using the ODBC sanctioned syntax for procedure call, which is still unsupported by pyodbc, AFAIK.  ODBC on *nix is over 10 years old at this point, you'd think we'd have a better story to tell by now, jeez.

 

Dunno if this is related, but pyodbc and adodbapi execute each statement in a separate context. This caused a problem with scope_identity, as in the original implementation with pyodbc, scope_identity always returned null.

I thought the original impetus for scope_identity was not multiple execution contexts, but rather things being fouled up for some users where they had nested INSERTs being done via a trigger on the mapped table, and the brain-dead SELECT @@identity_insert wasn't able to pluck out the correct PK. Was there another reason I'm missing?

Jason, how are coming on the dialect refactor? Things are heating up out here........

[Paul Johnston]

Hi,

I thought the original impetus for scope_identity was not multiple execution contexts, but rather things being fouled up for some users where they had nested INSERTs being done via a trigger on the mapped table, and the brain-dead SELECT @@identity_insert wasn't able to pluck out the correct PK. Was there another reason I'm missing?

You're right, that was the original motivation. I tried just changing @@identity for scope_identity(), which worked just fine on pymssql, but not on the other adapters. Did eventually get it working, but it involved pyodbc changes, that I was unable to do. Fortunately someone on the list volunteered, which was most appreciated.

Paul

[Rick Morrison]

You're right, that was the original motivation. I tried just changing @@identity for scope_identity(), which worked just fine on pymssql, but not on the other adapters. Did eventually get it working, but it involved pyodbc changes, that I was unable to do. Fortunately someone on the list volunteered, which was most appreciated.

Ah I missed that, thanks. Do you recall the nature of the changes? Could they be related to the mangled stack trace we recently saw on a different list thread?