DataFlex 2023 pre-release has been released for us all to test. There has been quite a bit of talk about the new features DataAccess is releasing to assist developers working closer with SQL. While all DataFlex has always had strong capabilities that allow us to achieve everything we need, these new additions are welcome in allowing us to developer features for our clients much faster.

The DataFlex 2023 pre-release is available for download here.

We have tested out the new Pre-Release on our internal application and we are excited about these features.

Migrating to DataFlex 2023

We are starting with our internal system to test its new features. Getting started with a new version is always straight forward with the in-place migration wizard.

As usual, everything went smooth. The only issue we faced was the new methods in GlobalFunctionsProcedures.pkg, UrlEncode and UrlDecode were added. This required updating the DFSecurity library to remove the function definitions there and update the relevant calling locations.

New SQL Changes

Sql Syntax highlighting in Strings

Let’s start with a basic SQL filter we use. This is part of an external emailing system that is managed by our DataFlex application. Basically, we want to fetch all users where the domain part matches the domain they manage.

Now with Syntax highlighting we get the following

This proves a lot more useful in larger queries that psan multiple lines

cSQLExecutor

There is a new cSQLExecutor package called cSQLExecutor.pkg which allows us to define a singleton object in a program which can be access via the global variable ghoSQLExecutor

We then define the object in a new package like so File -> New -> Other -> SQL Executor This creates a file like so

// Defines an object of cSQLExecutor class
// Can be be used as a singleton object in a program
// Use global variable ghoSQLExecutor to access the SQLExecutor instance
Use cSQLExecutor.pkg

Object oSQLExecutor is a cSQLExecutor
    Move Self to ghoSqlExecutor

    //Set psConnectionId to "SQLDATA"
End_Object

You can then set the psConnectionId to the one in your application.

This brings 3 new events, where the names are quite obvious their intentions.

• OnSqlError
• OnSqlPostExecute
• OnSqlPreExecute

The new methods include

• SqlColumnInfo
• SqlExecute
• SqlExecuteDirect
• SqlParameterInfo
• SqlPrepare
• SqlPrepareInfo
• SqlSetParamater

Calling SQL with named parameters.

Procedure DemoSQL
    Variant[][] aResults

    Send SQLPrepare of ghoSQLExecutor @SQL"
        SELECT *
        FROM timesheet
        JOIN employee ON timesheet.employeeid = employee.id
        WHERE employee.NAME = @employee_name AND DATE >= @from_date AND DATE <= @to_date"

    Send SQLSetParameter of ghoSQLExecutor "@employee_name" "Joseph Mullins"
    Send SQLSetParameter of ghoSQLExecutor "@from_date" "2022-10-01"
    Send SQLSetParameter of ghoSQLExecutor "@to_date" "2022-10-31"

    Get SqlExecute of ghoSQLExecutor to aResults

    If (not(Err)) Begin
        // Do Something
    End
End_Procedure

Give us results as we would expect, an array of results with all columns from the tables timesheets and employee

Built in Query Tester

The new benefits don’t stop there though. We can right click our SQL code and Open in Query Tester

We can then run and test our new SQL statement

This then gives us a new Struct Generator tool which we can then use to output the results of our query to. These all match native result types that the SQL returns.

Adding this to our code then gives us the ability to execute directly to those structs so our results are much easier to use like so

Procedure DemoSQL
    tTimesheet[] aTimesheets

    Send SQLPrepare of ghoSQLExecutor @SQL"
        SELECT *
        FROM timesheet
        JOIN employee ON timesheet.employeeid = employee.id
        WHERE employee.NAME = @employee_name AND DATE >= @from_date AND DATE <= @to_date"

    Send SQLSetParameter of ghoSQLExecutor "@employee_name" "Joseph Mullins"
    Send SQLSetParameter of ghoSQLExecutor "@from_date" "2022-10-01"
    Send SQLSetParameter of ghoSQLExecutor "@to_date" "2022-10-31"

    Get SqlExecute of ghoSQLExecutor to aTimesheets

    If (not(Err)) Begin
        // Do Something
    End
End_Procedure

This then allows us to loop over the array of structs and use the results with named values without having to guess the number of the column.

Seperating SQL to seperate files

If you write your SQL in seperate .sql files, which makes much more sense when code gets larger, calling them is straight forward with the new SqlExecuteDirect method. This also embeds your SQL into the program as Constants so you don’t need to include them when deploying.

Get SqlExecuteDirect of ghoSQLExecutor C_SQLEmployeeTimesheetsByDate to aTimesheets

WebApp Updates

Improved WebApp logging

Web App critical errors now log to the Windows Event Log. These are more verbose and include call stacks and other information where applicable. This is a large improvement for us as our Logstash / Kibana set up tightly integrates with the Windows Event Log using winlogbeats. This will be a welcome addition to our deployments

A new event after the client has received their info

A new event called OnReleaseProcess has been created. This allows you to have an event that is triggered after the request has sent all the client information they need. Any work done here will execute before the process is returned to the pool, but will not hold up the clients browser. This will provide easier methods of doing slower running tasks without impacting the client, such as, sending out an email after something has been triggered.

Desktop Changes

Flextron

Flextron is a new way of modernizing desktop apps. While our internal system doesn’t utilize a desktop app, Flextron will bring us web capabilities to the desktop apps we do maintain. This will mean we can start styling components with CSS and have more flexibility in how they look; meaning much more modern desktop applications.

This also provides a powerful migration path, giving code-reusability, when migrating Desktop apps to Mobile.

The new components in FlexTron include

cLocalWebControlHost For embedding web components, not views, inside a desktop app. This does not have Data Binding.

cDbLocalWebControlHost For embedding web components with data binding inside a desktop app.

cLocalWebAppHost For embedding a complete web application with views and all other functionality inside a desktop app.

CodeJock updates

CodeJock has been updated to 22.0 for all Windows components.

An exciting Development!

All in all, we are most excited about the SQL features and the powerful ways it can integrate with our applications. The native support and continued improvement of SQL handling gives us developers more powerful facilities to provide customers with the outcomes they need a lot quicker.