Jump to content
Sign in to follow this  
john renfrew

MS sqlserver just got easier

Recommended Posts

As promised...

With the new ScriptMaster which uses a sandboxed JVM it is now possible to load driver jars at runtime, whereas before they had to be installed in the Java/Ext folder

This makes a function to tak to MS sql server possible. Sample code below... 

Get the jar file referenced from the Microsoft website. As this is from some production code we are using record as the column name for the record index, and the data comes back pipe delimited, but you easily change those... If you leave the find blank it will just return the id of the first record in the list. if you UPDATE it should return the number of rows affected, and hopefully if you INSERT it will give you an array of the record ids that were inserted - use at your own risk obviously... test, test, test.


// MS_SQL( fm_query ; fm_find? )
// v4.5
// 18_10_14 JR 
// performs SQL query on MSsqlserver database and returns fm_find | record
// fm_query is SQL statement e.g. SELECT * FROM Table
// fm_find{optional} is list of field names to return in answer, and adds 'record' as last value - pipe delimited
// requires mssql-jdbc-7.0.0.jre8.jar - this can now be loaded at runtime
// assumes a column called 'record' as the ID

import groovy.sql.Sql
import java.sql.Statement
import com.microsoft.sqlserver.jdbc.SQLServerDataSource

t1 = System.currentTimeMillis()
//edit this for your solution
IDX = 'record'
db = [server:'servernameorip', port:49513, databaseName:'name', username:'user', password:'pass']

ds = new SQLServerDataSource()

sql = ds.getConnection()
Statement stmt = sql.createStatement()

answer = al = ex = ''
sqlt = fm_query[0..2].toUpperCase()

try {
	if(sqlt == 'UPD') {
		ex = stmt.executeUpdate (fm_query)
	} else if(sqlt == 'INS') {
		ex = stmt.executeUpdate(fm_query)
	} else {
		ex = stmt.executeQuery(fm_query)
	} //end if
} catch (e) {
	return '{"error":-1,"error_message":e.getMessage()}
} //end try

if(sqlt == 'INS' || sqlt == 'UPD') {
	//insert or update
	return ex
} else {
	if(!fm_find) {
		//default find is 'record' id of first returned answer
		answer = ex.getString(IDX)
	} else {
		sqlFind = fm_find.tokenize('\n')
		while (ex.next()) {
			for (i in 0..<sqlFind.size()){
				//pipe delimited, change last character for \t etc.
				al = al + ex.getString("${sqlFind[i]}") + '|'
			}// end for
			answer = (answer? answer + '\n' : '') + al + ex.getString(IDX)
			al = ''
		} //end while
	} //end if
} //end if

if(!answer) {
	return '{"error":-1,"error_message":"empty set"}'
} else {
	t1 = System.currentTimeMillis() - t1
} //end if

return answer.trim()




  • Thanks 1

Share this post

Link to post
Share on other sites


If you want to the same things with the mysql-connector-java-8.0.12.jar from MySql https://dev.mysql.com/downloads/connector/j/8.0.html

the you need to  change just three lines in the above

import com.mysql.cj.jdbc.MysqlDataSource


db = [server:'servernameorip', port:3306, databaseName:'name', username:'user', password:'pass']


ds = new MysqlDataSource()


Share this post

Link to post
Share on other sites


return '{"error":-1,"error_message":e.getMessage()}

is missing a final quote and some + signs, should be

return '{"error":-1,"error_message":' + e.getMessage() + '}'


Edited by john renfrew

Share this post

Link to post
Share on other sites

And for this of you that have read all the way to the bottom.......

We can also use this with FileMaker JDBC!!! The parts you need to change are

import com.filemaker.jdbc3.J3SimpleDataSource


db = [server:'myserver', port:2399, databaseName:'database', username:'user', password:'pass']


ds = new J3SimpleDataSource()

 you do need the file to be shared with JDBC/ODBC and a user account with access privileges.

Share this post

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.