Symantec Visual Cafe for Java 2.5/symantec-visual-cafe-2.5-database-dev-edition.iso/DBServ/SAMPLES/SAMPLES.ZIP/JDBCCALL/jdbcCall.java

Java Source | 1997-02-21 | 8.4 KB | 252 lines

//
// Copyright (c) 1996 Symantec.  All Rights Reserved.                                              *
//
// jdbcCall
//
// Author - Ron Emrick
// Date   - 2/1/97
//
// jdbcCall demonstrates how to make stored procedure calls using
// JDBC's CallableStatement class.
//
// Steps:
//   1) Start the Sybase SQL Anywhere 5.0 engine
//   2) Type at the DOS prompt: java jdbcCall
//   3) Watch the output
//
// These stored procedures are designed to work with Sybase SQL Anywhere.
// You should have little trouble porting them to other MS SQL Server,
// Oracle or Sybase SQL Server.  The stored procedure code can be found
// in the comments above each callXXX() method.
//

import java.util.Properties;

import symjava.sql.*;

//////////////////////////////////////////////////////////////////////////
//
// jdbcCall
//
//////////////////////////////////////////////////////////////////////////

class jdbcCall extends JDBCShell
{
    ///////////////////////////////////////////////////////////////////////////
    public static void main( String args[] )
    {
        jdbcCall jCall = new jdbcCall();
        jCall.shellMain( args );
    }

    ///////////////////////////////////////////////////////////////////////////
    protected void init()
    {
        m_strAppName = "jdbcCall";
    }

    ///////////////////////////////////////////////////////////////////////////
    public void execute()
    {
       callInt( m_nTestExecutions );
       callVarChar( m_nTestExecutions );
       callDate( m_nTestExecutions );
       callDouble( m_nTestExecutions );
    }

    ///////////////////////////////////////////////////////////////////////////
    //
    // create procedure "dba".sp_callint(@inVal integer,@outVal integer output) as
    // begin
    //    select @outVal=@inVal*2
    // end
    //
    public void callInt( int iterations )
    {
        try {
            // Drop existing stored procedure
            System.out.println( "Dropping existing stored procedure..." );
            Statement jdbcStmt = m_jdbcConn.createStatement();
            try {
                jdbcStmt.execute( "drop procedure sp_callint" );
            }
            catch( SQLException x ) {
            }

            // Create new stored procedure
            System.out.println( "Creating new stored procedure..." );
            jdbcStmt.execute( "create procedure \"dba\".sp_callint(@inVal integer,@outVal integer output) as " +
                              "begin select @outVal=@inVal*2 end" );
            jdbcStmt.close();

            // Execute CallableStatement
            System.out.println( "Executing stored procedure..." );
            CallableStatement cs = m_jdbcConn.prepareCall( "{ call sp_callint( ?, ? ) }" );
            cs.registerOutParameter( 2, Types.INTEGER );

            int seed = 10;
            for ( int i = 0; i < iterations; i++ ) {
                cs.setInt( 1, seed + i );

                cs.execute();

                int result = cs.getInt( 2 );
                System.out.println( "Integer Output = " + result );
            }

            cs.close();
        }
        catch( SQLException x ) {
            handleException( x, "SQLException", "" );
            return;
        }
    }

