Create SQL query parameters dynamically


#1

Hi @badita @vvaidya @aksh1yadav @akhi_s27 @Vikas.Jain @Vitan @beesheep ,

How to build query parameters dynamically rather defining them well ahead in the “Execute Query” activity,

Lets assume a query with parameters as such in a file called “employee_query.sql”,

select * from employee e where e.deptId = ? and e.startdate >= ? and e.enddate < ?

reading the above query from a file whose filepath is defined in config file.

All I want to understand is how can I dynamically create the parameters rather defining it in the “Execute Query” activity.

Can someone share your thoughts please to solve this issue.

Thanks!


#2

The sql Command property is String. Therefore you should be able to create your query and update it using variables e.g. strSQLCommand = "SELECT * FROM " + strTableName + " WHERE ID = " + strID + “” (you’ll have to work out how many end quotes!)

In the above strTableName and strID are variables generated in your process from any source, which are then included within the SQL query string. You would then put strSQLCommand into the sql Command property instead of the text.


#3

Yes, but I’d recommend to use parameters instead of building a string command - noone likes Bobby Tables ;):
https://www.uipath.com/activities-guide/execute-query
So the SQL command would be:
SELECT * FROM @tableName WHERE ID = @id
and set Parameters (as strings) in the relevant collection argument. Make sure that names match of course.


#4

Always defer to @andrzej.kniola’s advice over mine :slight_smile:


#5

Thanks guys.!

My Idea is to build a solution to dynamically collect the params for the query reading it from a config file and supply to the query to run rather defining it well ahead in the collections.

It was helpful and thanks for your ideas. Its all done, now.


#6

Hi Andrzej, i am struggling with using parameterized query in UiPath. I am not sure what i am doing wrong but i am not able to make it work.
E.g. when i am using this query “SELECT * FROM [NewSheet$] WHERE Col1= ‘abc’” everything works perfectly fine, but as soon as i change either table name or condition value to parameter i get an error.
I have defined these 2 parameters:


When i try this “SELECT * FROM @tabName WHERE [Col1]= ‘abc’” i get Syntax error in query. Incomplete query clause (like if the table name was not in correct format)
When i try “SELECT * FROM [NewSheet$] WHERE [Col1]= @colValue” i get Too few parameters. Expected 1 (like if the column name (not value itself) was not correct)
I tried similar also with Oracle DB but also without any luck.
Do you please know what could be wrong. Thanks a lot for your answer


#7

Have you tried with a tab name that doesn’t include the [NewSheet$] - wondering if the special chars are causing a problem?

RD


#8

Hi Richard, thank you for your reply, actual name of the sheet is NewSheet, the dollar sign is used only in the query as it is required by ODBC Excel Driver so it can recognize it as a sheet. Even if i hardcode this name and use the parameter only for the condition i still get an error. If i type entire query (without parameters) it works just fine.


#9

I think when I do this I save the query as a Stored Procedure in the DB. You’ll need to change the text in Command Type to Stored Procedure. Is that an option for you?


#10

As i am trying to pull the data from regular excel sheet (which acts like a DB) i dont think this is an option for me.


#11

This post helped, hence thanks.
Just to add a point: Its important to ensure that there are no new line char in the query string, if we try to format or intend the query this may introduce new line chars which will violate the syntax of a query string.


#12

Jan,
save that parameter value as a variable first before passing it as a parameter


#13

Hi TAFS,

thank you for your reply. Do you mean to use a variable instead of “abc” (used in the example above in Sql Parameters dialog). I have tried that but unfortunately i get the same error as before.


#14

@jan

I’m not sure if this issue below could helps you :


You can try it.


#15

Thank you Iainh, Execute query activity does not let me to use that script in Sql parameter as it is expecting only SQL statement. When i try it i get this error: ACEODBC.DLL : ERROR [42000] [Microsoft][ODBC Excel Driver] Invalid SQL statement; expected ‘DELETE’, ‘INSERT’, ‘PROCEDURE’, ‘SELECT’, or ‘UPDATE’.


#16

@jan

Make sure that you have import your target data from EXCEL into datatable,right?


#17

If anyone had the same issue, when you are using ODBC driver you dont use this syntax “@parName” but instead you use only “?” so if you have 2 parameters the only thing that matters is the order so the name can be whatever.
This query: SELECT * FROM [NewSheet$] WHERE col1=‘abc’ AND col2=‘cba’ is equal to
SELECT * FROM [NewSheet$] WHERE col1=? AND col2=?