How to use a variable number in a dbsqlquery command.

Question

question

William Proctor asked Nov 7, '17 Ben Wilson answered Nov 7, '17

How to use a variable number in a dbsqlquery command.

I am modifying a model to open and update an existing database with information from a global table. There is a row of the table that includes a unique experiment number. What I want to do is open a database, check to see if an experiment with the same number has been run. If it is duplicate experiment, I would like to update the current information in the database. If it is a new experiment, I would like to add the information from the proper column of the global table.

Right now I'm trying to use the following line of code to determine if I need to add a new database record and also identify the record if there is already one.:

dbsqlquery("Select * FROM ModelResults WHERE EXISTS (SELECT * FROM ModelResults WHERE Experiment_Number = $1)",Output[2][i]);

ModelResults is a table in the Access database

Experiment_Number is one of the database fields.

Output[2][i] is a global table value.

I would like to use the table value Output[2][i] to replace the "$1" in the formula but I am getting an error that i can't solve. I can run the query with no issues if I replace the "$1" with a number.

Thanks in advance for your help!

Software Version:

Choose One

dashboards sql database

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.

Answer 1 · 2017-11-07T16:09:33Z

Ben Wilson answered Nov 7, '17

Hi @William Proctor,

It looks like the dbsqlquery() syntax differs from the dot-syntax-style query() command. Here is the syntax for dbsqlquery():

dbsqlquery(str query[, num forwardOnlyCursor])

So in the case of dbsqlquery(), you will need to pass the query in as a single string. So in your case, try combining the variable into the string using concatenation:

dbsqlquery("Select * FROM ModelResults WHERE EXISTS (SELECT * FROM ModelResults WHERE Experiment_Number = "+string.fromNum(Output[2][i])+")");

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.

question