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.
All use of ESQL in a DataFlex application follows the same basic flow:
Im 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, its more likely you will want to find them in more than one state. So its simple enough to replace the FL in the above SQL with %1 and then use SFormat to replace the parameter. So Im 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 Booths 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 customers 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 "
Im 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:
Handle hoSQLMngr hdbc hstmt
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
Send SQLClose of hstmt
Send SQLDisconnect of hdbc
Send Destroy of hoSQLMngr
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
Using ESQL opens new avenues in your DataFlex applications. Often I find it can replace use of Alias tables in my applications. And its 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