Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

how to call parameterized stored procedue in jdbc

 
olabanji teebam
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:GeneralHospital");
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.


 
Claude Moore
Ranch Hand
Posts: 829
7
IBM DB2 Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
First, i'd not use jdbc-odbc driver but i'd use sql server jdbc driver.This said, you are correctly calling your procedure, the problem is that it's not going to return out params but a resultset (a cursor) instead. So, after calling ctsmt.execute method, try to get underlying resultset with ctsmt.getResultSet(), and cycle over it ( please note that i may have mispelled actual method names)
Hope this may help you.
 
olabanji teebam
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks but its still throwing that same exception: Error:java.sql.SQLException: Parameter 1 is not an OUTPUT parameter
 
Claude Moore
Ranch Hand
Posts: 829
7
IBM DB2 Java Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you post modified code?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic