If you still have clients on the DataFlex embedded DB, WHY?

I have been developing applications with DataFlex since 1988, specializing in small companies’ line of business applications on Windows. Over the years my company, Danes Bridge Enterprises, has been steadily deploying new applications and converting current customers to SQL-based back-ends.

Recently, I had one last customer using the embedded database, and maintaining their legacy system was a huge pain!

They adore their old Character Mode Application, which hasn’t changed much since 1990. I’ve migrated most of the functions to the new fancy Windows application (now in DataFlex 2019), but they kept breaking the database, fouling indexes, killing programs mid-process, etc., which would take time to resolve. They would also randomly come up with reporting needs for summaries, aggregations, cross tabs that were ’possible’ in regular reporting, but some were just begging for the ease of simple SQL statements.

Eventually I bit the bullet and decided I was going to eat the cost of the time it would take for the upgrade, and just charge them for the new licenses. (I can hear you thinking, "You really are insane, Mullan," all the way back here…)

Let me walk you through what was actually needed to ditch the embedded database:

1. Get a copy of the current embedded database on my laptop.
2. Run the DB Migration Wizard on DF19.0, selecting all tables, and don’t remove recnums. (Remember there is Character Mode code in here that I haven’t looked at, or recompiled, in over 10 years. Also, some of these steps only apply to those apps with Character Mode application components, as noted.) 
3. Oops, it failed.
   - Duplicate data in an index (the MSSQL DB has case insensitive indexes).
   - query.dat has overlap fields.
4. Fix the duplicate data in production. Remove "query.dat" from filelist, since they can’t use DFQuery due to the 64-bit OS.
5. Repeat steps 1, 2. Success!
6. Install and register the Connectivity Kit for Character Mode (basically unzip the files into the df31d\bin folder, and run register.exe).
7. Create a DFCONNID entry in MSSQLDRV.INT, in the Character Mode Bin folder.
8. Add a cConnection Object to the Windows Program.
9. Set up a DFINI.CFG file in the data folder.
10. Copy the new MSSQL DB and the data folder to the production server/workstation.
11. Fix the Firewall rules to allow MSSQL in and out.
12. Discover that reports don’t work...
13. Open all the reports in DataFlex Reports, run the Change DataSource Wizard on each report, and while you’re at it ’Check Database’ too.
14. Look at the Crystal Reports. Decide which is easier: replace in DataFlex Reports, or run the ’Change DataSource Wizard.’ I took the opportunity to replace them; there were only two left, and they were trivial.
    
    Job Done!

Warnings:

1. Overlaps in indexes may bite you. Maybe. I don’t use them, so I skated.
2. Case Sensitive Indexes. Try it and see. You can rebuild the database without Case Sensitive Collation if you need to, or just fix the data; it was probably wrong to start with. (See https://chartio.com/learn/databases/how-to-find-duplicate-values-in-a-sql-table/.)
3. Reporting. Basic Reports and RDS reports will still just work, you’ll need to touch all your Crystal or DataFlex Reports’ reports at least a little bit.
4. Firewall: You need to ’fix’ Windows Firewall to allow other computers to see the data.

Improvements you gain:

1. Ease of backups. (I recommend taking a look at sqlbackupandftp.com for offsite daily automatic backups.)
2. Ease of new reporting. You can get a count of customers with invoices over 45 days with one line of SQL.
3. DbSuggestionForms now work better.
4. Multi-User Speed.
5. Optimizing Batch updates and reporting. There were so, so many table scans in the old code...

Drawbacks:

None.

Conclusion:

How much time was actually spent doing the migration? A little less than 3 hours. The single largest portion of that was spent running the ’Change Datasource Wizard’ 58 times. Afterwards it was so very, very wonderful to make a change to the ’members’ table in MSSQL Management Studio, start up the Character Mode application, and see the data in there, proving that the upgrade worked!

I now have exactly zero applications left in the wild that use the embedded database. As it should be…

For more, read Dennis Piccioni’s Converting to SQL – A recipe for DataFlex developers blog for a simple-to-follow guide to converting to Microsoft SQL Server.