Need a GNU SQL CLI tool for Win32 with ODBC support.

Roy Souther wrote:

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.

It should be possible to use something simple like VBS to script these.
Should be simple. And you could open source it :-)

try activestate perl, I have used it a number of times on windows XP/2000 for CLI stuff (data loading).

---------- Original Message -----------
From: Roy Souther <roy@silicontao.com>
To: pgsql-general@postgresql.org
Sent: Wed, 01 Mar 2006 16:00:21 -0700
Subject: [GENERAL] Need a GNU SQL CLI tool for Win32 with ODBC support.

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.

------- End of Original Message -------

On Mar 1, 2006, at 6:00 PM, Roy Souther wrote:

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?

PHP is pretty easy to setup on Windows and appears to have good ODBC
support:

http://www.php.net/manual/en/ref.uodbc.php

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

activestate perl works a treat and crimson editor
(http://www.crimsoneditor.com/) as an editor. If you want a gui
database manager with sql scripting try aqua data :
http://www.aquafold.com/

Cheers
Noel

Roy Souther wrote:

On Wed, 2006-03-01 at 17:00, Roy Souther wrote:

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?

Not for ODBC. Have you looked at any of the GUIs to see if they have a
"type a query here" window in them?

CLI = Command Line Interface
GUI = Graphical User Interface
WSI = Web Server Interface

I need a CLI app. Sorry I was not clear on this.

On Thu, 2006-03-02 at 10:05 -0600, Scott Marlowe wrote:

On Wed, 2006-03-01 at 17:00, Roy Souther wrote:

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?

Not for ODBC. Have you looked at any of the GUIs to see if they have a
"type a query here" window in them?

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Royce Souther
www.SiliconTao.com
Let Open Source help your business move beyond.

For security this message is digitally authenticated by GnuPG.

Oh no, you were perfectly clear. I wrote my post because I don't know
of any CLI tools for windows that do this either, so it would seem that
a decent CLI tool wrapped in a GUI would still be acceptable. I mean,
it's a question of symatics at that point. So, that's why I suggested
some of the GUI tools that have a type your query here window.
Sometimes you just gotta use what you can get. Some of them have
history and all that fancy stuff, they just live inside a GUI app.

Me personally, I switched of Windows long ago due to issues like this.

Actually not semantics. I need to script the program and there is no way
to script a GUI app.

I have started using PHP. Not a great solution, extreme overkill, but it
does work well.

On Thu, 2006-03-02 at 11:29 -0600, Scott Marlowe wrote:

Oh no, you were perfectly clear. I wrote my post because I don't know
of any CLI tools for windows that do this either, so it would seem that
a decent CLI tool wrapped in a GUI would still be acceptable. I mean,
it's a question of symatics at that point. So, that's why I suggested
some of the GUI tools that have a type your query here window.
Sometimes you just gotta use what you can get. Some of them have
history and all that fancy stuff, they just live inside a GUI app.

Me personally, I switched of Windows long ago due to issues like this.

On Thu, 2006-03-02 at 10:34, Roy Souther wrote:

CLI = Command Line Interface
GUI = Graphical User Interface
WSI = Web Server Interface

I need a CLI app. Sorry I was not clear on this.

On Thu, 2006-03-02 at 10:05 -0600, Scott Marlowe wrote:

On Wed, 2006-03-01 at 17:00, Roy Souther wrote:

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?

Not for ODBC. Have you looked at any of the GUIs to see if they have a
"type a query here" window in them?

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Royce Souther
www.SiliconTao.com
Let Open Source help your business move beyond.

For security this message is digitally authenticated by GnuPG.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Royce Souther
www.SiliconTao.com
Let Open Source help your business move beyond.

For security this message is digitally authenticated by GnuPG.

Then yeah, php or perl are your only choices. Again, as is often the
case, better to tell us what you're tying to do, and ask for
suggestions, then to tell us what tool you think you need, and leave us
guessing as to motivation. Happens a lot though, don't worry about it.
It's just better to describe the problem generally.

Nice thing about using php or perl is that if you reach a point to where
ODBC can't give you what you need, there's native connect libraries for
most anything out there you can switch over to.

You could easily create a console application with Lazarus or Delphi to
do this, the console app could read the script from params, standard
input, or a text file and execute it
against the server of your choice using ODBC or a native interface to libpq.

Tony

Scott Marlowe wrote:

Then yeah, php or perl are your only choices. Again, as is often the
case, better to tell us what you're tying to do, and ask for
suggestions, then to tell us what tool you think you need, and leave us
guessing as to motivation. Happens a lot though, don't worry about it.
It's just better to describe the problem generally.

Nice thing about using php or perl is that if you reach a point to where
ODBC can't give you what you need, there's native connect libraries for
most anything out there you can switch over to.

ruby is another option if you're looking at scripting languages with DB
interfaces.

Reid Thompson schrieb:

Scott Marlowe wrote:

Then yeah, php or perl are your only choices. Again, as is often the
case, better to tell us what you're tying to do, and ask for
suggestions, then to tell us what tool you think you need, and leave us
guessing as to motivation. Happens a lot though, don't worry about
it. It's just better to describe the problem generally.

Nice thing about using php or perl is that if you reach a point to where
ODBC can't give you what you need, there's native connect libraries for
most anything out there you can switch over to.

ruby is another option if you're looking at scripting languages with DB
interfaces.

And of course python .-)
(which has built in interactive mode)

regards
Tino

On Mar 2, 2006, at 8:34 AM, Roy Souther wrote:

CLI = Command Line Interface
GUI = Graphical User Interface
WSI = Web Server Interface

I need a CLI app. Sorry I was not clear on this.

t4esql?

Never used it, but it may be what you need. It may or may not be open
source,
so if that's a personal hangup rather than a best-tool-for-the-job
thing that may
be an issue.

Cheers,
Steve

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@silicontao.com> To: pgsql-general@postgresql.org
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
----------------------------------------------------------------------------------------------

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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of
SCassidy@overlandstorage.com
Sent: Thursday, March 02, 2006 12:14 PM
To: roy@silicontao.com
Cc: pgsql-general@postgresql.org
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@silicontao.com> To:
pgsql-general@postgresql.org
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
----------------------------------------------------------------------------
------------------

On Wed, 2006-03-01 at 16:00 -0700, Roy Souther wrote:

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.

The Perl DBI Shell is intended to be the kind of thing your asking for:

http://search.cpan.org/~tlowery/DBI-Shell-11.93/

It's very basic, but functional. I have used it on Windows for
accessing SQLite. It can connect using DBD::ODBC, but can also use any
other native DBD drivers you might have installed.

Regards
Grant