Hi All,
Life has a way of repeating itself… 8 years ago I started a thread about a DBF driver that helped me and a few others connect to a FoxPro database. http://forum.inductiveautomation.com/t/connecting-to-visual-foxpro-database-dbc-file/2531
Now, 2200 miles away, 8 years older (but only a couple years wiser), I am doing a project where I need to connect to a 20-year old extruder running a dBase database on board. Guess what I needed again? A JDBC driver for DBF.
Turns out the old one I used for FoxPro had been sold to Sesame Software, who repackaged it as their DBF Driver:
https://sesamesoftware.com/solutions/jdbc-database-drivers-products/relational-junction-dbf-jdbc-driver/
Installation was pretty much the same as in my old post, but they’ve changed the syntax to reflect their own company…
While setting up the Driver Configuration:
- Classname is now: “com.relationaljunction.jdbc.dbf.DBFDriver2”
Upload the .jar file in their download and it should go smoothly.
Then setting up the database connection,
2) Connect URL format (my dBase files were on a shared folder of the extruder’s computer, so I used the SMB syntax:
- My username& password for this example are: admin/password
- The IP address of the Extruder’s computer is 10.10.10.222
- The shared folder on the extruder is SharedFolder/WSS/dBaseFolder (where “SharedFolder” is an alias for the D: drive) and the DBF files I want to query actually reside in “dBaseFolder”
So that gave me a Connect URL string of:
jdbc:relationaljunction:dbf:cache://smb://admin:password@10.10.10.222/SharedFolder/WSS/dBaseFolder
And it worked!
Um… not so fast… 
I could only query one time. The DBF files were being continually changed by the extruder’s datalogging functions, but Ignition could only query the DBF file once. I couldn’t see the file change with time. Resaving the Database Connection on the Gateway allowed me to run one more query, but that’s all.
Much effort troubleshooting, didn’t know what was broken, talked to everyone’s tech support, still a mystery.
Then I found the answer in the driver documentation…
Turns out the driver caches the DBF file contents once, then Ignition runs its queries against the cache. The cache never changes, even though the DBF file has changed in the background. So it appeared like I had a good db connection, but then lost it somehow. Maddening!
The fix was also in the driver documentation. A connection parameter called watchFileModifications. It is defaulted to FALSE. But when set to TRUE, it updates its cache every so often, given by the checkPeriod parameter.
So then I added those to the Connect URL in my db connection on the Gateway and all was right with the world. The full Connect URL is now (it refreshes every 1000ms):
jdbc:relationaljunction:dbf:cache://smb://admin:password@10.10.10.222/SharedFolder/WSS/dBaseFolder?watchFileModifications=true&checkPeriod=1000
I told Sesame they should really default watchFileModifications to TRUE. Not sure if they will in the future, but if you can’t get recent results from your DBF files, that may be the fix.
Happy programming!
Cas