The DataFlex programming language does not allow you to type in SQL statements as part of a program source code. Embedded SQL in DataFlex is implemented through SQL broker objects. You will send messages to the SQL broker objects that define statements and process the results.
Embedded SQL is defined in a package called sql.pkg. It can be included in a DataFlex program by the command “use SQL”.
If you want to use Embedded SQL in a DataFlex program, you will have to setup a proper environment for the SQL statements to be executed.
Before you can execute SQL statements, you will need to create a connection to the database. Even if the database is located on the same machine, a connection must be established. Connections are identified by connection handles.
Once a connection exists, statements can be created in that connection. Statements are identified by statement handles. Once you are done with the statement, you will need to dispose of the statement.
If all processing for a connection is finished, you should dispose of the connection handle.
Every Embedded SQL program will be of the form:
Open a statement handle
Manipulate the statement
Close a statement handle
It is possible to have multiple statement handles active at the same time. It is also possible to have multiple connections active at the same time. To limit the resources needed by the program we advise to free statement and connection handles as soon as they are no longer needed.
For connection allocation and de-allocation the Embedded SQL interface defines SQLConnect, SQLFileConnect and SQLDisconnect. Once a connection is established all the tables located in the connected database can be accessed.
To allocate and de-allocate statement handles SQLOpen and SQLClose have been defined. You need to close a statement handle whenever the statement changes.
Embedded SQL will perform all of its database access through SQL statements. Statements can be executed once the proper environment is created. There are several ways to execute statements in an Embedded SQL module.
In general, execution of an SQL statement is a two, or more, step process. The Database Server will compile the SQL statement into a so-called access plan. This plan describes in detail how the statement must be executed. It contains the indexes to use (if any), the order to create temporary results (if any) and much more. The second step is to actually execute the statement using the created access plan. The Embedded SQL interface offers various ways to execute statements:
Cursor Types in Embedded SQL