Using Embedded SQL (ESQL) in DataFlex

Most developers today are either already using a SQL database or considering moving to SQL in the near future. Once you have converted to SQL you can take advantage of having the SQL Server perform server side processing of your data and use built-in SQL functionality in DataFlex like psSQLFilters for constraining your data.

This also opens up a new world of using SQL in more interesting ways by directly embedding and calling SQL scripts in your DataFlex code, which is what I will demonstrate in this blog.

MS SQL is probably the most common SQL database used today, but if want your application to be able to run on other SQL databases you need to be aware that not all SQL is the same across all databases. The SQL language used by MS SQL is called Transact SQL or T-SQL. Oracle, MySQL and PostGres use different languages and may not be compatible with T-SQL.

Basic structure for using ESQL in your application

All use of ESQL in a DataFlex application follows the same basic flow:

• Connect
• Repeat
  • Open a statement handle
  • Manipulate the statement
  • Close a statement handle
• Until done
• Disconnect

Easy techniques to build your ESQL

I’m going to show a couple of different techniques I have used. Some of these I have learned from other developers and some I have adopted myself over the years. Your ESQL needs to be in a string. You can do this the simple way as a string as below:

Move “ SELECT Customer.Customer_Number, Customer.[Name],Customer.Address, Customer.City, Customer.State, Customer.Zip FROM Customer WHERE (Customer.State = ’FL’) “ to sSQL

As you can see, even this simple Select statement can be quite long. So one of my techniques is to put my ESQL into a constant:

Define C_FindCustomersInFL for “SELECT Customer.Customer_Number, Customer.[Name],Customer.Address, Customer.City, Customer.State, Customer.Zip FROM Customer WHERE (Customer.State = ’FL’)”

TIP: I like using Define because it makes my code a lot cleaner and easier to read.

While the above ESQL is really nice if you just want to find your customers in Florida, it’s more likely you will want to find them in more than one state. So it’s simple enough to replace the “FL” in the above SQL with “%1” and then use SFormat to replace the parameter. So I’m going to change the name of the constant to C_FindCustomer to make it more general:

Move C_FindCustomer to sSQL
Move (SFormat(sSQL,Customer.State)) to sSQL

And now a serious note about using the technique above to construct your ESQL: This technique is considered safe when using values from your validated database; it is NOT considered safe using values directly entered by a user due to the possibility of SQL Injection. Please read Marcia Booth’s blog about the dangers and ways to avoid these kinds of attacks.

So now lets’ actually put the pieces to together in a very simple example. In this example we are going to compute the customer’s balance using SQL. While Data Dictionaries do a really great job of maintaining customer balance fields using update/backout, consider if you are using SQL having a field for totaling is not really necessary. And in fact sometimes due to either bad programming or system failures these totals can sometimes get out of sync. Using aggregate function in SQL insures this total is always correct. Below is a SQL query to compute the order total for the customer in the sample Order Entry workspace.

Define C_CustomerBalanceSQL for "SELECT SUM (OrderHea.Order_Total) from OrderHea WHERE Customer_Number = %1 GROUP by Customer_Number "

I’m not going to teach SQL here, but I think you can see this returns the sum of all the Order_Totals for a customer.

So now we will build a procedure that can be called - let me explain the process:

1. Create a SQL Handle manager
2. Get connection to the server by referencing the Customer table.
3. Open a statement handle
4. Construct our SQL statement using sformat
5. Execute the statement on the server
6. Fetch the results
7. Get value from the results to a number and update the form in the view
8. Close the statement handle and disconnect from the SQL handle manager
9. Lastly we destroy the handle manager

Procedure ComputeCustomerBalanceSQL
Handle hoSQLMngr hdbc hstmt
Integer iFetchResult
String sSQL
Number nCustomerBal

Get Create (RefClass(cSQLHandleManager)) to hoSQLMngr
Get SQLFileConnect of hoSQLMngr Customer.File_number to hdbc
Get SQLOpen of hdbc to hstmt
Move (SFormat(C_CustomerBalanceSQL,Customer.Customer_Number)) to sSQL
Send SQLExecDirect of hstmt sSQL
Get SQLFetch of hstmt to iFetchResult
If (iFetchResult <> 0) Begin
Get SQLColumnValue of hstmt 1 to nCustomerBal
Set Value of oComputedBalance to nCustomerBal
End
Send SQLClose of hstmt
Send SQLDisconnect of hdbc
Send Destroy of hoSQLMngr

End_Procedure

The above is direct execution on the server, which is the easiest method. If you are calling the above SQL multiple times you can also use the SQLPrepare method to have the server build an execution plan once for the statement rather than each time the SQL is called:

// using this will have the server pre-build an execution plan for the statement,
Send SQLPrepare of hstmt sSQL
Send SQLExecute of hstmt sSQL

By adding a OnPostFind in the Customer_DD, the customer computed balance will automatically refresh on the Balance tab of the Customer.vw:

Procedure OnPostFind Integer eMessage Boolean bFound
Forward Send OnPostFind eMessage bFound

If (eMessage<> DDFindClear and bFound) Begin
Send ComputeCustomerBalanceSQL
End

End_Procedure

Summary

Using ESQL opens new avenues in your DataFlex applications. Often I find it can replace use of Alias tables in my applications. And it’s also useful to replace code that may trigger reentrancy errors in Data Dictionary operations – these can often be done using ESQL.

You can certainly do more interesting work with ESQL than the example above. With SQL, the world is your oyster limited only by your understanding and use of the language.

Some useful links to help with learning SQL

http://www.tsql.info
http://www.w3schools.com/sql/default.asp