Progress
JDBC Driver Guide


ProgressTest.java

You can change the connection URL to match your own Progress data source, then use ProgressTest.java to test your JDBC connection. You can also cut and paste all or part of the code into your own Java application. This sample program is located at $DLC/src/samples/sql92.

ProgressTest.java
/*************************************************************/
/* Copyright (c) 1984-2000 by Progress Software Corporation  */
/*                                                           */
/* All rights reserved.  No part of this program or document */
/* may be  reproduced in  any form  or by  any means without */
/* permission in writing from Progress Software Corporation. */
/*************************************************************/

//------------------------------------------------------------------------
//
// Module:		ProgressTest.java
//
// Description:			Test program for JDBC API interface.  This java application
//  will connect to a Progress JDBC driver, issue a select statement
//  and display all result columns and rows.
//




import java.net.URL;
import java.sql.*;
import java.io.*;


class  ProgressTest
{
    protected static final boolean debugFlag = true;
    protected static final PrintStream o = System.out;
    protected static final String ColString = "COLUMNS";
    protected static final String TblString = "TABLES";
    protected static final String ViewString = "VIEWS";
    protected static final String CallString = "CALL";
    protected static final String AutocommitString = "AUTOCOMMIT";
    protected static final int MAX_SQLSTMTLEN = 1000;
    
