I'm a hobbyist, or as Dan Shafer would say, 'an Inventive User'. I like mucking around with databases and the ease with which Revolution can communicate with databases makes it an excellent combination.

Whether you use 'official' databases, or are a Media user who rolls your own, working with databases invariably means working with Lists, and sometimes they are very big Lists. Even if you don't want to create a database of any form, a quick search of the Revolution Documentation reveals that there are around 49 commands, functions or properties that require or have optional input via a List.

So why this stack? When working with large Lists, finding a syntax error, or any error for that matter, out of a hundred odd List items can be very tedious.

The basic stack, which you can download from here took me 6 mins to create. For a novice Revolution user I'm pretty sure you can get this working in 8 min. So lets get started.

I created a New Mainstack, left it at it's default size and into it placed two long thin 'Text Entry' fields and filled the rest of the stack with a 'Scrolling List Field'. Nothing needs to happen until the 2nd list is entered into field "list2", so using the 'closeField' message which triggers only when field data is altered, list2's script moves through each item in list1 taking it and it's corresponding item in list2 and placing them on their own line in field 'output':

on closeField
put empty into fld "output"
repeat with tCounter = 1 to the number of items in fld "list1"
put item tCounter of fld "list1" & tab & item tCounter of fld "list2" & cr after fld "output"
end repeat
end closeField

Yep, that's it.

I don't know if I'm more embarrassed because this is all I can offer the Revolution community, or more amazed at how easy small utility apps like this are to make in Revolution!

Let's see how this little app can be helpful and maybe look at an improvement or 3.

Here's a simple SQL INSERT statement:

INSERT INTO my_table (id,surname,first,remarks,age,eyes,height,weight) VALUES ("96238","McGerkinsquirter","Fred","Plays football Mondays","35","Emerald","185","102")

If I copy the first list of column names into list1, and the second list of values into field list2, the result should be:

OK, I can see that I have successfully matched the column names to their values, and it's great that all the data is visible, unlike the Variable Watcher where long lists will appear like the data in field "list2" - you have to scroll to get to the end.

But how about a mistake:

INSERT INTO my_table (id,surname,first,remarks,age,eyes,height,weight) VALUES ("96238","McGerkinsquirter","Fred","Plays football Mondays, squash on Fridays",'35","Emerald","185","102")

So this has hi-lighted two errors; there's a comma in my data and that can muck things up because the default itemDelimiter is a comma, and I've accidentally used a ' instead of " which is incorrect for SQL.

So what about improvements. Jacque "ooooh-oo-oo. Oo. Oo." Landman-Gay has shown us how to make Revolution sing, so let's see if I can get it to dance a two-step. I'm constantly dealing with SQL INSERT statements, so why not have Revolution chop it up into two Lists automatically.

For newbies I've created a complete new stack which you can download here, so you can check out the differences. For brevity, I've generally removed all comments from the scripts here, but if you open up the stacks you'll see all the comments.

Into the script of field "list1" I put:

on closeField
switch
case (word 1 to 2 of me is "INSERT INTO")
hSQLinsert me
break
case (b)

break
end switch
end closeField

A little strange, but I have a few other improvements in the back of my mind and I know that the Switch control structure is one of the easiest ways to add additional functionality to an existing script. I've gone with a custom handler 'hSQLinsert' because I know that it's going to take a few lines to achieve the desired result and I generally like to keep my Switch statements nice and clean so it is visually apparent what their purpose is.

Here's the hSQLinsert handler, which resides in the card script:

on hSQLinsert pData
put matchText(pData,"\((.+)\) VALUES \((.+)\)",tList1,tList2) into it
set the cNewDelimiter of cd "input" to fNewDelimiter(tList1,tList2)
replace comma with the cNewDelimiter of cd "input" in tList1
replace quote & comma & quote with quote & the cNewDelimiter of cd "input" & quote in tList2
put empty into fld "list2" --just in case
hDisplayData tList1,tList2
end hSQLinsert

The smooth move here is with matchText, which is very powerful, and is why it looks so... odd. It uses GREP and I can never remember all that guff so I go to http://www.rexswain.com/perl5.html#regular for a nice summary. Very briefly it finds all the data between the first pair of () and puts it into variable tList1, and all the data between the second pair of () and puts it into tList2, neat.

Next, to solve the problem of random commas upsetting things, I'm going to find a character that isn't in either list and use it as the itemDelimiter, which will be saved as a custom property so that it can be easily accessed. To set cNewDelimiter I created a function fNewDelimiter().

Here's fNewDelimiter(), which also resides in the card script:

FUNCTION fNewDelimiter pList1,pList2
repeat with tCounter = 127 down to 1
if ((pList1 contains numToChar(tCounter)) OR (pList2 contains numToChar(tCounter))) then
--don't need to do anything, just proceed to next ASCII char
else
exit repeat -- a char has been found
end if
end repeat
--this is a final check that we haven't got all the way to 1
if (tCounter = 1) then
answer warning "Couldn't find a suitable delimiter." titled "Can Not Continue"
breakpoint
exit to top
else
return numToChar(tCounter)
end if
end fNewDelimiter

Pretty straight forward, numToChar() converts a number to it's ASCII equivalent character, we then check to see if that character exists in either list, if not, bingo, we can use it as an itemDelimiter without fear of conflict. I start with 127 because it's an unusual character, so far every time I've run it, 127 has been used. Also, although the itemDelimiter can be set to any of the 255 ASCII chars, split and combine can only use the first 127 so I want to stay compatible just in case I need to create some arrays.

The rest of hSQLinsert is pretty straight forward. tList1 can not contain stray commas, SQL names don't allow it, so replacing all the commas with the cNewDelimiter is easy. For tList2, there can be stray commas, but unlikely between quotes, so I only replace commas that are between quotes.

I finished off with a new handler, hDisplayData. I know that I'm going to have a few handlers display data, so why keep repeating the same old script lines. The contents of hDisplayData should be pretty familiar:

on hDisplayData pList1,pList2
set the itemDelimiter to the cNewDelimiter of card "input"
put empty into fld "output"
repeat with tCounter = 1 to the number of items in pList1
put item tCounter of pList1 & tab & item tCounter of pList2 & cr after fld "output"
end repeat
end hDisplayData

This is basically identical to where we started, the only changes:
1) so stray commas don't muck things up we use cNewDelimiter
2) instead of taking the items out of fields, they come from parameters.

