selecting data into arrays in vortex scripts

Post Reply
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

selecting data into arrays in vortex scripts

Post by 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.
User avatar
John
Site Admin
Posts: 2597
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

selecting data into arrays in vortex scripts

Post by 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
John Turnbull
Thunderstone Software
barry.marcus
Posts: 288
Joined: Thu Nov 16, 2006 1:05 pm

selecting data into arrays in vortex scripts

Post by barry.marcus »

Thanks for the info, John.
Post Reply