Uploaded image for project: 'Torque'
  1. Torque
  2. TORQUE-86

MSSQL data inserts with with IDENTITY key require IDENTITY_INSERT property to be on

Details

    • Bug
    • Status: Closed
    • Minor
    • Resolution: Fixed
    • 3.2
    • 3.3-RC3
    • Generator
    • None

    Description

      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.

      Attachments

        1. mssql_row.patch
          0.9 kB
          William Glass-Husain

        Activity

          added patch to set IDENTITY_INSERT property on row by row basis

          wglass William Glass-Husain added a comment - added patch to set IDENTITY_INSERT property on row by row basis

          remembered to add the feather.

          wglass William Glass-Husain added a comment - remembered to add the feather.
          tfischer Thomas Fox added a comment -

          Thanks for the patch, Will.
          Can a Torque developer who has access to a mssql db please confirm that this works ?

          tfischer Thomas Fox added a comment - Thanks for the patch, Will. Can a Torque developer who has access to a mssql db please confirm that this works ?

          great.

          note that you can download a free developer version from Microsoft. (last I checked).

          wglass William Glass-Husain added a comment - great. note that you can download a free developer version from Microsoft. (last I checked).
          monroe CG Monroe added a comment -

          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 .

          monroe CG Monroe added a comment - 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 .
          monroe CG Monroe added a comment -

          Slightly modified the supplied patch to include a test for idBroker method and tested against the test-project and MS-SQL Server 2000. Patch has been submitted.

          monroe CG Monroe added a comment - Slightly modified the supplied patch to include a test for idBroker method and tested against the test-project and MS-SQL Server 2000. Patch has been submitted.
          monroe CG Monroe added a comment -

          No comment on the committed fix, so closing the issue.

          monroe CG Monroe added a comment - No comment on the committed fix, so closing the issue.

          People

            monroe CG Monroe
            wglass William Glass-Husain
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: