Timecodes
- 00:00 Introduction
- 02:01 SQL Enhancements
- 08:57 Demonstration
- 23:30 Studio Query Tester
- 24:28 Demonstration
- 37:20 Questions
- 47:02 Upcoming Events & Resources
Questions & Answers
QUESTION 1
Will DF 2023 also support any SQL Data Definition Language (DDL) commands (e.g., DDL commands are SQL commands used to create and/or alter SQL database table structures) in addition to SQL Data Manipulation Language (DML) commands?
- Function and Procedure to execute ESQL are available – while function is expected to return result sets, procedures simply execute other SQL commands, like update, insert, etc.
- CodeSense only supports DML
- The focus of the new API is on DML having ESQL as an additional way to perform operations on tables part of a DataFlex application
- DDL may be used with cSQLExecutor but it is not recommended to be used on tables that are used in a DataFlex application since using ESQL will not update the DataFlex side (e.g. INT, CCH, DD, FD) of the application
QUESTION 2
How does the global ghoSQLExecutor determine what connection string to use, e.g. an application connects to tables in two different SQL databases via two different SQL defined in your connection INI file, which connection does it use? Should you create a separate global cSQLExecutor object for each connection in this scenario, or switch back and forth between connections with the single ghoSQLExecutor object?
- Connection is set using the cSQLExecutor psConnectionId property
- Multiple cSQLExecutor objects may be created
QUESTION 3
For a query or stored procedure that returns multiple result sets that have different columns in each result set, will it work to get results to a struct composed of struct arrays each of which matches the data structure for each result set, or do you need to use a 3D string/variant array?
- Result sets can be store in String[][] - Variant[][] - Struct[] - tWebRow[] - tDataSourceRow[]
- For multiple result sets, the return data will be stored in a separate dimension of the array
- Result Sets – Rows – Columns – i.e. a three-dimensional array for String[][][] or Variant[][][]
- Result Sets – Rows of Structure – i.e. a bidimensional array for the Struct types
- To use a struct array, all commands need to return the same structure
- If result sets return different data, use String or Variant arrays or split ESQL into separate SQL executions for each result set to be stored in their appropriate Struct array
The variant will convert to DF type, right?
- Yes, based on the SQL type returned by ODBC, a variant array will have the return data typed to the appropriate DataFlex types
QUESTION 4
How are SQL errors handled in this new class? Is the global Err set when an SQL error occurs?
- Yes, the global Err flag will reflect the result of the operation: False if operation was successful; True if the operation failed
- cSQLExecutor includes an event where you may get more information about the error: Procedure OnSQLError String sODBCError String sSQLErrMessage
- OnSQLError will be triggered whenever an ODBC error occurs and a DataFlex error will be generated; implementing this event without forward send will cancel the error
QUESTION 5
Will using column names (as in the names specified in the prepared query) instead of column number be available at some point?
- That is already done when you have a struct as the array used to store results sets
- Structs for the columns in a query may be generated by the Struct Generator part of the Studio Query Tester
- When a String or Variant array is used to store result sets, columns returned by the ESQL statement will be identified in the array by their position (index)
- Remember that data is returned into arrays in the form
- Result Sets – Rows – Columns – for String[][][] or Variant[][][]
- Result Sets – Rows of Structure – for Struct[][]
- Information about returned columns can be obtained by calling the function SQLColumnInfo, which returns tSQLColumnInfo[]
Struct tSQLColumnInfo
String sName
Integer iType
Integer iSize
Integer iDigits
Boolean bNullable
End_Struct
- Table column names can be obtained by reading the attribute DF_FIELD_SQL_COLUMN_NAME
QUESTION 6
How many Beta releases are you intending?
- There are a few minor updates in Beta 2
Resources
REFERENCE LIBRARY
