selecting data into arrays in vortex scripts

[barry.marcus]

I've been curious about something for a while... We have a lot of table data that is stored as comma-delimited lists in varchar columns. These are simple arrays that the code INSERTs INTO the table. When we need to iterate the individual values in the comma-delimited string I've always found it necessary, after retrieving the data from the table, to strip the starting and ending parens, then split the resulting string on the commas back into an array. Is there a way to simply retrieve the data directly into an array?

This is what I mean:

<$theData='a' 'b' 'c' 'd' 'e' 'f'>
<sql "insert into myTable (data) values ($theData)"></sql>

<!-- This... -->
<sql "select data from myTable"></sql>
<sandr "[()]" "" $data>
<split "," $ret>
<$data=$ret>
<loop $data>$data</loop>

<!-- is not the same as this... -->
<sql "select data from myTable"></sql>
<loop $data>$data</loop>

<!-- and not the same as this... -->
<sql "select data from myTable">$data</sql>


Using <sandr> and <split> isn't really a problem. I'm just curious if there is a way to select directly into an array given that the data is already stored the way it is.

[John]

Which version of Texis?

When inserting an array of values into a single row some conversion needs to happen. In Version 5 and earlier they are converted into a string suitable to use as a metamorph query.

Some options for you:

Insert separate rows into the table:

<loop $theData>
<sql "insert into myTable (data) values ($theData)"></sql>
</loop>

and then

<sql row "select data from myTable">
$data
</sql>

Alternatively with Texis 6 and new if the data column is a strlst instead of varchar then it should work as you expect as long as you don't change the arrayconvert setting. http://www.thunderstone.com/site/vortex ... s_and.html

[barry.marcus]

Thanks for the info, John.