On 9/10/06, Michael Bayer <[EMAIL PROTECTED]> wrote:
Oh, it gets even better. You can only have IDENTITY_INSERT on for one table at a time. Trying to set it on for another table while it is set on elsewhere will throw an error. It is necessary to turn it off first, then set it on for the other table.
Well that won't work for the reason given above. Right now I've got the MSSQL module set to (somewhat awkwardly) simulate the behavior of say PG, by doing the following:
a) sniff the incoming values on an INSERT
b) if a column known as an identity column has a value set, execute a "SET IDENTITY_INSERT ON" statement.
c) proceed with the insert
d) issue a "SET IDENTITY_INSERT OFF" if it was turned on in step (b)
As I mentioned in the last message to Kent (I think it may be on here, we've been working off-list on this), this scheme gets confusing for tables that have two identity columns. For these cases, either both potential identity values will need to be set, or both not set. Setting one and not the other is just impossible with MSSQL, as far as I can see. Since this is confusing, and since I thought multi-identity columns in MSSQL tables were pretty rare (there are other issues with them), I punted and made it unsupported.
Rick
On Sep 10, 2006, at 7:03 PM, Rick Morrison wrote:
> issue by simply disallowing multiple identity columns in a single
> table. I thought that multiple identity columns in a single table
> would be a pretty rare occurrence. Maybe I was wrong on that count.
> At any rate, the prohibition of multiple identity columns is a most
> probably somewhat artificial constraint that was added to prevent
> the above scenario, not an intrinsic limitation of SA or the MSSQL
> module.
>
oh ok, just disregard my previous email about ms-sql not allowing
composite primary keys...
> MSSQL instead is modal -- there is a connection-wide "SET
> IDENTITY_INSERT ON/OFF" setting that controls whether or not insert
> statements will generate new identity values or not. This means
> that the above scenario can never work, and either all values must
> be specified, or none. This is kind of out of step with the SA
> paradigm, so when I first wrote the MSSQL module, I just side-
> stepped the whole
So, if i SET IDENTITY_INSERT ON and then try to insert an explicit
value into the PK column, it throws an error ? thats a little weird.
Oh, it gets even better. You can only have IDENTITY_INSERT on for one table at a time. Trying to set it on for another table while it is set on elsewhere will throw an error. It is necessary to turn it off first, then set it on for the other table.
OK, well the MS-SQL dialect then should just have a keyword argument
for this setting, and we just set it on the connections. i think the
rest of SA wouldnt have to be affected, it would just mean that the
calling application would have to know whether or not explicit
identity values are allowed or not.
Well that won't work for the reason given above. Right now I've got the MSSQL module set to (somewhat awkwardly) simulate the behavior of say PG, by doing the following:
a) sniff the incoming values on an INSERT
b) if a column known as an identity column has a value set, execute a "SET IDENTITY_INSERT ON" statement.
c) proceed with the insert
d) issue a "SET IDENTITY_INSERT OFF" if it was turned on in step (b)
As I mentioned in the last message to Kent (I think it may be on here, we've been working off-list on this), this scheme gets confusing for tables that have two identity columns. For these cases, either both potential identity values will need to be set, or both not set. Setting one and not the other is just impossible with MSSQL, as far as I can see. Since this is confusing, and since I thought multi-identity columns in MSSQL tables were pretty rare (there are other issues with them), I punted and made it unsupported.
Rick
(that is assuming theres no other decent way to generate primary key
values...)
------------------------------------------------------------------------- Using Tomcat but need to do more? Need to support web services, security? Get stuff done quickly with pre-integrated technology to make your job easier Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________ Sqlalchemy-users mailing list Sqlalchemy-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
