Archive

Archives pour la catégorie ‘Groovy’

ExecuteSQLUpdates.groovy

28/01/2009
Commentaires fermés

import groovy.text.*
import groovy.sql.*;
import java.io.*;
import java.util.*;
import java.sql.*;
import java.text.*;

boolean debug = false;

void printUsage() {
	println "Usage: ExecuteUpdates.groovy [-debug] [-url URL] [SQL File]"
	println "       ExecuteUpdates.groovy [-debug] [-url URL] [SQL UPDATE/INSERT/DELETE Query]"
	println ""
	println "       NOTE: For executing more than one query you can use character / to separate them."
	println ""
	println "   This script is able to execute any type of queries, (usefull to create stored procedures)"
	System.exit(1);
}

// Prepare connection ------------------------------------------
driver = "oracle.jdbc.OracleDriver";
url  = "jdbc:oracle:thin:USER/PASS@HOST:1521:DB"
// -------------------------------------------------------------

// Verify inputs
if(args == null || args.length == 0 || args[0].length() == 0) printUsage()
switch(args[0]) {
	case ["-debug", "--debug", "debug", "-d"]:
		debug = true
		if(args.length == 1 || args[1].length() == 0) printUsage()
		args[0] = args[1]
		if(args.length >= 3 && args[2].length() > 0) args[1] = args[2]
		if(args.length >= 4 && args[3].length() > 0) args[2] = args[3]
	break;
	case ["-url", "--url", "url"]:
		if(args.length < 3 || args[1].length() == 0 || args[2].length() == 0)
			printUsage()
		url = args[1]
		args[0] = args[2]
		if(args.length > 3 && args[3].length() > 0) args[1] = args[3]
	break;
	default:
		if(args[0].startsWith("-")) printUsage()
	break;
}

switch(args[0]) {
	case ["-debug", "--debug", "debug", "-d"]:
		debug = true
		if(args.length == 1 || args[1].length() == 0) printUsage()
		args[0] = args[1]
		if(args.length >= 3 && args[2].length() > 0) args[1] = args[2]
		if(args.length >= 4 && args[3].length() > 0) args[2] = args[3]
	break;
	case ["-url", "--url", "url"]:
		if(args.length < 3 || args[1].length() == 0 || args[2].length() == 0 || args[3].length() == 0)
			printUsage()
		url = args[1]
		args[0] = args[2]
	break;
	default:
		if(args[0].startsWith("-")) printUsage()
	break;
}

println "Using URL: "+url

// ------- This script is not finish because I did not manage to add the entire code in -------

Groovy

DBShow.groovy

28/01/2009
Commentaires fermés

import groovy.sql.*;
import java.io.*;
import java.text.*;

/*
 * ====================================================
 *                    FUNCTIONS
 * ====================================================
 */

/**
 * Transform one date to a comprehensive String
 * @param timestamp The timestamp (as string) to convert.
 */
public static String dateToString(String timestamp) {
	try {
		return dateToString(new Date(Long.parseLong(timestamp)));
	} catch(Exception ex) {
		return timestamp;
	}
}

/**
 * Transform one date to a comprehensive String
 * @param date The date (as long or timestamp) to convert.
 */
public static String dateToString(long date) {
	return dateToString(new Date(date));
}

/**
 * Transform one date to a comprehensive String
 * @param date The date to convert.
 */
public static String dateToString(Date date) {
    DateFormat formater = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
    return formater.format(date);
}

/**
 * Transform one date to a comprehensive String
 * @param date The date to convert.
 */
public static String format(String columnName, Object columnValue, int colIndex, int nbCol, int defaultLen = 19, boolean trunc = true) {
    String value = ((columnValue==null)?"":columnValue.toString().replace('n', '
').replace('r', '
'));
    if(columnName!=null &amp;&amp; value.length() > 0) {
        switch(columnName) {
            case ["BIDDINGEND", "BIDDINGSTART", "LASTACTIVITYFORCLOSE"]:
                value = dateToString(value);
            break;
            case "STATUS":
                switch(value) {
                    case "1" : value = "Open"; break;
                    case "11": value = "Pending Next Stage"; break;
                }
            break;
            default:
                if(columnName.indexOf("TIME") > -1) value = dateToString(value);
            break;
        }
    }
    if(defaultLen > 0) {
	    int minLen = (columnName == null)?value.length():columnName.length();
	    if(defaultLen > minLen) minLen = defaultLen;
	    if(trunc &amp;&amp; value.length() > minLen) value = value.substring(0, minLen-2)+"..";
	    while(value.length() < minLen) value += " ";
	    return " "+value+((colIndex+1==nbCol)?"n":" |");
	} else {
	    return value+((colIndex+1==nbCol)?"n":"|");
	}
}

/*
 * ====================================================
 *                    GROOVY SCRIPT
 * ====================================================
 */
// Verify inputs
if(args == null || args.length == 0 || args[0].length() == 0) {
	println "usage: groovyFile.groovy [filename] [column lenght]";
	return
}

// Get queries
query = null
if((new File(args[0])).exists()) query = (new File(args[0])).getText();
else query = args[0];

start = new Date();
defaultLen = 19;
if(args.length >= 2 &amp;&amp; args[1].length() > 0 &amp;&amp; !args[0].equals(args[1])) defaultLen = Integer.parseInt(args[1])

// Prepare Connection
driver = "oracle.jdbc.OracleDriver";
url  = "jdbc:oracle:thin:USER/PASS@HOST:1521:DB"

sql = Sql.newInstance(url, driver)
nbRow = 0; nbCol = 0;
sql.eachRow(query) { row |
	len = 0;
	++nbRow;
	if(nbCol==0) {
		nbCol = row.getMetaData().getColumnCount();
		header = "";
		//for(i in 1..nbCol) header += format("null", row.getMetaData().getColumnName(i), i-1, nbCol, row.getMetaData().getColumnDisplaySize(i));
		for(i in 1..nbCol) header += format(null, row.getMetaData().getColumnName(i), (i-1), nbCol, defaultLen);
		print header
		if(defaultLen > 0) {
			for(i in 1..header.length()) print "-";
			print "n";
		}
	}
	for(i in 0..<nbCol) {
		value = row.getAt(i);
		column = row.getMetaData().getColumnName(i+1);
		//------------------
		/*
		value = ((value==null)?"":value.toString());
		if(value.length() > 0) {
			switch(column) {
				case "LASTACTIVITYFORCLOSE":
					value = dateToString(value);
				break;
			}
		}
		while(value.length() < column.length()) value += " ";
		value = value+((i+1==nbCol)?"n":"t");
		print value;
		/**/
		//------------------
		//print format(column, value, i, nbCol, row.getMetaData().getColumnDisplaySize(i))
		print format(column, value, i, nbCol, defaultLen)
	}
}
println "n"
println "Number of column: "+nbCol
println "Number of result: "+nbRow
end = new Date();
println "Executed in "+(end.getTime()-start.getTime())/1000+" s"

Groovy

NowToTimeStamp.groovy

28/01/2009
Commentaires fermés

date = new Date();
println date.getTime();

Groovy

TimestampToDate.groovy

28/01/2009
Commentaires fermés

println new Date(Long.parseLong(args[0]));

Groovy