The quick amongst you will realise that this means I need to modify my original script in field "list2", it now looks like:

on closeField
--if these are hand entered lists then the delimiter should be a comma
set the cNewDelimiter of card "input" to comma
hDisplayData fld "list1", field "list2"
end closeField

Here's the the output of an SQL INSERT statement:

Notice now that the commas inside data no longer effect output, and incorrectly quoted data is even more obvious to pick up.

For the last dance, it's going to be a Blues number. Lets face it, compared to the bird on Jacques' stack, mine's a little bland.

Colour in fields is handled by the htmlText property. Haven't done html in a while so the easiest option is to plagiarise. So into field list1 I typed:

item1 => item2

Using Revolution's own Text menu I set the colour of item1 to green, and the colour of item2 to blue. In the message box I typed:

put the htmlText of field "list1"

and got back:

<p><font color="#00FF00">item1</font> => <font color="#0000FF">item2</font></p>

So now I have the html needed to make one column green and the other blue. Actually, in Revolution, it can be even more English like:

<p><font color="green">item1</font> => <font color="blue">item2</font></p>

The modified hDisplayData handler looks like this. I could have made it a one liner, but for easy reading I split it into it's three components:

on hDisplayData pList1,pList2
set the itemDelimiter to the cNewDelimiter of card "input"
repeat with tCounter = 1 to the number of items in pList1
put "<p><font color=" & quote & "green" & quote & ">" & item tCounter of pList1 & "</font>" after tStore
put tab & "=>" & tab after tStore
put "<font color=" & quote & "blue" & quote & ">" & item tCounter of pList2 & "</font></p>" & cr after tStore
end repeat
set the htmlText of fld "output" to tStore
end hDisplayData

A little fooling around to get the quotes in the right place around the colour names, but other than that, inserting the variable into the html code is pretty straight forward.

Another cosmetic change I made was to create hResetData:

on hResetData
put empty into fld "list1"
put empty into fld "list2"
put empty into fld "output"
end hResetData

which is called by 'on openCard' so that on start up you're presented with a nice clean sheet. Again, for the newbies, a further version of this stack is here so you can compare the changes.

So here's the current version:

I'm really glad I whipped this up, and have already added further functionality via that Switch statement. For the novice user I hope you have a poke around inside this stack and chop and change things to see what happens. I plagiarise, so should you :-)