Accessing SQL Server with Groovy

Import Java.sql.Connection


Import Java.sql.DriverManager


Import Javax.sql.DataSource


Import GROOVY.SQL.SQL


def CLI = new Clibuilder (usage: ' Groovy querymssql.groovy-h-S sqlserverhost [-P port]-u userid-p password-v VA Lue-t textfile queryfile [queryfile] ... ')


cli.h (longopt: ' Help ', ' usage information ')


Cli.s (argname: ' servername ', longopt: ' Server ', Args:1, Required:true, type:gstring, ' sqlserverhost ')


CLI. P (argname: ' Port ', longopt: ' Port ', args:1, Required:false, type:gstring, ' Port ')


cli.u (argname: ' UserID ', longopt: ' UserID ', args:1, Required:true, type:gstring, ' userid ')


CLI.P (argname: ' Password ', longopt: ' Password ', args:1, Required:true, type:gstring, ' password ')


cli.v (argname: ' Value ', longopt: ' Value ', args:1, Required:true, type:gstring, ' value ')


CLI.T (argname: ' Textfile ', longopt: ' Text ', args:1, Required:true, type:gstring, ' text file ')


def opt = cli.parse (args)


if (!opt) return


if (opt.h) cli.usage ()


def port = 1433


if (opt. P) port = opt. P//If the port was defined


def servername = Opt.s


def userid = opt.u


def password = opt.p


def valuetobind = OPT.V


def textfile = opt.t


def outfile


def Outfilewriter


try {


outfile = new File (textfile)


outfile.write (""); Truncate if output file already exists


} catch (Exception e) {


println "error:unable to open $textfile for writing";


return;


}


Driver = Class.forName ("Com.microsoft.jdbc.sqlserver.SQLServerDriver"). newinstance ();


Connection conn = drivermanager.getconnection ("jdbc:microsoft:sqlserver://$servername: $port", userid, password);


try {


if (args.length = = 0) {


usage_error = "error:invalid number of arguments"


usage_error = "\n$usage_error\nusage:groovy querymssql.groovy queryfile\n"


throw new IllegalArgumentException (Usage_error)


　　 }


SQL sql = new SQL (conn)


//After options processing the remaining arguments are query files


//Go through "Query files one at a" for execution


for (Queryfilename in Opt.arguments ()) {


queryfile = new File (queryfilename)


query = ""//Initialize the query string


param_count = 0//number of placeholders needed for parameters to query


Pattern =/\?///pattern to look for to find number of parameters


//Read the query from the query file, and build it


Queryfile.eachline {It->


query = "" + It


　　　　 }


//number of BIND variables to satisfy are obtained by number of Seen in the query


Query.eachmatch (pattern) {param_count++}


println '-. ' * 40


println "Query is ${query}"


println "Output is:"


println ' = ' * 80


def count = 0//Row count


paramlist = []


if (valuetobind!= "None")


1.upto (param_count) {paramlist << valuetobind}


sql.eachrow (query, paramlist) {row->


count++; Increment number of rows seen so far


Println "$count. ${row.name} "//Print out the column name


recstr = ""//Initialize the string that represents row


meta = row.getmetadata ()//Get metadata about the row


for (col in 0..<meta.columncount) {


//record is stored in a string called Recstr


if (recstr = "") {


recstr = Row[col]


　　　　　　　 }


else {


Recstr + = "," + Row[col]


　　　　　　　 }


　　　　　 }


outfile.append (recstr + "\ n")


　　　　 }


　　 }


conn.close ()


} catch (Exception e) {


print e.tostring ()


}


finally {


}