Re: 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(at)silicontao(dot)com> To: pgsql-general(at)postgresql(dot)org
Sent by: cc:
Subject: [GENERAL] Need a GNU SQL CLI tool for Win32 with ODBC support.

pgsql-general-owner(at)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
----------------------------------------------------------------------------------------------