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

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)

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.

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

Hi Chris, that works! Thank you