Solved

Writing data back to MSSQL table containing an identity column

  • 3 June 2020
  • 4 replies
  • 183 views

Userlevel 1
Badge

I want to write back changes to a MSSQL database after reading information from a table.

The table is designed to provide a minimum working example and has two columns:

  • ID: ID column starting with 1, increments with 1
  • VALUE: column containing integer values.

I have managed to read from this database table, however, I want to be able to change a value in AIMMS and then write back the change using 

write p_parameter(i_ID) to table db_TableName;

 

However, this gives me an error stating that I'm not allowed to insert manally into an ID column. This makes sense of course, but how do I prevent AIMMS from trying to insert into the ID column and replace old values by new ones instead?

icon

Best answer by Chris Kuip 14 June 2020, 09:15

View original

4 replies

Userlevel 1
Badge

Hi Chris, that works! Thank you :slight_smile:

Userlevel 5
Badge +7

Hi Bjorn,

 

My answer uses MySQL and is partly based on https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html

 

After the SQL statements

CREATE TABLE `animals` (

  `id` mediumint(9) NOT NULL AUTO_INCREMENT,

  `val` double DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO animals (val) VALUES

    ('5'),('6'),('7'),

    ('1'),('2'),('3');

 

we have the table:

 

With the declarations:

    Set S {

        SubsetOf: Integers;

        Index: i;

    }

    Parameter P {

        IndexDomain: i;

    }

    DatabaseTable db {

        DataSource: sp_MySQLConnectionStringDB;

        TableName: "animals";

        Mapping: {

            "id"  -->i,

            "val" -->P( i )

        }

    }

We can read the data using

     read from table db ;

And we can write changed values back using

    p(i) := 11 * p(i); 

   write to table db in replace mode ;

after executing this reading and writing the data looks like:

 

Question is, can you also insert rows via AIMMS?

Yes you can, for instance using a statement like:

DirectSQL(sp_MySQLConnectionStringDB, "INSERT INTO animals (val) VALUES (\'88\'), (\'99\');");

The data looks like:

 

As you noted from this insert statement, the use of index i is avoided. 

Carefully keeping database column "id” matched to index i may require careful programming.

 

Hope this helps,

 

Chris

Userlevel 1
Badge

Hi Chris, thanks for you reply. Unfortunately I still get the same error (included at the bottom of this post). I started with creating a new sample table (in MSSQL):

CREATE TABLE [tmp].[aimmsTest](
[ID] [int] IDENTITY(1,1) NOT NULL,
[value] [int] NULL,
CONSTRAINT [PK_aimmsTest] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
INSERT INTO [tmp].[aimmsTest]([value]) VALUES (5);
INSERT INTO [tmp].[aimmsTest]([value]) VALUES (6);
INSERT INTO [tmp].[aimmsTest]([value]) VALUES (7);
INSERT INTO [tmp].[aimmsTest]([value]) VALUES (8);
INSERT INTO [tmp].[aimmsTest]([value]) VALUES (9);

And in AIMMS I made the exact same procedures, dbtable, parameter, set and index. Of course also the mapping in the dbtable is done in the same manner. 

Reading data into the set and parameter worked as expected, however, when trying to run 

write to table db in replace mode ;

I (still) get the following error: (in a spoiler because it is long)

Error writing to database table "db_GetData": ODBC[544] : 23000 [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert explicit value for identity column in table 'aimmsTest' when IDENTITY_INSERT is set to OFF. 23000 [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert explicit value for identity column in table 'aimmsTest' when IDENTITY_INSERT is set to OFF. 23000 [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert explicit value for identity column in table 'aimmsTest' when IDENTITY_INSERT is set to OFF. 23000 [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert explicit value for identity column in table 'aimmsTest' when IDENTITY_INSERT is set to OFF. 23000 [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot insert explicit value for identity column in table 'aimmsTest' when IDENTITY_INSERT is set to OFF.

 

The error message is one from MSSQL, which happens when you try to use the INSERT statement to insert values into an ID column.

I have not been able to set up a connection with a mySQL test table due to some other technical difficulties.

 

Userlevel 5
Badge +7

Hi Bjorn,

 

Sorry, I didn't have a MS SQL ready to test with, that is why I used MySQL.

 

Would you mind trying the following:

block where database_foreign_key_handling := 'assume',

            database_string_valued_foreign_keys := 'assume'  ;     

     write to table db in replace mode ;

endblock ;

 

The idea is that you force AIMMS to use a different writing strategy, see https://how-to.aimms.com/Articles/343/343-use-metadata-in-write-to-table.html

 

Hope this works,

 

Chris

Reply


Didn't find what you were looking for? Try searching on our documentation pages:

AIMMS Developer & PRO | AIMMS How-To | AIMMS SC Navigator