[sqlalchemy] Re: SqlAlchemy and Stored Procedures with variables
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 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SqlAlchemy and Stored Procedures with variables
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? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SqlAlchemy and Stored Procedures with variables
Rick, On May 19, 4:30 pm, Rick Morrison [EMAIL PROTECTED] wrote: 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 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 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SqlAlchemy and Stored Procedures with variables
On May 20, 10:13 am, Rick Morrison [EMAIL PROTECTED] 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 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SqlAlchemy and Stored Procedures with variables
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 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SqlAlchemy and Stored Procedures with variables
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/ --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SqlAlchemy and Stored Procedures with variables
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 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SqlAlchemy and Stored Procedures with variables
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 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SqlAlchemy and Stored Procedures with variables
On May 19, 2008, at 5:30 PM, Rick Morrison wrote: 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. theres OUT param support for Oracle - MS-SQL dialect could follow the same approach, provided pyodbc allows it. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
