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()
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()

 

 

 

  • Thanks 1

Share this post


Link to post
Share on other sites

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()

 

Share this post


Link to post
Share on other sites

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 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

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.

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.

Guest
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.