well you've circumvented the IDENTITY mechanics by doing an "INSTEAD OF INSERT" (first time I've ever seen that), and as a matter of course need to turn off IDENTITY INSERT which is part of mssqls overall ridiculousness in this area - so the IDENTITY feature really isn't being used here. But not sure if that means the @@identity variable wont work.
It's all very silly compared to a reasonable system like Postgresql but I use MSSQL in production too due to client requirements, so there you go :). On Apr 5, 2012, at 6:12 PM, Derek Litz wrote: > The use_scope_identity=False appears to be working as you've stated with the > sqlalchemy version you've linked. No errors. > I do, still, get the error without use_scope_identity=False, stack trace > appears identical. > > Good question on the IDENTITY directive, I thought the exact same thing, but > I'm currently far from an expert DBA, so I take my thoughts with a grain of > salt :). > > But, now I'm obliged to bring it up with the person who did write it who is a > DBA. > > Now, is this the reason for the bad behavior, or were you just asking because > it looks weird, and is, most likely, unnecessary? > > Thanks, > > Derek > > On Thursday, April 5, 2012 1:34:31 PM UTC-5, Michael Bayer wrote: > Pyodbc's docs seem to be wrong as far as "scope_identity()" in any case, I > seem to get back a number here whether I execute scope_identity() or select > @@identity. > > The flag does what's expected in ra98001d03a2f, though you'll have to confirm > it's working with that rather extreme trigger you have there. > http://hg.sqlalchemy.org/sqlalchemy/archive/default.tar.gz > > Why not remove the IDENTITY directive from that column, since you're not > using IDENTITY anyway? > > > > On Apr 5, 2012, at 11:46 AM, Derek Litz wrote: > >> Good news! I was able to reproduce the exception after modifying your test >> script. Attached is my info and the script. >> >> Below is also repeated at the beginning of the two files. >> >> NOTE: You will need to edit connect_to_mssql() >> I was able to get the following error using this script. >> >> --------mssql_implicit_ret.py-------------- >> >> ERROR: test_ins (__main__.TestInsWTrigger) >> ---------------------------------------------------------------------- >> Traceback (most recent call last): >> File "mssql_implicit_ret.py", line 56, in test_ins >> r = con.execute(self.t1.insert(), descr='there') >> File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", >> line 1450, in execute >> params) >> File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", >> line 1583, in _execute_clauseelement >> compiled_sql, distilled_params >> File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", >> line 1709, in _execute_context >> context.post_exec() >> File >> "/usr/local/lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.py", >> line 199, in post_exec >> self._lastrowid = int(row[0]) >> TypeError: int() argument must be a string or a number, not 'NoneType' >> >> ------------my_info.txt--------------------- >> >> Note, I had to update to sqlalchemy version 0.7.6 to run your unit test >> because engine did not have the begin method in 0.7.3. Below is my >> information prior to that single update. Nothing else needed to be updated. >> >> There are actually two potential bugs I'm seeing >> >> 1.) File >> "/usr/local/lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.py", >> line 199, in post_exec >> self._lastrowid = int(row[0]) >> 2.) use_scope_identity=False appears to do nothing. >> >> Thanks, >> >> Derek >> >> >> On Monday, April 2, 2012 5:37:35 PM UTC-5, Michael Bayer wrote: >> Also, attached is a test script based on our unit tests which illustrates >> the feature working as expected - can you run this on a test database on >> your end, and if it passes, try to modify the trigger/table def so that it >> reproduces your output ? the test creates/drops two tables and a trigger. >> thanks. >> >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To view this discussion on the web visit >> https://groups.google.com/d/msg/sqlalchemy/-/h9aqQCA-8WwJ. >> To post to this group, send email to [email protected]. >> 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. >> <my_info.txt><mssql_implicit_ret.py> > > > On Thursday, April 5, 2012 1:34:31 PM UTC-5, Michael Bayer wrote: > Pyodbc's docs seem to be wrong as far as "scope_identity()" in any case, I > seem to get back a number here whether I execute scope_identity() or select > @@identity. > > The flag does what's expected in ra98001d03a2f, though you'll have to confirm > it's working with that rather extreme trigger you have there. > http://hg.sqlalchemy.org/sqlalchemy/archive/default.tar.gz > > Why not remove the IDENTITY directive from that column, since you're not > using IDENTITY anyway? > > > > On Apr 5, 2012, at 11:46 AM, Derek Litz wrote: > >> Good news! I was able to reproduce the exception after modifying your test >> script. Attached is my info and the script. >> >> Below is also repeated at the beginning of the two files. >> >> NOTE: You will need to edit connect_to_mssql() >> I was able to get the following error using this script. >> >> --------mssql_implicit_ret.py-------------- >> >> ERROR: test_ins (__main__.TestInsWTrigger) >> ---------------------------------------------------------------------- >> Traceback (most recent call last): >> File "mssql_implicit_ret.py", line 56, in test_ins >> r = con.execute(self.t1.insert(), descr='there') >> File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", >> line 1450, in execute >> params) >> File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", >> line 1583, in _execute_clauseelement >> compiled_sql, distilled_params >> File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", >> line 1709, in _execute_context >> context.post_exec() >> File >> "/usr/local/lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.py", >> line 199, in post_exec >> self._lastrowid = int(row[0]) >> TypeError: int() argument must be a string or a number, not 'NoneType' >> >> ------------my_info.txt--------------------- >> >> Note, I had to update to sqlalchemy version 0.7.6 to run your unit test >> because engine did not have the begin method in 0.7.3. Below is my >> information prior to that single update. Nothing else needed to be updated. >> >> There are actually two potential bugs I'm seeing >> >> 1.) File >> "/usr/local/lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.py", >> line 199, in post_exec >> self._lastrowid = int(row[0]) >> 2.) use_scope_identity=False appears to do nothing. >> >> Thanks, >> >> Derek >> >> >> On Monday, April 2, 2012 5:37:35 PM UTC-5, Michael Bayer wrote: >> Also, attached is a test script based on our unit tests which illustrates >> the feature working as expected - can you run this on a test database on >> your end, and if it passes, try to modify the trigger/table def so that it >> reproduces your output ? the test creates/drops two tables and a trigger. >> thanks. >> >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To view this discussion on the web visit >> https://groups.google.com/d/msg/sqlalchemy/-/h9aqQCA-8WwJ. >> To post to this group, send email to [email protected]. >> 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. >> <my_info.txt><mssql_implicit_ret.py> > > > On Thursday, April 5, 2012 1:34:31 PM UTC-5, Michael Bayer wrote: > Pyodbc's docs seem to be wrong as far as "scope_identity()" in any case, I > seem to get back a number here whether I execute scope_identity() or select > @@identity. > > The flag does what's expected in ra98001d03a2f, though you'll have to confirm > it's working with that rather extreme trigger you have there. > http://hg.sqlalchemy.org/sqlalchemy/archive/default.tar.gz > > Why not remove the IDENTITY directive from that column, since you're not > using IDENTITY anyway? > > > > On Apr 5, 2012, at 11:46 AM, Derek Litz wrote: > >> Good news! I was able to reproduce the exception after modifying your test >> script. Attached is my info and the script. >> >> Below is also repeated at the beginning of the two files. >> >> NOTE: You will need to edit connect_to_mssql() >> I was able to get the following error using this script. >> >> --------mssql_implicit_ret.py-------------- >> >> ERROR: test_ins (__main__.TestInsWTrigger) >> ---------------------------------------------------------------------- >> Traceback (most recent call last): >> File "mssql_implicit_ret.py", line 56, in test_ins >> r = con.execute(self.t1.insert(), descr='there') >> File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", >> line 1450, in execute >> params) >> File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", >> line 1583, in _execute_clauseelement >> compiled_sql, distilled_params >> File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", >> line 1709, in _execute_context >> context.post_exec() >> File >> "/usr/local/lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.py", >> line 199, in post_exec >> self._lastrowid = int(row[0]) >> TypeError: int() argument must be a string or a number, not 'NoneType' >> >> ------------my_info.txt--------------------- >> >> Note, I had to update to sqlalchemy version 0.7.6 to run your unit test >> because engine did not have the begin method in 0.7.3. Below is my >> information prior to that single update. Nothing else needed to be updated. >> >> There are actually two potential bugs I'm seeing >> >> 1.) File >> "/usr/local/lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.py", >> line 199, in post_exec >> self._lastrowid = int(row[0]) >> 2.) use_scope_identity=False appears to do nothing. >> >> Thanks, >> >> Derek >> >> >> On Monday, April 2, 2012 5:37:35 PM UTC-5, Michael Bayer wrote: >> Also, attached is a test script based on our unit tests which illustrates >> the feature working as expected - can you run this on a test database on >> your end, and if it passes, try to modify the trigger/table def so that it >> reproduces your output ? the test creates/drops two tables and a trigger. >> thanks. >> >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To view this discussion on the web visit >> https://groups.google.com/d/msg/sqlalchemy/-/h9aqQCA-8WwJ. >> To post to this group, send email to [email protected]. >> 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. >> <my_info.txt><mssql_implicit_ret.py> > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To view this discussion on the web visit > https://groups.google.com/d/msg/sqlalchemy/-/ggloFYpcza8J. > To post to this group, send email to [email protected]. > 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. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. 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.
