New Embedded SQL API

DataFlex 2023 introduces a new way to work directly with SQL statements. A new engine for executing statements is built into the runtime that puts result sets directly into native DataFlex types. Results can now go directly into structs, multidimensional arrays and variants with proper type conversions done automatically. Parameterized queries now support named parameters and only a single class/object is needed for executing queries. Studio support for working with SQL statements is improved with syntax highlighting, code sense and a query test tool for SQL statements.

SQL Executor

The cSQLExecutor package provides the API for executing SQL statements from DataFlex. It can be used as a global singleton object for executing queries in a single line of code, or with an instance per connection or per prepared statement. Result sets are immediately fetched and made available in native types (structs, arrays, variants) using a flexible API. Prepared statements with named parameters are supported. There are APIs for querying the result set structure and the parameters to build dynamic applications.

To get full Studio support use the @SQL string or include queries from separate files using Include_Text.

The engine of the cSQLExecutor is inside the runtime and is exposed via the abstract cBaseSQLExecutor class. The runtime works directly with ODBC and relies on the connectivity kits to manage the connection. This means that the cSQLExecutor will work with the ODBC-based drivers (MS SQL, DB2 and ODBC).

SQL in the DataFlex language

SQL is now integrated into the DataFlex language. To identify a SQL string literal use the @SQL prefix. Multi-line strings are supported.

Example:

Get SQLExecDirect of ghoSQLExecutor @SQL"SELECT
Name,
City,
Zip
FROM Customer" to aResults

Using the cSQLExecutor class / object

Setting up the database connection

The database connection to the SQL database can be set up by using a Managed Connection. The psConnectionID property of cSQLExecutor should be set to one of the Connection IDs defined in the SQL Connection Manager of your workspace.

Example:

Set psConnectionId of ghoSQLExecutor to "MyConnectionID"

You can check pbIsConnected to test if a connection is initialized: see pbIsConnected.

Singleton

The simplest way to use the cSQLExecutor is to create a singleton instance within your project. This can be done using the template available in DataFlex Studio: File > New > Other > SQL Executor. This creates a .pkg file that can be included in a program.

Example:

Use cSQLExecutor.pkg
Object oSQLExecutor is a cSQLExecutor
    Move Self to ghoSQLExecutor
    //Set psConnectionId to "SQLDATA"
End_Object

ghoSQLExecutor is a predefined global variable that can be used to access the cSQLExecutor object throughout the program.

Use in WebApps

If you add a cSQLExecutor object in WebApp.src, make it a child object of the cWebApp object. Adding a cSQLExecutor before the cWebApp object can cause the WebApp to behave like a Windows application instead of a Web Application.

Events

The cSQLExecutor class has the following callback procedures:

Procedure OnSQLError String sSQLState String sSQLMessage
Triggered when an ODBC error occurs. By default a DataFlex error will be generated; implementing this event without Forward Send will cancel the default error.
sSQLState holds the ODBC error number.
sSQLMessage holds the ODBC error description.
Procedure OnSQLPreExecute String sSQLQuery
Triggered before the beginning of an execute.
sSQLQuery holds the query string.
Procedure OnSQLPostExecute String sSQLQuery
Triggered after an execute is finished.
sSQLQuery holds the query string.

Execution of queries

The cSQLExecutor class provides the following functions and procedures to prepare and execute a SQL query:

Procedure SQLPrepare String sSQLQuery
Function SQLExecute Returns Variant[]
Procedure SQLExecute
Function SQLExecDirect String sSQLQuery Returns Variant[]
Procedure SQLExecDirect String sSQLQuery

If an ODBC error occurs, OnSQLError is called and the global Err flag will be set.

Direct Execution of Queries

Use SQLExecDirect to execute a SQL query directly. SQLExecDirect can be used with or without a result set.

Example (with result set):

String[][] aResults
// Fetch results into aResults
Get SQLExecDirect of ghoSQLExecutor @SQL"SELECT
Name,
City,
Zip
FROM Customer" to aResults
// Test for errors
If (not(Err)) Begin
    // Process results here..
End

Example (without result set):

// This is normally used for queries without a result set
Integer iAffectedRows
Send SQLExecDirect of ghoSQLExecutor @SQL"UPDATE Customer
SET NAME = 'Thomas Hardy', City = 'London'
WHERE Customer_Number = 1"
// Test for errors
If (not(Err)) Begin
    Get piSQLAffectedRows of ghoSQLExecutor to iAffectedRows
End

Prepared Execution of Queries

SQLPrepare prepares the query without returning data.

Example:

