Little correction: The problem exists if parameters are used in the insert statement: "insert <table> (<columns>) values (?, ?, ...)" ODBC uses the procedure sp_executesql for execution insert statements with parameters. That procedure has another scope. If the following syntax is used: "insert <table> (<columns>) values (?, ?, ...) select ? =scope_identity()" the select statement is also executed within the procedure sp_executesql and so within the same scope as the insert statement. The identity value is returned as a result set, NOT as a parameter. I don't know why. But you have to bind the returning parameter.
Regards, Michael On 30 Mai, 18:26, Mike <[EMAIL PROTECTED]> wrote: > Hi, > > it's a scope problem. > After the insert statement is executed the scope is lost for the next > "select scope_identity()" (<- new scope outside the insert) > Both statements, the insert and the select have to be executed within > one call: > "insert <table> (<columns>) values (<values>) select scope_identity()" > prepare -> execute -> fetch -> a voila > I use this solution in the products in our company. It works very well > without any problems. > If someone knows a better solution please email me. > > It should be fixed in sqlalchemy I think. > > By the way: the workaround with select @@identity didn't fetch the > correct value in some > situations. For example if you have a trigger which inserts a new row > in another table you > will get the id from the insert in the trigger not your from your own > insert statement. > In worst cases you can also get the id from the insert of another user > I think. > > Regards, > Michael > > On 25 Mai, 21:07, "Jin Lee" <[EMAIL PROTECTED]> wrote: > > > > > Rick, Paul - > > > Sorry for the late reply (my day job has been keeping me quite busy) > > > It looks like ?use_scope_identity=0 works, I am no longer getting the error. > > > Rick, to answer your questions, the pk is a field of type IDENTITY. I > > am using pyodbc 2.0.35, and MSSQL 2000 SP 4 > > > Thanks guys, > > > Jin > > > On 5/24/07, Paul Johnston <[EMAIL PROTECTED]> wrote: > > > > Hi Jin, > > > > >I keep getting this error after I do an insert. The insert is > > > >successful, but I think there is a problem in getting the pk > > > >afterward. > > > > Yes, I'm afraid this is a known bug in 0.3.7. A workaround is to specify > > > ?use_scope_identity=0 at the end of your database URL. > > > > Paul- Zitierten Text ausblenden - > > > - Zitierten Text anzeigen -- Zitierten Text ausblenden - > > - Zitierten Text anzeigen - --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