    public static void main (String args[]) 
    {
        try 
        
{
            String url    = null;
            String userid = null;
            String passwd = null;
            String query  = null;
                
            switch (args.length)
            {
            case 0:
                //url   = "jdbc:JdbcProgress:T:isis:testdb";
                url   = "jdbc:JdbcProgress:T:isis:5000:testdb";  
                userid= "sysprogress";
                passwd= "dummy";
                break;
            case 1:
                url   = args[0];
                userid= "sysprogress";
                passwd= "dummy";
                break;
            case 2:
                url   = args[0];
                userid= args[1];
                passwd= "dummy";
                break;
            case 3:
                url   = args[0];
                userid= args[1];
                passwd= args[2];
                break;
            default:
                System.out.println("\nUsage: java/jview ProgressTest " +
                                   " [[[<url>]  <userid>] <passwd>] ");
                
                System.exit(0);
            }
             
           // Load the driver

           Class.forName ("com.progress.sql.jdbc.JdbcProgressDriver");

            java.io.PrintStream      pStream = null;
  
            if (debugFlag)
            {
                 //  Create  a PrintStream using System.out
                pStream = new java.io.PrintStream(System.out, true);
            } 
            else
            {
                //  Create  PrintStream using a file.
                java.io.FileOutputStream outFile   = 
                    new java.io.FileOutputStream("JdbcProgress.log");
                pStream   = new java.io.PrintStream(outFile, true);
            }            

            // Enable JDBC tracing
            DriverManager.setLogStream(pStream);

            
            // Attempt to connect to a driver.  Each one
            // of the registered drivers will be loaded until
            // one is found that can process this URL

            java.util.Properties  prop = new java.util.Properties();
            prop.put("user", userid);
            prop.put("password", passwd);

            // We have to add any other options as additional
            // properties in the prop argument.
            // e.g., prop.put\("Caller", "ProgressTest"\);

            Connection con = DriverManager.getConnection (
                      url, prop);

            // If we were unable to connect, an exception
            // would have been thrown.  So, if we get here,
            // we are successfully connected to the URL 
           // Check for, and display and warnings generated
           // by the connect.

            checkForWarning (con.getWarnings ());


            // o.println("\nConnected to " + url);
     
            // Get the DatabaseMetaData object and display
            // some information about the connection
            
            DatabaseMetaData dma = con.getMetaData ();

             o.println("\nConnected to " + dma.getURL()); 
             o.println("Driver       " + 
                 dma.getDriverName());
             o.println("Version      " +
                 dma.getDriverVersion());
             o.println("");
             if (con.getAutoCommit())
                 o.println("Autocommit is on");
               else
                 o.println("Autocommit is off");
               o.println("");

             byte[] bArray = new byte[MAX_SQLSTMTLEN + 1];
             boolean           rs_exists = false;
             ResultSet         rs   = null;
             PreparedStatement pstmt = null;
             CallableStatement callstmt = null;

            while (true)
            {
                o.print ("ProTest> ");
                o.flush();
               int l = System.in.read (bArray, 0, MAX_SQLSTMTLEN);

                query = new String (bArray, 0, 0, l);

                query = query.trim();
                
                if (query.endsWith(";"))
                {
                    query = query.substring(0, query.length() - 1);
                    query = query.trim();
                } 
               if (query.length() == 0)
                    continue;

                if (query.equalsIgnoreCase("quit"))
                    break;

                // Execute the statement.

                try
                {
                    if (tablesCmd(query))
                    {
                        String[] types = {"TABLE","SYSTEM TABLE"};
                        String   t_patrn= query.substring(TblString.length());
                        t_patrn = t_patrn.trim();  

                        if (t_patrn.length() == 0)
                            rs = dma.getTables("","","%",types);
                        else
                            rs = dma.getTables("","",t_patrn,types);
                        rs_exists = true;
                    } else if (viewsCmd(query) == true)
                    {
                       String[] types = {"VIEW"};
                         String   v_patrn= 
query.substring(ViewString.length());
                        v_patrn = v_patrn.trim();  

                        if (v_patrn.length() == 0)
                            rs = dma.getTables("","","%",types);
                        else
                            rs = dma.getTables("","",v_patrn,types);
                        rs_exists = true;
                    } else if (autocommitCmd(query) == true) 
{
                        String[] types = {"VIEW"};
                        String   a_patrn= query.substring(
AutocommitString.length());
                        a_patrn = a_patrn.trim();  

                        if (a_patrn.equalsIgnoreCase("off"))
                            con.setAutoCommit( false );
                        else if (a_patrn.equalsIgnoreCase("on"))
                            con.setAutoCommit( true );
                        else o.println( "Form is autocommit ( on | off )");
                        if (con.getAutoCommit())
                            o.println("Autocommit is on");
                        else
                            o.println("Autocommit is off");

                    } else if (columnsCmd(query) == true)
                    {
                           String   t_patrn= 
query.substring(ColString.length());
                           t_patrn = t_patrn.trim();   

                        if (t_patrn.length() == 0)
                        {
                            //rs = dma.getColumns("","","%","%");
                            o.println("Specify a table pattern");
                            continue;
                        }
                        else
                            rs = dma.getColumns("","",t_patrn,"%");
                        rs_exists = true;
                    } else if (callStmt(query) == true)
                    {
                        callstmt = con.prepareCall(query);
                        setCallParams(callstmt);
                        rs_exists = callstmt.execute();
                        if (rs_exists == true)
                        {
                            rs = callstmt.getResultSet();
                        } else 
                        {
                            int updCount = callstmt.getUpdateCount();
                            o.println( "RowCount is " + updCount);
                        } 
          } else if (query.equalsIgnoreCase("types"))
                    {
                        rs = dma.getTypeInfo();
                        rs_exists = true;
                    } else if (query.equalsIgnoreCase("commit"))
                    {
                        con.commit();
                        rs_exists = false;
                    } else if (query.equalsIgnoreCase("rollback"))
                    {
                        con.rollback();
                        rs_exists = false;
                    } else if (query.equalsIgnoreCase("?"))
                    { 
                          o.println( "Commands are: "); 
                          o.println( "        TABLES  [name]"); 
                          o.println( "        VIEWS   [name]"); 
                          o.println( "        COLUMNS [name]"); 
                          o.println( "        CALL    [name]"); 
                          o.println( "        types"); 
                          o.println( "        commit"); 
                          o.println( "        rollback"); 
                          o.println( "        autocommit on | off"); 
                          o.println( "        quit"); 
                          o.println( "        ?"); 
                          o.println( "        SQL statement to prepare, 
execute");  
                          o.println( "            -- statement can have 
parms"); 
                    }else
                    {
                        pstmt = con.prepareStatement(query);
                        setParams(pstmt);
                        rs_exists = pstmt.execute();
                        if (rs_exists == true)
                        {
                            rs = pstmt.getResultSet();
                        } else 
                        {
                            int updCount = pstmt.getUpdateCount();
                            o.println( "RowCount is " + updCount);
                        }
                    }
                    if (rs_exists == true) 
             {
                        // Display all columns and rows from the result set
                        dispResultSet (rs);
                        rs.close();
                        rs_exists = false;
                    } 
                }
                catch (SQLException ex) 
                {
                    o.println(ex.getMessage());
                    continue;
                }
                // Close the statement
                if (pstmt != null)
                    pstmt.close();
                pstmt = null;

                if (callstmt != null)
                    callstmt.close();
                callstmt = null;
            }
            // Close the connection
            con.close();
        }
        catch (SQLException ex) 
        {

            // A SQLException was generated.  Catch it and
            // display the error information.  Note that there
            // could be multiple error objects chained
            // together
          o.println ("*** (debugging) in sql exception block...");

            while (ex != null) 
            {
                o.println ("SQLState: " +
                                    ex.getSQLState ());
                o.println ("Message:  " +
                                    ex.getMessage ());
                o.println ("VendorCode:   " +
                                    ex.getErrorCode ());
                ex = ex.getNextException ();
                o.println ("");
            } 
          }
        catch (java.lang.Exception ex)
        {
            o.println ("*** (debugging) in general exception block...");
            // Got some other type of exception.  Dump it.
            ex.printStackTrace ();
        }

        finally
        {
            o.println ("@ProgressTest:finally");
        }

    }

    //-------------------------------------------------------------------
    // checkForWarning
    // Checks for and displays warnings.  Returns true if a warning
    // existed
    //-------------------------------------------------------------------

    private static boolean checkForWarning (SQLWarning warn)
        throws SQLException
        {
            boolean rc = false;
            // If a SQLWarning object was given, display the
            // warning messages.  Note that there could be
            // multiple warnings chained together

            if (warn != null) 
            {
                o.println ("\n *** Warning ***\n");
                rc = true;
                while (warn != null) 
                {
                    o.println ("SQLState: " +
         warn.getSQLState ());
                    o.println ("Message:  " +
         warn.getMessage ());
                    o.println ("Vendor:   " +
         warn.getErrorCode ());
                    o.println ("");
                    warn = warn.getNextWarning ();
                }
            }
            return rc;
        } 
     //-------------------------------------------------------------------
     // dispResultSet
     // Displays all columns and rows in the given result set
     //-------------------------------------------------------------------

    private static void dispResultSet (ResultSet rs)
        throws SQLException
    {
            int i,j;

            // Get the ResultSetMetaData.  This will be used for
            // the column headings

            ResultSetMetaData rsmd = rs.getMetaData ();

            // Get the number of columns in the result set

            int numCols = rsmd.getColumnCount ();

     // Display column headings

            for (i=1; i<=numCols; i++)
            {
                if (i > 1) 
                    o.print(",");

                String label  = rsmd.getColumnLabel(i);
                o.print(label);
            }
            o.println();

            for (i=1; i<=numCols; i++)
            {
                if (i > 1) 
                    o.print("-");
                String label  = rsmd.getColumnLabel(i);
                for (j = 0; j < label.length(); j++)
                    o.print("-");
            }
            o.println(); 
               // Display data, fetching until end of the result set

            while (rs.next ())
            {
                // Loop through each column, getting the
                // column data and displaying

                for (i=1; i<=numCols; i++)
                {
                    if (i > 1) System.out.print(",");
                    o.print(rs.getString(i));
                }
                o.println("");

                // Fetch the next result set row

            }
		}

     //-------------------------------------------------------------------
     // tablesCmd returns true if its a Tables Command
     // else false
     //-------------------------------------------------------------------
    private static boolean tablesCmd (String query)
    {
        
        String u_query = query.toUpperCase();
        if ((u_query.startsWith(TblString)) ||
             (u_query.startsWith(TblString + " ")))
            return true;
        return  false;
    }

      //------------------------------------------------------------------
      // ViewsCmd returns true if its a Views Command
      // else false
      //------------------------------------------------------------------
    private static boolean viewsCmd (String query)
    {
        
        String u_query = query.toUpperCase();
        if ((u_query.startsWith(ViewString)) ||
            (u_query.startsWith(ViewString + " ")))
            return true;
        return  false;
    } 
      //-----------------------------------------------------------------
      // columnsCmd returns true if its a Columns Command
      // else false 

//-------------------------------------------------------------------
    private static boolean columnsCmd (String query)
    {
        
        String u_query = query.toUpperCase();
        if ((u_query.startsWith(ColString)) ||
            (u_query.startsWith(ColString + " ")))
            return true;
        return  false;
    }

   //------------------------------------------------------------------
   // CallStmt returns true if its a Procedure Call Statement
   // else false
   //------------------------------------------------------------------
    private static boolean callStmt (String query)
    {
        
        String u_query = query.toUpperCase();
        if ((u_query.startsWith(CallString)) ||
            (u_query.startsWith(CallString + " ")))
            return true;
        return  false;
    }


     //-------------------------------------------------------------------
     // autocommitCmd returns true if its an autocommit Command
     // else false
     //-------------------------------------------------------------------
    private static boolean autocommitCmd (String query)
    {
        
        String u_query = query.toUpperCase();
        if ((u_query.startsWith(AutocommitString)) ||
            (u_query.startsWith(AutocommitString + " ")))
            return true;
        return  false;
    } 
   //------------------------------------------------------------------
   // setParams prompts for parameters and sets them.
   //-------------------------------------------------------------------
    private static void  setParams (PreparedStatement stmt) 
        throws SQLException
        {
            byte[] bArray  = new byte[MAX_SQLSTMTLEN + 1];
            String param = null;

            int paramCount = ((ProgressPreparedStatement)stmt).getNparams();

            if (paramCount > 0)
                o.println("Parameters Required = " + paramCount );

            try 
            {
                for (int i = 0; i < paramCount; i++)
                {
                    o.print ("Type parameter no " + i +" : ");
                    int l = System.in.read (bArray, 0, MAX_SQLSTMTLEN);
                    
                    param = new String (bArray, 0, 0, l);
                    param = param.trim();

                    if (param.equalsIgnoreCase("null"))
                        stmt.setNull(i+1, java.sql.Types.CHAR);
                    else
                        stmt.setString(i+1, param);
                }
            }
            
            catch (java.lang.Exception ex)
            {
                // Got some other type of exception.  Dump it.
                ex.printStackTrace ();
            }
        } 
   //-------------------------------------------------------------------
   // setCallParams prompts for parameters and sets them.
   //-------------------------------------------------------------------
    private static void  setCallParams (CallableStatement stmt) 
        throws SQLException
        {
            byte[] bArray  = new byte[MAX_SQLSTMTLEN + 1];
            String param = null;

            int paramCount = ((ProgressCallableStatement)stmt).getNparams();

            if (paramCount > 0)
                o.println("Parameters Required = " + paramCount );

            try 
            {
                for (int i = 0; i < paramCount; i++)
                {
                    o.println ("Parameter " + i );

                    while (true)
                    {
                        o.print ("    Type IN , OUT or INOUT : ");

                        int l = System.in.read (bArray, 0, MAX_SQLSTMTLEN);
                    
                        String paramtype = new String (bArray, 0, 0, l);
                        paramtype = paramtype.trim();
                        paramtype = paramtype.toUpperCase();

                        if (paramtype.equalsIgnoreCase("IN"))
                        {
                            o.print ("    Type ParamValue : ");

                            l = System.in.read (bArray, 0, MAX_SQLSTMTLEN);
                    
                            String paramvalue = new String (bArray, 0, 0, l);
                            paramvalue = paramvalue.trim();

                            if (paramvalue.equalsIgnoreCase("null"))
                                stmt.setNull(i+1, java.sql.Types.CHAR);
                            else
                                stmt.setString(i+1, paramvalue);

                            break; 
   } else if (paramtype.equalsIgnoreCase("INOUT"))
                        {
                            o.print ("    Type ParamValue : ");

                            l = System.in.read (bArray, 0, MAX_SQLSTMTLEN);
                    
                            String paramvalue = new String (bArray, 0, 0, l);
                            paramvalue = paramvalue.trim();

                            if (paramvalue.equalsIgnoreCase("null"))
                                stmt.setNull(i+1, java.sql.Types.CHAR);
                            else
                                stmt.setString(i+1, paramvalue);
 
                            stmt.registerOutParameter (i+1, 
                                                        
java.sql.Types.CHAR);
                            break;
                        } else if (paramtype.equalsIgnoreCase("OUT"))
                        {
                            stmt.registerOutParameter (i+1, 
                                                        
java.sql.Types.CHAR);
                            break;
                        } 
                    }
                }
            }
            
            catch (java.lang.Exception ex)
            {
                // Got some other type of exception.  Dump it.
                ex.printStackTrace ();
            }
        } 


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095