#!/bin/sh
#
# This is a shell archive.  To extract its contents,
# execute this file with /bin/sh to create the file(s):
#
# jselect.java	    jselect.readme
#
# This shell archive created: Fri May 28 12:29:33 CDT 1999
#
echo "Extracting file jselect.readme"
sed -e 's/^X//' <<\SHAR_EOF > jselect.readme
X1. Introduction
X===============
XThe program jselect.java provides a user-interface to run SELECT statements
Xagainst an Informix database and output the query results in either tabular,
Xcolumnar or pipe-delimited format, with or without a header line.
X
XI created this program mainly to use in scripts that access the database,
Xso I could get the results back in a known format regardless of my SQL. 
XThis does not happen with dbaccess, since it "intelligently" formats the
Xoutput depending on the output width.
X
XHowever, this script can be used interactively as well. To specify options
Xthat need to be set often, you can reduce typing by specifying a config
Xfile with the --file (or -f) option. The program will read both the config
Xfile, if specified, and the options set from the command line, with the
Xoptions on the command line overriding the ones in the file.
X
X2. Environment
X==============
XTo run this program you will need to have java installed as well as the
XInformix JDBC driver. I have tested this program with the following 
Xconfiguration:
X
XJava Development Kit 1.1.6
XInformix JDBC driver 1.40.JC2
XInformix Dynamic Server 7.30.UC3
XSun OS 5.5.1
X
XHowever, as long as your setup has a compatible set of the above softwares,
XI dont think you should have any problems with compiling and running the
Xprogram.
X
X3. Installation
X===============
XPrior to compiling and running this, make sure that you have the Java
XDevelopment Kit and the Informix JDBC driver installed.
X
XTo install the program, make sure you have java and javac in your path.
XTo figure this out, type the following at your command prompt:
X
X    $ which java
X    $ which javac
X
XIf it gives you a message like "no java in ...", then you need to set 
Xyour environment variable PATH to point to it.
X
XCheck to see if your CLASSPATH is pointing to the ifxjdbc.jar file. This
Xfile contains the Informix JDBC driver. If it is not, you need to set the
XCLASSPATH to point to this. Also you need to have CLASSPATH point to the
Xdirectory where you will install the class file jselect.class. For example,
Xif you have installed your JDBC driver in /usr/informix/jdbc and you are
Xinstalling the jselect.class file in /home/informix/tools, then your 
XCLASSPATH should minimally contain:
X
X	CLASSPATH=/usr/informix/jdbc/lib/ifxjdbc.jar:/home/informix/tools
X
XTo compile the jselect.java file, simply run the following command at the
Xprompt:
X
X    $ javac jselect.java
X
X4. Usage
X========
XThis is how you would call the jselect.class file.
X
XUsage: java jselect [switch=value ...]
XSwitches:
X--host|-h:      Host String in the format hostname:port/database@server,
X                where hostname = machine name, port = TCP port-# for instance,
X                database = database name, and server = value of INFORMIXSERVER
X--auth|-a:      Authentication String in the format username/password,
X                where username = Linux login name and password = password
X                for the database user.
X--style|-s:     Determines the output style. Valid values are col(umnar),
X                tab(ular) and pip(e-delimited).
X--title|-t:     Determines if titles appear in the output. Valid values
X                are yes and no.
X--query|-q:     SQL query to be executed. The program does not do any
X                syntax checks, but behaves strangely on invalid SELECT syntax
X--file|-f:      Full path name of a file that contains the rest of the
X                switches in name=value format, just like the command
X                line arguments.                                                
X
XYou can use either the GNU-style -- "long" options or the regular - "short"
Xoptions or a combination of the two. You can specify some or all of the 
Xswitches on the command line or in the file. In the file, however, you
X*must* use the long -- form to specify arguments. Here is a sample file,
Xcalled jselect.conf, for example:
X
X--host=avalanche:1101/strax@avalanche_soc
X--auth=sujit/xxx
X--query=SELECT * FROM systables WHERE tabid > 99
X--title=yes
X--style=tab 
X
XAnd here is the command line to call jselect with the config file parameters
X
X    java jselect --file=jselect.conf
X
XHere is an example command line call to jselect without the config file:
X
X    java jselect --host=avalanche:1101/strax@avalanche_soc \
X      --auth=sujit/xxx --title=yes --style=tab \
X      --query="SELECT * FROM systables WHERE tabid > 99"
X
XOut of the switches, the --style and --title are optional. If they are not
Xspecified, then the program defaults them to --style = tab[ular] and 
X--title = no. All other switches are mandatory.
X
X5. Copyright and Contact Information
X====================================
XThis program was written by Sujit Pal and is freely distributable under
Xthe conditions of the GNU General Public License. Please mail suggestions 
Xfor improvement or bugs to the author at the following email address: 
Xsujitpl@ibm.net.
SHAR_EOF
if [ `wc -c < jselect.readme` -ne     4872 ]
then
    echo "Lengths do not match -- Bad Copy of jselect.readme"
