How to use WHERE clause in Database.Connection.query()?

Question

question

shanice.c asked Apr 25, '22 shanice.c commented Apr 26, '22

How to use WHERE clause in Database.Connection.query()?

Hello,

In my model, I'd like to read values from database. I use Database.Connection.query() to write query() method directly. However, I'm having problem with the WHERE clause, since $1 value would dynamic change, but the following code couldn't let the query conditions change while $1 value changes. Could anyone help me with fixing SQL query below?

Software Version:

FlexSim 22.0.1

sql database

1650898867014.png (38.0 KiB)

Answer 1 · 2022-04-25T15:25:42Z

Iago MF answered Apr 25, '22 shanice.c commented Apr 26, '22

Hi,

You can create the query concatenating strings:

string query = "SELECT * FROM TaskTable WHERE RouteID = " + token.count;

Database.ResultSet result = con.query(query);

I hope it helps!

· 3

shanice.c commented · Apr 25, 2022 at 03:53 PM

Hi @Iago MF , thank you for your method, I could write the SQL as I want. But may I ask why it says no Routetable exists?

I think I have my RouteTable here.

And I've checked the database is connected successfully.

I think the problem may be the "Database.ResultSet" in line19 is not in bold? Though I've tried to typed it again, but still couldn't become bold.

0 ·

1650901878934.png (131.1 KiB)

Iago MF shanice.c commented · Apr 26, 2022 at 06:50 AM

Hi,

I'm not sure what could be wrong. It looks like it's not finding that table in DataBase . Maybe DataBaseConnector1 is connected to another DB where there isn't any table with that name?

0 ·

shanice.c Iago MF commented · Apr 26, 2022 at 01:54 PM

Hi,@Iago MF Thank you for the response. I've found the mistake is that I missed out the ".db", and now it's working ok.

0 ·

1650981132084.png (21.9 KiB)

Answer 2 · 2022-04-25T15:32:55Z

Jason Lightfoot answered Apr 25, '22 shanice.c commented Apr 25, '22

I would suggest you consider the use of prepared statements with parameter binding.

· 1

shanice.c commented · Apr 25, 2022 at 04:05 PM

Hello @Jason Lightfoot

Thank you for reply. Previously, I've used "prepared statements" to insert data to external database. And I found the statement long and a kind of not easy to read. I've gone through the manual, and would like to know is "prepared statements" also could be used for any SQL, the difference is that it could directly assign each fields with specific values? Since the examples in manual demo inserting data to db. And now I'm trying to get data need from database, so I ask this question.

0 ·

question