Even though the DataFlex platform has provided developers with all the tools for the efficient development and deployment of database applications for many years, it was originally designed around its own proprietary embedded database. As developers moved to using industry-standard SQL backends, like Microsoft SQL Server, DB2, Oracle and, more recently, MySQL and PostgreSQL, the methods of accessing data in SQL and DataFlex have been refined and improved, culminating in Managed Connections.
Let’s start with looking at how data is accessed in SQL environments. Essentially, any tool, or application, goes through the same steps, regardless of the SQL backend in use.
SQL naturally uses a “top-down” approach; we connect to a server and database. By “connect” we mean “logging in”.
Once the connection is made, we create and access tables within the database to manipulate data (add, edit or delete). When we have completed our manipulations, we log out of the server.
The meta-data that defines all the aspects of the data (column names, data types and lengths, indexes, relationships and more) is stored completely on the server.
Application portability (deploying in different, sometimes multiple locations) is achieved by changing the login information to different servers and databases with the same fundamental table structures.
Of course, there can often be more than one server and/or database involved, and transactions almost always involve data in multiple tables.
In the DataFlex application environment, data is accessed differently than in SQL, using a “bottom-up” approach. A configuration file, or filelist, acts as a catalog for all the tables used in a particular application and stores the table names and their physical locations. The application opens specific tables, manipulates data, and then closes the tables. The header record of the DataFlex table contains all the meta-data; column names, types and lengths, index definitions, table relationships and more.
Application portability is achieved by managing the information in the filelist “catalog”, by chaining filelists during development and deployment, and copying the table files to various physical locations. One important thing to remember as we progress is that the meta-data required for DataFlex applications is not the same as the meta-data supplied by the various SQL servers. As we can see, the methods for accessing data in both environments is fundamentally different, but the DataFlex language and frameworks have successfully married the two worlds together in such a way that applications can be developed that are easily moved from one host environment to the other, and easily managed and deployed once there. We started by defining an intermediate file for each table, the int-file, which resides in the workspace’s Data folder. It contains two main categories of information:
The filelist points to the intermediate file for each table, instead of the table location. When we open a table, the intermediate file is read, the connection is made to the server, the SQL meta-data is read and then combined with the additional DataFlex meta-data.
In the next lesson, we’ll look more closely at Connection Strings and IDs…