SQL and DataFlex
Now I need to get this documented once and for all. I'm tired of spending an hour every time I'm trying to figure out how to write a simple SQL statement in DataFlex just in order to get some data out of the database. This is supposed to be extremely simple in a framework that is buildt to create database applications.
The information here is as much a walktrhough of the issues I run in to as I reason about how to do this and in the end a complete template that shows how to get it to work. So, if you are only looking for the solution, head on to the bottom of the page.
The task
In this case I just want to make a simple SQL statement like "SELECT id, name FROM my_table WHERE id=X" and then get the name into a variable.
I know that in DataFlex there are a few different commands needed to do this, which is fine. But it looks different in different samples. Some are from older versions of DataFlex and some are just written using other naming conventions and since you are dependant on a few variables this adds to the confusion.
Using the documentation
My default mode of solving this is to open a page in the same workspace where I have an SQL- statement and copy that code. Usually this works quite well. If I have not done any SQL in the current workspace I start to look at older workspaces and this is when things start to get hairy. I do not dare look in the documentation of DataFlex when it comes to SQL as it is littered with conflicting information and samples for different versions of DataFlex that might not work anymore.
But just for fun, let's start in the documentation for good measures. Searching for "dataflex documentation" leads me to "DataFlex 2025 Help - Beta 1" which was not what I expected. But it is the correct link.
In the documentation I search for sql to see what we got to work with. There are two pages among all the Driver documentations that look interesting, the "old" and the new way of doing Embedded SQL. I'll go with the new one since I am working in DataFlex 25 for this project. (New Embedded SQL API)
I search on the page for "param", since I know I want to use a parameterized query and find this sample:
Now trying this in my code, but I get below error? Invalid message... On the line "Send SQLEtParameter".
Usually when this happends, the reason is that the object, in this case ghoSQLExecutor is not defined. So, I right click on it and click "Go to definition" and it is found in VDFBase.pkg. So, that should not be an issue. And now I'm lost. Because this is irraly similar to some code I have in another workspace, and it is working there. Looking for "MSG_SQLPREPARE" in the help to try to get any explanation of this error does not result in any results at all and google is comming up with just as much...
Looking for other code samples
Now I just want to get this to work. I, do not want to spend time debugging sample code. So, I try to copy some basic code from another code sample of mine. The updated code looks like this:
Object oSQLHandler is a cSQLHandleManager
Move Self to hoSQLMngr
End_Object
Move @SQL"SELECT id, name FROM my_table WHERE id=__id__" to sQuery
Move (Replaces('__id__', sQuery, iId)) to sQuery
Get SQLFileConnect of hoSQLMngr my_table.File_number to hdbc
Get SQLOpen of hdbc to hStmt
Send SQLExecDirect of hStmt sQuery
Repeat
Get SQLFetch of hStmt to iFetchResult
If (iFetchResult <> 0) Begin
Get SQLColumnValue of hStmt 1 to iId
Get SQLColumnValue of hStmt 2 to sName
End
Until (iFetchResult = 0)
Send SQLClose of hstmt
Send SQLDisconnect of hdbc
This is actually working. But I'm not happy with the __id__ replacement, I want to use the more modern version without all the SQLFileConnection, SQLOpen, hdbc and hStmt stuff that in my eyes are just cluttering down the code with information that make it harder to figure out what it is actually doing. And I would also like to use the new SQL-builder to manage the SQL. So, I have to get the new sample working somehow. But if I was doing something for a customer, I might just stop here and call it a day, since it is working.
Getting the new method to work
This is not a customer project and I want to be able to get the code to run properly and I will save this page as reference, the next time I'm going to do any SQL queries in DataFlex. So, I need to get this to work. So, I start to read the entire documentation from the top and realize that there might bee an issue with the ghoSQLExecutor after all. When I create a workspace I add a connection to the database and I assume that the ghoSQLExecutor is accessing that connection by default. It turns out that this is not the case. And just because the class cBaseSQLExecutor is found in the code does not mean that there is an active object of it that ghoSQLExecutor can access.
So, in order to get the sample to work we need to instantiate an object like this and set the Self to ghoSQLExecutor. Which is a global variable defined in the VDFBase.pkg-file. I add this under the oApplication-object in the src-file. So, that it can be accessed from all over the application. This also means that if I want to use another connection, I can just call Set psConnectionId of oSQLExecutor to "MY_OTHER_CONNECTION" and now all sql-queries will use the other connection.
Important things to note
- This will not change the connection used by the datadictionaries, which might be both good and bad. Just be aware of it!
- Another issue is that if you create a new project, using the same DataDictionaries and so on, you need to add this code to the .src-file of that workspace as well for your queries to work.
Use cSQLExecutor.pkg
Object oSQLExecutor is a cSQLExecutor
Move Self to ghoSQLExecutor
Set psConnectionId to "MY_SQL_CONNECTION"
End_Object
Using the query builder
Now that we got the code working, we can move the quiery in to a separate SQL-file. I usually create a folder called sql under the AppSrc-folder where I store the queries. This allows us to change the __id__ to ${id}. In here we can even test the query by typing some test values in the Prepare tab and the click Exeute to get the result.
In the code we now have to replace the SQL-string with Include_Text sql/MyQuery.sql as C_Query. This puts the prepared query inside of a Constant C_Query. This have caused some issues for me when trying to replace parts of the query after the fact. Since there is a limit to how much you can replace inside of a Constant. In these cases you want to copy the query into a string like this instead Move C_Query to sQuery befor any further modifications.
Inside the query builder, you also find a Struct generator. If you are returning many columns or just want a convenient way of parsing all data using one variable, it is a good idea to create one of those and add in the top of your file. Then you can initiate a ResultSet array and a DataSet variable to store and access your values from.
The only thing you change is this:
String[] aStrings
String[] aData
...
Get SQLExecute of ghoSQLExecutor to aStrings
Move Strings[0] to aData
Move aData[0] to iId
Move aData[1] to sValue
into this:
tMyStruct[] aResultSet
tMyStruct tDataSet
...
Get SQLExecute of ghoSQLExecutor to aResultSet
Move aResultSet[iRow] to tDataSet
Move tDataSet.iId to iId
Move tDataSet.sName to sValue
The final solution
Important! Befor you use embedded SQL in DataFlex, ensur that you have created an object of cSQLExecutor.
With that in place you can create a sql-file like AppSqr/sql/myQuery.sql that looks like this:
"SELECT id, name FROM my_table WHERE id=${id}"
Then implement this code to get the data:
tMyStruct[] aResultSet
tMyStruct tDataSet
Include_Text sql/MyQuery.sql as C_Query
Send SQLPrepare of ghoSQLExecutor C_Query
Send SQLSetParameter of ghoSQLExecutor "id" iId
Get SQLExecute of ghoSQLExecutor to aResultSet
Move 0 to iRow
While (SizeOfArray(aResultSet) > iRow)
Move aResultSet[iRow] to tDataSet
// Do what you want with tDataSet.id and tDataSet.name
Increment iRow
Loop
Usefull stuff
Sometimes you want to filter on a value, but leaving it blank will filter on a blank value. This is not the same as not filtering at all. One solution to this is to prepare the value with "__BLANK__" and then update the WHERE-statement like this:
WHERE (id=${id} OR ${id}='__BLANK__')
Then the id is ignored if it is sent in as __BLANK__.
If you have any tips and trix, post them in the comments below. If I figure out some new things I'll add it here under the usefull stuff when I do.
- DataFlex SQL