Passing a string into an SQL query IN statement

[Former Community Member]

Hello,



I need to connect to a database to pull some data to dynamically create a form based on the data I pull back. My SQL query works fine when I manually run it through a SQL client tool, but when I try to pass it through my workflow I'm having trouble with passing my string into the IN part of the statement. So if for example my SQL query is:



SELECT Field1, Field2, Field3 FROM Table1 WHERE Field4 IN (?)



I have a process variable that has the string I'm trying to pass into the ?, but I don't seem to be able to get the query to run. I have tried setting up my query to run as a Parameterized Query (passing my string process variable into the ?), and by setting the query up through xPath (where I am calling my process variable with an xPath declaration), but am not having any luck.



The process variable I am trying to pass is formatted such that I'm passing 'Value1','Value2','Value3' but I can reformat this string if need be. Even with using test data I can't get the query to return anything. For test data I have tried: 'Value1','Value2','Value3' ; Value1','Value2','Value3 ; Value1,Value2,Value3 but the query never returns any data. I can't seem to see how to format the string to pass into the query. The Query will work with a single Value in the test data, but as soon as I try to pass multiple values within the string it fails. Any suggestions?

[Former Community Member]

How are you constructing your string? Maybe try the concat function in an XPath expression.



concat("'", string(Value1), "','", string(Value2), "','", string(Value3), "'")

[Former Community Member]

The problem looks to be a limit on what I can pass into the SQL query component. My string is coming from data returned from another database. I take the xml output from that database call, pass it through a set variable component to remove my xml tags from the string, and then format the string in a script component (I have to do it this way because of the way the data coming out of my first database call). I've put in loggers, and can see that the string I'm passing into my query that is giving me problems, is formatted the same way as if I were to use the concat function Scott listed above. It looks like there is a limitation on what can be passed in my variable. I have tried creating my entire SQL query statement in a set variable component, and then just calling the process variable that holds that statement, but there is a character limit of 128 character for what can be passed in a variable through xpath in the SQL query component.



The next thing I tried was changing my SQL where clause. Instead of passing my variable directly into the IN statement I set up a PATINDEX('%:'+countyname+ ':%', ?) > 0 call to check for the values in my database call. As you can see I took out the "," that I was passing as part of my string, thinking that the SQL component was getting confused by them, and placed ":" characters around my values being passed in my string variable. No matter what I try to do though I'm not able to get the query to run. The component looks like it is taking my string, and is seeing the whole thing as a string instead of passing it as individual values within a string.



I think I'm getting close, but I keep getting a Content not allowed in prolog exception in the server logs.

[Former Community Member]

As a last resort you can use the script component to do execute the parameterized query,let me know if you need help on doing it

thanks

mergeandfuse@gmail.com

[HowardTreisman]

Try something like this:

SELECT Field1, Field2, Field3 FROM Table1

WHERE Field4 IN

({$/process_data/@mystring$})



where mystring is a string variable containing the list of strings.



This will inject the data directly into the query string, rather than trying to use variable binding.



Hope this helps... Let us know...

Howard

http://www.avoka.com



PS I'd also log this as a bug with Adobe - I think it should work.

[Former Community Member]

Sorry I should have reposted a response to this. The issue ended up being with the SQL database we use to run LiveCycle. There is a limitation to the number of XML process variables that a single process can have associated with it. When my SQL statement was failing I was using about four different XML process variables to store data being queried from various databases. In working with Adobe support we came to realize that the limitation is with the SQL database and LiveCycle. The SQL database looks to only be able to handle a max of 2 XML process variables per process (mySQL has this same limitation though Adobe claims you can have up to 7 XML process variables before this type of error occurs). If you have more than this, when you go to store data in the variables LiveCycle throws the content is not allowed in prolog exception. The workaround was for me to reuse my XML variables and rework my process so that I was using at most two XML variables in the process. The SQL query component does function properly, and once I only had two XML process variables in my process my query with the in statement ran just fine.