Class USqlStatement() Foundation

The USqlStatement() class allows the creation and execution of local SQL statements with the embedded SQL engine of Xbase++. For a general discussion of working with SQL statements, including those executed against external SQL DBMS, please see the SQLStatement() class documentation.

In fact, the embedded SQL engine is a specialized version of SQLite. The Xbase++ runtime automatically initializes a global SQLite in-memory database which is shared between threads and supports multi-threaded access. This database is the default data source for SQL statements being executed locally via the USqlStatement class.

Accessing workareas, arrays and arrays of objects in the FROM clause of a local SQL statement is managed via the concept of SQLite virtual tables. When using Universal SQL statements, that is, statements embedded directly into Xbase++ code as syntactical elements, these virtual tables are created automatically, and the statements are then executed using functionality provided by USqlStatement(). The following code snippet demonstrates this by using Universal SQL for performing queries against arrays and a workarea. The code can be run in the command window of the Xbase++ Workbench. The result sets will then be shown automatically in the query window.

SELECT * FROM (Directory()) 

SELECT field1 AS filename, field2 AS size FROM (Directory()) ORDER BY size DESC 

USE (GetEnv("userprofile")+"\documents\xbase++\source\samples\data\northwind\dbf\customers.dbf") ALIAS cust 
SELECT country,Count(*) AS cnt FROM cust GROUP BY country ORDER BY cnt DESC 

The usage of parametrized SQL statements with the embedded SQL engine is also fully supported. This involves using an instance of the USqlStatement class directly for creating and executing SQL statements. This is illustrated in the following example, which creates an in-memory table, adds sample data, and then performs a query for retrieving the data. For more details about the :execute() and :query() methods, see the SqlStatement() class.

#include "dac.ch" 

PROCEDURE main 
LOCAL oStmt 
LOCAL aData 

oStmt := USqlStatement():new():fromChar("CREATE TABLE customer( name CHAR(30), age INT )") 
oStmt:build():execute() 

oStmt := USqlStatement():new():fromChar("INSERT INTO customer( name, age ) VALUES ('hello',5)") 
oStmt:build():execute() 

oStmt := USqlStatement():new():fromChar("SELECT * FROM customer") 
oStmt:build():query(USQL_RESULT_ARRAY, @aData) 

? aData 
RETURN 

Accessing a workarea or array in the FROM clause of a SQL statement with the embedded SQL engine requires a virtual table to be created. This is done automatically when using Universal SQL statements. When using parametrized SQL statements, however, the virtual tables must be created explicitly and added to the embedded SQL engine. This is done with the methods :createVirtualTableFromWorkarea()and :createVirtualTableFromArray(), and is demonstrated in the following example.

Note that it is legal to join in-memory tables with virtual tables from a workarea or an array.

#include "dac.ch" 

PROCEDURE main 
LOCAL oStmt 
LOCAL aData 

USE (GetEnv("userprofile")+"\documents\xbase++\source\samples\data\northwind\dbf\customers.dbf") ALIAS cust 

oStmt := UsqlStatement():new() 
oStmt:createVirtualTableFromWorkarea( "cust" ) 

oStmt:fromChar( "SELECT country,Count(*) AS cnt FROM cust WHERE country=::country" ) 
oStmt:country := "Germany" 
oStmt:build():query(USQL_RESULT_OBJECTS, @aData) 
? Var2JSON( aData ) 
RETURN 

PROCEDURE DbeSys 
DbeLoad("foxdbe") 
DbeLoad("cdxdbe") 
DbeBuild("foxcdx","foxdbe","cdxdbe") 
RETURN 

Using a SQLite database on disk is also possible via the :attachDatabase()/:detachDatabase()class methods. After attaching a database to the embedded SQL engine, the on-disk database and the global in-memory database exist simultaneously. In the following example, the northwind SQLite database from the Xbase++ sample collection is attached and subsequently used for performing parametrized SQL queries.

#include "dac.ch" 

PROCEDURE main 
 LOCAL oStmt 
 LOCAL aData 

 ? UsqlStatement():attachDatabase( GetEnv("userprofile")+"\documents\xbase++\source\samples\data\northwind\northwind.sqlite3.db" ) 

 oStmt := UsqlStatement():new() 
 oStmt:fromChar( "SELECT country,Count(*) AS cnt FROM customers WHERE country=::country" ) 

 oStmt:country := "Germany" 
 oStmt:build():query(USQL_RESULT_OBJECTS, @aData) 
 ? Var2JSON( aData ) 

 oStmt:country := "USA" 
 oStmt:build():query(USQL_RESULT_OBJECTS, @aData) 
 ? Var2JSON( aData ) 
RETURN