Change Database Table cell value Problem

Question

question

shanice.c asked Dec 26, '21 Jason Lightfoot edited Jan 10, '22

Change Database Table cell value Problem

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

Software Version:

FlexSim 21.1.0

database

test-db.fsm (41.3 KiB)

db.7z (973 B)

1640508763216.png (209.4 KiB)

1640506822976.png (51.7 KiB)

· 1

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

Jeanette F ♦♦ commented · Jan 07, 2022 at 02:53 AM

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.

0 ·

Answer 1 · 2021-12-28T18:55:55Z

Jordan Johnson answered Dec 28, '21 Jason Lightfoot edited Jan 10, '22

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.

· 10

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

shanice.c commented · Jan 03, 2022 at 02:03 PM

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

0 ·

1641217913951.png (151.9 KiB)

test-db.fsm (41.8 KiB)

db0103.7z (1.1 KiB)

Jason Lightfoot ♦♦ shanice.c commented · Jan 03, 2022 at 02:18 PM

It's EQName not EQNames in your database table

0 ·

Jordan Johnson ♦♦ shanice.c commented · Jan 03, 2022 at 05:20 PM

@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).

0 ·

shanice.c Jordan Johnson ♦♦ commented · Jan 04, 2022 at 01:03 PM

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.

0 ·

1641300926278.png (60.2 KiB)

Show more comments

Answer 2 · 2021-12-28T11:35:36Z

Jason Lightfoot answered Dec 28, '21 Jason Lightfoot commented Dec 29, '21

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.

· 4

5 |100000

Attachments: Up to 12 attachments (including images) can be used with a maximum of 23.8 MiB each and 47.7 MiB total.

shanice.c commented · Dec 28, 2021 at 12:55 PM

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?

0 ·

Jason Lightfoot ♦♦ shanice.c commented · Dec 28, 2021 at 06:02 PM

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

0 ·

Jordan Johnson ♦♦ commented · Dec 28, 2021 at 06:59 PM

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();

0 ·

Jason Lightfoot ♦♦ Jordan Johnson ♦♦ commented · Dec 29, 2021 at 10:25 AM

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

0 ·

question