Query - version 3.00

Developers Guide

Created by GLR Software at

Utility designed and developed by Gregory L. Reichert

Last Updated: October 3, 2018

GLR Software

Developers Guide

Information in this document, including URL and other Internet Web site references, is subject to change without notice. Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, e-mail address, logo, person, place or event is intended or should be inferred. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of GLR software.

The names of actual companies and products mentioned herein may be the trademarks of their respective owners.

© 2011. GLR software. All rights reserved.

6822 Kirby Arms Drive

Memphis, Tennessee 38115 USA

Phone 901.730.1166

Email:

Web Site:

Table of Contents

Query - version 3.00

Developers Guide

Overview

Purpose

Introduction

Overview

List of Features

Requirements

OS and Visual FoxPro

Using Query

General Usage

General Syntax

Batch Processing multiple statements

SELECT statements

INSERT statement

UPDATE statement

DELETE statement

MERGE statement

The OUTPUT clause

TRUNCATE statement

Assigning data types and sizes

Generating temporary cursors with ‘@’

Data Drivers

SQL Clause Extensions

Extending the operation of SQL clauses

Passing Parameters

Error Messages

Debugging and Testing

FUnit Test Cases

Properties and Methods

Header file – Query.H

Conclusion

Known Problems

Change History

Templates

Figures

Figure 1: A Query function SQL Select statement

Figure 2: Query function syntax

Figure 3: Query function parameter definition

Figure 4: Instantiate Query as Object

Figure 5: Multiple Query batch processing.

Figure 6: Query function SQL SELECT syntax

Figure 7: Standard SQL Select to cursor

Figure 8: Select to Collection object

Figure 9: Filter and Sorting a collection

Figure 10: Filling an Array from a delimited string.

Figure 11: Browsing and editing an array

Figure 12: Limits: No literal strings in non-SELECT statements

Figure 13: INSERT statement syntax

Figure 14: UPDATE statement syntax

Figure 15: DELETE statement syntax

Figure 16: MERGE command

Figure 17: Sample MERGE statement using Cursors

Figure 18: MERGE statement using a Collection and a String to update the Collection

Figure 19: TRUNCATE statement

Figure 20: Defining data type and size to columns

Figure 21: Example of QUERY returning error message otherwise empty.

Figure 22: RETURN types

Figure 23: Example of reading a value from INI file.

Figure 24: Using the ‘@’ substitution.

Figure 25: Data Drivers example

Figure 26: Table data driver

Figure 27: SQL data driver

Figure 28: Example of SQL data driver usage

Figure 29: Collection data driver

Figure 30: Filling, then filtering and sorting a collection

Figure 31: Naming the column in Object Collection

Figure 32: Array data driver

Figure 33: String data driver

Figure 34: Normal delimited string

Figure 35: Delimiter character token words

Figure 36: XML data driver

Figure 37: CVS data driver

Figure 38: FILE data driver

Figure 39: Example of getting file names with keyword in content

Figure 40: Listbox syntax

Figure 41: PEM syntax

Figure 42: VOID destination syntax

Figure 43: Querying QUERY driver names

Figure 44: Example of querying the Drivers list

Figure 45: Data Driver class definition header

Figure 46: Setting an alternate Drivers path

Figure 47: Template.prg

Figure 48: CASE statement syntax

Figure 49: PIVOT syntax

Figure 50: Example of PIVOT statement

Figure 51: Example of the DataSession statement

Figure 52: Example of the PAGE statement

Figure 53: Example of the REGEXP expression

Figure 55: Example of the SET statements

Figure 54: Passing Parameters

Figure 55: example template

Figure 56: table template

Query 3.0 - Users Guide

Chapter 1

Overview

Purpose

The purpose of this manual is to guide the developer through using the Query Engine.

Introduction

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.

