New Embedded SQL API

DataFlex 2023 comes with 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. This means that your results can now go directly into structs, multidimensional arrays and variants with the proper type conversions being 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 by adding 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 within only 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 and smart 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 this is exposed in 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

An important part of SQL is the query language. To improve the SQLNess of the language, we have incorporated SQL statements into our language. This goes for partial statements as well as full SQL statements. Currently the places where SQL statements are allowed (SQL Filters and Embedded SQL) use regular strings. To identify a SQL query string, the @SQL prefix was added and multi-line strings are supported now.

Example:

 

Using the cSQLExecutor class / object

Setting up the database connection

The database connection to the SQL database can be setup 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"

pbIsConnected can be checked to test if a connection is initialized.

 

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 the Create New dialog in the DataFlex Studio: File Menu > New > Other > SQL Executor.

This will create 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 SQLExecutor object throughout the program.

 

Use in WebApps

If you wish to add a cSQLExecutor object in WebApp.src, be sure to make a child object of the cWebApp object. If you add a cSQLExecutor object before the cWebApp object, it will cause the WebApp to behave like a Windows application, rather than a Web Application.

 

Events

The cSQLExecutor class has the following callback procedures:

1. Procedure OnSQLError String sSQLState String sSQLMessage

2. Procedure OnSQLPreExecute String sSQLQuery

3. Procedure OnSQLPostExecute String sSQLQuery

 

OnSQLError will be triggered if a ODBC error occurs. By default a DataFlex error will be generated, implementing this event without forward send will cancel this error.

• sSQLState holds the ODBC error number

• sSQLMessage holds ODBC error description  

 

OnSQLPreExecute will be triggered before the beginning of an Execute

• sSQLQuery holds the query string

 

OnSQLPostExecute will be triggered after an Execute is finished.

• sSQLQuery holds the query string

 

Execution of queries

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

1. Procedure SQLPrepare String sSQLQuery        

2. Function SQLExecute Returns Variant[]

3. Procedure SQLExecute

4. Function SQLExecDirect String sSQLQuery Returns Variant[]       

5. Procedure SQLExecDirect String sSQLQuery

 

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

 

Direct Execution of Queries

Call SQLExecDirect to execute a SQL query directly. SQLExecuteDirect can be used in two ways, with or without a result set.

Example of SQLExecDirect 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 of SQLExecDirect 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 will prepare the query without returning information.

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 in two ways, with or without a result set.

Example of 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 of 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

The result sets of a query can be fetched into the following types:

String

Variant

Struct

tWebRow

tDataSourceRow

The destination array for String and Variant should be defined as a two-dimensional array and the array for Structs, tWebRow and tDataSourceRow should be defined as 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 result in a two dimensional array of [100][3] i.e. 100 rows with 3 columns of type string.

 

Variant array example:

Variant[][] 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 result in a two dimensional array of [100][4] i.e. 100 rows with 4 columns. Note that in this case the column Customer_Number will be of type Integer and the Name, City and Zip column are of type 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 result in a one dimensional array of [100] i.e. 100 rows of struct tResult holding the data.  

 

The cSQLExecutor class also embeds the possibility of fetching result set data directly into tDataSource and tWebRow to make the use of grids very convenient. In combination with the column info, one can make the grids dynamic with a few lines of code.

tDataSoureRow 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 result in a one dimensional array of [100] i.e. 100 rows of struct tDataSourceRow holding the data.

 

tDataSoureRow 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 oCJGridColumn1 is a cCJGridColumn

            Set piWidth to 200

            Set psCaption to "City"

        End_Object

                 

        Object oCJGridColumn1 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 grids i.e. 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 result in a one dimensional array of [100] i.e. 100 rows of struct tWebRow holding the data.

 

Multiple Result Sets

In case of multiple result sets, the destination array for String and Variant should be defined as a three-dimensional array and the array for Structs, tWebRow and tDataSourceRow should be defined as 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

This query will result in a three dimensional array of [2][n][n]i.e. 2 result sets with 1 result set i.e. [0][0][n] of 4905 rows of Strings holding the data and a second result set i.e. [1][0][n] of 10 rows.

The array has the following format, [RESULT SET][ROW][DATA].

 

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 the grids i.e. fill, lets take two grids for example

    Send InitializeData of oCJGrid1 aDataSourceRows[0] // result set 1

    Send InitializeData of oCJGrid2 aDataSourceRows[1] // result set 2

End

This query will result in a two dimensional array of [2][n] i.e. 2 result sets with 1 result set i.e. [0][n] of 4905 rows of tDataSourceRow holding the data and a second result set i.e. [1][n] of 10 rows.

The array has the following format, [RESULT SET][ROW].

 

Parameterized Queries

To use named parameters in a query, parameter name should have the $ sign as prefix and be enclosed in curly brackets i.e. ${param_name}. For now, SQLExecDirect cannot handle named parameter queries! The named parameter query should always be prepared first. After the prepare, the parameters can bet set and the parameter information can be retrieved.

The following procedure and function are available 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

 

SQLGetParameterInfo example:

tParamInfo[] 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 a array of tSQLColumnInfo structs. If you want to put the results into a grid the column information for example can be used to initialize your grid columns. Always call SQLColumnInfo after a 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 a aColumnsInfo array with 3 elements holding the information for the columns Name, City and Zip.

  

Query Tester

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

Read more about the Query Tester.

 

Include_Text

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

 

See Also

What's New in DataFlex 2023