question

shanice.c avatar image
0 Likes"
shanice.c asked shanice.c commented

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?

1650898867014.png


FlexSim 22.0.1
sqldatabase
1650898867014.png (38.0 KiB)
5 |100000
Toggle Comment visibility. Current Visibility: Viewable by all users

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

2 Answers

Iago MF avatar image
-1 Like"
Iago MF answered shanice.c commented

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
5 |100000
Toggle Comment visibility. Current Visibility: Viewable by all users

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

shanice.c avatar image shanice.c commented ·

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?

1650901878934.png

I think I have my RouteTable here.

1650901896365.png

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 Likes 0 ·
1650901878934.png (131.1 KiB)
Iago MF avatar image Iago MF shanice.c commented ·

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 Likes 0 ·
shanice.c avatar image shanice.c Iago MF commented ·

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.

1650981132084.png

0 Likes 0 ·
1650981132084.png (21.9 KiB)
Jason Lightfoot avatar image
2 Likes"
Jason Lightfoot answered shanice.c commented

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

· 1
5 |100000
Toggle Comment visibility. Current Visibility: Viewable by all users

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

shanice.c avatar image shanice.c commented ·

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 Likes 0 ·