Thunderstone Support Forums

Hi,

I am trying to run a vortex script using the texis command line. My script executes a sql statement.

My command is : texis DATABASE=/mytexisdb var=19507,19508 myvortexscript

In my vortex script, I have the following
<SQL OUTPUT=xml "select ID from MyTable
where ID in ($var) ">

When I run this, I only get back the first value,"19507". How can I set this up so that I get back both "19507" and "19508"?

Thanks.

The assignment `var=19507,19508' gives $var one string (varchar) value -- `19507,19508'. Give it two values with two assignments: `var=19507' `var=19508'. Then your <sql> should return a row for each value -- assuming `ID' is an integral type, arrayconvert for parameters is on (the default), and this is a version 6 or later Texis.

For version 5 Texis, this should work (untested):

<sum "%s," $var>
<$param = (convert($ret, 'varstrlst' ))>
<sql ... " ... where ID in ($param)"> ...

Since there is no arrayconvert in version 5, convert the parameter to a strlst first. The IN operator should be able to handle it (though there were some issues with IN in version 5).

With version 5 you would want to convert to strlst first, e.g.

<sandr "[^,]=>>=" "\1," $var><!-- Make sure there is a trailing comma -->
<$qvar=(convert( $ret , 'strlst' ))>
<sql output=xml "select ID from MyTable where ID in ($qvar)">

Variables are always passed in to SQL as parameters to prevent SQL injection security holes by pasting strings directly in.