JDBC Sample CallableStatementSample needs to be fixed

[Aruna Sadashiva]

The JDBC CallableStatementSample needs to be fixed.
1. Have to create a library before creating procedure and should point to jar file, not class file.
2. Create procedure statement has to specify library name and remove external path. Also, the example SPJ does not have SQL, so no need for CONTAINS SQL clause.
3. Should not print cstmt, its printing some internal statement handle id.
4. README needs to be fixed: the class file should be put in a jar file. And the jar file has to be copied to all nodes in a cluster.

Here is the fixed sample code:

// @@@ START COPYRIGHT @@@
//
// (C) Copyright 2013-2014 Hewlett-Packard Development Company, L.P.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
//
// @@@ END COPYRIGHT @@@

import java.sql.*;
public class CallableStatementSample
{
    public static void main(String args[]) throws Exception
    {
        try
        {
            Connection conn = Utils.getPropertiesConnection();
            Statement stmt = conn.createStatement();

            try
            {
              String st = "drop procedure CallableStatementSample";
              try {
              stmt.executeUpdate(st);
              } catch (Exception e) {}
       st = "drop library qaspj";
              try {
              stmt.executeUpdate(st);
              } catch (Exception e) {}
       st = "create library qaspj file '/home/trafodion/qaspj.jar'";
       stmt.executeUpdate(st);
              st = "create procedure CallableStatementSample(out OUT_PARAM INTEGER) EXTERNAL NAME 'IntegerSPJ.Integer_Proc(int[])' LANGUAGE JAVA PARAMETER STYLE JAVA NO ISOLATE LIBRARY QASPJ";
              stmt.executeUpdate(st);
              stmt.close();
            }
            catch (SQLException e)
            {
              e.printStackTrace();
              System.exit(0);
            }

            // get the CallableStatement object
            CallableStatement cstmt = conn.prepareCall("{call CallableStatementSample(?)}");
            //System.out.println("The Callable Statement " + cstmt);

            //register the output parameters
            cstmt.registerOutParameter(1, java.sql.Types.VARCHAR);

            //execute the procedure
            cstmt.execute();

            //invoke getInt method
            int nRetVal = cstmt.getInt(1);

            System.out.println("Out parameter is " + cstmt.getInt(1));
        }
        catch(Exception ex)
        {
            System.err.println("Unexpected Exception" + ex.getMessage());
            throw new Exception("Call to getWarnings is Failed!");
        }
    }
}

[Kevin Xu (kai-hua-xu)]

1. Have to create a library before creating procedure and should point to jar file, not class file.
To do: document the correct process in README

2. Create procedure statement has to specify library name and remove external path. Also, the example SPJ does not have SQL, so no need for CONTAINS SQL clause.
To do: Library is required. Remove CONTAINS SQL

3. Should not print cstmt, its printing some internal statement handle id.
To do: comment the log

4. README needs to be fixed: the class file should be put in a jar file. And the jar file has to be copied to all nodes in a cluster.
To do: document this comments in README