How to pass a variable in an SQL query

Hello all, I am trying to execute a dynamic SQL query (SQLite), which looks like this:

“SELECT address
FROM Clients
WHERE client = ‘(varClientId)’”

(varClientId) is a place holder for what would be my string variable.

Except it’s wrong, because I get an error. I already tried ‘$[variable_name]’, but it returns an empty result.

Furthermore I tried :

“SELECT address
FROM Clients
WHERE client = “+”'”+varClientID+“'”+"

But seems like you have to execute one string command.

Thank you,

Hello,
Have you tried making a string variable and then using that as the query.This is my perferred way to run sql queries.

queryStr = “SELECT address FROM clients WHERE client = “+”’”+varClientID+”’“+”
Then our your dynamic SQL query use queryStr as the string.

3 Likes

Hello Sergio,

I believe this will work for you:
"Select address From Clients Where client = '" + varClientID + "'"

Here’s the output if it is run in a log message activity:
image

2 Likes

Hi @eccprog

Here is the sample to pass the parameter to the query.

Regards
Balamurugan.S

3 Likes

Thank you all, this worked!

1 Like

Capture

do you know why i’m getting this?

Hi @Raghad,

It seems dependencies are missing in your workflow for that you need to install packages go to manage package install - Uipath.Database.Activities

Thanks,
Neelima.

Hi, that was a good example. could you please help me how to use insert for mysql db?
I tried this
“INSERT INTO uipathdb.employee (EmpID, FirstName, LastName, Age) VALUES (@EmpID, @FN, @LN, @Age);”
and in arguments passed
EmpID=102
FN=“Test”
LN=“Test2”
Age=50

but i got error
“Exception Message for Transaction Log:ERROR [HY000] [MySQL][ODBC 8.0(w) Driver][mysqld-8.0.23]Column ‘EmpID’ cannot be nullmyodbc8w.dll”,

Thanks I found a link in which they explained how to use insert for Mysql.

Below are the detals, it worked for me

If you want to parameterize and avoid SQL injection attacks, you have to do the following for MySQL statements (queries and non-queries):

  • in the CommandType property choose StoredProcedure
  • in the Sql field (statement) replace all values that you want to transform in parameters with a question mark (?); don’t use @parametername, that is for Microsoft SQL, and don’t use apostrophes like this ‘?’, use just plain simple question marks
  • in the Parameters sub-window, you must store all the parameters in the order you have them in your Sql statement (query). The first (topmost) parameter should correspond to the first question mark, the second parameter should correspond to the second question mark and so on. I think the name of the parameter is irrelevant here, what is important is the order of the parameters. You can re-arrange them with the small top-down arrows in the corner of the Parameters window.

Example:
"INSERT INTO table (field1, field2, field3, field4) VALUES (?,?,?,'success')"

And then, in the Parameters sub-window, the topmost parameter should correspond to what you want delivered to field1, the second should correspond to field2, and the last one to field3.

Be careful. In the Sql statement, you may need to add backticks (`) to the table name and field names.

1 Like

There are two ways to handle this problem if it is insert or update we can pass variables as @Variable but incase of Select query you can use String queryStr=“SELECT * FROM ida_db.address a, ida_db.client b, ida_db.contact_details c Where a.Address_Id = b.Client_Postal_Address_Id_Fk And b.Client_Contact_Id_Details_Fk = c.Contact_Id And b.Client_Id =”+In_ClientID

Also use this in One line in the Editor otherwise error will pop up.

Hi, I hope u already found the solution. just i tried. its working string variable need to use in the sql as “Select address from Clients where client ='”+VarClientid+"’ "

Attached is how i solved this.
You will need to have the variable already created THEN create an argument via “Parameters” that lets you call the variable you are looking to use.

After that just use @[Argument_Name] in your condition.
PS. you will need to make sure the datatypes are alligned, hence the use of the convert() function
I believe this only work for certain queries (Update, Insert, Delete) otherwise you will need to use one of the solutions above.

1 Like

Nice one, @Tevernaugh. I was going to post this.

It’s much safer to use parameters (or even better, stored procedures) to avoid SQL injection attacks.

1 Like