db-torque-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "CG Monroe (JIRA)" <j...@apache.org>
Subject [jira] Commented: (TORQUE-86) MSSQL data inserts with with IDENTITY key require IDENTITY_INSERT property to be on
Date Mon, 15 Oct 2007 14:58:50 GMT

    [ https://issues.apache.org/jira/browse/TORQUE-86?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12534863
] 

CG Monroe commented on TORQUE-86:
---------------------------------

FYI - I'll take verify this patch today or tomorrow against my SQL servers.

If I have time, I may make it possible to turn this on / off via an options tag.  There may
be situations that you DON'T want this protection turned off.

In general, we may want to optionally allow the exclusion of native mode autogenerated fields
in the Torque generated SQL.  E.g, I want the records but don't want to deal with id collisions
on "import".  But then again, there is alway my XML import/export framework ( http://torque-addons.sf.net/
)  for full control :).

> MSSQL data inserts with with IDENTITY key require IDENTITY_INSERT property to be on
> -----------------------------------------------------------------------------------
>
>                 Key: TORQUE-86
>                 URL: https://issues.apache.org/jira/browse/TORQUE-86
>             Project: Torque
>          Issue Type: Bug
>          Components: Generator
>    Affects Versions: 3.2
>            Reporter: Will Glass-Husain
>            Priority: Minor
>         Attachments: mssql_row.patch
>
>
> For Microsoft SQL Server, if a primary key is of type IDENTITY (autoincrement), then
you will not be able to insert a record containing a primary key.  
> For example, the following line will give an error if field WEB_APP_ID is a primary key.
> INSERT INTO WEB_APP (WEB_APP_ID,WEB_APP_NAME,ROOT_PATH,BASE_URL,IS_PRO,ALLOW_NATIVE_VENSIM)
>     VALUES (1,'Broadcast','c:/www/sims','http://localhost/sims/',1,1);
> The global property IDENTITY_INSERT can prevent this error.  However, if your dataset
has a mix of tables, some with autoincrement and some without then you will not be able to
set this globally.
> The solution is to check to see if the primary key is has the auto_increment property,
and to wrap the insert with an IDENTITY_INSERT setting if so.  Example:
> SET IDENTITY_INSERT WEB_APP ON;
> INSERT INTO WEB_APP (WEB_APP_ID,WEB_APP_NAME,ROOT_PATH,BASE_URL,IS_PRO,ALLOW_NATIVE_VENSIM)
>     VALUES (1,'Broadcast','c:/www/sims','http://localhost/sims/',1,1);
> SET IDENTITY_INSERT WEB_APP OFF;
> I've attached a patch to the templates which implements this.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscribe@db.apache.org
For additional commands, e-mail: torque-dev-help@db.apache.org


Mime
View raw message