Subversion Repositories DevTools

//==============================================================================

//

//    ERG TRANSIT SYSTEMS      Licensed software

//    (C) 2006                 All rights reserved

//

//==============================================================================

//

// Description: This program will take an SQL connection information from its

//              commad line arguments.

//

//              The program will listen on stdin for an SQL statement. It will

//              then prepare and execute the statement.

//

//              The results are streamed out of the program on STDOUT

//

//              The program is intended to assist in replacing the DBI::ODBC

//              packages within the JATS deployments scripts.

//

//

// Inputs:

//      Arguments:

//              url             - Fed into the getConnection

//              user            - Fed into the getConnection

//              password        - Fed into the getConnection

//      StdIn:

//              query           - An SQL query

//

// Output:      Data is streamed out sdtout. It is intended to processed by

//              another program, and not a human.

//              Each line constits of a a tag and a data field. The tag describes

//              the data that follows and the progress of the operation.

//

//              Output lines:

//                      Status:xxxxx                        - Indicates program status

//                      Error:xxxxx                         - Error messages

//                      Info:ColumnCount:x                  - SQL info: number of Cols of data

//                      Info:ColumnInfo:x:name:width:type   - SQL info: Names of all of the columns and other data

//                      DataStart:                          - Start of SQL response

//                      Data:xxxx,yyyy,zzzz,...             - SQL response

//                      DataEnd:                            - End of SQL response

//

//              The Data: lines contain coma-seperated fields. The fields are

//                        encoded as 4 bits per byte added to an ascii-0. A cheap

//                        hex encoding.

//

//              Program exist code:     0: Sucess

//                                      1: Error encountered

//

//==============================================================================

package com.erggroup.jats;

import java.io.IOException;

import java.io.InputStreamReader;

import java.io.BufferedReader;

import java.sql.CallableStatement;

import java.sql.Statement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import com.erggroup.jats.TimedBufferedReader;

public class ReleaseManagerSelect

{

  public static void main(String[] args)

  {

    int EndSelect = 0;

    System.out.println( "Status:Program startup" );

    if ( args.length != 3 )

    {

      System.out.println( "Error: Not enough arguments. Need 3, Have " + args.length );

      System.exit( 1 );

    }

    //

    //  Extract user parameters

    //

    String url = args[0];

    String user = args[1];

    String passwd = args[2];

    System.out.println( "Status:url:" + url );

    System.out.println( "Status:user:" + user );

    System.out.println( "Status:passwd:" + passwd );

    //

    //  Use a read routine that will timeout if no activity occurs

    //  the process will exit, otherwise we appear to get stray instances

    //  of java running - if the user control-C while running.

    //

    BufferedReader br = new TimedBufferedReader(new InputStreamReader(System.in));

    String query = null;

    //

    // Initiate the connection

    //

    try

    {

        //  Load the Oracle JDBC driver

        try {

            Class.forName("oracle.jdbc.OracleDriver") ;

            System.out.println("Oracle JDBC driver loaded ok.");

        } catch (Exception e) {

            System.out.println("Exception: " + e.getMessage());

            System.exit(1);

        }

        Connection conn = DriverManager.getConnection( url, user, passwd);

        System.out.println( "Status:Connection Opened" );

        System.out.println( "ConnectionOpened:" );

        //

        //    Process requests

        //

        while ( EndSelect == 0 )

        {

            //

            //    Read in the next select request from the input stream

            //    Only SELECT statements will do something useful, but this could

            //    be extended.

            //

            System.out.println( "Status:ReadyForQuery");

            try {

                query = br.readLine();

            } catch (IOException ioe) {

                System.out.println("Error:IO error trying to read input");

                System.exit(1);

            }

            if ( query.length() <= 0 )

            {

              System.out.println( "Status:Zero length query" );

              EndSelect = 1;

              continue;

            }

            //

            //  Determine the correct operation

            //      SELECT

            //      BEGIN

            //

            String begin = query.toUpperCase();

            if ( begin.startsWith("SELECT") )

            {

                do_select( conn, query );

            }

            else

            {

                do_begin( conn, query );

            }

            //

            //  Ensure end of data is seen, even if an error is encountered

            //

            System.out.println( "DataEnd:");

        }

    conn.close();

    System.out.println( "Status:ConnectionClosed" );

    System.exit( 0 );

    }

    catch( SQLException e )

    {

        System.out.println( "Error:main caught java.sql.SQLException " );

        System.out.println( "Error:" + e.getMessage().replace('\n', '~' ));

        System.out.println( "Status:ConnectionClosed" );

        System.exit( 1 );

    }

  }

