You are viewing limited content. For full access, please sign in.

Question

Question

Pass a list of of string values into a SQL query from Workflow

Workflow Version 8
Updated May 13, 2014
asked on April 9, 2014

I'm using the Custom Query activity in Workflow and trying to pass a list of string values to insert into the where clause.

Ex: Where accountNumber in ('1234','5678')

 

In the Workflow query, I've replaced the values with a variable:

where accountNumber in (@AcctNbrs)

 

No matter what I try, I can't find a way to pass a list of account numbers into the variable without getting an error. I've tried providing a multi-value token seperated by ','

Ex: %(RetrieveFieldValues_Account Numbers_All#[',']#)

All other attempts have been variations on this.

 

Is there a way to do this with a variable number of account numbers?

0 0

Replies

replied on April 9, 2014

Looking at your SQL command, if your syntax is where accountNumber in (@AcctNbrs) , then I think your query would end up missing the leading and trailing single quotes, like so: where accountNumber in (1234','5678) instead of the correct syntax where accountNumber in ('1234','5678')
 

 

replied on April 10, 2014

I think you're nearly there, you're just missing the opening and closing quotes either side of the token.

 

Have you tried?

'%(RetrieveFieldValues_Account Numbers_All#[',']#)'

 

 

-Ben

 

 

0 0
View 4 previous replies
replied on April 10, 2014

Yes, I have tried that. When I run a test on the query from within workflow, I get an error.

 

I have found that if I add quotes around any variable that is a string in the query, I get an error. In other words, I get an error even if I manually pass '1234','4567'.

0 0
replied on April 10, 2014

I used the token editor to test and it worked fine so I wonder what's going on. I didn't test against the actual database and am not near a test system just now...

 

how about escaping the quotes?

 

\'324\',\'2342\'

 

 

0 0
replied on April 10, 2014

The queries are parametrized, so injecting values is not going to work. We need to look into what the SQL driver does a bit more to see if there is a solution.

1 0
replied on April 10, 2014

You may have already tried this, but if you run the query Where accountNumber in ('1234','5678') manually on the SQL server, does it work? It seems a bit odd that an accountNumber would be using the string type rather than a numeric type like integer.

replied on April 10, 2014

I figured Laserfiche Workflow was being "smart" about how it handles the parameters and I needed to find a way around it.

 

So, no one has tried to pass a list of values into a SQL query from workflow? I am trying to avoid looping through and making a query for each value (acct number).

 

Thanks, Miruna!

0 0
replied on April 10, 2014

How variable is the number of values?

 

You could build the whole query outside as single token and use that token by itself as this post describes.

 

Or if it's a reasonable number of possible values, you can add them to the list as separate parameters and use token indexing to populate them. If the index goes past the maximum number of values in the list, the token resolves to the last value in the list, so your query wouldn't break.

 

(Still looking for a better solution)

2 0
replied on April 10, 2014

The query as a big token is interesting as it would allow me to use one query activity to find cases where there are no values provided.

Or if it's a reasonable number of possible values, you can add them to the list as separate parameters and use token indexing to populate them. If the index goes past the maximum number of values in the list, the token resolves to the last value in the list, so your query wouldn't break.


This is what I've done so far. The maximum allowed number of values is 4. I'd like to know of another solution for when we run into a scenario when we need more.

 

Thanks for the feedback. This is very valuable.

 

0 0
replied on May 13, 2014 • Show version history

In the current version you could perhaps join the token values together with a unique identifier like !!! and investigate one of the myriad string split methods for SQL: https://www.google.com/search?q=sql+function+to+split+string 

 

 

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...