How To: FileMaker as a Dataprovider (Custom XML JDBC Driver)

[sbutler]

So, we all want the reliability, speed and scalability of a FileMaker Server as our Servoy backend right? OK, seriously, at our company we needed to integrate Servoy and FileMaker to help the transition, since we have several hundtred FileMaker databases and can't transfer them overnight.

The Solution....
There is a free XML JDBC driver out there from 360 Works, however it doesn't work with servoy becuase just like the FM Driver, it doens't implement primary keys. So, I've modified the driver in several places and allowed you to specify the PK in the connection URL when setting it up as a Datasource. I've tested it with FileMaker 7 & 8, it may work in 5 depending if they implement the logical OR operator in the URL parameters.

There are many bugs since it goes through FileMaker's XML, so I've listed below what I have tested so far and what does & doesn't work. Hopefully most of the stuff will work in the future with some more modifications to the JDBC driver.

-Scrolling through records, making relations, valuelists, (your basic stuff) all works.
-Sorting doesn't work
-New Record doesn't work
-Delete Record works, but doesn't update Servoy until a refresh
-Wildcard Search doesn't work
-Text fields display, search, and update correctly
-Date fields don't display, but CAN be searched, and if you edit in servoy, will update correctly in FileMaker
-Number fields don't display, but CAN be searched, and used in relationships, and will work correctly
-For performance, the driver only returns back 200 records. All records can be searched, but only up to 200 returned
-SQL commands with the getDatasetByQuery work
-SQL commands with the Raw SQL plugin work

How to get it Working:

First, since it uses XML you must obviously have Web Publishing enabled in FileMaker. I'll assume everyone knows how to do that.

Download the Jar attached in this post and put in in your /Servoy/Drivers folder.
Restart Servoy, go to the preferences and add a new DB Sever.
Specify the username and password for your solution
Choose com.ddc.fmjdbc.Driver for the JDBC Driver
The connection URL is in the format of:
jdbc:ddc://192.168.0.0/xJDBCTest;pk=Key
where
-192.168.0.0 is the ip of your Web Publishing Engine (may be the same as your FileMaker DB server, unless you used a 2-tier approach)
-xJDBCTest is the name of your filemaker database
-Key is the name of your primary key field for that database (and all tables within that database)

And that should be it!

Let me know if you have problems with it. Also, if there are some good Java/XML/FileMaker IWP developers who are interested in speeding this project along, I'm willling to share.

----UPDATE-----
For first time visitors to this posting, this driver has and will continue to get several updates. For instructions and a link to download the driver, please visit the project home page at http://goldcougar83.googlepages.com OR to view the source code, visit http://code.google.com/p/filemaker-to-servoy-jdbc-driver/
Future updates will be made on that page, but you can continue posting bugs and such on this post for eveyone to see, and I will respond on this forum topic as well.

Additional Information for converting FileMaker applications to Servoy: http://goldcougar83.googlepages.com/convert_filemaker_to_servoy
----------

[log-out]

Does it work with FM 6?

[sbutler]

I haven't tested it with FM 6. It should work in 5 & 6 as long as they support the OR logical operator, "-lop=or" in the url. That allows it to turn the "where id IN( ?, ?)" into "where id = ? or id = ?" , which then turns into the URL of
http://192.168.0.0/fmi/xml/FMPXMLRESULT ... =200&-find

Just did a quick google search and it appears it should work. FM 5 and 6 URLs look like this..

http://192.168.0.0/FMRes/FMPro?-db=xJDB ... =200&-find

So, I think it should work. Please let me know.

[log-out]

I have tested it with FM 6 and it doesn't work. I tried this:

- I enabled Web Companion, port 591
- The DB is named "list.fp5"
- FM doesn't have PKs!!! so I created a calculation field named "recid", and it shows the RecID of each Record.

So, the URL would be:

Code: Select all

jdbc:ddc://127.0.0.1:591/list.fp5;pk=recid

But I received this error:

Code: Select all