    ///////////////////////////////////////////////////////////////////////////
    //
    // create procedure sp_callvc(@inVal varchar(50),@outVal varchar(50) output)
    // begin
    //   select @outVal=@inVal+@inVal
    // end
    //
    public void callVarChar( int iterations )
    {
        try {
            // Drop existing stored procedure
            System.out.println( "Dropping existing stored procedure..." );
            Statement jdbcStmt = m_jdbcConn.createStatement();
            try {
                jdbcStmt.execute( "drop procedure sp_callvc" );
            }
            catch( SQLException x ) {
            }

            // Create new stored procedure
            System.out.println( "Creating new stored procedure..." );
            jdbcStmt.execute( "create procedure sp_callvc(@inVal varchar(50),@outVal varchar(50) output) " +
                              "begin select @outVal=@inVal+@inVal end" );
            jdbcStmt.close();

            // Execute CallableStatement
            CallableStatement cs = m_jdbcConn.prepareCall( "{ call sp_callvc( ?, ? ) }" );
            cs.registerOutParameter( 2, Types.VARCHAR );

            String seed = new String( "dbANYWHERE Server is cool stuff!" );
            for ( int i = 0; i < iterations; i++ ) {
                cs.setString( 1, seed );

                cs.execute();

                String result = cs.getString( 2 );
                System.out.println( "VarChar Output = " + result );
            }

            cs.close();
        }
        catch( SQLException x ) {
            handleException( x, "SQLException", "" );
            return;
        }
    }

    ///////////////////////////////////////////////////////////////////////////
    //
    // create procedure sp_calldate(@inVal date,@outVal date output) as
    // begin
    //   select @outVal=months(@inVal,1)
    //   select @outVal=days(@outVal,1)
    //   select @outVal=years(@outVal,1)
    // end
    //
    public void callDate( int iterations )
    {
        try {
            // Drop existing stored procedure
            System.out.println( "Dropping existing stored procedure..." );
            Statement jdbcStmt = m_jdbcConn.createStatement();
            try {
                jdbcStmt.execute( "drop procedure sp_calldate" );
            }
            catch( SQLException x ) {
            }

            // Create new stored procedure
            System.out.println( "Creating new stored procedure..." );
            jdbcStmt.execute( "create procedure sp_calldate(@inVal date,@outVal date output) as " +
                              "begin " +
                              "select @outVal=months(@inVal,1) " +
                              "select @outVal=days(@outVal,1) " +
                              "select @outVal=years(@outVal,1) " +
                              "end " );
            jdbcStmt.close();

            // Execute CallableStatement
            CallableStatement cs = m_jdbcConn.prepareCall( "{ call sp_calldate( ?, ? ) }" );
            cs.registerOutParameter( 2, Types.DATE );

            Date seed = new Date( 97, 1, 1 );
            for ( int i = 0; i < iterations; i++ ) {
                cs.setDate( 1, seed );

                cs.execute();

                Date result = cs.getDate( 2 );
                System.out.println( "Date Output = " + result.toString() );
            }

            cs.close();
        }
        catch( SQLException x ) {
            handleException( x, "SQLException", "" );
            return;
        }
    }

    ///////////////////////////////////////////////////////////////////////////
    //
    // create procedure sp_calldouble(@inVal double,@outVal double output) as
    // begin
    //   select @outVal=@inVal+3.1415
    // end
    //
    public void callDouble( int iterations )
    {
        try {
            // Drop existing stored procedure
            System.out.println( "Dropping existing stored procedure..." );
            Statement jdbcStmt = m_jdbcConn.createStatement();
            try {
                jdbcStmt.execute( "drop procedure sp_calldouble" );
            }
            catch( SQLException x ) {
            }

            // Create new stored procedure
            System.out.println( "Creating new stored procedure..." );
            jdbcStmt.execute( "create procedure sp_calldouble(@inVal double,@outVal double output) as " +
                              "begin select @outVal=@inVal+3.1415 end" );
            jdbcStmt.close();

            // Execute CallableStatement
            CallableStatement cs = m_jdbcConn.prepareCall( "{ call sp_calldouble( ?, ? ) }" );
            cs.registerOutParameter( 2, Types.DOUBLE );

            double seed = 10;
            for ( int i = 0; i < iterations; i++ ) {
                cs.setDouble( 1, seed + i );

                cs.execute();

                double result = cs.getDouble( 2 );
                System.out.println( "Double Output = " + result );
            }

            cs.close();
        }
        catch( SQLException x ) {
            handleException( x, "SQLException", "" );
            return;
        }
    }

} // class jdbcCall