DBShow.groovy
28/01/2009
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 && 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 && 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 && args[1].length() > 0 && !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"