topic Re: Change Database Table cell value Problem in FlexSim Forum

Change Database Table cell value Problem

shanice_c — Sun, 26 Dec 2021 08:57:59 GMT

[ FlexSim 21.1.0 ]

Hi everyone, previously I've successfully import from and export to database. Now I'd like to test only change specific cell value in database along with the event happening, though this it's easy to do these things using FlexSim tool (GlobalTable), but still liked to try this function.

There are two problems, first is that in the manual says that Database.PreparedStatement is a class for execution and manipulation of sql statement. From my export part of code, it seems that Database.PreparedStatement is matching the header of my FlexSim GlobalTable5 and SQLite Table named Query2. Then the rest of statement.bindParam is dealing with value.

However, if i simply need to change a cell value, I would need to filter that cell out first, but it tells that there are too many parameters. How could I fix this problem?

Second problem is that do I have to disconnect database everytime an event happened then trigger the use of database? Or I could simply connect to database until the whole simulation has completed then disconnect from database. Thank you!

Attached is my model and db.

test db.fsm

DB.7z

Re: Change Database Table cell value Problem

jason_lightfoot_adsk — Tue, 28 Dec 2021 11:35:36 GMT

You don't pass parameters into the prepareStatement function like that - you use a single sql statement with parameters that are bound seperately. So in this case you need a second bound parameter called EQName.

If you're not executing the statement multiple times, I question the value of using a prepared statement, other than it handling parameter data types for you.

You should not need to keep connecting and disconnecting - but you might want to check the connections is open each time and connect if it is not.

Re: Change Database Table cell value Problem

shanice_c — Tue, 28 Dec 2021 12:55:32 GMT

Hello @Jason Lightfoot Thank you for the reply. But I couldn't understand why a second parameter is needed. I thought I have used a parameter "A" that represent EQName.

Also, I use prepared statement because the manual says that Database.PreparedStatement is a class for execution and manipulation of sql statement, so I use it to change db cell value. Or what should I use to update db cell value?

Re: Change Database Table cell value Problem

jason_lightfoot_adsk — Tue, 28 Dec 2021 18:02:35 GMT

You can just use the query() method of the connection and pass in an SQL UPDATE statement.

Re: Change Database Table cell value Problem

JordanLJohnson — Tue, 28 Dec 2021 18:55:55 GMT

It looks to me like you are trying to use features of Table.query() in a database connection. In Table.query(), you bind parameter values directly in the statement, and placeholders are specified with $1, $2, etc. Table.query() accepts the text and any parameter values.

Database.Connection.prepareStatement() only accepts one argument, which is the text of the query. Placeholders need to be specified with the correct syntax. Our database connector connects with SQLite through SQLAPI++, which expects :1, :2, etc, rather than $1, $2.

So your statement should probably look like this:

var stmt = con.prepareStatement("UPDATE EQTable SET Status = :1 WHERE EQName = :2");
stmt.bindParam(1, 1, Database.DataType.Int); // bind :1
stmt.bindParam(2, token.EQ, Database.DataType.String); // bind :2
stmt.execute();

In general, it is better to avoid communication with a database during a model run, just because it is slow. Usually, customers follow a pattern like this:

On Reset, open the connection, read in any input data, close the connection
Run the model to some stop time
At the stop time, open the connection, dump all data, and close the connection

If you need to communicate during the model run, then I recommend leaving the connection open. You'd get two advantages:

You won't disconnect and reconnect during the run, which will be faster, and
If you use the prepareStatement() method, each unique statement will only be prepared once. Calling it again on identical text will just give back the previous result.

Re: Change Database Table cell value Problem

JordanLJohnson — Tue, 28 Dec 2021 18:59:32 GMT

It's fine to use prepared statements on one-time queries. SQL engines prepare the statement internally anyways, and then just return the result. So if the user wants to use two lines of code instead of one, that's no problem.

string stmtText = "SELECT stuff FROM someTable";
// good
var result = con.query(stmtText); 

// also good
var stmt = con.prepareStatement(stmtText);
var result = stmt.execute();

Re: Change Database Table cell value Problem

jason_lightfoot_adsk — Wed, 29 Dec 2021 10:25:42 GMT

Then what's the purpose of the query() method?

Re: Change Database Table cell value Problem

shanice_c — Mon, 03 Jan 2022 14:03:21 GMT

Hi @Jordan Johnson Thank you for your detailed reply. I still have some problem with how to export to db. The sql statement in Database.PreparedStatement, as there are not many examples I could imitate. I follwed how I succeed export whole GlobalTable5 to db.

But the tablename behind INSERT INTO should be db table name, and follow the globalTable column header. behind the sql statement VALUES is the db table column header. This is my understand, but the exception keep exists, I also change the value opposite. Could you help me figure out what mistake I made?

test db.fsm

db0103.7z

Re: Change Database Table cell value Problem

jason_lightfoot_adsk — Mon, 03 Jan 2022 14:18:54 GMT

It's EQName not EQNames in your database table

Re: Change Database Table cell value Problem

JordanLJohnson — Mon, 03 Jan 2022 17:20:21 GMT

@Jason Lightfoot is correct. Your global table has a column called EQNames, but the name of the column in the database is EQName (no s on the end).

Re: Change Database Table cell value Problem

shanice_c — Tue, 04 Jan 2022 13:03:57 GMT

Hello@Jason Lightfoot @Jordan Johnson

I feel sorry for couldn't solve this issue. I think I've checked connection is succeeded. And I have checked the data type defined in database table. I think these things should be no problem. I still cannot insert value through prepareStatement.

Re: Change Database Table cell value Problem

jason_lightfoot_adsk — Tue, 04 Jan 2022 13:12:02 GMT

What error message are you getting?

Re: Change Database Table cell value Problem

shanice_c — Tue, 04 Jan 2022 14:44:30 GMT

@Jason Lightfoot There's no error message now. I feel like the problem come from syntax error in sql part.

Re: Change Database Table cell value Problem

jason_lightfoot_adsk — Tue, 04 Jan 2022 15:10:05 GMT

You don't show an execute of the statement in the graphic. You need that:

statement1.execute();

Re: Change Database Table cell value Problem

Jeanette_Fullmer — Fri, 07 Jan 2022 02:53:56 GMT

Hi @Fiona C, was one of Jordan Johnson's or Jason Lightfoot's answers helpful? If so, please click the "Accept" button at the bottom of the one that best answers your question. Or if you still have questions, add a comment and we'll continue the conversation.

If we haven't heard back from you within 3 business days we'll auto-accept an answer, but you can always unaccept and comment back to reopen your question.

Re: Change Database Table cell value Problem

shanice_c — Sun, 09 Jan 2022 00:26:37 GMT

@Jason Lightfoot I really missed that. The EQName column can't be written correctly, it shows "no path". Could I use Database.DataType.VarChar for pointer data type in a cell?

Re: Change Database Table cell value Problem

jason_lightfoot_adsk — Sun, 09 Jan 2022 08:24:55 GMT

Try using the name property or getPath method. If you want it shown just as the table in FlexSim that would be:

EQT["EQNames"].getPath(model(),1)

Re: Change Database Table cell value Problem

shanice_c — Mon, 10 Jan 2022 10:55:14 GMT

@Jason Lightfoot Thank you for your help! I could write cell value into database table now.