hey,
i am using the callable statement with the result type and result concurrency.. but how do we access the data returned by the procedure using results sets?
i wrote a simple procedure just to test the working of it, that is..
create procedure spselectparticulars as
select * from tbl1
GO
please help me out.. thanks
// con is connection object
CallableStatement cstmt = con.prepareCall('{ ? = call method(arg1,arg2)}');
cstmt.registerOutParameter(1,java.lang.String);
String str = cstmt.execute();
Here ? is out parameter which we have define using this line
// cstmt.registerOutParameter(1,java.lang.String);
call is used to call the stored procedure name and method is stored procedure method name and arg1 and arg2 is the IN parameter.
u get the string as a return type when u call cstmt.execute();
The problem is your stored procedure has no output variables, it doesn't return anything, so there is nothing to get.... (in short, this is not a good example of a stored procedure...)
Typically a stored procedure will be defined with OUTPUT parameters. Once the stored procedure executes, these OUTPUT parameters will contain whatever useful tidbits of data were created by the store procedure.
You then use a CallableStatement to call your stored procedure, registering the OUTPUT parameters so you can retrieve them.
Here's a better example that should help:
SQL (We need one parameter in and we return one parameter):
CREATE PROCEDURE dbo.AvailableCreditSp(
@CustNum VARCHAR(50),
@CreditAvailable DECIMAL(18, 5) OUTPUT
)
AS
DECLARE
@OrderBal DECIMAL(18, 5),
@PostedBal DECIMAL(18, 5),
@CreditLimit DECIMAL(18, 5)
SELECT
@OrderBal = order_bal,
@PostedBal = posted_bal,
@CreditLimit = credit_limit
FROM
dbo.CustomerTable
WHERE
cust_num = @CustNum
SET @CreditAvailable = @CreditLimit - (@OrderBal + @PostedBal)
GO
Java:
String cust_num = request.getParameter("cust_num");
if (cust_num != null) {
CallableStatement st = con.prepareCall("{call dbo.AvailableCreditSP(?, ?)}");
st.registerOutParameter(2, Types.DOUBLE);
st.setString(1, cust_num);
st.execute();
double available_credit = st.getDouble(2);
// Do something with available credit value..
}
HTH.
hey thanks frank26 and linxpda for your replies,
i have tested with inputs and outputs to the stored procs and now they work.. but i have still some confusions..
what if the results return multiple records instead of one?
how do we get the results similar to using the resultset.next() method?
hey i tried to bind my callable statement results to a recordset through two methods but it doesnt work.. pls help me out..
below is how my stored procedure looks like...
CREATE PROCEDURE spSelectParticulars (@arg1 INT, @arg2 VARCHAR(60) OUTPUT) AS
DECLARE
@name VARCHAR(60)
Select
@name = Name
FROM
tblParticulars
WHERE
Number = @arg1
SET
@arg2 = @name
GO
and below is the two methods i tried...
try {
CallableStatement cstmt = conn.prepareCall("{CALL spSelectParticulars(?, ?)}");
cstmt.setInt(1, 123);
cstmt.registerOutParameter(2, Types.VARCHAR);
ResultSet rs = cstmt.executeQuery();
rs.next();
rs.getString(2);
msg += rs.getString(2);
rs.close();
}
catch (SQLException e)
{
throw new UnavailableException("error: " + e);
}
conn.close();
the above returns the following msg:
javax.servlet.UnavailableExeption: error: java.sql.SQLException: [Microsoft]SQLServer 2000 Driver for JDBC] No Resultset set was produced.
CallableStatement cstmt = conn.prepareCall("{CALL spSelectParticulars(?, ?)}");
cstmt.setInt(1, 123);
cstmt.registerOutParameter(2, Types.VARCHAR);
cstmt.execute();
ResultSet rs = cstmt.getResultSet();
rs.next();
rs.getString(2);
msg += rs.getString(2);
rs.close();
}
catch (SQLException e)
{
throw new UnavailableException("error: " + e);
}
conn.close();
the below returns the follownig msg:
java.lang.NullPointerException
by sending in 123 should return two Names which are A and B... if i just use cstmt.getString(2);
ill only get B... how can i be able to access all the data collected from the query?
Use this code
try {
CallableStatement cstmt = conn.prepareCall("{CALL spSelectParticulars(?, ?)}");
cstmt.setInt(1, 123);
cstmt.registerOutParameter(2, Types.VARCHAR);
ResultSet rs = cstmt.executeQuery();
while( rs.next()){
msg+ = rs.getString(1); //if u r getting only 1 coloum name
}
System.out.println( " Message "+msg);// will display both A &B
rs.close();
}
catch (SQLException e)
{
throw new UnavailableException("error: " + e);
}
conn.close();
