Hi all,

Over the last weekend, I was inspired by LINQ in writing a small utility that allows developers to apply SQL statements (Select, Insert, Update, and Delete) to various lists (Collections, Array, delimited strings, Cursors, XML, SQL tables, etc.), and then output the result in any of those formats. You can mix and match the various formats, filter, sort, and do most any standard SQL expression. While the testing is going on, I will be writing a users guide.

I am looking for few people to beta test this utility before I place it in the download section. If you are interested in helping me, please email me and I will send you a copy of it, including a demo program that illustrates more examples on how it is used.

You must have VFP 8.0 or newer.

Here are a few examples...

cTest1 = ["aaaaa"]+crlf+["bbbbb"]+crlf+[ccccc]+crlf+["ddddd"]+crlf+["eeeee"]+crlf+["fffff"]
cTest2 = ["11111"]+crlf+["22222"]+crlf+["33333"]+crlf+["44444"]+crlf+["55555"]+crlf+["66666"]+crlf+["77777"]+crlf+["88888"]+crlf+["99999"]

***************
lxCollection = CREATEOBJECT("Collection")
? 'Join delimited strings to collection'
? QUERY([Select * from cTest2{comma,crlf} where exp1>"55555"  union (select * from cTest1) order by 1 desc into collection lxCollection])

***************
cTab=""
? 'Converted to tab delimited'
? QUERY("Select * from ctest1{comma,crlf} order by 1 into string ctab{comma,tab}")
? cTab

? 'Insert new value'
? QUERY([insert into ctab{comma,tab} (exp1) values ("xxxxx")])
? cTab

? 'Change new value'
? QUERY([Update ctab{comma,tab} set exp1="ggggg" where exp1="xxxxx"])
? cTab

? 'Remove new value'
? QUERY([Delete from ctab{comma,tab} where exp1="ggggg"])
? cTab

? 'Convert to CRLF delimited'
? QUERY([Select * from ctab{comma,tab} order by 1 into string ctest1{comma,crlf}])
? cTest1

***************
DIMENSION aTest[1,1]
? 'Literal list to array'
? QUERY([Select * from ("first",1;"second",2;"third",3;"forth",4;"fifth",5) order by 2 desc where exp2>2 into array aTest])

***************
lcConnStr = "DSN=SQL Server;UID=sa;PWD=;DATABASE=Northwind"
?'SQL table to array'
DIMENSION laCustomers[1,1]
? QUERY("Select CompanyName, ContactName from SQL Customers order by ContactName into array laCustomers" , lcConnStr)
FOR x=1 TO MIN(10,ALEN(laCustomers,1))
	? laCustomers[x,1],laCustomers[x,2]
NEXT

***************
? ‘Query a literal string into a collection.’
DIMENSION aTest[1,2]
oColl = CREATEOBJECT("Collection")
? QUERY('Select exp1{C10}, exp2{N3.0} from ("first",1;"second",2;"first",3;"first",4;"second",5) order by 2 desc into array aTest')
? QUERY("SELECT atest.exp1{C10}, max(atest.exp2) AS exp2{N3} FROM atest GROUP BY atest.exp1 ORDER BY atest.exp1 into collection oColl")
FOR EACH oo IN oColl
	? oo.Exp1, oo.Exp2
NEXT

***************
? 'User defined destination field names and data type'
? QUERY( 'Select field1{C20}, field2{N10.2} from ("first",1;"second",2;"third",3;"forth",4;"fifth",5) order by 2 desc into cursor qTest' )
SELECT qTest
SCAN ALL
	? Field1, field2
ENDSCAN