jdbc driver not loading with v5 of plugin

[john renfrew]

We used to put the jdbc drivers into /Library/Java/Extensions restart FileMaker and then either of the follwing methods would connect (second is more Groovy)

import groovy.sql.Sql

try{
	driver =  Class.forName('com.filemaker.jdbc.Driver').newInstance()
} catch (e) {
	return e.getMessage()
}

try{
	sql = Sql.newInstance('jdbc:filemaker://myserver/file', 'user', 'pass', 'com.filemaker.jdbc.Driver')
} catch (e) {
	return e.getMessage()
}

 

If I create an /ext folder in the JRE/lib then ScriptMaster does not load correctly, if I put the jar in the /lib or in a /lib/Extensions folder then all you get back from that is

com.filemaker.jdbc.Driver

meaning the driver hasn't loaded

This is true with the jdbc for MySql too... not just a FIleMaker issue -we can connect using the JDBC plugin but that's not a workable solution long term

 

This is getting to the point where this is unusable. can anyone shed light on the correct place to put the jdbc jar and the correct way to load it??

Edited July 24, 2018 by john renfrew

[Tom Assia]

Hello,

You said you used to do this so what changed ?

Have you try to specifiy the patch in your classpath ?

Tom

[john renfrew]

Brand new machine

Latest ScriptMaster, on High Sierra downloads the 9.0.1JRE

 

Now this is used instead of any other java path...

[Tom Assia]

have you try to specifiy the full path in ClassFor.Name ('mypath/java....') ?

[john renfrew]

Just got that working with the help of 360Works... Will post some code when I get a minute to tidy it up.

 

In previous versions that method of loading on the fly for database drivers would not work.

[Tom Assia]

ok thanks John

[marek]

Hi John, could you post code you mentioned? I have the same problem after upgrade to SM 5.1 and FM17.

 

Best regards,

Marek

[john renfrew]

Marek

Code here... you will need to adapt but it uses the jar from a known folder location

 

// SCsql ( fm_query ; fm_find )
// 18_10_04 JR 
// v3.1
// performs SQL query on database and returns fm_find | id 

import groovy.sql.Sql
import java.sql.Connection
import java.sql.Driver


db = [url:'jdbc:mysql://server:3306/database', username:'user', password:'password', driver: null, host:'server:3306', database:'filename']

jarFile = '/Path/To/File/mysql-connector-java-8.0.13.jar'
def driverName = 'com.mysql.jdbc.Driver'

def sqlQuery = fm_query //you can input whatever query statement(s) you want here


URL url = new File( jarFile ).toURI().toURL()
driver = (Driver)Class.forName( driverName, true, new URLClassLoader( [url] as URL[] ) ).newInstance()

Properties info = new Properties()
info.setProperty( 'user', db.username )
info.setProperty( 'password', db.password )

Connection conn = driver.connect( db.url+ '?useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC', info )

//groovy way to exceute query once we have a connection
def sql = new Sql( conn )

sqlc = fm_query[0..2]
try{
	if ( sqlc == 'UPD'){
		def ex = sql.executeUpdate (fm_query)
		return ex
		//return  c + " rows"
	 } else {
		def ex = sql.execute (fm_query)
	}// end if
} catch (e) {
	return 'ERROR - ' + e.getMessage()
}// end try

sqlFind = fm_find.tokenize('\n')
Long unix
answer = al = ''
sql.eachRow(fm_query){ row ->
	for (i in 0..<sqlFind.size()){
		al = al + row."${sqlFind[i]}"+ '|'
	}// end for
	answer = (answer? answer + '\n' : '') + al + row.id //id is identity field name in table
	al = ''
	try{
		unix = row.lastlogin
	} catch (e){
		//do nothing with error
	}// end try
}// end eachRow

sql.close()
if(!answer){
	return 'ERROR - empty set'
}// end if

return answer

 

[marek]

Thank you John. I'll try it.

[marek]

Hi Jon,

What does it mean input variable "fm_find" and what values are acceptable for it in your script?

 

[john renfrew]

Marek

This is code from a working example.... where the sql identity field was called id, and fm_find is a list of field names that you want to return from the found set

So with a table set up of

id, firstname, lastname, username, website, email, verified

The query SELECT * FROM table WHERE lastname = 'Renfrew' and an fm_find of List ( "first name";"last name";"email") would return to you 

John|Renfrew|[email protected]|23
Luke|Renfrew|[email protected]|27  etc.etc.

[marek]

Thanks a lot John, you helped me