Skip to content

Class: cSQLExecutor

Properties | Events | Methods | Index of Classes

Provides the API for executing SQL Statements from DataFlex

Hierarchy

cObject > cBaseSQLExecutor > cSQLExecutor

Show full hierarchy and direct subclasses

Library: Common Class Library

Package: cSQLExecutor.pkg

Description

The cSQLExecutor class 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.

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

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

The engine of the cSQLExecutor is inside the runtime and 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 works with the ODBC based drivers (MS SQL, DB2 and ODBC).

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

When a cSQLExecutor object is placed above a cWebApp object, as in the example below, error "This program is a webapp program and cannot be run standalone. This program must be run under DataFlex 23.0 WebApp Application Server" is displayed. The error occurs because cSQLExecutor uses ComponentType metadata tag and the file is seen as a Windows project.

Use cSQLExecutor.pkg
Object oSQLExecutor is a cSQLExecutor
    Move Self to ghoSQLExecutor
End_Object

Object oWebApp is a cWebApp

Include a PKG file with the desired code instead:

Use MycSQLExecutorObject.pkg

Object oWebApp is a cWebApp

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

This query will result in a two dimensional array of [100][3] i.e. 100 rows with 3 columns of type string.

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

Variant Array Example

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.

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

Struct Array Example

This query will result in a one dimensional array of [100] i.e. 100 rows of struct tResult holding the data. 

//Define the struct; struct member names match query column names
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

Notes - The name of struct members must match the column name in the query, otherwise, unmatching struct members will be empty - Use the Name metadata tag to avoid mismatch or column name issues (e.g. unsuported characters in the name) and be able to use hungarian notation for struct member names. For example, when connecting to an SQL table whose columns contain spaces or $ in the name, the SQL driver will automatically replace those with an underscore ("_") character. Structs using those columns will need to have the Name metadata tag specified.

// this sctruct includes the Name metadata tag for all columns and applies the data type prefix to the member names

Struct tCustomerFunkyCols
    { Name="Customer_Number" }
    Integer iCustomer_Number
    { Name="Name" }
    String  sName
    { Name="Phone Number" }
    String  sPhone_Number
    { Name="Purcha$es" }
    Number  nPurcha_es
End_Struct
  • In the Studio, Query Tester may be used to generate proper formed structs that include the name metadata tag for each column and the type prefix in the name of each struct member

Use with Grids

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

This query will result in a one dimensional array of [100] i.e. 100 rows of struct tDataSourceRow holding the data.

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

tDataSoureRow Array Example with Static Grid

// 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

This query will result in a one dimensional array of [100] i.e. 100 rows of struct tWebRow holding the data. 

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

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:

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].

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

Multiple Result Set tDataSourceRow Array Example:

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].

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

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}. 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

Positional Parameters

SQLExecutor uses named parameters by default, but it is also possible to use positional parameters (the ? in a query)

Positional parameters must be passed as '?1' '?2' etc.

SELECT * from Customer where State = ? And City = ?

Sample

String sSQLQuery
String [][] asTheRows 

Move @SQL"
    SELECT * from Customer where State = ? And City = ?        
    "           to sSQLQuery

Send SQLPrepare of ghoSQLExecutor sSQLQuery 

Send SQLSetParameter of ghoSQLExecutor '?1' 'FL'
Send SQLSetParameter of ghoSQLExecutor '?2' 'Miami'

Get SQLExecute of ghoSQLExecutor to asTheRows

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

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

This query will result in a aColumnsInfo array with 3 elements holding the information for the columns Name, City and Zip. 

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

Multiple Result Set SQLColumnInfo Example

A SQLExecutor query can return multiple result sets. To retrieve the column info for all result sets the tSQLColumnInfo must be defined as a 2 dimensional array.

Procedure TestSQLMultiResults
    String[][][] aResults
    tSQLColumnInfo[][] aSQLColumnsInfo

    String sSQLQuery

    Move @SQL"""
    SELECT * FROM Vendor   
    SELECT * FROM Customer 
    """ to sSQLQuery

    Get SQLExecDirect of ghoSqlExecutor sSQLQuery to aResults
    Get SQLColumnInfo of ghoSQLExecutor to aSQLColumnsInfo        

    Showln (SFormat("Query returned %1 result sets",(SizeOfArray(aResults)) ))
    Showln (SFormat("SQLColumnInfo returned %1 result sets",(SizeOfArray(aSQLColumnsInfo)) ))

    Integer iIx
    For iIx from 0 to ((SizeOfArray(aSQLColumnsInfo)) - 1)
        Showln (SFormat("Result set %1 has %2 columns",iIx,(SizeOfArray(aSQLColumnsInfo[iIx])) ))
    Loop
End_Procedure