Overview

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.
  • Tables
  • Cursors
  • Collection of values
  • Collection of objects (Created from SCATTER command)
  • Arrays (one and two dimensional)
  • CSV style delimited string variables
  • Literal strings as delimited data source
  • XML formatted data in variables
  • XML formatted data in a files
  • SQL Server via Connection String
  • And over 40 more.
  • Include various standard SQL clauses to form the output list.
  • TOP n [PERCENT]
  • DISTINCT
  • WHERE
  • ORDER BY
  • GROUP BY … HAVING
  • 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
  • Now with more than 50 data drivers
  • CLAUSE Drivers (Clauses\qc*.prg) to refine and expanded actions on the SQL Clauses themselves.
  • SET Drivers to allow for SET Statements in the Query statement.
  • Multiple-line Query Statements
  • More complete FUnit test cases and QTestCase.prg to run each.
  • Implicit INTO SCREEN BROWSE if no INTO clause is provide to Select statement.
  • Correction of many small issues in both the Query engine and the Drivers.
  • Intelligence ‘QD’ to generate Data Driver Template for new Drivers.
  • Regular Expression as an condition operator.
  • Automatically corrects Memo style fields in cursors.
  • New third parameter toErrMsg; a collection of error messages.
  • SQL version of<fld> LIKE <pat> [CASE] expression - convert VFP wildcards to SQL, and allow case insensitivity.
  • 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 or enhanced Drivers and Clauses
  • SQL CASE WHEN statement
  • SQL MERGE command
  • PIVOT clause to perform cross-tabulation on result data set.
  • Defaults using vfpXTab.prg
  • Optional uses of Fastxtab.prg
  • Optional uses of Matxtab.prg
  • PAGE <page#>,<pagelen> clause to return subset of the resulting query.
  • TIMEOUT clause to prevent long running queries.
  • DEBUG / NODEBUG clause to control the debug more on individual SQL statements.
  • ENGINEBEHAIVOR ## clause to control the behavior of individual query statements.
  • DATASESSION clause to insure the query is ran in the right form’s data session.
  • TRANSACTION clause to enable and disable Transaction processing.
  • If an error occurs, the transaction is rolled back, otherwise committed.
  • INDEX clause to generate temporary index on the data source cursor. Included the additional clause PERSIST and the generated clause is not remove at the end of the query.
  • EXCLUSIVE clause to open tables in exclusive mode.
  • CLOSE clause to close data source(s) after completing the Query.
  • RETURN clause defines the type of return values returned by Query(). There are different types of Query() return values of Number, Duration, Alias, Error and Value.
  • ARRAY, COLLECTION, and LISTBOX drivers respect TRIM clause to trim all strings after loading the data.
  • SHOW clause displays a progress bar during query run.
  • 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.
  • Multiple file skeletons are now allowed in the FILE driver (semi-colon delimited).
  • Ig. “PRGS\*.prg;Text\*.txt”
  • VOID data driver, when the resulting output is not required.
  • **
  • CLAUSE and SETS driver return a list of available Query Clauses and Query Set statements.
  • DBC Driver to query parts of database container.
  • – FIELD
  • Fileuser
  • – indextag
  • PROJECT driver to manage the VFP Project.
  • – stack
  • – task
  • – vfpset

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.

OS and Visual FoxPro

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, not required if driver not used:

  • vfpCompression.fllused by Zip.prg
  • vfpConnection.fllused by FTP.prg
  • filer.dllused by File.prg
  • efoxsql.fllused by Query.prg

Runtime:

  • msvcr71.dll
  • msvcr.dll

CHapter 4

Using Query

General Usage

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, delimited strings and dozen more. Both addition data drivers can be added by dropping PRG based class library into the Data_Drivers folder. These class library are based off a class named qBase class found in the Query.prg file. We will get into more details on their structures and predefined methods in the Data Drivers chapter.

General Syntax

The following is the basic syntax used to call the Query engine. The engine is compiled to a APP file. General the Query.app and support files (FLL and DLL) are all that is required for you project. Be sure to add the Query.app to the Code->Applications section of your Project. If you forget to do this, the Project Manager will attempt to add the Query.prg and ALL the drivers.

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 Parametersfor 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 INQueryErrors
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.

Batch Processing multiple statements

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.

SELECT statements

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]
[PAGE #,#]
[SHOW]
[DATASESSION #] [ENGINEBEHAVIOR ##] [EXCLUSIVE] [CLOSE [ERASE]] [TRANSACTION ON | OFF]
[ERRORLOG <filename> | OFF]
[RETURNCOUNT | DURATION | VALUE | ALIAS | ERROR]
[[WITH] INDEX [TAG] fieldlist> [ASC | DESC] [PERSIST]]
[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] <SELECTCommand>]
[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