java.io.IOException: Server returned HTTP response code: 501 for URL: http://127.0.0.1:591/fmi/xml/FMPXMLRESULT.xml

[sbutler]

I don't have a FM 5 or 6 Server here to test, but judging from the URL it is trying to load, it is not detecting the FileMaker version...becuase it is using the FM 7 URL format.

So, it looks like my modification to the Driver to specify the PK has somehow interfered with that. I"ve made some changes and attached a new driver. Please download and try again, and use this as your URL to be explicit about the version..

Code: Select all

jdbc:ddc://127.0.0.1:591/list.fp5?fmversion=6;pk=recid

[log-out]

Please download and try again, and use this as your URL to be explicit about the version..

Thanks!!

The connection works, but when I was traying to create a Form, I received this error:

Code: Select all

java.sql.SQLException: java.io.IOException: Server returned HTTP response code: 501 for URL: http://127.0.0.1:591/fmi/xml/fmresultset.xml java.io.IOException: Server returned HTTP response code: 501 for URL: http://127.0.0.1:591/fmi/xml/fmresultset.xml

[sbutler]

Looks like the 360 Works folks forgot to test for the version in the metadata class. I've made a change are recompiled the driver again....

[log-out]

Now I receive an error when I try to create a Form:

Code: Select all

Error while creatig form in repository

But when I click on th Details>> button, I receive a blank message

[sbutler]

Can you paste this into the URL of your web browser and let me know what it shows...

Code: Select all

http://192.168.0.0/FMRes/FMPro?-db=xJDBCTest&-lop=or&-lay=xJDBCTestLayout&id=1&id=2&-max=200&-find

where..
192.168.0.0= the IP of your filemaker server with IWP enabled
xJDBCTest = the name of your fm database
xJDBCTestLayout = a layout in your database with the primary key on it
and finally, replace..
id=1&id=2
with the name of your primary key and possible values in it...
So, if you pk is recid, and values start with 100, then use
recid=100&recid=101

If all goes well, you should see some XML displayed in your browser. If it doesn't work, then let me know if this format works..

Code: Select all

http://192.168.0.0/FMRes/FMPro?-db=xJDBCTest&-lay=xJDBCTestLayout&id=1&-max=200&-find

Thanks

[antonio]

Scott, thanks for this. I'm not clear how to use it for fp7 files with more than one table and pk.

How do we point it to the local host? I've tried setting the IP to 10.0.1.3 (the IP of my machine on the LAN, and also tried localhost, but no success.

Web companion is turned on in FM.

[log-out]

The URL should be:

Code: Select all

http://192.168.0.0/FMPro?-db=lista.fp5&-lop=or&-format=fmp_xml&-lay=cgi&recid=1&recid=2&-max=200&-find

It shows a valid XML, similar to this:

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>
<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
<ERRORCODE>0</ERRORCODE>
<PRODUCT BUILD="2/10/2004" NAME="FileMaker Pro Web Companion" VERSION="6.0v3"/>
<DATABASE DATEFORMAT="d/M/yyyy" LAYOUT="cgi" NAME="lista.fp5" RECORDS="557" TIMEFORMAT="k:mm:ss"/>
<METADATA>
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="id_p" TYPE="TEXT"/>
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="codigo" TYPE="TEXT"/>
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="cd" TYPE="TEXT"/>
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="cd_p2" TYPE="TEXT"/>
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="k_con" TYPE="TEXT"/>
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="estado" TYPE="TEXT"/>
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="nivel" TYPE="TEXT"/>
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="nif" TYPE="TEXT"/>
<FIELD EMPTYOK="NO" MAXREPEAT="1" NAME="recid" TYPE="NUMBER"/>
<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="recid2" TYPE="NUMBER"/>
</METADATA>
<RESULTSET FOUND="2">
<ROW MODID="4" RECORDID="33200">
<COL><DATA>53503</DATA></COL>
<COL><DATA>86001</DATA></COL>
<COL><DATA>0A611</DATA></COL>
<COL><DATA>P000795</DATA></COL>
<COL><DATA>0A611_86001</DATA></COL>
<COL><DATA>baja</DATA></COL>
<COL><DATA>JVTAS</DATA></COL>
<COL><DATA>12390149A</DATA></COL>
<COL><DATA>1</DATA></COL>
<COL><DATA>33200</DATA></COL>
</ROW>
<ROW MODID="4" RECORDID="33201">
<COL><DATA>50408</DATA></COL>
<COL><DATA>86001</DATA></COL>
<COL><DATA>0A611</DATA></COL>
<COL><DATA>P000795</DATA></COL>
<COL><DATA>0A611_86001</DATA></COL>
<COL><DATA>baja</DATA></COL>
<COL><DATA>VN</DATA></COL>
<COL><DATA>12390149A</DATA></COL>
<COL><DATA>2</DATA></COL>
<COL><DATA>33201</DATA></COL>
</ROW>
</RESULTSET>
</FMPXMLRESULT>

