Our production database is done in Visual Foxpro (I am told the database file has a .DBC extension).
Has anyone gone through the steps to connect to that type of a database? Do I need to use the ODBC-JDBC bridge that comes with Ignition? I see there are 3rd party JDBC drivers for Visual FoxPro (see csv-jdbc.com for a JDBC driver for DBF files?? ) - does something like this make any sense?
Unfortunately I am not a DBA and have very limited knowledge of database connections (everything I have learned was so I could get FactorySQL and Factory PMI to work, lol). Please don’t assume I will understand any technobabble.
If anyone can give me a dumbed-down step-by-step process on how to build a new JDBC driver, implement a 3rd party one, or tell me how to do this some other way I would be eternally grateful.
I would try to get that “StelsDBF” JDBC driver to work. You should be able to download it (as a zip file) and then extract the “dbfdriver.jar” file.
Upload this as a new Database driver in Ignition. The driver’s classname is “[tt]jstels.jdbc.dbf.DBFDriver[/tt]”
The URL format is something like this: “[tt]jdbc:jstels:dbf:c:/mydir/dbffiles[/tt]”
See their docs for more information (thats where I got all this info from)
I would try to get this route going before I fell back on the JDBC-ODBC bridge.
This is just a follow-up for anyone who may be faced with the same issue.
I was able to get the JStels JBDC driver to work, and get a good data connection to our FoxPro database tables.
The steps were essentially:
[ol]
[li] Download the evaluation copy of the DBF driver from http://www.csv-jdbc.com[/li]
[li] Extract the .jar file into some location on your computer (the other jdbc drivers in Ignition are in this folder: C:\Program Files (x86)\Inductive Automation\Ignition\contexts\main\jdbc)[/li]
[li]In the Configure > Database > Drivers link of the Ignition Gateway, enter this into the Classname field with no parentheses: (jstels.jdbc.dbf.DBFDriver)[/li]
[li]Upload your .jar file[/li]
[li]Enter the URL Format. This will depend upon where the DBF files are. If they are on the same computer as Ignition, in a folder called, say, “C:\IATest” then enter with no parentheses (jdbc:jstels:dbf:c:/IATest). In my case, the database was on another server computer that was shared on our corporate LAN. In that case, I had to use the following syntax with no parentheses: (jdbc:jstels:dbf:cache://smb://admin:adminPW@ServerName/Shared_Folder_Filepath). In this case, you need to know the name of the admin user profile and the associated password. Put those in where “admin:adminPW” are. Then the @ sign and then the filepath of the folder in the shared location.[/li]
[li]In the Default Validation Query Field, enter something like: (SELECT * FROM “dbf.dbf”) and make sure there’s a file actually called dbf.dbf, or whatever filename you use. And yes, use the double quotes in the syntax.[/li]
[li]Save that & then go to Configure > Database > Connections link in Ignition Gateway. Open a new connection and insert the same URL Format as was in step 5 above.[/li]
[li]Check “Show Advanced Properties” and change Initial Size, Max Active, Max Idle, and Min Idle to 1 for all entries.[/li]
[li]Save and cross your fingers. If the Connection is Faulted, you may get a clue by clicking on the Database Connection Status link. If not, the tech support at support@csv-jdbc.com is almost as good as Inductive Automation’s tech support.[/li][/ol]
A few years later, I also became a victim to connect with a FoxPro database.
I’ve meanwhile found a driver combination that’s a bit stable, using the RMI connection to a locally installed JDBC driver from sesamesoftware (the site you go to when you follow the old link to csv-jdbc).
However, I still have problems with the character encoding. The text in the console should be “lumière”, but it ends up with a strange S (same as in the interface). So it appears that the è is encoded as 0x8a, which would point to IBM437 being used as I found on stackoverflow.
The driver offers the ability to alter the encoding, however, it appears that the IBM437 encoding is no standard encoding. What would be the best option to display the accents correctly?
OT: Ah, memories! I was a heavy user of FoxPro back in the pre-Windows days. I even have a Fox Software coffee mug from back then. Microsoft bought them out to gain control of their DB engine (Jet), which became the foundation of MS Access.
Yes, the driver worked, but we had big performance issues with it. Whenever we wanted to write to the DB, apparently a bunch of files got locked. This in turn caused the reads to time out, which seemed to cause repeated cache invalidation, until everything got stuck.
At the moment, we only read from the DBF files, reporting back to it is done manually via the old VFP interface.
