This content has been marked as final. Show 5 replies
How are you constructing your string? Maybe try the concat function in an XPath expression.
concat("'", string(Value1), "','", string(Value2), "','", string(Value3), "'")
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.
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
Try something like this:
SELECT Field1, Field2, Field3 FROM Table1
WHERE Field4 IN
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...
PS I'd also log this as a bug with Adobe - I think it should work.
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.