pyodbc+mssql and implicit_returning: False: Is this a bug? Any work arounds?

[Derek Litz]

Hello,

First time using sqlalchemy against a table with triggers and it doesn't seem usable.  Can anyone confirm or deny my situation as a bug, or enlighten me to my user error?

First I ran into this issue:

ERROR:root:Database Error
Traceback (most recent call last):
  File my_code...
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 645, in commit
    self.transaction.commit()
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 313, in commit
    self._prepare_impl()
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 297, in _prepare_impl
    self.session.flush()
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1547, in flush
    self._flush(objects)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1616, in _flush
    flush_context.execute()
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 328, in execute
    rec.execute(self)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 472, in execute
    uow
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 2220, in _save_obj
    execute(statement, params)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1399, in execute
    params)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1532, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1640, in _execute_context
    context)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1633, in _execute_context
    context)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 330, in do_execute
    cursor.execute(statement, parameters)
ProgrammingError: (ProgrammingError) ('42000', "[42000] [FreeTDS][SQL Server]The target table 'a_table_name' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause. (334) (SQLPrepare)") 'INSERT INTO a_table_name (column_1, column_2, column_3, column_4) OUTPUT inserted.the_id VALUES (?, ?, ?, ?)' ('123000', 2, None, None)

I managed to track this down to http://docs.sqlalchemy.org/en/latest/dialects/mssql.html#triggers

So, I did that only to find myself with this error, which seems like a bug, rather then a limitation.

ERROR:root:Database Error
Traceback (most recent call last):
  File my_code...
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 645, in commit
    self.transaction.commit()
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 313, in commit
    self._prepare_impl()
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 297, in _prepare_impl
    self.session.flush()
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1547, in flush
    self._flush(objects)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1616, in _flush
    flush_context.execute()
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 328, in execute
    rec.execute(self)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 472, in execute
    uow
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 2220, in _save_obj
    execute(statement, params)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1399, in execute
    params)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1532, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1652, in _execute_context
    context.post_exec()
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/dialects/mssql/pyodbc.py", line 195, in post_exec
    self._lastrowid = int(row[0])
TypeError: int() argument must be a string or a number, not 'NoneType'

I couldn't figure out any solution to this problem.  I did try setting use_scope_identity to False, which not only seemed like a bad idea, but yielded absolutely no effect on the error (I passed this as a keyword argument to create_engine).  Any ideas?

[Michael Bayer]

What SQL are you seeing, do you see "; select scope_identity()" at the end of the INSERT statement ?   that's how that works.   Also software versions ?   (SQLA, freetds, pyodbc). 

--
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/-/c7eYQzrxbkMJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Michael Bayer]

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.

[Derek Litz]

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

[Michael Bayer]

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?

--
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 sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

<my_info.txt><mssql_implicit_ret.py>

[Derek Litz]

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

To unsubscribe from this group, send email to sqlalchemy+unsubscribe@googlegroups.com.

To unsubscribe from this group, send email to sqlalchemy+unsubscribe@googlegroups.com.

To unsubscribe from this group, send email to sqlalchemy+unsubscribe@googlegroups.com.

[Michael Bayer]

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 :).

To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/ggloFYpcza8J.

To post to this group, send email to sqlal...@googlegroups.com.

To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.

[Domas]

I had this problem. I solved it using this:
http://docs.sqlalchemy.org/en/latest/dialects/mssql.html#triggers

On 3 Bal, 00:27, Derek Litz <litzoma...@gmail.com> wrote:
> Hello,
>
> First time using sqlalchemy against a table with triggers and it doesn't
> seem usable.  Can anyone confirm or deny my situation as a bug, or
> enlighten me to my user error?
>
> First I ran into this issue:
>
> ERROR:root:Database Error
> Traceback (most recent call last):

>   *File my_code...*

> I managed to track this down tohttp://docs.sqlalchemy.org/en/latest/dialects/mssql.html#triggers

>
> So, I did that only to find myself with this error, which seems like a bug,
> rather then a limitation.
>
> ERROR:root:Database Error
> Traceback (most recent call last):

>   *File my_code...*