Chapter 1
Overview
The purpose of this manual is to
guide the developer through using the Query Engine.
The Query Engine was inspired by the
.Net LINQ enhancement. Like LINQ, Query
allows the developer to manipulate lists of heterogeneous data (Collections, Arrays, delimited strings, etc)
as if they were cursors or tables, using an enhanced SQL statements. In addition to the structures just mentioned,
regular cursors, tables, SQL tables, XML files or strings can also be
involved. The output can also result in
any of the mentioned formats.
I was reading about the LINQ
(Language Integrated Query) feature introduced to Microsoft’s .NET, and was
inspired about the idea of using SQL statements to manipulate lists the same
way we do tables and cursors in VFP. I
set out to see if I could devise a means of getting VFP to perform queries
against Collection objects. Soon I
discovered that it was not that difficult to convert a collection of SCATTER
objects into a temporary cursor, execute the SQL statement (the SELECT
statement), and then convert the result back to a Collection of object. This continued to evolve to the point that I
included other form of lists, including Arrays, CVS style of delimited strings,
standard tables and cursors, and even XML formats of files and strings. It became possible to mix and match the
various formats in a single SQL statement.
For example, I could query a table of customers for customer names and
filter the list using a collection of cities, and output the result to a XML
string.
SELECT
Customer.FirstName, Customer.LastName FROM TABLE Customer.dbf
WHERE Customer.City in (SELECT
CityCollection.City FROM
COLLECTION CityCollection)
ORDER BY Customer.LastName ASC
INTO XML lcXMLstring.xml
Figure
1:
A Query function SQL Select statement
Of course, I cannot do this
statement directly. The Query routine
parses the SQL statement and temporarily converts all the various data sources
to cursors of the same names. Then the
SQL statement can be perform as written, and executed using the temporary
cursors to generate a resulting cursor.
The final step is to convert the result to “INTO” data type.
In addition to the SQL SELECT
statement, Query can also perform SQL INSERT, UPDATE, and DELETE statements
against the various data sources.
Chapter 2
List of Features
This is a
general overview of the feature provided from Query Engine.
·
Perform SQL SELECT queries against various list
formats.
o Tables
o Cursors
o Collection
of values
o Collection
of objects (Created from SCATTER command)
o Arrays
(one and two dimensional)
o CSV
style delimited string variables
o Literal
strings as delimited data source
o XML
formatted data in variables
o XML
formatted data in a files
o SQL
Server via Connection String
·
Include various standard SQL clauses to form the
output list.
o TOP
n [PERCENT]
o DISTINCT
o WHERE
o ORDER
BY
o GROUP
BY … HAVING
o INNER
| [FULL | LEFT | RIGHT ] OUTER JOIN … ON
·
Perform SQL INSERT statements to add new data to
the lists.
·
Perform SQL UPDATE statements to modify data in
the lists
·
Perform SQL DELETE statements to remove data
from the lists.
·
Output to any of the list formats mentioned
above.
·
Define the delimiters when outputting to CSV
style delimited strings.
·
Name the output data fields.
·
Define data type and size for output fields that
did not originate from typed data.
·
Passing LOCAL variables in a Collection object
as Name / Value pairs.
·
Switch data session to a specific data session
while querying.
What is new in version 3.00 since 2.00:
·
General features
o Now
with more than 50 data drivers
o CLAUSE
Drivers (Clauses\qc*.prg) to refine and expanded actions on the SQL Clauses
themselves.
o SET
Drivers to allow for SET Statements in the Query statement.
o Multiple-line
Query Statements
o More
complete FUnit test cases and QTestCase.prg to run each.
o Implicit
INTO SCREEN BROWSE if no INTO clause is provide to Select statement.
o Added
a TOP # clause after the FROM clause to restrict the number of items
drawn from initial source. (if TOP # is
after SELECT, then all items drawn from the source first.)
o Correction
of many small issues in both the Query engine and the Drivers.
o Intelligence
‘QD’ to generate Data Driver Template for new Drivers.
o Regular
Expression as an condition operator.
o Automatically
corrects Memo style fields in cursors.
o New
third parameter toErrMsg; a collection of error messages.
o SQL version of <fld> LIKE <pat> [CASE] expression - convert VFP
wildcards to SQL, and allow case insensitivity.
o If
the Driver requires a name for the FROM and/or INTO clauses, an ‘@’ can be used
to auto-generate unique name for temporary use.
·
New Clauses
o SQL
CASE WHEN statement
o SQL MERGE command
o PIVOT
clause to perform cross-tabulation on result data set.
§ Defaults
using vfpXTab.prg
§ Optional
uses of Fastxtab.prg
§ Optional
uses of Matxtab.prg
o PAGE
<#rec>,<pagelen> clause to return subset of the resulting query.
o TIMEOUT
clause to prevent long running queries.
o DEBUG
/ NODEBUG clause to control the debug more on individual SQL statements.
o ENGINEBEHAIVOR
## clause to control the behavior of individual query statements.
o DATASESSION
clause to insure the query is ran in the right form’s data session.
o TRANSACTION
clause to enable and disable Transaction processing.
§ If
an error occurs, the transaction is rolled back, otherwise committed.
o INDEX
clause to generate temporary index tags on the data source cursor.
o EXCLUSIVE
clause to open tables in exclusive mode.
o CLOSE
clause to close data source after completing the Query.
o Different
types of Query() return values of Number, Duration, Alias, Error and Value.
o SHOW
clause displays a progress bar during query run.
·
New Drivers and additions to drivers
o The
FILE driver has a new clause called ‘Content’.
When added after the FROM FILE clause, the content of the file is
included in a column called Content.
o Multiple
file skeletons are now allowed in the FILE driver.
§ Ig.
“PRGS\*.prg;Text\*.txt”
o VOID
data driver, when the resulting output is not required.
o
Chapter 3
Requirements
This section outlines the minimum
requirements from both Windows operating system and Visual FoxPro. Plus, outlines the external dependencies
that the main application and drivers rely on.
These are the
minimum operations and Visual FoxPro version that this system has been designed
for and test on.
Required:
·
MS Visual FoxPro 8.0 or greater. Either the
development version or just runtime.
Optional (depending on the drivers):
·
vfpCompression.fll
·
filer.fll
·
efoxsql.fll
·
CHapter 4
Using Query
Normally,
Query is called by passing it a SQL statement and executes the statement, then
either return a count of the number of rows effected or a negative number
representing an error has occurred (see Error Messages). The other method is create an instants of the
qQuery class and calling the Exec() method.
We will cover both these method in the coming chapters.
One of the
biggest advantages in version 2.0 over that of version 1.0 is the extendibility
of the data drivers. I have provided the
base data drivers to query Tables, Cursors, Collections, Arrays, SQL server,
and delimited strings. Both addition
data drivers can be added by dropping PRG based class library into the Drivers
folder. These class library are based
off a class named qBase class found in the Query.prg file. They are identified as drivers because their
filename are prefixed with “Query_”. We
will get into more details on their structures and predefined methods in the Data Drivers chapter.
The following
a the basic syntax used to call the Query function.
result
= Query( <SQL Statement> [,[<Collection
of parameters>][,ErrMsg collection] )
Figure
2:
Query function syntax
|
Parameter
|
|
Description
|
|
SQL Statement
|
Required
|
Standard SQL Select / Insert / Update / Delete statement
|
|
Parameters Collection
|
Optional
|
If the data source is in a local variable, then you can
pass the variable in a collection object.
Each item in the collection represents a single parameter as a Name
(item) / Value (key) pair. When the
Query SQL statement is executed, the collection is parsed and the reference
to the variable are re-established.
See Passing Parameters for more detailed information on how
to properly pass variable and get updated return results through the use of a
Collection object.
|
|
Error Message Collection
|
Optional
|
If provided, is populated with a collection of error
message object consisting of a property named Message.
|
Figure
3:
Query function parameter definition
A second
method of generating access to the Query Engine is to instantiate it as a
object.
|
LOCAL loQuery AS qQuery IN
Query.prg
LOCAL lnTally AS NUMBER
loQuery = Query()
lnTally = loQuery.Exec( [<SQL statement>] )
IF lnTally<0
? loQuery.ErrMessage
ENDIF
OR
IF QUERY( [SQL
Statement] )<0
SCAN ALL
?
QueryErrors.Message
ENDSCAN
USE IN QueryErrors
ENDIF
OR
loErrs = CREATEOBJECT(“Collection”)
IF QUERY( [SQL
Statement], ,@loErrs )<0
FOR EACH loErr IN loErrs
? loErr.Message
NEXT
ENDIF
|
Figure 4:
Instantiate Query as Object
The advantage
in using Query as a object is having more control over how the execution and
error monitoring (see Figure 4). The object is created by calling the Query
Engine without any arguments. Instead of
returning a count from executing the SQL statement, a reference to the Query
object is returned. We then execute the
SQL statement by calling the Exec() method.
If the return value from the Exec() method is less than zero, and error
occurred. The ErrMessage will then
contain a carriage-return/line-feed delimited string of all the error message
collected during the execution.
In the next
sections we will cover the SQL statement syntaxes and some of the extensions
added to aid the Query Engine in processing them. After that we will look into Data Drivers and
specifics surround each of them.
Sometime during the further development of
2.0+ version, I allow for multiple line batch processing. As in Figure 5, (even though poor example)
illustrates a collection data from a source and then in a second statement
building a ComboBox listing. This came
in very handy when I introduced the SET Statements.
|
PRIVATE laTemp[1]
laTemp = ""
PRIVATE loCBO
loCBO = CREATEOBJECT("Combobox")
TEXT TO lcSQL NOSHOW TEXTMERGE
SELECT
distinct type FROM foxuser ;
ORDER BY type ;
INTO ARRAY laTemp
SELECT * FROM array laTemp ;
INTO combobox loCBO
ENDTEXT
=Query(lcSQL)
? loCBO.ListCount
|
Figure
5: Multiple Query batch processing.
I attempt to
retain the original SQL syntax when I built the Query routine. With the exception there are more data
source and destination types, the syntax is very similar. Those clauses in red
are the Query enhancements, those in blue are the standard VFP clauses (all the
clauses may not be show for VFP.)
|
SELECT [ALL | DISTINCT] [TOP # [PERCENT]]
<field
names>[ {datatype[size.decimal]}][ AS <alias name>] | *
| [CASE [<exp>] WHEN
<exp> THEN <result1> ELSE <result2> END ]
FROM
[FORCE] <data driver> <source name>|@ [{<delimiters>}] [AS <alias>]
| (<delimited string>) [{<delimiters>}] [AS <alias>]
[WITH (BUFFERING = lExpr)] [WITH NOLOCK]
[TOP
#] [PAGE #,#]
[SHOW]
[DATASESSION #] [ENGINEBEHAVIOR ##] [EXCLUSIVE] [CLOSE] [TRANSACTION
ON | OFF]
[ERRORLOG <filename> | OFF]
[RETURN COUNT | DURATION | VALUE | ALIAS | ERROR]
[[WITH]
INDEX <fieldlist>]
[TIMEOUT <millisec>]
[DEBUG
| NODEBUG]
[
[[INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER]]
JOIN <data driver> <source name> [{<delimiters>}] ON <condition> ]
[WHERE
<condition>]
[GROUP
BY <field list> [HAVING <condition>]]
[UNION
[ALL] <SELECT Command>]
[ORDER
BY <field list> [ASC | DESC]]
[PIVOT ON | STANDARD | MAT | FAST <rowfield>
AND <colfield> SUM <datafield> WITH TOTAL]
[INTO
| TO <data driver> <destination name> | @ [{<delimiters>}]]
[PREFERENCE PreferenceName] [NOCONSOLE] [PLAIN]
[NOWAIT] [READWRITE] [NOFILTER]
Where:
<data
driver> equals
ARRAY, CLASSLIB, COLLECTION, COMBOBOX, CSV, CURSOR, DATABASE, DLL, DRIVE, DRIVER,
DSN,
ENVIRONMENT, EVENTLOG, EXCEL, EXTERNAL, FIELD, FILE, FILEUSER, FONTS, FTP,
GRAPHIC,
INDEXTAG, INI, KEYWORD, LANGUAGEID, LISTBOX, LOCALE, NETRESOURCE, ODBC,
PATH,
PEM, PRG, PRINTER, PROCESS, PROJECT, QUERY, REGISTRY, SCREEN, SERVICE,
SOURCE,
SPELLCHECK,
SQL, STACK, STRING, TABLE, TASK, TIMEZONE, TXT, USERGROUPS, USERS,
VARIABLE,
VFPSET, VOID, XML, ZIP
If driver is
STRING then <delimiters> are defined as <field
delimiter>,<record delimiter>
<condition>
equals
[,expression]
[CASE [<exp>] WHEN <exp> THEN
<result1> ELSE <result2> END ]
[<string> REGEXP
"<regexp>" [CASE]]
<field list> (for INDEX) equals
<column name> [ASC | DESC]
[PERSIST]
|
Figure
6:
Query function SQL SELECT syntax
Lets start with a basic SQL SELECT
statement and expand on it. First we
will need a data source. In this case, a
table will do, like Customers table.
nCount=Query(“SELECT
FirstName,LastName FROM TABLE Customers INTO CURSOR CustName”)
Figure
7:
Standard SQL Select to cursor
Granted, this is nothing new. We could already do this is Visual FoxPro
right out of the box. With the exception
of the “<data driver>”, this is a standard FoxPro SQL statement. The “<data driver>” represents the many
data sources we can access; Table, Cursor, XML, ListBox, Array, Collections,
etc over 40 in all.
Normally we would check the value of the
“nCount” variable for negative number, in case there was a error
generated. It an error did occur during
the execution, the ErrMessage property would contain all the errors as a CRLF
delimited string.
Let’s move on, what if the data destination
is not a FoxPro cursor, but a collection.
With a few changes, this too become possible.
Private
CustName
CustName
= CreateObject(“Collection”)
nCount=
Query([SELECT FirstName,LastName FROM TABLE Customers]+;
[ INTO COLLECTION OBJECT
CustName])
Figure
8:
Select to Collection object
Notice, with the exception of generating
the destination variables (CustName), the only other changes designating the
data driver for the source and destination of the data source in the SQL
statement. Because there are more than
one output columns in the statement, the collection (CustName) is actual a
collection of SCATTER NAME objects, with each object representing one of the
rows from the source data. If we had
specified only one column, the result would be a collection of the result of
that column.
Now, using the collection we created, let
us filter and sort it. Let us filter to
show only those from France, Germany, and the UK, and sort the result on
Country and LastName. The result is
place back into the same Collection.
nCount
= Query([SELECT * FROM COLLECTION CustName ]+;
[ WHERE Country IN
(“France”,”Germany”,”UK”) ORDER BY Country, LastName ]+;
[ INTO COLLECTION OBJECT CustName])
Figure
9:
Filter and Sorting a collection
I wonder how
many lines of code it would have taken to do the same thing in the last few
lines of code. Not only that, but
because SQL tends to be self-documenting by its syntax, the code is more
understandable.
In the next
example we are going to use the Browse window to edit an array. But first we need the array. By using the literal string feature, we use
Query to fill the array.
DIMENSION
laList[1,3]
Query([SELECT
* FROM STRING
("Test1",1,9;"Test2",10,19;"Test3",20,29”) INTO
ARRAY laList] )
Figure
10:
Filling an Array from a delimited string.
Assuming that the array had already
existed, and was not created as we did, then the next example performs the
actual browsing of the array.
*-- convert array to cursor
Query([SELECT Item{C10}, Start{N4.0},
End{N4.0} FROM ARRAY laList INTO CURSOR qList READWRITE])
*-- browse cursor
BROWSE
*-- convert cursor back to
array
Query([SELECT * FROM CURSOR qList ORDER BY
2,3 INTO ARRAY laList])
*-- close cursor
USE IN qList
Figure
11:
Browsing and editing an array
This concludes
the simple examples of using the Query routine.
In the next few sections examine the other SQL type statements (INSERT,
UPDATE, and DELETE.)
There are a
few differences from the SELECT statement from that of the INSERT, UPDATE, and
DELETE statements. First, is that the
data source cannot be a embedded literal delimited string. The reason is that the result has no variable
to receive the resulting values.
|
PRIVATE lcStr as STRING
LcStr = [“First”,1;”Second”,2;”Third”,3;”Forth”,4]
…
Query(“UPDATE STRING lcStr {comma,semicolon} SET
Exp1=”Test” WHERE Expr2=3] )
|
Figure
12:
Limits: No literal strings in non-SELECT statements
The second limitation involves that these
SQL statement cannot use the SQL Server data driver as the destination of the
results. There is currently no
underlying method to return the resulting cursor to the SQL server table
reliably. I plan to resolve this
limitation in a future release of the tool.
The INSERT
statement is near exactly like the one used in Visual FoxPro and ANSI SQL with
the only exception that the Data Driver must be provided. With this in mind, follow the rules as
outlined in the Visual FoxPro help file.
|
INSERT INTO <driver> <data
source> [(<field1> [,<field2>[…]] )]
VALUES (<value1>[, <value2>[…]] )
Or
INSERT INTO <driver> <data
source> [(<field1> [,<field2>[…]] )]
SELECT fname1 [, fname2,
...] FROM <driver> <data source> WHERE <condition>
|
Figure 13:
INSERT statement syntax
The Insert SQL statement cannot be
used as the destination of the Insert SQL statement.
Like the INSERT statement, the UPDATE statement
follows the VFP SQL standard with the exception of requiring a Data Driver
name. A data source of STRING cannot be
a literal string.
|
UPDATE <driver> <data
source>
SET <column_name1>=<expression>
[, … ]
WHERE <condition>
|
Figure 14:
UPDATE statement syntax
The limitation
currently imposed on the Update command involves the FILE data driver and
having only the ability to modify the files (and folders) attributes. Hopefully there other members will be added
soon.
Follow suit of
the last two SQL commands, the DELETE follows the VFP standards. Data Driver clause is require, and not
literal string type of data source.
|
DELETE FROM <driver> <data
source>
WHERE <condition>
|
Figure 15:
DELETE statement syntax
|
IMPORTANT…
do to
complication in the development phase of this feature, the merge statement is
being deferred to a future release.
|
One if the interesting addition to the SQL
language is the MERGE command. It allows
you to perform complex synchronization between two similar data sources. The Figure below is the syntax used by the Query
Engine.
See http://technet.microsoft.com/en-us/library/bb510625.aspx to see how the
command works.
|
MERGE [ NOSHOW ]
INTO <data driver> <target_table> [ AS <table_alias> ] [INDEX
<fieldlist>]
USING <data driver>
<table_source> [ AS <table_alias> ]
[INDEX <fieldlist>]
ON <merge_search_condition>
[ WHEN MATCHED [ AND
<clause_search_condition> ]
THEN <merge_matched> ]
[ WHEN NOT MATCHED BY TARGET [ AND
<clause_search_condition> ]
THEN <merge_not_matched> ]
[ WHEN NOT MATCHED BY SOURCE [ AND
<clause_search_condition> ]
THEN <merge_matched> ]
[ OUTPUT <output_cursor> ]
|
Figure 16:
MERGE command
The follow Figure demonstrates a
sample MERGE execution.
|
LOCAL lcSQL
TEXT TO lcSQL NOSHOW TEXTMERGE
*SET DEBUG on
SET return count
Merge ;
into cursor qLocal AS qLocal index name ;
using
cursor qRemote as qRemote index name ;
on qLocal.Name=qRemote.Name ;
when not matched by
target then insert values (qRemote.name,qRemote.address)
;
when not matched by source and qRemote.Name="Dog" then delete ;
when matched then Update set qLocal.Address=qRemote.address ;
output
qCnt
ENDTEXT
? query( lcSQL )
|
Figure
17:
Sample MERGE statement
It is typical
for a cursor to have expected column data types and sizes. Otherwise it would be difficult to perform
SQL statements like UNION and JOIN. So
to resolve this problem I introduced the ability to define the columns into the
SQL statement.
|
QUERY([SELECT exp1 {C10}, exp2 {N3.0} FROM ];
+[STRING
("first",1;"second",2;"first",3;"first",4;"second",5)];
+[
{comma,semicolon} ORDER BY 2 DESC INTO ARRAY aTest])
QUERY([SELECT atest.exp1 {C10}, MAX(atest.exp2) {N3} AS exp2];
+[ FROM ARRAY atest GROUP BY
atest.exp1 ORDER BY atest.exp1 ];
+[ INTO COLLECTION OBJECT oColl])
|
Figure
18:
Defining data type and size to columns
The data type
and size are defined by placing them whit in curly brackets ‘{}’ after each
column definitions (see Figure 16.) As you
will note; the definition is similar to that of the FoxPro CREATE command, with
the exception that instead of the decimal place being preceded with a comma it
is a period (there are coding issues that currently forced me to do this.)
Now we’ll look
at the engine behind Query. Query is
made of two parts; the Core Engine and the Data Drivers. The purpose of the Core is to interrupt the
incoming SQL statement, and call Data Drivers methods. The key to what goes on inside the Data
Drivers. Data Drivers translates various
data sources (Array, Collection, etc.) into cursors (general with the same name
as data sources). Once all the data
source have been converted to cursors, the SQL statement can be executed
normally. The resulting cursor is then
passed to the data driver to be translated from the cursor to the destination
data source.
|
|
TEXT TO lcSQL NOSHOW TEXTMERGE
SELECT * from table Customers
;
LEFT JOIN SQL Orders CONSTRING lcConStr ON Customers.CustID=Orders.CustID
;
WHERE City in (Select
Exp1 from Collection Cities) ;
ORDER BY CustID ;
INTO XML OBJECT CustOrder.xml
ENDTEXT
Query( lcSQL )
|
|
Figure
19:
Data Drivers example
Figure 17 illustrates the
process of a complex SQL statement using multiple data sources. The query joins
the VFP table ‘Customer’ to a SQL Server table ‘Orders’, using a Collection of
cities to filter the results. The result
is outputted by CustID to a XML file.
This is done all in a single statement.
As you can
see, there are several data drivers provide with the Query Engine. Unfortunately, not all data drivers
(currently) can provide both input (left-side) and output (right-side)
conversion of the cursors to their respective data sources.
In VFP, Tables
(and Cursors) are the focus in SQL statements.
But in the Query Engines, they are the exception. But to provide an overall access to all data
sources, they are included in the default set of data drivers. When accessing tables the keyword “TABLE” (or
“CURSOR” for cursors) must precede the table name (or cursor alias). If the table is in database (DBC), then the
table name can be prefixed with the database name and the exclamation point
“!”. Also, if a table is being
referenced and no filename extension is provided, the extension of “DBF” is
assumes as default.
|
… TABLE
[<database>!]<table name> [AS
<alias>] …
|
Figure
20:
Table data driver
In the real
world, SQL statement are generally directed toward external database
application like MS SQL Server, Oracle, MySQL, Access, etc. But in FoxPro, the built-in SQL engine only
is directed to FoxPro tables and cursors.
Query allows the developer to address not only FoxPro data sources but
to address external database also; in the same statement. (see Figure 17)
|
… SQL <table
name> [CONSTRING <literal
connection string> | <variable>]
[NAME <connection alias name>] [AS <alias>] …
|
Figure
21:
SQL data driver
All external
database tables are preceded with the keyword “SQL”. Also either a connection string ( or variable
containing the connection string) is provided and/or a alias name that was
previously provided is used to make the connection to the database and
tables. The forth example if Figure 20 demonstrates the
usage of a named connection string and the using a variable containing the
connection string.
|
SELECT Name FROM SQL customers
CONSTRING
“Driver={SQL
Server};Server=(local);Database=Northwind;Trusted_Connection=Yes;”
INTO
CURSOR qCust
SELECT Name FROM SQL customers
CONSTRING
“Driver={SQL
Server};Server=(local);Database=Northwind;Trusted_Connection=Yes;”
NAME
NorthWind INTO CURSOR qCust
lcConStr
= “Driver={SQL
Server};Server=(local);Database=Northwind;Trusted_Connection=Yes;”
SELECT Name FROM SQL customers CONSTRING
lcConStr INTO CURSOR qCust
lcConStr
= “Driver={SQL
Server};Server=(local);Database=Northwind;Trusted_Connection=Yes;”
SELECT Customer.Name FROM SQL customers CONSTRING
lcConStr NAME NorthWind
JOIN
SQL orders NAME NorthWind ON
Customer.CustID = Order.CustId
INTO
CURSOR qCust
|
Figure
22:
Example of SQL data driver usage
When addressing external data
source, one must always take into consideration that the ALL the data in the
referenced tables are going to be copied to the temporary cursors before the
actual Query SQL statement is executed.
This can create a large delay in the execution of the queries.
The next data
driver we will look at are Collections.
|
… COLLECTION [OBJECT | VALUE] <collection variable name> …
|
Figure
23:
Collection data driver
The Collection object was introduced
in Visual FoxPro 8.0. Collections are a
handy place to store Name / Value pair of data.
Like arrays, we can store large quantities of data, but with
collections, we can also add a unique name to each item of data. Normally, collections are not a valid
destination for a SQL Select statement.
In the example below, we will create a Collection and populate it with a
list of names. Then we will perform a query against the collection, and filter
out only those names that start with the letter “P”. Finally, we’ll sort the results and store
them back the original collection. When
the resulting collection is displayed, we will see that only “Paul” and “Peter”
are shown.
*-- First, we need a
collection as a data source
ox = CreateObject(“Collection”)
Query([SELECT * FROM STRING
("Peter";"John";"Sam";"Paul";"Greg"){comma,semicolon}
] ;
+[ INTO COLLECTION ox])
*-- Now query the
collection and filter for items starting with “P”
Query([SELECT Exp1 FROM COLLECTION ox
WHERE Exp1="P" ORDER BY exp1 ASC INTO COLLECTION ox])
*-- Display the resulting
collection
FOR EACH oItem IN ox
?
oItem
NEXT
Figure
24:
Filling, then filtering and sorting a collection
Visual FoxPro
does not provide functionality to manipulate collection in these manner; such
as filtering and sorting. Of course,
other SQL clauses can be applied against the collection using the Query Engine.
This next
example demonstrates one of the types of collections produced with Query(); the
Value Collection. A Value Collection is
a standard collection type where the data is added directly to the collection
using the Add method. If the data source
is a single column type of data, then this method is default for the resulting
collection. The other type is the Object
Collection. It is a collection of object
created from a SCATTER NAME command. It
is the default method used when the resulting data has more than one
column. If our resulting data does in
fact have only a single column, but we want it resulting collection to be an
Object Collection, we can override the default by including the clause “OBJECT”
between the COLLECTION clause and the collection name. The next example demonstrates generating a
Object Collection.
*-- Now query the
collection and filter for items starting with “P”
Query([SELECT Name FROM COLLECTION VALUE
ox ] ;
+[ WHERE Name="P" ORDER BY
Name ASC ] ;
+[ INTO COLLECTION OBJECT ox])
*-- Display the resulting
collection
FOR EACH oItem IN ox
?
oItem.Name
NEXT
Figure
25:
Naming the column in Object Collection
Notice that we
are specifying the resulting column name in the field list. This provides a name for the property in the
resulting object stored in the collection.
Also, we are explicitly changing the type of collection we have from the
Value Collection to the Object Collection.
If the “VALUE” clause is included
then the result is stored as a standard collection. The first parameter is used as the Item value
and second parameter as the Key. All
other field are ignored.
Now, let us
talk about using Arrays as either source and/or destination in our queries. Granted, FoxPro comes with several functions
that can work with arrays, but Query provides a bit more flexibility with the
uses of SQL syntax.
|
… ARRAY
<array variable name> …
|
Figure
26:
Array data driver
You are
allowed to work with either one or two dimensional arrays as if we were
referring to a table.
We have
already seen a few queries that involved delimited string as their data
source. Now let’s take a closer look at
how to derive and use them.
|
… STRING <string variable> | (<literal string>
<column delimiter> <literal string>
<row delimiter> … [{column delimiter , row delimiter}] …
|
Figure
27:
String data driver
Delimited
Strings can be defined as Row only style, Column only style, or both as two
dimensional. You can also define the
type of characters to be used as the delimiters with in the SQL statement, by
default the column character is the comma, and the row character is the
carriage-return. By defining the
delimiters, you can then specify characters that is not used in the actual data
of the string.
Before we
start working with delimited strings in the Query function, let’s insure we
have an understanding as to what a delimited string is. A delimited string is a string that contains
several data elements that are divided by special characters to represent the
division between the rows and the columns.
The next example is a delimited string with names, birthdates, and ages,
with the carriage-return (CRLF) represent the row breaks, and commas represent
the column breaks.
lcStr = ;
[“Tom
Jones”,{^1960/06/10},45]+crlf+;
[“Peter
Rabbit”,{^1963/07/20},42]+crlf+;
[“Daffy
Duck”,{^1950/04/02},55]+crlf
Figure
28:
Normal delimited string
In order to
keep Query function as simple as possible (and flexible), and I encoded some
basic ground rules when representing data as delimited strings.
1. Text
data must be between double quote characters.
Only exception is if the delimited string is a single data element of
text, and the delimiters are a single space (SingleSpace) and carriage-return
(CRLF)
2. Delimiters
cannot be inside any part of the actual data.
They must be uniquely used as the delimiters only.
3. Dates
and Datetime data must be formatted in a strict date format, and between curly
brackets.
4. If
the data is logical or null, then they are represented as .T., .F., and .NULL.
5. Quotation
marks and brackets cannot be used as delimiters.
6. If
the delimited string is a literal string embedded in the SQL statement as the
data source, then the Carriage-return cannot be used as a delimiter. It is recommended to use the semicolon
instead.
7. In
the SQL statement, and delimiters must be specified, otherwise the comma,
semicolon combination is assumed.
The rule 7, it is
mentioned that the delimiters need to specified in the SQL statement. An example of this can be seen in Figure 22, directly after
the delimited string there are two word inside a pair of curly brackets (
{comma,semicolon} ). When a delimited
string is encountered in the SQL statement, the parse attempts to locate these
curly brackets to determine the delimiters used in the string. If not found, the comma / semicolon pair are
assumed. As you can see, we are using
words that represent the characters.
This is not true with all characters, but in addition to the terms Comma
and Semicolon there are few others words that are difficult to present in the
delimiter list. They are all list next.
|
Word Token
|
Comment
|
|
Comma
|
|
|
Semicolon
|
|
|
CRLF
|
Carriage-Return/Line-Feed
|
|
Tab
|
|
|
Space
|
Single space. This
is handy when parsing a sentence.
|
|
Colon
|
|
Figure 29:
Delimiter character token words
|
… XML <XML
filename> | <XML variable name> …
|
Figure
30:
XML data driver
·
Data source can be either a XML file (must have
filename extension), or a string variable containing a XML string.
·
XML format adheres to CursorToXml() function
output format when the flags is only 4 (preserve white spaces).
As of the time
of writing this document I have developed few other data drivers. But the sake of space, I am not going to
explain each as I have done the others.
I will simply mention each and prove a brief explanation of them. For more details, look at the header portion
of the PRG files. The syntax follows
similar format as those previously mentioned.
Each are simplistic and have much room for improvement.
The CVS driver
is similar to the String driver with the exception that it contains field names
as the first line. The columns in the
data is always delimited by commas and strings are always contained in
double-quotes marks. Rows are carriage-return
/ linefeed delimited. The driver uses the FoxPro APPEND FROM and
COPY TO commands to convert the CVS file to a cursor.
Figure
31:
CVS data driver
The File data
driver is handy when processing files in a folder, or even folder names
themselves. Either a straight filename
can be provided, which will produce a single row of just that filename, or a
filename skeleton containing wildcard characters (“?” and “*”) to get a list of
filenames. The Filename or skeleton must
be enclosed in either single or double quote marks. The resulting filename list preserves the
normally formatted of upper and lower case as they appear on the disk. The optional UPPERCASE or LOWERCASE forces
the result to a consistent case.
|
… FILE
“<filename> | <Filename skeleton>” [UPPERCASE | LOWERCASE]
…
|
Figure
32:
FILE data driver
The LISTBOX
data driver is an example of how the contents of an object can be the source
and destination of the data source. This
is a simple example, and can be expanded on to be more flexible for the various
row source of the ListBox. Also, you can
clone and modify it to drive other types of objects like ComboBoxes and Grids.
|
… LISTBOX
<object name> …
|
The PEM data
drivers uses the AMEMBERS function to derive an array of property, method, and
event names from an object. Currently,
it can only be used as the source of data in a SELECT statement.
The VOID data
driver is a special data driver in that no resulting destination data is
returned. This is handing when a count
is only the interested result. (I
originally added it for testing purpose.)
Since the
initial release of this document for version 2.0, there has been dozens of more
drivers added to the tool. See the
Data_Drivers folder for a list of all the drivers. Examples some of the ways they can be used
can be found either at the header section of the Data Driver (.prg) and/or in
the Test Case section. (see FUnit
chapter to see how Test Cases are used.)
In the next
section we will explore how you can create your own data drivers, and how to
implement them.
I am sure that by now you may have
ideas of a different source of data that you would like to query. In this section we will discuss how to create
your own data driver plug-in. Even
though it may appear complex in the beginning, it is rather simple once you get
the handle of it.
To start, we will get over the basic
rules of how to define a data driver class so that the Query Engine can
identify it. (It is my intension to refine and refactor this to simplify to
class identification, but for now this is how it is.) List below are the rules the tool expects to
follow to locate and load the data drivers.
Rule 1:
All data drivers are classes in a PRG
based class library. Their base class
MUST be from the “qBase” which is located in the Query.prg program file. The based way to copy the “_Template.prg” file
located in the Data_Driver folder and rename the copy to the desired data
driver.
|
DEFINE CLASS qDriver AS qbase OF QUERY.prg
|
Figure
33:
Data Driver class definition header
Rule 2:
The filename of the class MUST be the
name of the data driver as it would appear in the SQL statement. Hence, if the data source is a array then the
filename is named “Array.prg”.
Rule3:
The Query Engine looks for Drivers in
the Data_Driver folder under the folder the application started in. This can be changed by setting the “path”
property in the Drivers class.
|
LOCAL oQuery AS Query OF Query.prg
oQuery = Query()
oQuery.Drivers.Path = “.\MyDrivers”
oQuery.Drivers.Load() && reload drivers.
|
Figure
34:
Setting an alternate Drivers path
Rule 4:
The class name must have the same name
as the data driver filename, but, also, must begin with the letter “q”. This was included because some of the data
drivers I provided are reserved words in FoxPro and would cause a conflict
otherwise. Also, while we are speaking
of the class definition, because the class name and file name are related,
there can only be one data driver class per class library file.
Rule 5:
As I mentioned before; it is best to
clone the “_Template.prg” class library file to create new data drivers. The main reason is there are three methods
defined in them that the Query Engine expects to be there. Even though the drivers are inherited from a
predefined class that has these methods, the template provides a basic
framework of code the aids the Query Engine.
All the driver developer needs to code is the works in the places where
the comments “PLACE CODE HERE” reside.
See Figure 33 to better understand of the structure of
the driver class and class library.
Rule 6:
Lastly, the Load method converts the
data source (Array, Collection, String, etc.) to a cursor. And the Save method converts a cursor to the
data source INTO reference. As the SQL
statement is scanned (from left to right), it encounters the data driver
names. When this happens the Query
Engine converts the data source to a cursor with the same name as the data source
(or a unique name if it cannot be determined.)
But in the case of a SQL SELECT statement, when the INTO clause is
discovered, the INTO method is called to perform any last moment code that may
pertain to the outgoing result.
|
* Program:
Template.PRG
* Description: abstract template to be used as a
starting
* point
for data drivers in Query.prg
* Created:
12/12/2006
* Developer:
Gregory L Reichert - UT #046387
* Copyright:
Copyright (c) 2006 GLR software
*------------------------------------------------------------
*------------------------------------------------------------
* How to make a Data Driver class:
* 1. Copy this file and rename the copy to
QUERY_<data type>.prg
* 2. Place the new file in the Drivers
folder.
* 3. Modily the new data driver file, and
complete the Load and Save methods.
* a. The Save methed must result in a cursor
with the same name
* as
the data source passed in the argument lcName
* b. The Load method expects there to be a
cursor with the same
* as
the word at the tnIndex position of the laWords array.
*------------------------------------------------------------
DEFINE CLASS qTemplate AS qbase OF QUERY.prg
*------------------------------------------------------------
*
Description: Load data source into cursor
*
Parameters: tnIndex, req, def=,
current poistion in laWords array
* pointing to
the name of the data source.
*
Return:
* Use:
*------------------------------------------------------------
* Id
Date By Description
* 1 01/05/2007 Gregory L Reichert Initial Creation
*
*------------------------------------------------------------
PROCEDURE LOAD
LPARAMETERS tnIndex AS INTEGER
DO
CASE
CASE VARTYPE(tnIndex)<>"N"
QUERY.ErrMessage = "Parameter tnIndex is not a number."
CASE NOT BETWEEN(tnIndex,1, ALEN(laWords,1))
QUERY.ErrMessage = "Parameter tnIndex is out of range."
OTHERWISE
*-- get xml file name
EXTERNAL ARRAY laWords
LOCAL lcName AS STRING
lcName = laWords[tnIndex]
*--------------------------------------------------
* PLACE CODE HERE
*--------------------------------------------------
*-- remove data driver identifer
laWords[tnIndex-1]="" &&
renove reference to data driver
laWords[tnIndex] = JUSTSTEM(lcName) &&
convert data source to cursor name
ENDCASE
RETURN DODEFAULT( tnIndex )
ENDPROC
*------------------------------------------------------------
*
Description: Save cursor to data source
*
Parameters: tcName, req, def=, Name of
the cursor
*
Return:
* Use:
*------------------------------------------------------------
* Id
Date By Description
* 1 01/05/2007 Gregory L Reichert Initial Creation
*
*------------------------------------------------------------
PROCEDURE SAVE
LPARAMETERS tcName AS STRING
*--
validate
DO CASE
CASE VARTYPE(tcName)<>"C"
QUERY.ErrMessage = "Parameter tcName is not a string."
CASE NOT USED(JUSTSTEM(tcName))
QUERY.ErrMessage = "Cursor "+JUSTSTEM(tcName)+" does
not exist."
OTHERWISE
*--------------------------------------------------
* PLACE CODE HERE.
*--------------------------------------------------
ENDCASE
RETURN DODEFAULT( tcName )
ENDPROC
*------------------------------------------------------------
*
Description: This perform after the Load, but before the Save
*
Parameters: <para>,
<req/opt>, D=<def>, <desc>
*
Return:
*
Use:
*------------------------------------------------------------
* Id
Date By Description
* 1 01/19/2007 Gregory L Reichert Initial Creation
*
*------------------------------------------------------------
PROCEDURE
InTo
LPARAMETERS
tnIndex as Integer
*--------------------------------------------------
* PLACE CODE HERE
*--------------------------------------------------
RETURN
DODEFAULT( tnIndex )
ENDPROC
ENDDEFINE
|
Figure
35:
Template.prg
In version 3.0, we now have control
over the actual clauses used in the SQL statements. As the SQL statement is being parse and
examined, the various clauses used to call an Clause routines (.\clauses\qc*.prg). If one found, the routine is called. This allows for the expansion of the SQL
language while allowing better control over the existing clauses. Uses a copy
of the qcTemplate.prg to jump start your own SQL Clause extensions. Here is a list of the standard and extended
clauses, with a short description.
Standard Clauses:
The standard
list (below) are some of the original the SQL clauses found in FoxPro SQL
statements. Little occurs outside of the
expected when these Clause routines are performed.
Select
Update
Insert
Delete
From
Where
Order
Group
InTo
Extended Clauses:
These clauses were introduced to
extend the functionally and features available to the Query Engine.
Case When
After battling
with ICASE and IIF statement, I implemented the SQL CASE clauses. Of course this only works with Query Engine,
and actually is translated into a ICASE statement while the SQL Statement is
being parsed. Both MS SQL versions was
introduced.
|
Simple CASE expression:
CASE input_expression
WHEN when_expression THEN
result_expression
[ ...n ]
[
ELSE else_result_expression ]
END
Searched CASE expression:
CASE
WHEN Boolean_expression THEN
result_expression
[ ...n ]
[ ELSE else_result_expression ]
END
|
PIVOT …
Performs a
Pivot / Cross-tabulation on the resulting cursor before transforming it into
the final data set destination.
Query.app uses the vfpXTab.prg to rotate the cells. Warning: not all data destination types can
support multiple columns.
|
SELECT … FROM type <name>
PIVOT ON | MAT | FAST <row_name> AND
<column_name>
[SUM | COUNT | PERCENT <data_name>]
[WITH TOTAL]
|
|
TEXT TO lcSQL NOSHOW TEXTMERGE
select top 25 * ;
from
table foxuser close ;
pivot on id and type sum ckval with total ;
order
by id ;
into cursor
qtemp
ENDTEXT
Query( lcSQL )
|
DataSession ##
Work the same as the FoxPro
version. I have noticed that if external
routines then those in the a Form with Private Data Session will automatically
switch to the default data session. This
forces Query to a specific data session, say the Form’s data session. Replace the ## portion with the data session
of the caller routine before sending it to Query.
|
TEXT TO lcSQL
NOSHOW TEXTMERGE
Select * from
cursor qPayroll ;
DataSession
<<Thisform.DataSessionID>> ;
Into ListBox myForm.myListbox
ENDTEXT
|
Debug | NoDebug
The Debug |
NoDebug control whether the displays the error messages and suspends the Query
Engine when one occurs. As an inline
clause, they only effect the current SQL statement. The DEBUG clauses can appear anywhere in the
SQL statement as a valid clause.
EngineBehavior 70 | 80
| 90
Like the
DataSession clause, the EngineBehavior allows for independent control of the
FoxPro engine used to executed the SQL statements.
|
*NOTE
requires post 9 union rules as an example.
|
PAGE #,#
The PAGE
clause limits the result to a subset of intermitted resulting cursor. The first number if the page number of data
to retrieve. The second number is the
length of each page. For example, if we
used 3,10, then we would get the rows 21,30.
1,10 = 1,10
2,10 = 11,20
3,10 = 21,30
…
|
Select * from file
"*.xml" ;
content ;
page 3,25 ;
into cursor qXML
|
Get next 25 XML files; starting at row 26 (3*25)+1.
RegExp
The RegExp
(Regular Expression) operator can be used in expression to extend the pattern
matching abilities of Query. The CASE
statement refers to match case of the letters.
|
select * from
file "*.*";
where
ALLTRIM(name) regexp
“.*\.TXT$” case
into cursor temp
|
In this
example, we are search the file system for file names with extension of ‘.TXT’.
TimeOut ##
The Timeout
clause attempts to prevent long running query.
If provided in the SQL statement, and the time runs out, Query will
throw a exception error, and end the query.
A negative one (-1) is returned to indicate the query had failed.
Top ## (after the FROM
clause)
If a TOP clause is discovered after
the FROM clause in a SELECT statement, then only the first rows from the source
are returned. Unlike the original TOP
statement, this one is used while the source is being read, not after all the
rows have been read. This comes in handy
when the source is long and requires much time (i.e. reading large file set.)
Show | NoShow
Shows or hides the Progress bar
while the Query in running.
Index
<fieldlist>
Generates temporary indexes, with
unique tag name, for the temporary cursors created by the drivers. This can help on optimizing long running
queries. When the cursor is closed at
the end of the Querying, the temporary index tags are closed and removed
too. If the actual source of the data is
a FoxPro table, the routine will attempt to create a real index tag for the
table. Of course, the table will need
exclusive access.
<table name>
Exclusive
Open the table in Exclusive mode.
Best to follow the data source name.
<table name>
Close
Close the source data (either table
or cursor) at the conclusion of the query.
Aids in cleaning up the daggling tables and cursor referenced in the SQL
statement. The result would be the
resulting data destination defined in the SQL expression.
In addition to having control over
the behavior of the SQL Clauses, Query has a small set of SET Statement to help
controlling the environment the SQL statements are being executed in. In version 2.0, several of the additional
clauses where represented as clauses in the SQL statement itself. But in 3.0, many of these have been added as
precursor SET statements. The 2.0
versions are still supported in 3.0 for backward compatibility.
Here is a list and description of
the available SET Statement with syntax.
The option in capitals are the default values if the SET statement is no
present.
SET CloseCursors ON |
off
While processing
the QUERY SQL statement, temporary cursor can be generated. If the CloseCursors is ‘On’ (or
Query.CloseCursors=.T.) then any temporary cursor generated and marked as
temporary (use Query.MarkAsTemp(“<cursor alias>”) in driver) are
automatically closed. This will override
the CLOSE clause used in the SQL Statement.
SET DataSession ##
Work the same
as the FoxPro version. I have noticed
that if external routines then those in the a Form with Private Data Session
will automatically switch to the default data session. This forces Query to a specific data session,
say the Form’s data session. Replace the
## portion with the data session of the caller routine before sending it to
Query.
SET Debug on | OFF
Enters or leaves the Debug mode.
SET EngineBehavior 70
| 80 | 90
Like VFP, determines the FoxPro database engine to use by VFP
version.
SET ErrorLog
<Filename.dbf>
Defines the name of the error log cursor or table. By default the cursor would be named
“QueryErrors”.
SET Return COUNT |
duration | value | alias | value | error
When the Query Engine is called as a function, it returns a
count by default. This SET setting
determine alternate return values. The
“duration” return the time it took in fraction of a seconds. The “Value” return the first row of the first
column of the result set. The “alias”
return the result set cursor name. handy
when using the “@” as a temporary cursor name.
SET Talk on | OFF
Displays a FoxPro Talk window while querying.
SET TimeOut <#
seconds>
If a query runs longer then the TimeOut, Query attempts to
cancel the query. If the cancellation
occurs, Query will return a -1.
SET Transaction on |
OFF
Place the entire Query process in a Transaction block. If a error occurs, the transaction is rolled
back.
When the Query Engine accesses data
sources, it expects them to be within the same scope. But, in real world applications, sometimes
the source is local to the routine calling the Query Engine, therefore it is
out of scope (The String data driver for example). To resolve this one can do one of two things;
either declare the source (variable) as Private (or Public) or pass the
variable to the Query Engine. In this
section we will discuss how to pass variables to the tool.
In
order to keep the tool as flexible as possible, I decided (actually it was
suggested) that the parameters would be passed in a collection object. The collection consists in Name/Value pair
configuration; where the Item value is the value of the variable and the Key
the name of the variable. When the
collection is passed to the Query Engine, the tool then walks down the
collection and recreates the variables in it scope. This allows the SQL statement when executed
to have access to the values in the variables.
Note that in the SQL statement it is wise to prefix the variable name
with the “M.” prefix. This prevents the
SQL engine from confusing fields with variables.
Figure 34 demonstrates two
examples of using the parameter argument to gain access to local
variables. The first example shows how
to create the collection to hold the local variables, and how those variables
are referenced in the SQL statement. The
second example demonstrates how to use Query Engine to create a collection of
variables to be used in a SQL statement.
|
LOCAL
lcStart, lcEnd
lcStart = “F”
lcEnd = “H”
LOCAL loPara
AS COLLECTION
loPara = CREATEOBJECT("Collection")
loPara.ADD(lcStart,"lcStart")
loPara.ADD(lcEnd,"lcEnd")
DIMESION
alng[1]
ALANGUAGE(alng,2)
? QUERY([select * from array aLng where exp1>=m.lcStart and
exp1<=m.lcEnd into cursor qlang], loPara)
LIST
USE IN qlang
RELEASE
alng, loPara, lcStart,lcEnd
*-------------------------------------------------
* Use Query to
fill the Parameter collection
*--------------------------------------------------
PRIVATE
loPara
loPara = CREATEOBJECT("Collection")
ALANGUAGE(alng,2)
? QUERY([select * from string
("R","lcStart";"T","lcEnd")
{comma,semicolon} into collection value loPara])
? QUERY([select * from array aLng where exp1>=m.lcStart and
exp1<=m.lcEnd into cursor qlang], loPara)
LIST
|
Figure
36:
Passing Parameters
Error
messages are collected in a property on the Query object called
“ErrMessage”. As the errors occur, each
message is appended to the property with carriage-return/line-feed as
delimiter. At the end of the run, if
anything exists in this property, special cursor is generated called
“QueryErrors” that contain all the error messages generate from the last run.
In version 3.0, we now have control over
the actual clauses used in the SQL statements.
In version 3.0, we now have control over
the actual clauses used in the SQL statements.
CHapter 7
Conclusion
I would like
take opportunity to wish all who have discovered this application the best of
wishes.
If you have any problems or suggestions, please contact me at
GregReichert@GLRsoftware.com. I will include your name as a
contributor. Also, please report to the
email address any errors or bugs discovered while using the product.
Thank you,
Greg Reichert
GLR Software
http://www.GLRsoftware.com
Appendix 1
Known Problems
This is a list
of known problems with Query or in interacting with external application or
objects.
|
Date
|
Issue
|
|
1/19/2007
|
Problem with SQL statement where the field list contains
cursor reference from more then one source.
|
Appendix 2
Change History
|
Date
|
Version
|
Notes
|
|
??/??/2011
|
3.00.0000
|
Version 3.0 release
|
|
1/19/2007
|
2.00.0000
|
Version 2.0 release
|
|
10/03/2005
|
1.00.0000
|
Initial release
|
|
09/26/2005
|
1.00.0000
|
Beta test release
|
