Trouble to use dbexporttable

Question

question

Sung Kim asked Apr 02 2019 at 5:04 PM Sung Kim commented Apr 09 2019 at 3:46 PM

Trouble to use dbexporttable

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.

Software Version:

FlexSim 19.0.0

database export data

· 2

Phil BoBo ♦♦ commented · Apr 02 2019 at 5:29 PM

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.

1 ·

Sung Kim commented · Apr 08 2019 at 5:58 PM

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[i][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

0 ·

______

Cookie preferences

Your privacy is important to us and so is an optimal experience. To help us customize information and build applications, we collect data about your use of this site.

May we collect and use your data?

Learn more about the Third Party Services we use and our Privacy Statement.

Strictly necessary – required for our site to work and to provide services to you

These cookies allow us to record your preferences or login information, respond to your requests or fulfill items in your shopping cart.

YES

Improve your experience – allows us to show you what is relevant to you

These cookies enable us to provide enhanced functionality and personalization. They may be set by us or by third party providers whose services we use to deliver information and experiences tailored to you. If you do not allow these cookies, some or all of these services may not be available for you.

YES

NO

Customize your advertising – permits us to offer targeted advertising to you

These cookies collect data about you based on your activities and interests in order to show you relevant ads and to track effectiveness. By collecting this data, the ads you see will be more tailored to your interests. If you do not allow these cookies, you will experience less targeted advertising.

YES

NO

Are you sure you want a less customized experience?

We can access your data only if you select "yes" for the categories on the previous screen. This lets us tailor our marketing so that it's more relevant for you. You can change your settings at any time by visiting our privacy statement

Your experience. Your choice.

We care about your privacy. The data we collect helps us understand how you use our products, what information you might be interested in, and what we can improve to make your engagement with Autodesk more rewarding.

May we collect and use your data to tailor your experience?

Explore the benefits of a customized experience by managing your privacy settings for this site or visit our Privacy Statement to learn more about your options.

Answer 1 · 2019-04-08T18:11:42Z

Phil BoBo answered Apr 08 2019 at 6:11 PM Sung Kim commented Apr 09 2019 at 3:46 PM

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
}

· 1

question