// Prepare the query
Send SQLPrepare of ghoSQLExecutor @SQL"SELECT
Name,
City,
Zip
FROM Customer"
// Test for errors
If (not(Err)) Begin
    // Execute the query here..
End

Call SQLExecute to execute a prepared SQL query. SQLExecute can be used with or without a result set.

Example (SQLExecute with result set):

String[][] aResults
// Prepare the query
Send SQLPrepare of ghoSQLExecutor @SQL"SELECT
Name,
City,
Zip
FROM Customer"
// Test for errors
If (not(Err)) Begin
    // Fetch the results into aResults
    Get SQLExecute of ghoSQLExecutor to aResults
End

Example (SQLExecute without result set):

// This is normally used for queries without a result set
Send SQLPrepare of ghoSQLExecutor @SQL"UPDATE Customer
SET NAME = 'Thomas Hardy', City = 'London'
WHERE Customer_Number = 1"
// Test for errors
If (not(Err)) Begin
    // Execute the query
    Send SQLExecute of ghoSQLExecutor
End

Result Sets

Result sets can be fetched into the following types:

String arrays
Variant arrays
Struct arrays
tWebRow arrays
tDataSourceRow arrays
The destination array for String and Variant should be defined as a two-dimensional array.
The array for Struct, tWebRow and tDataSourceRow should be defined as a one-dimensional array.

String array example:

String[][] aResults
// Fetch results into aResults
Get SQLExecDirect of ghoSQLExecutor @SQL"SELECT
Name,
City,
Zip
FROM Customer" to aResults
// Test for errors
If (not(Err)) Begin
    // Process results here..
End

This query will typically result in a two-dimensional array of [100][3] (100 rows with 3 columns of type string).

Variant array example:

Variant[][] aResults
// Fetch results into aResults
Get SQLExecDirect of ghoSQLExecutor @SQL"SELECT
Customer_Number,
Name,
City,
Zip
FROM Customer" to aResults
// Test for errors
If (not(Err)) Begin
    // Process results here..
End

This query will typically result in a two-dimensional array of [100][4]. Note that Customer_Number may be of type Integer while Name, City, and Zip are String.

Struct array example:

// Define the struct
Struct tResult
    String Name
    String City
    String Zip
End_Struct

tResult[] aResults
// Fetch results into aResults
Get SQLExecDirect of ghoSQLExecutor @SQL"SELECT
Name,
City,
Zip
FROM Customer" to aResults
// Test for errors
If (not(Err)) Begin
    // Process results here..
End

This query will typically result in a one-dimensional array of [100] (100 rows of tResult structs holding the data).

The cSQLExecutor class also supports fetching result set data directly into tDataSourceRow and tWebRow to make using grids convenient. Combined with column info, grids can be initialized dynamically.

tDataSourceRow array example:

tDataSourceRow[] aDataSourceRows
// Fetch results into aDataSourceRows
Get SQLExecDirect of ghoSQLExecutor @SQL"SELECT
Name,
City,
Zip
FROM Customer" to aDataSourceRows
// Test for errors
If (not(Err)) Begin
    // Process results here..
End

This query will typically result in a one-dimensional array of [100] (100 rows of tDataSourceRow structs).

tDataSourceRow array with static grid example:

// the grid object
Object oCJGrid1 is a cCJGrid
    Set Size to 192 517
    Set Location to 6 10
    Object oCJGridColumn1 is a cCJGridColumn
        Set piWidth to 300
        Set psCaption to "Name"
    End_Object
    Object oCJGridColumn2 is a cCJGridColumn
        Set piWidth to 200
        Set psCaption to "City"
    End_Object
    Object oCJGridColumn3 is a cCJGridColumn
        Set piWidth to 60
        Set psCaption to "Zip"
    End_Object
End_Object

// further in the code
tDataSourceRow[] aDataSourceRows
// Fetch results into aDataSourceRows
Get SQLExecDirect of ghoSQLExecutor @SQL"SELECT
Name,
City,
Zip
FROM Customer" to aDataSourceRows
// Test for errors
If (not(Err)) Begin
    // Initialize the grid (fill)
    Send InitializeData of oCJGrid1 aDataSourceRows
End

tWebRow array example:

tWebRow[] aWebRows
// Fetch results into aWebRows
Get SQLExecDirect of ghoSQLExecutor @SQL"SELECT
Name,
City,
Zip
FROM Customer" to aWebRows
// Test for errors
If (not(Err)) Begin
    // Process results here..
End

This query will typically result in a one-dimensional array of [100] (100 rows of tWebRow structs).

Multiple Result Sets

For multiple result sets:

The destination array for String and Variant should be a three-dimensional array.
The array for Struct, tWebRow and tDataSourceRow should be a two-dimensional array.

