MSSQL Inserting values into ID fields

[paj]

The MSSQL dialect has hooks to issue "set indentity_insert ... on" when you insert into an IDENTITY column. There are some issues with this.

1) This was broken for table with a schema, and I've attached a patch to correct it.

2) If the query fails, the code to "set identity_insert ... off" afterwards is not called, which can cause subsequent queries to fail. This makes the unit test sql.constraints.ConstraintTest.test_check_constraint fail.

I had a go at fixing 2, by introducing post_exec_finally. Unfortunately it doesn't quite work, I'm hoping someone else will be able to spot what's wrong.

The first attempt used: self.cursor.execute("SET IDENTITY_INSERT %s OFF" % self.compiled.statement.table.name)

This caused: ProgrammingError: Attempt to use a closed cursor.

Using self.connection instead gives: InvalidRequestError: This Connection is closed

(both these are weird, because I'm sure other queries manage to reuse the connection)

Using self.connection.engine.contextual_connect() does succeed itself, but the original problem re-occurs.

So, I'm a bit stuck! Any suggestions appreciated.

[paj]

Patch that doesn't quite work

[paj]

Just for extra fun, the two patches conflict with each other :-)

[Former user]

(original author: ram) Since the "identity_insert" mode connection-specific, and since the connection is already compromised because of the previous error, one short-term option may be to simply force the connection closed.

This would of course lose any in-flight transactions on that connection -- but presumably those would have been cleaned up when the previous statement error-ed and the application had a chance to recover.

[paj]

Ok, the trick is to do this in Dialect.do_execute / do_executemany, using context as a parameter. Fixed in 3829.

[Mike Bayer]

Removing milestone: 0.4.xx (automated comment)