    //----------------------------------------------------------------------------

    // FUNCTION           : do_begin

    //

    // DESCRIPTION        : Perform a SELECT operation

    //

    //

    // INPUTS             : conn            - The connection

    //                      query           - The query

    //

    // RETURNS            : nothing

    //

    public static void do_begin(  Connection conn, String query )

    {

        try

        {

            System.out.println( "Status:begin:" + query );

            CallableStatement stmt = conn.prepareCall( query );

            stmt.executeUpdate();

            stmt.close();

            System.out.println( "DataStart:");

        }

        catch( SQLException e )

        {

            System.out.println( "Warning:main caught java.sql.SQLException " );

            System.out.println( "Warning:" + e.getMessage().replace('\n', '~' ));

        }

    }

    //----------------------------------------------------------------------------

    // FUNCTION           : do_select

    //

    // DESCRIPTION        : Perform a SELECT operation

    //

    //

    // INPUTS             : conn            - The connection

    //                      query           - The query

    //

    // RETURNS            : nothing

    //

    public static void do_select(  Connection conn, String query )

    {

          System.out.println( "Status:query:" + query );

          //

          //    Execute the query and return the results to the user

          //    All the results are streamed to the user in one block

          //    It is not possible to interleave requests and fetches at this

          //    level. That should be done by the consiming wrapper.

          //

          try {

              Statement stmt = conn.createStatement();

              stmt.setFetchSize(1000);

              System.out.println( "Status:Statement prepared" );

              ResultSet rset = stmt.executeQuery(query);

              System.out.println( "Status:Query Done" );

              //

              //  Determine the number of columns

              //

              ResultSetMetaData rsmd = rset.getMetaData();

              int numberOfColumns = rsmd.getColumnCount();

              System.out.println( "Info:ColumnCount:" + numberOfColumns );

              // The column count starts from 1

              for (int i = 1; i <= numberOfColumns; i++ ) {

                  System.out.println( "Info:ColumnInfo:" + i 

                                      + ":" + rsmd.getColumnName(i) 

                                      + ":" + rsmd.getColumnDisplaySize(i) 

                                      + ":" + rsmd.getColumnTypeName(i) );

              }

              //

              //    Return all rows to the user

              //

              System.out.println( "DataStart:");

              while (rset.next ())

              {

                for ( int ii = 1; ii <= numberOfColumns; ii++ )

                {

                    if ( ii != 1 )

                        System.out.print( "," );

                    else

                        System.out.print( "Data:" );

                    String s = rset.getString(ii);

                    StringBuffer sb = new StringBuffer (200);

                    //

                    // We have the data as text, but it may have characters in it

                    // that we can't pass through to our user. Need to esacpe

                    //          returns

                    //          new lines

                    //          commas

                    //

                    // Convert all data to text bytes. Not quite HEX. Use 0123456789:;<=>?

                    // Simple and rude way to ensure that all data gets through

                    //

                    if ( s != null )

                    {

                        for (int i = 0; i < s.length (); i++)

                          {

                               char c = s.charAt (i);       // Extract character

                               char c1 = (char)(c & 0x0f);

                               char c2 = (char)((c >> 4) & 0x0f);

                               sb.append( (char)(c2 + '0') );

                               sb.append( (char)(c1 + '0') );

                          }

                    }

                    System.out.print( sb );

                }

                System.out.println();

              }

              //

              //    Release resources

              //

              rset.close();

              stmt.close();

            }

            catch( SQLException e )

            {

              System.out.println( "Warning:main caught java.sql.SQLException " );

              System.out.println( "Warning:" + e.getMessage().replace('\n', '~' ));

            }

    }

}