Campfire #11 New Features in Flex2SQL v14 [Webinar 42min]

On August 21, 2014, Mertech held a Campfire to provide our developer community with details on the enhancements that have been made to our latest version of Flex2SQL which has been available in Release Candidate status for several weeks. This Introduction to Flex2SQL v14 session was led by Mertech representative Oliver Nelson (Director of Engineering).

The Flex2SQL product is 16 years old, but we continue to innovate and add new features as the database servers that we support continue to evolve. The main changes in this version of Flex2SQL include compatibility with DataFlex 18.0, support for all major releases of database servers, and major speed improvements to the driver’s LOB support.

First, there are two major changes that relate to DataFlex 18.0: native data type editing and DDO SQL filters.  With the new Native Data Type Mapping in Dataflex 18.0 and corresponding Mertech Driver updates, developers can now create or edit tables right in the Studio with full support for whatever native data type they prefer to use for any supported database server. Previously this needed to be done using the Mertech Migration Utility. This tighter integration with the DataFlex Studio enables better workflows for table creation.

The second DataFlex 18.0 feature that is important in relation to the Mertech driver involves DDO SQL Filters. An SQL filter can now be set independently without impacting the rest of the application. Previously, records had to be filtered through DataFlex, i.e., on the client side, to see if they met the filtering requirement. Now, through the Mertech driver, this can be done on the server side by setting up a new data dictionary, which improves the performance of the search action and can enable developers to improve the accuracy of their filter by enabling more flexible SQL search criteria.

Although Mertech had previously gone around the limitation of DataFlex and enabled our developers to do the same thing via custom commands, now that DataFlex has incorporated this feature into the runtime and DDO classes it is much easier for developers using the Mertech driver to execute these searches. Essentially, the SQL filters are giving us a much better way to do something on the server side that was previously only possible on the client side and quite difficult to do with DataFlex. As Oliver says, “the fact that you can run expressions that are being added to the SQL statement that the driver builds on the back-end gives you a lot of power in what you can do”.

Next we talked about the improvements that were made to the support for LOB Access. In previous driver versions, the developer had to pass the field number and the variable length to access the LOB, and then the LOB would be read in 16 kb chunks. This was not ideal for large files such as images, and the way we wrote to an LOB was not compatible with data dictionaries. The advantage was that our record buffer always stayed small, conserving memory, in contrast to other competitive drivers which had field access but kept the record buffer large at all times which significantly affected the performance of the transaction. We wanted to find a way to keep our advantage and make the reading and writing process much smoother. After we innovated around the process, we now can load the image by looping through and loading or “reading” large (16 MB) chunks of the file. We do this instead of executing a single call to maintain our ability to work directly with the file buffer and not take up memory.

The audience had several questions: Has the new driver been tested with Web App (it has), and what versions of VDF are compatible with our revised driver (version 7.0 and upwards). In addition, one audience member had a question about whether the LOB data was stored on the shadow copy of the DDO structure that DataFlex usually saves on the client side. Oliver clarified that the data from the LOB fields is not stored in the shadow copy, and it is only accessible via the driver commands that we incorporated into v14.

Last, we discussed support for new versions of database servers. Essentially, the improvements made on the server side were primarily back-end related. For example, Microsoft SQL Server 2014 added support for a durable in-memory database, while MySQL and PostgreSQL both significantly improved their support for editing tables. We have incorporated support for all these changes into the v14 driver.

This was a great summary of the new features. We look forward to more Campfires as we continue to develop our products and services.