dbquery

DbQuery.java (5436B)

---

 // dbquery - Query RDBMS, result as S-expression
 // Copyright (C) 2008 Tomas Hlavaty
 
 // This program is free software: you can redistribute it and/or modify
 // it under the terms of the GNU General Public License as published by
 // the Free Software Foundation, either version 3 of the License, or
 // (at your option) any later version.
 //
 // This program is distributed in the hope that it will be useful,
 // but WITHOUT ANY WARRANTY; without even the implied warranty of
 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 // GNU General Public License for more details.
 //
 // You should have received a copy of the GNU General Public License
 // along with this program.  If not, see <http://www.gnu.org/licenses/>.
 
 // Usage:
 // $ java -cp "<.>:<jdbc.jar>" DbQuery <driver> <cstr> <username> <pwd> <query>
 
 // Postgres:
 //   postgresql-8.3-603.jdbc4.jar
 //   org.postgresql.Driver
 //   jdbc:postgresql:<sid>
 
 // Oracle:
 //   classes12.jar
 //   oracle.jdbc.driver.OracleDriver
 //   jdbc:oracle:thin:@<host>:<port>:<sid>
 
 // MySQL:
 //   com.mysql.jdbc.Driver
 //   jdbc:mysql://<host>:<port>/<sid>
 
 import java.io.*;
 import java.sql.*;
 
 public class DbQuery {
 
     public static void main(String[] args) throws Exception {
         String driver = args[0];
         String cstr = args[1];
         String username = args[2];
         String pwd = args[3];
         String query = args[4];
 
         Class.forName(driver);
         Connection conn = DriverManager.getConnection(cstr, username, pwd);
         Statement stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery(query);
         System.out.println("(");
         printHeader(rs);
         printBody(rs);
         System.out.println(")");
         rs.close();
         stmt.close();
         conn.close();
     }
 
     public static void printHeader(ResultSet rs) throws Exception {
         ResultSetMetaData rsm = rs.getMetaData();
         int ncols = rsm.getColumnCount();
         System.out.print("(");
         for(int i = 0; i < ncols; i++) {
             if(i == 0) {
                 System.out.print("(");
             } else {
                 System.out.print(" (");
             }
             print(rsm.getColumnName(i + 1));
             System.out.print(" ");
             print(rsm.getColumnTypeName(i + 1));
             if(i < ncols - 1) {
                 System.out.println(")");
             } else {
                 System.out.print(")");
             }
         }
         System.out.println(")");
     }
 
     public static void printBody(ResultSet rs) throws Exception {
         ResultSetMetaData rsm = rs.getMetaData();
         int ncols = rsm.getColumnCount();
         while(rs.next()) {
             System.out.print("(");
             for(int i = 0; i < ncols; i++) {
                 if(i > 0) {
                     System.out.print(" ");
                 }
                 printCell(rs, i);
                 if(i < ncols - 1) {
                     System.out.println();
                 }
             }
             System.out.println(")");
         }
     }
 
     public static void print(String s) {
         if(s != null) {
             s.replaceAll("\\\\", "\\\\\\\\");
             s.replaceAll("\"", "\\\\\"");
             System.out.print('"' + s + '"');
         } else {
             System.out.print("NIL");
         }
     }
 
     public static void printCell(ResultSet rs, int i) throws Exception {
         ResultSetMetaData rsm = rs.getMetaData();
         int col = i + 1;
         if("number".equalsIgnoreCase(rsm.getColumnTypeName(col))) {
             String val = rs.getString(i + 1);
             if(val != null) {
                 System.out.print(rs.getString(i + 1));
             } else {
                 System.out.print("NIL");
             }
         } else if("clob".equalsIgnoreCase(rsm.getColumnTypeName(col))) {
             String filename = readClob(rs, i);
             print(filename);
         } else if("blob".equalsIgnoreCase(rsm.getColumnTypeName(col))) {
             String filename = readBlob(rs, i);
             print(filename);
         } else {
             print(rs.getString(i + 1));
         }
     }
 
     public static String readClob(ResultSet rs, int i) throws Exception {
         String tempName = null;
         int col = i + 1;
         Clob lob = rs.getClob(col);
         if(lob != null) {
             File temp = File.createTempFile("dbq", ".tmp");
             FileOutputStream out = new FileOutputStream(temp);
             InputStream in = lob.getAsciiStream();
             byte[] buf = new byte[10 * 1024];
             int n;
             while((n = in.read(buf)) > 0) {
                 out.write(buf, 0, n);
             }
             out.close();
             in.close();
             tempName = temp.getAbsolutePath();
         }
         return tempName;
     }
 
     public static String readBlob(ResultSet rs, int i) throws Exception {
         String tempName = null;
         int col = i + 1;
         Blob lob = rs.getBlob(col);
         if(lob != null) {
             File temp = File.createTempFile("dbq", ".tmp");
             FileOutputStream out = new FileOutputStream(temp);
             InputStream in = lob.getBinaryStream();
             byte[] buf = new byte[10 * 1024];
             int n;
             while((n = in.read(buf)) > 0) {
                 out.write(buf, 0, n);
             }
             out.close();
             in.close();
             tempName = temp.getAbsolutePath();
         }
         return tempName;
     }
 }