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

log-out · June 1, 2006, 8:01am

Thanks… I use FM6 at work, I can help you if you need

LogOut
Madrid-Spain

bubba · June 21, 2006, 1:52pm

Scott:

Thanks for your efforts along these lines.

I tried out your driver, and was able to connect to my DB and see a list of tables and fields and make a form.

The form comes up in Runtime and shows the proper number of records, but does not show any data in the fields. When I tried to do an Insert, Servoy hung.

This certainly has promise, and I would love to get this working, as it would make life SO much easier while we transition some of our app from FM8 to Servoy.

If there’s anything I can check in the meantime, I would appreciate knowing how to diagnose this.

Thanks,
Lee

Rene · June 24, 2006, 3:23pm

Hello Scott,

I`ve downloaded the latest version and was able to connect to the fmp6 file resided on my local machine.

However I recieved the same error as log-out.
Here is my Servoy logfile when creating a form (Error: Error while creating form in repository) (no details available)

Thread:AWT-EventQueue-0 : 2006-06-24 16:44 : java.lang.NullPointerException
	at com.ddc.fmjdbc.FmMetaData.getColumns(FmMetaData.java:362)
	at com.servoy.j2db.persistence.Server.try(Unknown Source)
	at com.servoy.j2db.persistence.Server.new(Unknown Source)
	at com.servoy.j2db.persistence.Server.getTable(Unknown Source)
	at com.servoy.j2db.develop.z.aC(Unknown Source)
	at com.servoy.j2db.develop.z.actionPerformed(Unknown Source)
	at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
	at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
	at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
	at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
	at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
	at java.awt.Component.processMouseEvent(Unknown Source)
	at javax.swing.JComponent.processMouseEvent(Unknown Source)
	at java.awt.Component.processEvent(Unknown Source)
	at java.awt.Container.processEvent(Unknown Source)
	at java.awt.Component.dispatchEventImpl(Unknown Source)
	at java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.awt.Component.dispatchEvent(Unknown Source)
	at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
	at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
	at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
	at java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.awt.Window.dispatchEventImpl(Unknown Source)
	at java.awt.Component.dispatchEvent(Unknown Source)
	at java.awt.EventQueue.dispatchEvent(Unknown Source)
	at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
	at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
	at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
	at java.awt.Dialog$1.run(Unknown Source)
	at java.awt.Dialog.show(Unknown Source)
	at java.awt.Component.show(Unknown Source)
	at java.awt.Component.setVisible(Unknown Source)
	at com.servoy.j2db.util.b.setVisible(Unknown Source)
	at com.servoy.j2db.develop.z.a(Unknown Source)
	at com.servoy.j2db.develop.c.bc.doIt(Unknown Source)
	at com.servoy.j2db.cmd.c$2.run(Unknown Source)
	at java.awt.event.InvocationEvent.dispatch(Unknown Source)
	at java.awt.EventQueue.dispatchEvent(Unknown Source)
	at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
	at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
	at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
	at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
	at java.awt.EventDispatchThread.run(Unknown Source)
Thread:AWT-EventQueue-0 : 2006-06-24 16:44 : java.lang.NullPointerException
	at com.ddc.fmjdbc.FmMetaData.getColumns(FmMetaData.java:362)
	at com.servoy.j2db.persistence.Server.try(Unknown Source)
	at com.servoy.j2db.persistence.Server.new(Unknown Source)
	at com.servoy.j2db.persistence.Server.getTable(Unknown Source)
	at com.servoy.j2db.develop.z.aC(Unknown Source)
	at com.servoy.j2db.develop.z.actionPerformed(Unknown Source)
	at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
	at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
	at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
	at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
	at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
	at java.awt.Component.processMouseEvent(Unknown Source)
	at javax.swing.JComponent.processMouseEvent(Unknown Source)
	at java.awt.Component.processEvent(Unknown Source)
	at java.awt.Container.processEvent(Unknown Source)
	at java.awt.Component.dispatchEventImpl(Unknown Source)
	at java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.awt.Component.dispatchEvent(Unknown Source)
	at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
	at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
	at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
	at java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.awt.Window.dispatchEventImpl(Unknown Source)
	at java.awt.Component.dispatchEvent(Unknown Source)
	at java.awt.EventQueue.dispatchEvent(Unknown Source)
	at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
	at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
	at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
	at java.awt.Dialog$1.run(Unknown Source)
	at java.awt.Dialog.show(Unknown Source)
	at java.awt.Component.show(Unknown Source)
	at java.awt.Component.setVisible(Unknown Source)
	at com.servoy.j2db.util.b.setVisible(Unknown Source)
	at com.servoy.j2db.develop.z.a(Unknown Source)
	at com.servoy.j2db.develop.c.bc.doIt(Unknown Source)
	at com.servoy.j2db.cmd.c$2.run(Unknown Source)
	at java.awt.event.InvocationEvent.dispatch(Unknown Source)
	at java.awt.EventQueue.dispatchEvent(Unknown Source)
	at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
	at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
	at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
	at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
	at java.awt.EventDispatchThread.run(Unknown Source)

The ddc_fm_jdbc logfile:

http://192.168.80.10/FMPro?-format=-fmp_xml&-max=0&-dbnames
http://192.168.80.10/FMPro?-format=-fmp_xml&-db=DPD_1av0.fp5&-layoutnames
http://192.168.80.10/FMPro?-format=-fmp_xml&-max=0&-dbnames

When I type the next codes in a browser (IE) I recieve errors

http://192.168.80.10/FMRes/FMPro?-db=DPD_1av0.fp5&-lop=or&-lay=Release&ID=26&ID=27&-max=200&-find

Error:Format File Not Found: No formatfile was specified.

http://192.168.80.10/FMPro?-db=DPD_1av0.fp5&-lop=or&-format=fmp_xml&-lay=Release&ID=26&ID=27&-max=200&-find

Error: Format File Not Found: The format file “fmp_xml” could not be found.

where:
Local IPaddress = 192.168.80.10
Database file = DPD_1av0.fp5
Layout residing all needed data = Release
Primary key attribute = ID

Environmental details:
Servoy Developer
Version R2 2.2.5-build 337
Java version 1.5.0_06-b05 (Windows XP)
FileMaker Pro 6.0v4

I Realy hope you can get this to work with fmp6.
PS I only need to search for data in a FMP6 database.

Kind Regards Rene[/quote]

sbutler · June 26, 2006, 1:12pm

I appreciate everyone’s feedback. I’ve got a few other things going on here are work, but I do plan to get this working for FileMaker versions 5-8. I am working on just getting a demo database setup so that we can all work from the same file, and then we’ll go from there. I’ll be posting the latest update for the driver shortly.

Thanks for being patient.

bubba · June 26, 2006, 2:31pm

goldcougar:
I appreciate everyone’s feedback. I’ve got a few other things going on here are work, but I do plan to get this working for FileMaker versions 5-8. I am working on just getting a demo database setup so that we can all work from the same file, and then we’ll go from there. I’ll be posting the latest update for the driver shortly.

Thanks for being patient.

Scott:

If we can “contribute” to your cause someway on a financial basis maybe that would help you be able to move things along? If we could getting FM8 working as a full function data provider in Servoy, it would be a great aid to our upgrade efforts.

Thanks for you time and attention to this.

Sincerely,
Lee

BDuncan · June 26, 2006, 4:48pm

FYI –

Scott and his co-worker, Mike McManus (also a frequest Forum poster) will be speaking at ServoyWorld 2006!

One of their sessions will be specifically on making the FileMaker connection and expanding the discussion on this JDBC driver.

If you have been thinking about going, NOW is the time to register! As you can see, ServoyWorld will be jam packed with specific, great, hands on information about utilizing Servoy to the maximum potential.

http://www.servoy.com/servoyworld/main.jsp

Can’t wait to see you all there!

Brenda Duncan

bubba · June 26, 2006, 5:22pm

Brenda:

I was registered a month ago, but thanks!

Lee

BDuncan · June 26, 2006, 5:48pm

Hi Lee,

Well, of COURSE I know you are coming to ServoyWorld! I know all the names of the 600+ people who are coming… ha ha

I guess I was talking to the whole forum in general, they should ALL be coming to ServoyWorld, right?!

See you there!

Brenda Duncan

sbutler · June 27, 2006, 7:24pm

Here is the latest version of the driver. It was a complete rebuild using the latest from Fmp360’s woof Driver…which included many fixes. So, I’ve gone through and made many of the modifications again to get it working with Servoy. I was able to test it with very simple databases in FM 6 & FM 8…but of course there are still some bugs to be worked out.

I have also converted the project over from BlueJ to Eclipse, so the driver has been renamed (meaning you need to delete the old driver and change the driver name in the dataprovider section)

After you have it installed, change/select “com.ddc.drivers.fmpxml.Driver” as the driver name.

ddc_fmp_jdbc.jar (96.6 KB)

bcusick · June 28, 2006, 5:44am

Hey Scott,

Great stuff! I’m trying with FMP6.

I used the driver URL:

jdbc:ddc://localhost:591/cnt.fp5?fmversion=6;pk=recid

I get this error in the log:

Thread:AWT-EventQueue-0 : 2006-06-27 22:38 : com.servoy.j2db.persistence.RepositoryException: com.ddc.drivers.fmpxml.FileMakerException: 100: Unknown error (JDBC URL: null / SQL statement: null ) 100: Unknown error (JDBC URL: null / SQL statement: null )
	at com.servoy.j2db.persistence.Server.new(Unknown Source)
	at com.servoy.j2db.persistence.Server.getTable(Unknown Source)
	at com.servoy.j2db.develop.z.aC(Unknown Source)
	at com.servoy.j2db.develop.z.actionPerformed(Unknown Source)
	at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
	at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
	at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
	at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
	at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
	at java.awt.Component.processMouseEvent(Unknown Source)
	at javax.swing.JComponent.processMouseEvent(Unknown Source)
	at java.awt.Component.processEvent(Unknown Source)
	at java.awt.Container.processEvent(Unknown Source)
	at java.awt.Component.dispatchEventImpl(Unknown Source)
	at java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.awt.Component.dispatchEvent(Unknown Source)
	at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
	at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
	at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
	at java.awt.Container.dispatchEventImpl(Unknown Source)
	at java.awt.Window.dispatchEventImpl(Unknown Source)
	at java.awt.Component.dispatchEvent(Unknown Source)
	at java.awt.EventQueue.dispatchEvent(Unknown Source)
	at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
	at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
	at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
	at java.awt.Dialog$1.run(Unknown Source)
	at java.awt.Dialog.show(Unknown Source)
	at java.awt.Component.show(Unknown Source)
	at java.awt.Component.setVisible(Unknown Source)
	at com.servoy.j2db.util.b.setVisible(Unknown Source)
	at com.servoy.j2db.develop.z.a(Unknown Source)
	at com.servoy.j2db.develop.c.bc.doIt(Unknown Source)
	at com.servoy.j2db.cmd.c$2.run(Unknown Source)
	at java.awt.event.InvocationEvent.dispatch(Unknown Source)
	at java.awt.EventQueue.dispatchEvent(Unknown Source)
	at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
	at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
	at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
	at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
	at java.awt.EventDispatchThread.run(Unknown Source)
Thread:AWT-EventQueue-0 : 2006-06-27 22:38 : java.sql.SQLException: Cannot work without primaryKey on table  'cnt.fp5|form___main_address'
	at com.servoy.j2db.dataprocessing.r.a(Unknown Source)
	at com.servoy.j2db.dataprocessing.r.if(Unknown Source)
	at com.servoy.j2db.dataprocessing.ag.getSharedFoundSet(Unknown Source)
	at com.servoy.j2db.dataprocessing.ag.do(Unknown Source)
	at com.servoy.j2db.dataprocessing.ag.if(Unknown Source)
	at com.servoy.j2db.FormPanel.byte(Unknown Source)
	at com.servoy.j2db.FormPanel.cL(Unknown Source)
	at com.servoy.j2db.FormManager.if(Unknown Source)
	at com.servoy.j2db.FormManager.getCurrentForm(Unknown Source)
	at com.servoy.j2db.J2DBClient.setTitle(Unknown Source)
	at com.servoy.j2db.develop.ad.if(Unknown Source)
	at com.servoy.j2db.develop.c.bc.doIt(Unknown Source)
	at com.servoy.j2db.cmd.c$2.run(Unknown Source)
	at java.awt.event.InvocationEvent.dispatch(Unknown Source)
	at java.awt.EventQueue.dispatchEvent(Unknown Source)
	at java.awt.EventDispatchThread.pumpOneEventForHierarchy(Unknown Source)
	at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
	at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
	at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
	at java.awt.EventDispatchThread.run(Unknown Source)

I added the FMP field “recid” as a number, auto-enter serial. I then filled the field with a number (there is only a single record in the test DB). It says it can’t work without a PK - but I thought I did specify it correctly in the URL.

sbutler · June 28, 2006, 1:11pm

Hey Bob, thanks for trying the driver. Here are a couple of quirks I’ve noticed that can mess the driver up…

Don’t use recid as the primary key. FileMaker actually has some metadata with that name, so it causes problems. I recommend ‘id’ or ‘pk’
Can you make sure the primary key field is on your layout your trying to get to… looks like it is “cnt.fp5|form___main_address”. Since it is using Web Publishing, the fields have to actually be on the layout in order for the XML part of the Driver to get to them.
For the definition of your primary key, I have been using Text, Auto-Enter Serial, and Validated to be Not Empty and Unique. I use Text because numbers have some display issues with the driver, but they will still work.
For the driver URL, don’t include the .fp5 extension. The extra period messes things up since the SQL notation is tableName.fieldName

I’ve also included a little test database…just 4 fields. It has the field ‘id’ as its primary key. the password is ‘servoy’

IWPTest.zip (3.89 KB)

log-out · June 29, 2006, 8:32am

Thanks, great job!
It works fine (but Fm is very SLOW!! :cry: )

Log-Out
Madrid - Spain

sbutler · June 29, 2006, 8:46pm

I made a change to the Driver to implement the setMaxRows function. That allows Servoy to only ask for a certain about of rows. In the previous Driver, it did not limit the rows, so it should speed it up a bit. If you look at the log file, you will see the “&-max=” in the url’s where the max is set by Servoy.

ddc_fmp_jdbc.jar (96.7 KB)

log-out · June 30, 2006, 6:10am

FANTASTIC!

With the setMaxRows function the driver works quickly

Log-Out
Madrid-Spain

log-out · June 30, 2006, 7:14am

I found a strange behaviour… I have to set the displayType of a number field (in Filemaker) to TEXT_AREA to see the data. If I set to TEXT_FIELD, I don’t see anything

Log-Out
Madrid - Spain

sbutler · June 30, 2006, 3:54pm

There are a good list of bugs that I haven’t gotten to or figured out yet…but I’m glad you found a work-around. Since this appears to be a ‘mostly’ working driver now, I have setup a page at http://it.dnacenter.com/servoy/ with a download link, instructions, and bug list. Future updates will be made on that page, but you can feel free to continue posting bugs and such as well on this post for eveyone to see, and I will respond on this topic as well.

Thanks.

log-out · July 3, 2006, 11:53am

The page doesn’t work… :cry:

sbutler · July 3, 2006, 12:52pm

aparently some subdomain issues…

Try this URL.

http://www.dnacenter.com/servoy/

log-out · July 7, 2006, 12:25pm

Thanks…

A query like this doesn’t work:

"SELECT field FROM fmtable WHERE ID = 1 OR ID = 2"

The driver only use the last value of the field (2 in this case), so I have to use two queries:

"SELECT field FROM fmtable WHERE ID = 2;
"SELECT field FROM fmtable WHERE ID = 1"

sbutler · July 7, 2006, 12:41pm

I haven’t been focusing the driver much on raw SQL…I’ve mostly just been trying to get it to work within the Servoy GUI. Servoy uses IN statements on their SQL queries…so try something like…

SELECT field FROM fmtable WHERE ID IN (1, 2)

Topic		Replies	Views
filemaker as a data source Classic Servoy	22	16771	May 14, 2007
Opinion On Servoy over FileMaker Classic Servoy	11	8393	November 15, 2004
Filemaker Developers: What would Help you Convert to Servoy? Classic Servoy	40	23889	December 29, 2004
Servoy Advanced Programming Guide For FileMaker Developers Classic Servoy	29	25065	September 4, 2003
ODBC connction to Visual FoxPro files impossible? Classic Servoy	25	10584	February 21, 2005

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

Related topics