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                - SQL info: number of Cols of 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 );

              //
              //    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', '~' ));
            }
    }
}