Thanks!

[sbutler]

log-out,
I've recompiled and added a logging feature. Whenever a request is made, it is writing out to a file in the Servoy folder called ddc_fm_jdbc.txt , and it will show the URL's it is attempting to load. Please take a look and post back if it is incorrect. I appreciate your help.

antonio,
This driver will work with multiple tables inside of one FileMaker file. Just use the URL to point it at the correct FM Database. The catch is, since FileMaker doesn't implement primary keys, is that all of your tables within the same database must use the same name for their PK. So, it doesn't matter the number of tables, just that they use the same exact field name for their PK.
For local host, try 127.0.0.1, and it that doesn't work, open up your browser and try to enter a URL in the FM 7 format that I described above to make sure filemaker is working properly.

[log-out]

goldcougar, I appreciate yout patient!!

Unfortunately, I can't test de driver, because I can't create any form. I receive the same error I said above:

Code: Select all

Error while creatig form in repository

The log file only says:

Code: Select all

http://127.0.01:591/FMPro
http://127.0.01:591/FMPro
http://127.0.01:591/FMPro
http://127.0.01:591/FMPro


Thanks

LogOut
Madrid - Spain

[sbutler]

I wish I hade FM 6 server here and I could just get it working....so I appreciate you testing it with me. Can you try this one and show me the log again?

[log-out]

Error persists... This is the log:

Code: Select all

http://127.0.01:591/FMPro?-format=-fmp_xml&-max=0&-dbnames
http://127.0.01:591/FMPro?-format=-fmp_xml&-db=lista.fp5&-layoutnames


These URL's are valid, and there are the xml returned by FM:

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>
<FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult">
<ERRORCODE>0</ERRORCODE>
<PRODUCT BUILD="2/10/2004" NAME="FileMaker Pro Web Companion" VERSION="6.0v3"/>
<DATABASE DATEFORMAT="" LAYOUT="" NAME="DBNAMES" RECORDS="1" TIMEFORMAT=""/>
<METADATA>
<FIELD EMPTYOK="NO" MAXREPEAT="1" NAME="DATABASE_NAME" TYPE="TEXT"/>
</METADATA>
<RESULTSET FOUND="1">
<ROW MODID="0" RECORDID="0">
<COL><DATA>lista.fp5</DATA></COL>
</ROW>
</RESULTSET>
</FMPXMLRESULT>


Code: Select all

<?xml version="1.0" encoding="UTF-8"?><FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult"><ERRORCODE>0</ERRORCODE><PRODUCT BUILD="2/10/2004" NAME="FileMaker Pro Web Companion" VERSION="6.0v3"/>
<DATABASE DATEFORMAT="" LAYOUT="" NAME="DBNAMES" RECORDS="1" TIMEFORMAT=""/>
<METADATA>
<FIELD EMPTYOK="NO" MAXREPEAT="1" NAME="DATABASE_NAME" TYPE="TEXT"/>
</METADATA>
<RESULTSET FOUND="1">
<ROW MODID="0" RECORDID="0">
<COL><DATA>lista.fp5</DATA></COL>
</ROW>
</RESULTSET>
</FMPXMLRESULT>