I don't know if this is the correct place, or that I have to go to a Java forum to find a solution.

This is the goal: connect FileMaker to AddressBook on Mac OSX by using the sqlite database behind the AddressBook.

Via the terminal this is a piece of cake, but I cannot manage to get this working using ScriptMaster.

I have downloaded the library "Sqlitejdbc-V056.jar", downloaded from “http://www.zentus.com/sqlitejdbc/" and loaded it into ScriptMaster.

Apparently you have to load the driver class in your code in the following way:

Class.forName("org.sqlite.JDBC");

However this does not seem to work in ScriptMaster. I get the following error:

java.lang.ClassNotFoundException: org.sqlite.JDBC

My very "complex" script is the following:

import java.sql.Connection;  

import java.sql.DriverManager;  

import java.sql.ResultSet;  

import java.sql.Statement; 

Class.forName("org.sqlite.JDBC");

Is the Class.forName not supported or am I trying to do something that is simply not possible or ... (am I just too dumb : )?

Thanks in advance for a push in the right direction :-)

Edited August 11, 201015 yr by Guest

This is happening because Class.forName("whatever") only works if "whatever" was loaded in the boot class loader. Since ScriptMaster loads jars at runtime, these must be loaded in a child classloader.

Try this instead:

this.getClass().getClassLoader().loadClass( "org.sqlite.JDBC" );

If that doesn't work, simply referencing the class should trigger the static code in the class that is needed to register it as a JDBC driver:

Class c = org.sqlite.JDBC.class;

If you feel like sharing the code, please let us know how it goes, accessing the Address Book would be a pretty cool ScriptMaster feature!

Edited August 11, 201015 yr by Guest
Fixed coding mistake

thanks for the help, and of course I am willing to share the code, but I am wondering if I will be able to make it on my own :-).

I "simplified" the code, just to see I am able to load a driver, and to see if the driver accepts the URL:

import java.sql.*;

import java.sql.DriverManager;  



c = getClass().getClassLoader().loadClass( "org.sqlite.JDBC" );



java.sql.Driver d = (java.sql.Driver)c.newInstance ();



return (" Does this driver accept my URL? " + d.acceptsURL(url) );

Well, I tried many things for the URL... but I always get the response: false... Does it accept the filepath to the local db file? Can I be sure that the driver is actually loaded now?

(I created a test database in my documents folder, so I am not even trying to connect to the addressbook...yet...)

Edited August 11, 201015 yr by Guest

You don't typically directly create a new instance of a JDBC driver. After registering the class (by referencing it), you usually call DriverManager.openConnection(). Please try that and let me know what happens.

I know, but this was a test scenario I found on another site, because I was not able to connect.

This was the code I tried to run:

import java.sql.*;

import java.sql.DriverManager;  



c = getClass().getClassLoader().loadClass( "org.sqlite.JDBC" );



Connection conn = DriverManager.getConnection("jdbc:sqlite:" + url );

I get the exception:

java.sql.SQLException: No suitable driver found for jdbc:sqlite:/../Users/andries/Documents/test.db

I read that the reason of this exception can be:

- URL is incorrect

- driver is not loaded

So this is why I created the test scenario to see if the driver would accept my URL.

Edited August 11, 201015 yr by Guest

Try placing the JDBC driver in your /Library/Java/Extensions directory. This will load it with the system classloader, so you can test it as if it were not using ScriptMaster.

If this works, then it's something related to class loading in ScriptMaster, and I will investigate it further.

If this doesn't work, then it's something related to the JDBC driver itself or the test code.

Hi Jesse

thanks for all the effort, really appreciated!

placing the library in the extension folder didn't make a lot of difference, so I think it is not ScriptMaster.

However the driver is - as I understand - some kind of "standard" in the java world to use, so I don't think the problem is there, but I will try to find another one and test it.

Concerning the test code, there is not much to fail... as I simply try to connect to the database.

My guess is that the URL I provide to the db is incorrect.

This is why I want to verify first if the driver is correctly loaded.

