Thread: how to call parameterized stored procedue in jdbc

Hello i am Olabanji, please i really need your help in calling a parameterized stored procedure in java jdbc from sql server.
The stored procedure goes like this in sql

create proc patientreg
@id int
as
begin
select [patient_id],[Psurname], [pFirstname], [pMiddlename], [reg_date], [DOB], [Sex], [Phone_num], [Addr],[Email],[dbo].[fncomputeage](DOB) from [dbo].[Patient_registration] where [patient_id] = @id
end
please note dbo.fncompute(DOB) is a function

To call it in jdbc it goes like this

try{
String str = "{call patientreg(?)}";
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbcdbc:GeneralHospit al");
cstmt = con.prepareCall(str);
cstmt.setInt(1, Integer.parseInt(t.getText()));


cstmt.execute();


int pid = cstmt.getInt(1);
String sname = cstmt.getString(2);
String fname = cstmt.getString(3);
String mname = cstmt.getString(4);
String regdate = cstmt.getString(5);
String dob = cstmt.getString(6);
String sex = cstmt.getString(7);
String phonenum = cstmt.getString(8);
String address = cstmt.getString(9);
String email = cstmt.getString(10);
int age = cstmt.getInt(11);

l1.setText(sname+""+ fname+""+mname);
l3.setText(Integer.toString(pid));
l4.setText(regdate);
l5.setText(dob);
l6.setText(Integer.toString(age));
l7.setText(sex);
l8.setText(phonenum);
l9.setText(address);
l10.setText(email);
cstmt.close();

}
catch(Exception ex)
{
System.out.println("Error occured");
System.out.println("Error:"+ex);


}

}

after doing it this way it throwing an exception: Error:java.sql.SQLException: Parameter 1 is not an OUTPUT parameter
Please i really need your help on this and i will really appreciate it.

it just saying that the parameter in your stored procedure is not an output parameter. but you still attempt to get it by the statement in your code:
int pid = cstmt.getInt(1);
I believe that that is an input parameter, you also declared this:
cstmt.setInt(1, Integer.parseInt(t.getText()));

can you provide the code for your stored procedure?

create proc patientreg
@id int
as
begin
select [patient_id],[Psurname], [pFirstname], [pMiddlename], [reg_date], [DOB], [Sex], [Phone_num], [Addr],[Email],[dbo].[fncomputeage](DOB) from [dbo].[Patient_registration] where [patient_id] = @id
end

I'm not good in sql server (Actually I have no experience on it).
But in the database that I used now in my work, we declare an output parameter. Output parameter is different from input parameter.

I believe that this is how it goes in sql server:
if you are going to pass Input parameters, then declare it. and if you want an output parameter, declare it also as output

CREATE PROCEDURE MY_PROCEDURE (
@THIS_IS_INPUT_PARAMETER NVARCHAR(50),
@THIS_IS_OUTPUT_PARAMETER NVARCHAR OUTPUT)
AS
SELECT @THIS_IS_OUTPUT_PARAMETER = COLUMN_NAME FROM TABLE_NAME WHERE ANOTHER_COLUMN_NAME = @THIS_IS_INPUT_PARAMETER


in java part, first parameter is input, and second parameter is the output. Meaning there is not third parameter and so on, so getting a value from parameter greater than 2 will cause a sql exception.

I suggest you to study the ref cursor in sql and resultset in java. that might help you do want you want.