MS sqlserver just got easier

[john renfrew]

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()
ds.setUser(db.username)
ds.setPassword(db.password)
ds.setServerName(db.server)
ds.setPortNumber(db.port)
ds.setDatabaseName(db.databaseName)

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

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

try {
	if(sqlt == 'UPD') {
		//update
		ex = stmt.executeUpdate (fm_query)
	} else if(sqlt == 'INS') {
		//insert
		ex = stmt.executeUpdate(fm_query)
	} else {
		//select
		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
	sql.close()
	return ex
} else {
	if(!fm_find) {
		//default find is 'record' id of first returned answer
		ex.next()
		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
sql.close()

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

return answer.trim()

 

 

 

[john renfrew]

UPDATE

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

and

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

and

ds = new MysqlDataSource()

 

[john renfrew]

apologies:

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 October 25, 2018 by john renfrew

[john renfrew]

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

and

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

and 

ds = new J3SimpleDataSource()

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