Using Variables in a SQL Query - String Commands must end in Double Quotes

Help Studio
, , , , , , ,
1

Hello,

I recently was able to connect to our Database to start performing some SQL queries with UiPath Studio. However - I have only ever been able to complete the query by inserting the criteria in single quotes within the SQL (i.e., ‘ABC Company’, ‘US Corporate’, ‘123456’, etc.).

I want to turn my SQL query into one that works with variables and have been following the examples of other threads on the Community, but haven’t gotten my query to work yet.

Thoughts on what I’m doing wrong? All five variable I have setup as string variables in a multiple assign before the Execute Query (OUStr, InvNumstr,SupplierNameStr,SupplierNumbeStr,INvAmtStr). I’ve even trying assigning those 5 variables to @OU, @InvNum, @SuppName, etc. within the parameters screen of the SQL query with no luck. I can’t seem to find the right syntax for them to recognize my criteria in the SQL query using variables.

“Select Distinct abc.INVOICE_NUM, abc.name OPERATING_UNIT, abc.vendor_name SUPPLIER_NAME, abc.segment1 SUPPLIER_NUMBER, abc.INVOICE_AMOUNT
From abc.ap_invoices_all abc,
abc.HR_ALL_ORGANIZATION_UNITS abc,
abc.ap_suppliers abc
Where abc.org_id = abc.ORGANIZATION_ID(+)
And abc.vendor_id(+) = abc.vendor_id
And abc.name Like " + OuStr + "
And abc.INVOICE_NUM Like " + InvNumstr + "
And abc.vendor_name Like " + SupplierNameSt + "
And abc.segment1 Like " + SupplierNumber + "
And abc.INVOICE_AMOUNT Like " + InvAmtStr +”"

image

2

I had a similar issue where I was trying to assign a command to an argument that I passed into Start Command Prompt Process. I was needing to pass the following argument (w/o the <>) in:
<“C:\Program Files\UiPath\Studio\UiPath\SetupExtensions.exe” /chrome>

The string argument would work if I used:
““C:\Program Files\UiPath\Studio\UiPath\SetupExtensions.exe” /chrome”

Assigning the argument the following worked:
chr(34)+“C:\Program Files\UiPath\Studio\UiPath\SetupExtensions.exe”+Chr(34)+" /chrome"

Using the chr(34) in place of the double quotes may work for you.

Good luck!

symilawr

3

This didn’t change anything - still getting the “String Constants must end with a Double Quote”.

4

Hi,

Can you try String.Format method as the following?

img20220413-1
img20220413-1742×610 25.5 KB 

String.Format(query,{OuStr,InvNumstr,SupplierNameSt,SupplierNumber,invAmtstr})

Main.xaml (8.2 KB)

Regards,

5

This solution didn’t work - continued to get a ORA-0093 error - SQL command not properly ended.

Any other thoughts? There has to be a way to complete this.

6

Hi @taxguy33 ,

In the Multiple Assign Activity, Try Adding Single Quotes to the Values like below :

InNumStr = "'12345'"

Do this for all the other Values and Check if you are able to get the Output

7

Ultimately - I found out that for some reason this SQL only works with “Variables” if the whole SQL is on 1 single line (there were new line returns in the original SQL). I delete all of those new line returns and put it all on 1 row with Variables and it is successful (the string constants must end with a double quote error disappears).

“Select Distinct abc.INVOICE_NUM, abc.name OPERATING_UNIT, abc.vendor_name SUPPLIER_NAME, abc.segment1 SUPPLIER_NUMBER, abc.INVOICE_AMOUNT From abc.ap_invoices_all a, abc.HR_ALL_ORGANIZATION_UNITS a2, abc.ap_suppliers e Where abc.org_id = abc.ORGANIZATION_ID(+) And abc.vendor_id(+) = abc.vendor_id And abc.name = '”+OUstr+“’ And abc.INVOICE_NUM = '”+InvNumstr+“’ And abc.vendor_name = '”+SupplierNameStr+“'”

Does anyone know how I’d be able to have this SQL on multiple lines as it’s awful to read on just one read? Should I use some “Environment.NewLine” values in the SQL?

8

@taxguy33 Didn’t the Modification of the Variable values with Single Quotes work ?

9

It did not work at all because of the multi-line issue. One I made the SQL a single line all the variable insertion into the code worked with your suggestion, but also with all the other ways that I’ve seen online to do. The multi-line issue was the overarching problem in this situation.

10

@taxguy33 ,

Ok. In that case, Maybe you can keep the Query in a Text file with the formatters. Then Read the Text file and Perform the String.format(query,{"YourVariablesValuesToBeSubstituted"}) operation.

11

you should pass variable in sql query
“select * from your query”‘+varname+’“”
like this

12

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.