[prev in list] [next in list] [prev in thread] [next in thread] 

List:       turbine-torque-dev
Subject:    [jira] Created: (TORQUE-86) MSSQL data inserts with with IDENTITY
From:       "Will Glass-Husain (JIRA)" <jira () apache ! org>
Date:       2007-03-09 23:27:09
Message-ID: 6347710.1173482829387.JavaMail.jira () brutus
[Download RAW message or body]

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


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


[prev in list] [next in list] [prev in thread] [next in thread]