Anyway, the day is over here in Brussels, will look further into it tomorrow. If I find a solution I will come back to this forum with hopefully a nice bridge between FileMaker and AddressBook.

Thanks again!

So the day didn't finish yet...

"I" wrote the following java code:

import java.sql.*;



public class Test {

  public static void main(String[] args) throws Exception {

    Class.forName("org.sqlite.JDBC");

    Connection conn =

      

	DriverManager.getConnection("jdbc:sqlite:/Users/andries/Library/Application Support/AddressBook/AddressBook-v22.abcddb");

	  

	  Statement stat = conn.createStatement();

	  ResultSet rs = stat.executeQuery("select * from ZABCDEMAILADDRESS;");

	  while (rs.next()) {

		  System.out.println("email = " + rs.getString("ZADDRESS"));

	  }

	  rs.close();

	  conn.close();

  }

}

compiled it and ran it with the following statement in the Terminal:

java -cp .:sqlitejdbc-v056.jar Test

The result was a list of all my emailaddresses in AddressBook!

So I found the correct url and apparently the driver is also working. So Jesse, could you please take a look to this code why it is not working in ScriptMaster.

When I run this code in ScriptMaster I get the same errors again.

Thank you very much :-)

Edited August 11, 201015 yr by Guest

OK, thanks for testing this so thoroughly. With the FileMaker Developer Conference approaching, I will not have any time available until the beginning of September, but I will be happy to take a look at it then.

thank, have fun at the DevCon!

ok got it working :-)

Indeed placing the file in the extension folder of java solved everything (I must have been sleeping yesterday).

Running the following code (after changing the URL to the correct path), will return a list of all the emailaddresses in your AddressBook

import java.sql.*;

import java.sql.DriverManager;  



String Result = ""



String URL = "/Users/andries/Library/Application Support/AddressBook/AddressBook-v22.abcddb"



Class.forName("org.sqlite.JDBC")



Connection conn = DriverManager.getConnection("jdbc:sqlite:" + URL)



Statement stat = conn.createStatement()



ResultSet rs = stat.executeQuery("SELECT ZADDRESS from ZABCDEMAILADDRESS;")

	  while (rs.next()) {

		  Result += "email = " + rs.getString("ZADDRESS") + "n"

	  }





rs.close()

conn.close()

return Result

Enjoy!

Edited August 12, 201015 yr by Guest
Class.forName is needed

Does indeed work...

Fantastic...

Hello andries,

I've placed the "Sqlitejdbc-V056.jar" in /Library/Java/Extension/ , copied the code from you into the Scriptmaster Script field, changed the Users path into mine...

.. but get an error: java.lang.ClassNotFoundException: org.sqlite.JDBC :

Have I to restart FMP or the Mac to get it work?

Thanks

HB

...yes indeed. You have to restart FMP!

HB :

just a "small" issue: reading works well, but writing to the sqlite database of AddresBook while AddressBook is open is not working all the time (crashes AddressBook). This is of course logic as an sqlite database is single user and apparently AddresBook creates a lock on the database when it is open.

The better solution is to pass via the AddressBook API, which can be accessed via the Cocoa Framework. However the bridge between Java and Objective-C is depreciated. There is however a java library called Rococoa that can handle this.

I recognized that the issue is already solved, but ... shame on me ... I did not find the corresponding directory for /Library/Java/Extensions on my PC (I think /Library/Java/Extensions is a MAC directory).

When I use ...

this.getClass().getClassLoader().loadClass( "org.sqlite.JDBC" );

... or ...

Class c = org.sqlite.JDBC.class;

... Java tells me that the statement cannot be invoked on a null object. So I assume that the DriverManager does not recognice the classes loaded in child objects. The connection remains null.

I solved it by accessing the driver directly to create the connection:

Driver dr = (Driver)com.microsoft.sqlserver.jdbc.SQLServerDriver.newInstance();

Properties pr = new Properties();

pr.setProperty("user", "myUserName");

pr.setProperty("password", "myPassword");

con = dr.connect( connectionUrl, pr);

At my system it works.