fi
echo "Extracting file jselect.java"
sed -e 's/^X//' <<\SHAR_EOF > jselect.java
X/*
X * jselect.java
X * 
X * This provides a user-interface to an Informix database for
X * executing SELECTs using the Informix JDBC driver interface.
X *
X * Author: Sujit Pal              Date Written: May 15 1999
X *
X */
X
Ximport java.util.*;
Ximport java.io.*;
Ximport java.sql.*;
X
Xpublic class jselect
X{
X	// Environment Variables
X	private static String envHost;
X	private static String envAuth;
X	private static String envTitle;
X	private static String envStyle;
X	private static String envQuery;
X	private static String envFile;
X
X	private static Properties configFile;
X	private static int argCount;
X	private static String hostName;
X	private static String portNumber;
X	private static String dbName;
X	private static String serverName;
X	private static String userName;
X	private static String passWord;
X	private static String sqlQuery;
X
X	private static Connection conn;
X	private static Statement stmt;
X
X	/*
X	 * The main method calls the parseArgs() method to scan for
X	 * command line args and the parseConfig() method to load up
X	 * arguments from the command file. It then passes off the 
X	 * SQL to the select() method which processes the query on 
X	 * the database and returns the results.
X	*/
X	public static void main(String [] args)
X	{
X		// Read arguments off the command line
X		parseArgs(args);
X		parseConfig();
X		try
X		{
X			select(envQuery, envTitle, envStyle);
X		}
X		catch (SQLException sqle)
X		{
X			System.out.println(sqle.getErrorCode() + ": " 
X			  + sqle.getMessage());
X			// sqle.printStackTrace();
X		}
X	
X	} // main()
X
X	/*
X	 * This method picks up the arguments specified on the
X	 * command line.
X	*/
X	private static void parseArgs(String [] args)
X	{
X		int argCount;
X
X		for (argCount=0; argCount < args.length; argCount++)
X		{
X			if ((args[argCount].indexOf("--host=") == 0) ||
X				(args[argCount].indexOf("-h=") == 0))
X			{
X				envHost = args[argCount];
X				envHost = envHost.substring(envHost.indexOf("=") + 1,
X				  envHost.length());
X			}
X			if ((args[argCount].indexOf("--auth=") == 0) ||
X				(args[argCount].indexOf("-a=") == 0))
X			{
X				envAuth = args[argCount];
X				envAuth = envAuth.substring(envAuth.indexOf("=") + 1,
X				  envAuth.length());
X			}
X			if ((args[argCount].indexOf("--title=") == 0) ||
X				(args[argCount].indexOf("-t=") == 0))
X			{
X				envTitle = args[argCount];
X				envTitle = envTitle.substring(envTitle.indexOf("=") + 1,
X				  envTitle.length());
X			}
X			if ((args[argCount].indexOf("--style=") == 0) ||
X				(args[argCount].indexOf("-s=") == 0))
X			{
X				envStyle = args[argCount];
X				envStyle = envStyle.substring(envStyle.indexOf("=") + 1,
X				  envStyle.length());
X			}
X			if ((args[argCount].indexOf("--query=") == 0) ||
X				(args[argCount].indexOf("-q=") == 0))
X			{
X				envQuery = args[argCount];
X				envQuery = envQuery.substring(envQuery.indexOf("=") + 1,
X				  envQuery.length());
X			}
X			if ((args[argCount].indexOf("--file=") == 0) ||
X				(args[argCount].indexOf("-f=") == 0))
X			{
X				envFile = args[argCount];
X				envFile = envFile.substring(envFile.indexOf("=") + 1,
X				  envFile.length());
X			}
X		}
X
X	} // parseArgs()
X
X	/*
X	 * This method reads arguments off the config file if specified
X	 * with a --file= option.
X	*/
X	private static void parseConfig()
X	{
X
X		// Read arguments from config file, if they exist. Note
X		// that command line args will override the ones in the
X		// config file, so if a command line argument already 
X		// exists, then do not read from the config file.
X		if (envFile != null)
X		{
X			try
X			{
X				configFile = new Properties();
X				configFile.load(new FileInputStream(envFile));
X			}
X			catch (FileNotFoundException fnfe)
X			{
X				System.out.println("File not found: " + envFile);
X				usage();
X				// fnfe.printStackTrace();
X				System.exit(1);
X			}
X			catch (IOException ioe)
X			{
X				System.out.println("IO Exception: " + envFile);
X				usage();
X				// ioe.printStackTrace();
X				System.exit(1);
X			}
X			if (envHost == null)
X			{
X				envHost = (String) configFile.get("--host");
X			}
X			if (envAuth == null)
X			{
X				envAuth = (String) configFile.get("--auth");
X			}
X			if (envTitle == null)
X			{
X				envTitle = (String) configFile.get("--title");
X			}
X			if (envStyle == null)
X			{
X				envStyle = (String) configFile.get("--style");
X			}
X			if (envQuery == null)
X			{
X				envQuery = (String) configFile.get("--query");
X			}
X		}
X
X		// Check if any arguments are still empty. If so, print
X		// usage and exit.
X		if ((envHost == null) || (envAuth == null) || 
X		  (envQuery == null))
X		{
X			usage();
X			System.exit(1);
X		}
X
X		// Parse the arguments
X		hostName = envHost.substring(0, envHost.indexOf(":"));
X		envHost = envHost.substring(envHost.indexOf(":") + 1, 
X		  envHost.length());
X		portNumber = envHost.substring(0, envHost.indexOf("/"));
X		envHost = envHost.substring(envHost.indexOf("/") + 1, 
X		  envHost.length());
X		dbName = envHost.substring(0, envHost.indexOf("@"));
X		serverName = envHost.substring(envHost.indexOf("@") + 1,
X		  envHost.length());
X		// Check if envHost parsed OK. If not print usage and exit.
X		if ((hostName == null) || (portNumber == null) ||
X			(dbName == null) || (serverName == null))
X		{
X			usage();
X			System.exit(1);
X		}
X
X		userName = envAuth.substring(0, envAuth.indexOf("/"));
X		passWord = envAuth.substring(envAuth.indexOf("/") + 1, 
X		  envAuth.length());
X		// Check if envAuth parsed OK. If not print usage and exit.
X		if ((userName == null) || (passWord == null))
X		{
X			usage();
X			System.exit(1);
X		}
X
X		if (envTitle == null)
X		{
X			envTitle = "no";
X		}
X
X		if (envStyle == null)
X		{
X			envStyle = "tab";
X		}
X
X	} // parseConfig()
X
X	/*
X	 * The select() method opens the connection to the database
X	 * and executes the SQL statement passed in, and returns the
X	 * result to STDOUT.
X	*/
X	private static void select(String sqlQuery, 
X	  String envTitle, String envStyle) throws SQLException
X	{
X		String sqlResult = "";
X		String url = " ";
X		int cols = 0;
X		int pos = 0;
X		String colName, colValue;
X		int colHeadSize, colDispSize, colRealSize;
X		int pad = 0;
X
X		try
X		{
X			Class.forName("com.informix.jdbc.IfxDriver");
X			url = "jdbc:informix-sqli://" + hostName + 
X			  ":" + portNumber + "/" + dbName + ":informixserver=" + 
X			  serverName + ";user=" + userName + ";password=" + 
X			  passWord;
X			// System.out.println(url);
X			conn = DriverManager.getConnection(url);
X			stmt = conn.createStatement();
X		}
X		catch (SQLException sqle)
X		{
X			throw sqle;
X		}
X		catch (Exception e)
X		{
X			System.out.println("Error: " + e.getMessage());
X			// e.printStackTrace();
X		}
X
X		try
X		{
X			if (envStyle.equals("tab"))
X			{
X				ResultSet rs = stmt.executeQuery(sqlQuery);
X				ResultSetMetaData rsmd = rs.getMetaData();
X				cols = rsmd.getColumnCount();
X				for (pos = 1; pos <= cols; pos++)
X				{
X					colName = rsmd.getColumnName(pos);
X					colHeadSize = rsmd.getColumnDisplaySize(pos);
X					colRealSize = colName.length();
X					colHeadSize = (colRealSize >= colHeadSize) ? 
X				  	colRealSize : colHeadSize;
X					for (pad = colRealSize; pad <= colHeadSize; pad++)
X					{
X						colName += " ";
X					}
X					sqlResult += colName;
X				}
X				if (envTitle.equals("yes"))
X				{
X					sqlResult += "\n";
X					System.out.println(sqlResult);
X					sqlResult = "";
X				}
X				else
X				{
X					sqlResult = "";
X				}
X				while (rs.next())
X				{
X					for (pos = 1; pos <= cols; pos++)
X					{
X						colValue = rs.getString(pos);
X						if (colValue == null)
X						{
X							colValue = " ";
X						}
X						colHeadSize = rsmd.getColumnName(pos).length();
X						colDispSize = rsmd.getColumnDisplaySize(pos);
X						colRealSize = colValue.length();
X						colDispSize = (colHeadSize >= colDispSize) ?
X					  	colHeadSize : colDispSize;
X						colDispSize = (colRealSize >= colDispSize) ? 
X					  	colRealSize : colDispSize;
X						for (pad = colRealSize; pad <= colDispSize; pad++)
X						{
X							colValue += " ";
X						}
X						sqlResult += colValue;
X					}
X					System.out.println(sqlResult);
X					sqlResult = "";
X				}
X			}
X			if (envStyle.equals("col"))
X			{
X				ResultSet rs = stmt.executeQuery(sqlQuery);
X				ResultSetMetaData rsmd = rs.getMetaData();
X				cols = rsmd.getColumnCount();
X				while (rs.next())
X				{
X					for (pos = 1; pos <= cols; pos++)
X					{
X						colName = rsmd.getColumnName(pos);
X						colHeadSize = colName.length();
X						for (pad = colHeadSize; pad <= 18; pad++)
X						{
X							colName += " ";
X						}
X						colValue = rs.getString(pos);
X						if (envTitle.equals("yes"))
X						{
X							sqlResult = colName + ":" + colValue;
X						}
X						else
X						{	
X							sqlResult = colValue;
X						}
X						System.out.println(sqlResult);
X					}
X					System.out.println(" ");
X				}
X			}
X			if (envStyle.equals("pip"))
X			{
X				ResultSet rs = stmt.executeQuery(sqlQuery);
X				ResultSetMetaData rsmd = rs.getMetaData();
X				cols = rsmd.getColumnCount();
X				for (pos = 1; pos <= cols; pos++)
X				{
X					colName = rsmd.getColumnName(pos);
X					sqlResult += colName.trim() + "|";
X				}
X				if (envTitle.equals("yes"))
X				{
X					sqlResult += "\n";
X					System.out.println(sqlResult);
X					sqlResult = "";
X				}
X				else
X				{
X					sqlResult = "";
X				}
X				while (rs.next())
X				{
X					for (pos = 1; pos <= cols; pos++)
X					{
X						colValue = rs.getString(pos);
X						if (colValue == null)
X						{
X							colValue = "";
X						}
X						sqlResult += colValue.trim() + "|";
X					}
X					System.out.println(sqlResult);
X					sqlResult = "";
X				}
X			}
X			stmt.close();
X			conn.close();
X		}
X		catch (SQLException sqle)
X		{
X			throw sqle;
X		}
X		catch (Exception e)
X		{
X			System.out.println("Error: " + e.getMessage());
X		}
X
X	} // select()
X
X	private static void usage()
X	{
X		System.out.println("Usage: java jselect [switch=value ...]");
X		System.out.println("Switches:");
X		System.out.println("--host|-h:\tHost String in the format " +
X		  "hostname:port/database@server,"); 
X		System.out.println("\t\twhere hostname = machine name, " + 
X		  "port = TCP port-# for instance,");
X		System.out.println("\t\tdatabase = database name, and " +
X		  "server = value of INFORMIXSERVER");
X		System.out.println("--auth|-a:	Authentication String in the " +
X		  "format username/password,");
X		System.out.println("\t\twhere username = Linux login name " +
X		  "and password = password");
X		System.out.println("\t\tfor the database user.");
X		System.out.println("--style|-s:	Determines the output style. " +
X		  "Valid values are col(umnar),");
X		System.out.println("\t\ttab(ular) and pip(e-delimited).");
X		System.out.println("--title|-t:	Determines if titles appear " +
X		  "in the output. Valid values");
X		System.out.println("\t\tare yes and no.");
X		System.out.println("--query|-q:	SQL query to be executed. " +
X		  "The program does not do any");
X		System.out.println("\t\tsyntax checks, but behaves strangely " +
X		  "on invalid SELECT syntax");
X		System.out.println("--file|-f:	Full path name of a file " +
X		  "that contains the rest of the");
X		System.out.println("\t\tswitches in name=value format, " +
X		  "just like the command");
X		System.out.println("\t\tline arguments.");
X
X	} // usage()
X
X} // JSelect
SHAR_EOF
if [ `wc -c < jselect.java` -ne    10991 ]
then
    echo "Lengths do not match -- Bad Copy of jselect.java"
fi
echo "Done."
exit 0