Welcome to the Java Programming Forums


The professional, friendly Java community. 21,500 members and growing!


The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.


>> REGISTER NOW TO START POSTING


Members have full access to the forums. Advertisements are removed for registered users.

Results 1 to 4 of 4

Thread: how to call parameterized stored procedue in jdbc

  1. #1
    Junior Member
    Join Date
    Aug 2013
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default 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.


  2. #2
    Member
    Join Date
    Oct 2013
    Location
    Manila, Philippines
    Posts
    285
    My Mood
    Amused
    Thanks
    6
    Thanked 64 Times in 61 Posts

    Default Re: how to call parameterized stored procedue in jdbc

    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?

  3. #3
    Junior Member
    Join Date
    Aug 2013
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Re: how to call parameterized stored procedue in jdbc

    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

  4. #4
    Member
    Join Date
    Oct 2013
    Location
    Manila, Philippines
    Posts
    285
    My Mood
    Amused
    Thanks
    6
    Thanked 64 Times in 61 Posts

    Default Re: how to call parameterized stored procedue in jdbc

    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.

Similar Threads

  1. Replies: 1
    Last Post: March 17th, 2014, 04:11 AM
  2. can I create an array in a parameterized class
    By mia_tech in forum What's Wrong With My Code?
    Replies: 2
    Last Post: July 7th, 2012, 08:13 PM
  3. Replies: 1
    Last Post: May 1st, 2011, 11:13 AM
  4. Not Generic; cannot be parameterized
    By javaoo in forum What's Wrong With My Code?
    Replies: 3
    Last Post: September 15th, 2010, 10:53 AM
  5. Is it possible to export Stored Procedures using JDBC?
    By sid7 in forum JDBC & Databases
    Replies: 0
    Last Post: August 28th, 2010, 06:34 PM