Multiple result set string array example:

String[][][] aStrings
// Fetch results into aStrings
Get SQLExecDirect of ghoSQLExecutor @SQL"
SELECT * FROM OrderDetail
SELECT TOP 10 NAME, City, Zip FROM Customer" to aStrings
// Test for errors
If (not(Err)) Begin
    // Process results here..
End

The array format is [RESULT SET][ROW][DATA]. For example [0][0][n] refers to the row n of result set 0.

Multiple result set tDataSourceRow array example:

tDataSourceRow[][] aDataSourceRows
// Fetch results into aDataSourceRows
Get SQLExecDirect of ghoSQLExecutor @SQL"
SELECT * FROM OrderDetail
SELECT TOP 10 NAME, City, Zip FROM Customer" to aDataSourceRows
// Test for errors
If (not(Err)) Begin
    // Initialize two grids from two result sets
    Send InitializeData of oCJGrid1 aDataSourceRows[0] // result set 1
    Send InitializeData of oCJGrid2 aDataSourceRows[1] // result set 2
End

The array format is [RESULT SET][ROW]. For example aDataSourceRows[0] is the set of rows from the first result set.

Parameterized Queries

To use named parameters in a query, parameter names should have the $ sign as prefix and be enclosed in curly brackets, e.g. ${param_name}. Note: SQLExecDirect cannot handle named-parameter queries — named-parameter queries must be prepared first with SQLPrepare. After prepare, parameters can be set and parameter information retrieved.

Available APIs for parameterized queries:

Procedure SQLSetParameter String sParamName Variant Value
Function SQLParameterInfo Returns tSQLParamInfo[]

The tSQLParamInfo struct is defined in cSQLExecutor.pkg:

Struct tSQLParamInfo
    String sName
    String sValue
End_Struct

SQLSetParameter example:

String[][] aStrings
// Prepare the query
Send SQLPrepare of ghoSQLExecutor @SQL"SELECT
Name,
City,
Zip FROM Customer
WHERE City = ${City}"
// Test for errors
If (not(Err)) Begin
    // Set the parameter value
    Send SQLSetParameter of ghoSQLExecutor "City" "Miami"
    // Get the result set
    Get SQLExecute of ghoSQLExecutor to aStrings
    // Process results here..
End

SQLParameterInfo example:

tSQLParamInfo[] aParamsInfo
// Prepare the query
Send SQLPrepare of ghoSQLExecutor @SQL"SELECT
Name,
City,
Zip FROM Customer
WHERE City = ${City}"
// Test for errors
If (not(Err)) Begin
    // Set the parameter value
    Send SQLSetParameter of ghoSQLExecutor "City" "Miami"
    // Get the parameters info
    Get SQLParameterInfo of ghoSQLExecutor to aParamsInfo
    // Process results here..
End

Querying Result Set Structure

SQLColumnInfo returns the column information for the result set into an array of tSQLColumnInfo structs. If you want to populate a grid dynamically, use the column information to initialize your grid columns. Call SQLColumnInfo after SQLExecute or SQLExecDirect.

The tSQLColumnInfo struct is defined in cSQLExecutor.pkg:

Struct tSQLColumnInfo
    String  sName      // column name (returned)
    Integer iType      // SQL data type of column
    Integer iSize      // Data size of column in table
    Integer iDigits    // Number of digits after the decimal separator
    Boolean bNullable  // Whether column is nullable
End_Struct

SQLColumnInfo example:

String[][] aResults
tSQLColumnInfo[] aSQLColumnsInfo
// Fetch results into aResults
Get SQLExecDirect of ghoSQLExecutor @SQL"SELECT
Name,
City,
Zip
FROM Customer" to aResults
// Test for errors
If (not(Err)) Begin
    // Get the columns info
    Get SQLColumnInfo of ghoSQLExecutor to aSQLColumnsInfo
    // Process results here..
End

This query will result in aSQLColumnsInfo containing 3 elements with information for the Name, City, and Zip columns.

Query Tester

A Query Tester has been added to Studio. It helps create, test and execute SQL queries and includes a Struct Generator.

Include_Text

Include_Text allows .SQL and other text files to be included as constants with a single line of DataFlex code.

New Embedded SQL API

SQL Executor

SQL in the DataFlex language

Using the cSQLExecutor class / object

Setting up the database connection

Singleton

Use in WebApps

Events

Execution of queries

Direct Execution of Queries

Prepared Execution of Queries

Result Sets

Multiple Result Sets

Parameterized Queries

Querying Result Set Structure

Query Tester

Include_Text

See Also