SQL data types for AnyLogic

Question

I am saving the output of my AnyLogic model into an SQL server database. For non-AnyLogic aficionados, AnyLogic is based on Java. However, I am not sure what data types I need to specify for my columns in the database.

So far I am using these:

• double in AnyLogic : float in SQL
• string in AnyLogic : varchar in SQL
• int in AnyLogic : int in SQL

I also have parameters that are of type Option list, which is, if I understand correctly, a form of Java enum. I tried to save those parameters as varchar, but this (obviously) does not work. In addition, my model contains various boolean parameters. For my boolean parameters, I add columns of type bit in SQL by running:

ALTER TABLE myTable 
   ADD my_bool BIT NOT NULL DEFAULT 0;

However, running the model returns this error

SQLServerException: Invalid column name 'false'. Caused by: Invalid column name 'false'

So concretely, how can I export parameters of type Option list and boolean?

Answer 1

This addresses the original question which was tagged MySQL.

I don't know all the issues around "option list". Seems like a string (with a length such as varchar(255)) would work. You can also look into the built-in enum type, although I would not normally recommend using enums.

I would recommend using boolean instead of bit as the equivalent for boolean. Seems more mnemonic.

That said, MySQL understands false as a constant. You can check this by running:

select false

This also works:

select "false"

However, this returns the error that you specify:

select `false`

I suspect that the code being generated is using this construct. You will need to look at the code -- and you might need to figure out some other way of handling this. In MySQL you can use 0 for false and that might fix your problem.

Answer 2

The AnyLogic database is a standard HSQLDB database (not something proprietary) but they've added AnyLogic client functionality to define 'column types' as though they are Java types (with special types for option lists and compiled-on-the-fly-and-run Java code).

If you look at the db.script file (HSQLDB just stores the persistent DB data as an SQL script which creates the tables and INSERTs the values) you can see the underlying HSQLDB types which map closely to SQL Server types.

boolean --> BOOLEAN
double --> DOUBLE
int --> INT
String --> VARCHAR(16777216)
Date --> TIMESTAMP
Code --> VARCHAR(16777216)
Option List --> VARCHAR(255)

NB: The 'Java column types' are supposed to make it easier for a non-technical user to understand what they will get from a Java perspective when querying that column but, for example, they are confusing in that queries will return Java nulls for missing values, so a boolean column actually effectively returns a Boolean.

That should help.

Answer 3

I managed to address part of my problem. I am now able to store String variables from Java into my SQL database. The issue was due to incorrect use of quotations.

Java uses double quotations for String variables (e.g.: ""). SQL expects single quotations (e.g.: '') for string-like columns such as varchar() and char()

I had to amend my SQL query to this: String insertTableSQL = "INSERT INTO test (my_string) VALUES(" +" '"+my_variable_string+"' )";

Note that my_variable_string is a derivative of a Java enum, which I obtained by executing String my_variable_string= my_enum.name();