Trouble to use dbexporttable

[sung_kim]

[ FlexSim 19.0.0 ]

I tried to export one of global table from FlexSim to my local database. I use SQL Express free version for DB. The script I use is as follows;

//Script Starts***********************************************************************************

dbclose(); //In case

dbopen("BOBSTR", "select count(*) from [dbo].[BSOrders]", 0);
double numLines = dbgettablenum(1, 1);

Table tblItemRecord = reftable("ItemRecord");

//dbexporttable("ItemRecord", 1, 1, tblItemRecord.numRows, tblItemRecord.numCols);
dbexporttable("ItemRecord", 1, 1, 3, 3);

dbclose();

//Script Ends***********************************************************************************

I believe the connection is successfully done since I have correct 'numLines' return value. However, after 'dbexporttable' is executed, no tables are exported to BOBSTR database. I tested both (1) ItemRecord exists in BOBSTR DB and (2) ItemRecord not exists.

Does anybody have same or similar issue? Anyone can help me to export the global table? I am not sure what I missed. Thank you for your help much in advance.

ps. I think it would not help much to upload the model, since it is tied to my local DB. I hope someone can duplicate the issue using its own local DB. Thanks.

[philboboADSK]

Why are you using the old db...() commands instead of the new Database Connector tool and/or Database FlexScript API commands?

I'm not an expert in how the old db...() commands work, but in your example, you are passing 0 into dbopen(), which uses "SQL Mode" instead of "Table Mode" according to the documentation for that command.

Perhaps dbexporttable() only works when it is in "Table Mode" instead of "SQL Mode"? Again, I'm not an expert on the db...() commands, but based on the documentation, that might be the issue.

The old db...() commands require a lot of setup outside of FlexSim to configure Data Sources in the Windows Control Panel. Also, because they obfuscate what is actually happening during the command execution, they are hard to debug and often are much slower than they need to be.

The new Database Connector tool and Database FlexScript API will easily communicate with a SQL Server database without a bunch of configuration outside of FlexSim, and their inputs actually align with what is happening during the command execution, so they are much easier to manage and usually much faster.

[sung_kim]

Thank you for the tip, Phil.

I tried to use the new DB Connector and the API as you suggested, and it works. However, when I use the same script in the FlexSim experimenter 'End of Replication' trigger, it gives me an error. As you see below, it seems to have something to do with table duplication. But I made sure that there is no such tables in SQL before running the experimenter. I also stepped through it but no clue why it complained about the table existence because there was none before execute the line. The script generates the two tables in SQLDB anyhow though. If you (and any users) can help me to find what I did wrong or missed, I would appreciate much!!

Script in End of Replication ****************************************************************************

//Since I could not find direct export method, I tried INSERT INTO and SELECT INTO for copying and make backups. Any other suggestions?

double replication = param(1);
double scenario = param(2);
Database.Connection con = Database.Connection("DBConnector1");
con.connect();
 
con.query("truncate table [dbo].[ItemRecord]");
 
Database.PreparedStatement statement = con.prepareStatement("INSERT INTO ItemRecord (SimTime) VALUES (:simtime)");
Table tblGlobal = Table("ItemRecord");
for (int i = 1; i <= tblGlobal.numRows; i++) {
statement.bindParam("simtime", tblGlobal[1], Database.DataType.Float);
statement.execute();
}
 
con.query(concat("select * into ItemRecord", numtostring(scenario, 0, 0), "_", numtostring(replication, 0, 0), " from [dbo].ItemRecord"));
con.disconnect();

Error message ********************************************************************************************

exception: FlexScript exception: 42S01 [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]There is already an object named 'ItemRecord1_2' in the database. at MODEL:/Tools/UserCommands/command2/code

exception: FlexScript exception: 42S01 [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]There is already an object named 'ItemRecord1_1' in the database. at <no path> c: MODEL:/Tools/UserCommands/command2/code

[philboboADSK · Accepted solution]

The End of Replication trigger fires twice for each replication: once on the background child thread that is running the replication and then again on the main FlexSim thread.

When it executes on the main FlexSim thread, it passes a treenode reference to the child's experimenter node. That way, you can pass data from the child to the parent via this trigger and that node.

See the "Write to a GlobalTable" pick option code for an example of storing data in the child's experimenter node and then reading it from the main FlexSim thread.

Depending on whether you want your code to fire on the child background process or on the main FlexSim process, you should add a condition to your code:

if (objectexists(childexpfolder)) {
    // End of Replication on the main process
} else {
    // End of Replication on the child process
}

[sung_kim]

THANKS, Phil!!

Yes, as you explained, the firing twice of End of Replication was the key to fix the issue. With the conditional script as you suggested, it works without any error messages (Obviously the duplication issue was gone). I appreciate very much your help!!