Is it possible to pass a array of string to UIpath Sql Parameter(Collection)

Help
1

I am trying to call a SQL which has a IN Clause. in this in clause i want to pass dynamically the list of id which are comma delimited string values. i get issue while doing this where message is typically that String array cannot be cast to object. Now if i change the in type to String in Paramater then i can pass the string but then i need to manually build the string like ‘123’,‘345’

Is this a UIPath constraints of not able to pass the array to the Sql query, or is there some other way to achieve this. ?
For now we are relying to create the entire SQL in the assign variable and then pass the SQL as a string. Any guidance here is highly appreciated.

2

I built something recently where I need to build a Query with a list of invoices.
Here is a snapshot:
image
I did not use any arguments and just concatenated the Query string with variables that I was providing it such as the column name and a comma-delimitted list of invoices.

If you have an array you can use .Select() and String.Join() on it to quickly manipulate it with 's and ,s
Here is an example:

String.Join(",",arrayvar.Select(Function(x) "'"+x+"'"))

Hopefully any of this is helpful.

Regards.

2 Likes
3

yes, that is what i am doing currently. Joining the string and then trying to execute… Was just wondering if this impact the performance of the query. typically a query is a prepared one if parameter are injected into the query and is more performannt. but if there is no choice then what you suggested is the only way.

The other behavior see is that if i pass a single argument in the In(UiPath collection) then it works , but as soon as i added one more element with a comma there is a errpr. i tried to convert the In type to array of string. It looks oracle client while doing the conversion of datatype is not able to map the Array to the variable in the Sql…

Not able to digest that this simple thing is not possible…:slight_smile:

image

Error
System.Exception: Unknown datatype System.String for parameter value of type Object.

Server stack trace:
at System.Data.OracleClient.MetaType.GetMetaTypeForObject(Object value)
at System.Data.OracleClient.OracleParameter.GetMetaType(Object value)
at System.Data.OracleClient.OracleParameterBinding.PrepareForBind(OracleConnection connection, Int32& offset)

4

Yeah, I had a similar challenge with trying to get an array passed in to the query. I’m not a pro at SQL syntax but seemed like it would only work if the list was in a string form rather than an array. I was also trying to use a DataTable as a table but that didn’t work either.

In the end, my solution works pretty fast, which only takes a few seconds to pull a list of rows from millions and millions of lines of data. Additionally, I had to match with another ID number to filter down the data and match with each invoice. To do that, I ran the distinct ID numbers in one column through a For each and merged the tables that were returned from the Query for each group of invoices within that ID number… if that makes sense.

5

Yep that make sense. though i have not yet got that much data to test the performance of this approach but it should work.
I also tried passing the common delimited of string type rather than string array type. on that case UI path will not complain as type is string , but it will not return the result. Now this may be that comma delimited string is treated as one value rather than the token for IN clause, so it does not find anything.
Anyhow, keeping project time in mind i am taking the approach you mentioned earlier.

thanks