Trouble calling SQL Server stored procedure within Java

Question

Below is a generic class I wrote that calls a stored procedure on the server:

public class StoredProc {

Connection con = null;
ResultSet rs = null;
CallableStatement cs = null;

public StoredProc(String jdbcResource, String storedProcName){

    this(jdbcResource, storedProcName, new String[0], new String[0]);
}

public StoredProc(String jdbcResource, String storedProcName, String[] params,String[]        paramTypes){

    Connection con = new databaseConnection(jdbcResource).getConnection();

    //Get length of parameters and sets stored procs params (?, ?, ...etc)
    String procParams = "";
    int paramSize = params.length;
    if(paramSize != 0){
        for(int i = 0; i < paramSize; i++){
            if(i == paramSize){
                procParams += "?";
            }else{
                procParams += "?, ";
            }
        }
    }

    try{           

        CallableStatement cs = this.con.prepareCall("{?=call "+storedProcName+"  ("+procParams+")}");

        for(int j = 0; j < params.length; j++){
            if (paramTypes[j].equalsIgnoreCase("Int")) {
                int x = 0;
                try{
                    x = Integer.parseInt(params[j]);
                } catch(Exception e) {}
                cs.setInt(j, x);
            } else if (paramTypes[j].equalsIgnoreCase("Boolean")) {
                boolean x = false;
                try{
                    x = (params[j].equalsIgnoreCase("True")) || (params[j].equalsIgnoreCase("T")) || (params[j].equalsIgnoreCase("1")) || (params[j].equalsIgnoreCase("Yes")) || (params[j].equalsIgnoreCase("Y"));
                } catch(Exception e) {}
                cs.setBoolean(j, x);
            } else if (paramTypes[j].equalsIgnoreCase("String")) {
                cs.setString(j, params[j]);
            }
        }

    }catch(Exception e){
        System.out.println("---------------------------------------------");
        System.out.println("Problem constructing callableStatement: "+e);
        System.out.println("---------------------------------------------");
    }

}

public ResultSet runQuery(){

    try{
        rs = cs.executeQuery();
    }catch(SQLException e){
        System.out.println("---------------------------------------------");
        System.out.println("Problem executing stored procedure: "+e);
        System.out.println("---------------------------------------------");
    }
    return rs;

}

public void runUpdate(){

    try{
        cs.executeUpdate();
    }catch(SQLException e){
        System.out.println("---------------------------------------------");
        System.out.println("Problem executing stored procedure: "+e);
        System.out.println("---------------------------------------------");
    }
}

} //end of class

for some reason I'm getting a NullPointerException on the line I'm trying to construct a CallableStatement --> CallableStatement cs = this.con.prepareCall("{?=call "+storedProcName+" ("+procParams+")}");

The callable statement should look like this at run time:

cs = this.con.prepareCall({?=call getUnlinkedDirectdeposits()});

The stored proc is called this in the database: [dbo].[getUnlinkedDirectdeposits]

Any help would be appreciated! Thanks in advance,

wds · Accepted Answer · 2012-03-02 09:26:42Z

2

You are using the wrong "con" variable. In your method you're initialising a variable (local to the method) called con:

Connection con = new databaseConnection(jdbcResource).getConnection();

But then you use this.con, which is the con field of the StoredProc object you're currently executing in. Since it was never initialised, you get a NullPointerException.

answered Mar 2, 2012 at 9:26

wds

32.3k11 gold badges63 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TPete · Accepted Answer · 2012-03-02 09:27:31Z

1

Your Connection field is null!

You create a new Connection instance in StoredProc instead of assigning it to the field con of your class. But when trying to created the CallableStatement your are using the this.con which has not been set before.

answered Mar 2, 2012 at 9:27

TPete

2,0594 gold badges24 silver badges26 bronze badges

Collectives™ on Stack Overflow

Trouble calling SQL Server stored procedure within Java

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related