You should also be able to Windows Scripting Host for this. It has complete ODBC connection support, generally comes installed on all versions of windows, and Microsoft has buckets of example scripts available on their site. -----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of SCassidy@xxxxxxxxxxxxxxxxxxx Sent: Thursday, March 02, 2006 12:14 PM To: roy@xxxxxxxxxxxxxx Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: [GENERAL] Need a GNU SQL CLI tool for Win32 with ODBC support. Since you mentioned Java, I thought I'd mention this. There is no reason you cannot write a command line tool using Java. I'm no Java guru, but I quickly wrote a simple Java program to connect to PostgreSQL (on a Linux box) from a Windows box. I used a nice command line parser library called JSAP to allow me to specify fancy command line parameters for queries. You can then easily script the use of your Java program. Sample output (remember this is quick and dirty, you can fancy it up all you want): C:\JavaPgms\database>java -classpath .;\workarea\pg74.215.jdbc3.jar;\JavaPgms\JSAP-2.0a.jar test_db_cli -c 2 -q "select id, testdata from test2" Row: 1: 1 abcde Row: 2: 2 fghijkl Row: 3: 3 mnopqrs Row: 4: 4 the quick brown Row: 5: 5 fox jumped over the Row: 6: 7 lazy dog. C:\JavaPgms\database>java -classpath .;\workarea\pg74.215.jdbc3.jar;\JavaPgms\JSAP-2.0a.jar test_db_cli -c 2 -q "select id, testdata from test2" -v Number of cols specified: 2, query: select id, testdata from test2 Row: 1: 1 abcde Row: 2: 2 fghijkl Row: 3: 3 mnopqrs Row: 4: 4 the quick brown Row: 5: 5 fox jumped over the Row: 6: 7 lazy dog. C:\JavaPgms\database>java -classpath .;\workarea\pg74.215.jdbc3.jar;\JavaPgms\JSAP-2.0a.jar test_db_cli --cols 2 --query "select id, testdata from test2" -v Number of cols specified: 2, query: select id, testdata from test2 Row: 1: 1 abcde Row: 2: 2 fghijkl Row: 3: 3 mnopqrs Row: 4: 4 the quick brown Row: 5: 5 fox jumped over the Row: 6: 7 lazy dog. C:\JavaPgms\database>java -classpath .;\workarea\pg74.215.jdbc3.jar;\JavaPgms\JSAP-2.0a.jar test_db_cli Usage: java test_db_cli (-c|--cols) <cols> (-q|--query) <query> [-v|--verbose] Of course, you could add parameters for database name, etc. My sample program is 95 lines (including comments, etc.). It may not be great Java, so don't laugh: Installing the PostgreSQL .jar driver is as simple as downloading it. /* Java sample jdbc program test_db_cli.java */ import java.io.*; import java.sql.*; import com.martiansoftware.jsap.*; public class test_db_cli { Connection conn; // holds database connection Statement stmt; // holds SQL statement public test_db_cli(int returnVals, String queryStr) throws ClassNotFoundException, FileNotFoundException, IOException, SQLException { int i = 0; int c = 0; Class.forName("org.postgresql.Driver"); // load database interface // connect to the database - ip address, user, etc. have been munged - just insert your real values: conn = DriverManager.getConnection("jdbc:postgresql://10.20.xx.xx/testdb1", "yourdbuser", "yourpwifnecessary"); stmt = conn.createStatement(); ResultSet res = stmt.executeQuery(queryStr); if (res != null) while(res.next()) { i++; System.out.print("Row: " + i + ":\t"); //output the data any way you want for (c=1;c<=returnVals;c++) { String item1 = res.getString(c); System.out.print(res.getString(c) + "\t"); } System.out.println(""); } res.close(); stmt.close(); conn.close(); } public static void main(String args[]) throws Exception { JSAP jsap = new JSAP(); int numcols; String query; FlaggedOption opt1 = new FlaggedOption("cols") .setStringParser(JSAP.INTEGER_PARSER) .setDefault("1") .setRequired(true) .setShortFlag('c') .setLongFlag("cols"); jsap.registerParameter(opt1); FlaggedOption opt2 = new FlaggedOption("query") .setStringParser(JSAP.STRING_PARSER) .setRequired(true) .setShortFlag('q') .setLongFlag("query"); jsap.registerParameter(opt2); Switch verbose = new Switch("verbose") .setShortFlag('v') .setLongFlag("verbose"); jsap.registerParameter(verbose); JSAPResult config = jsap.parse(args); // check whether the command line was valid, and if it wasn't, // display usage information and exit. if (!config.success()) { System.err.println(); System.err.println("Usage: java " + test_db_cli.class.getName()); System.err.println(" " + jsap.getUsage()); System.err.println(); System.exit(1); } numcols=config.getInt("cols"); query=config.getString("query"); if (config.getBoolean("verbose")) { System.out.println("Number of cols specified: " + numcols + ", query: " + query); } try { test_db_cli test = new test_db_cli(numcols, query); } catch(Exception exc) { System.err.println("Exception caught.\n" + exc); exc.printStackTrace(); } } } Just an idea. Susan Roy Souther <roy@xxxxxxxxxxxxxx> To: pgsql-general@xxxxxxxxxxxxxx Sent by: cc: Subject: [GENERAL] Need a GNU SQL CLI tool for Win32 with ODBC support. pgsql-general-owner@pos |-------------------| tgresql.org | [ ] Expand Groups | |-------------------| 03/01/2006 03:00 PM Please respond to roy I love Linux, any tool you need it has it. Just try to find the most basic of tools for Windows, what a joke. I need an Open Source SQL command line tool for Windows that will let me script queries to a database over an ODBC connection. It must use ODBC because it may or may not be PostgreSQL. Some times it will but the rest of the time I have no idea what is on the other end. Most crap I am finding is shareware and everything is GUI. Some stuff is Open Source but requires Java, not good but still not CLI. Has anyone seen such a tool? Royce Souther www.SiliconTao.com Let Open Source help your business move beyond. For security this message is digitally authenticated by GnuPG. (See attached file: signature.asc) ---------------------------------------------------------------------------- ------------------ Simply protected storage solutions ensure that your information is automatically safe, readily available and always there, visit us at http://www.overlandstorage.com ---------------------------------------------------------------------------- ------------------
