Find records not working Jdbc stored procedure

I’m a beginner of writing JDBC stored procedures. I created the stored procedure successfully. If I find the record, I couldn’t find it. I get the error:

Check connectionjava.sql.SQLException: Can’t set IN parameter for return value of stored function call.

I attached the code that I tried so far

 public class JdbcFind {
    
        public static void main(String[] args) {
    Connection con=null;
            
        String id,fname,lname;
    
                try {
                    Class.forName("com.mysql.jdbc.Driver");
                    con = DriverManager.getConnection("jdbc:mysql://localhost/abclibrary", "root","");  
                    CallableStatement stat1 = con.prepareCall("{?=call authors(?,?)}");
    
                
                    stat1.registerOutParameter(1,Types.VARCHAR);
            stat1.setString(2, "1");
            stat1.registerOutParameter(3,Types.VARCHAR);
    
                    stat1.execute();
                    int k = stat1.getInt(1);
    
                    if(k == 0)
                    {
                        System.out.println("Customer id Not FOund");
                        System.exit(0);
                    }
    
                    System.out.println(stat1.getString(1));
                    System.out.println("   ");
                    System.out.println(stat1.getString(2));
                    
                    }catch(ClassNotFoundException cle)
                    {
                    System.out.println("check your jdbc connection" );
    
    
                    }catch(SQLException sqe)
                    {
                    System.out.println("Check connection" +sqe);
                    }finally{
                            try{
                                if(con != null)
                                con.close();
                                }catch(Exception e){}
                        }
    
            }

Stored procedure:

 String str = "CREATE PROCEDURE authors (p_auth_id varchar(15), OUT p_auth_fname varchar(20), OUT p_auth_lname varchar(20))n" +
                      "BEGINn" +
                       "  SELECT auth_fname, auth_lname INTO p_auth_fname, p_auth_lname FROM authors where auth_id = p_auth_id;n" +
                       "END;";

Answer

Your callable statement has three parameters (question marks). According to the JavaDoc you are using the form with a result parameter, which means that you must set it as an OUT parameter.

To clarify I’ve numbered the parameters for you:

{?=call authors(?,?)}
 1              2 3

Without knowing the definition of your stored procedure I would guess that it takes one IN parameter, has one OUT parameter and returns a result. In that case you should set the parameters as follows:

stat1.registerOutParameter(1,Types.VARCHAR);
stat1.setString(2, "1");
stat1.registerOutParameter(3,Types.VARCHAR);

---

Edit:

With the stored procedure defined as

CREATE PROCEDURE authors (p_auth_id varchar(15), OUT p_auth_fname varchar(20), OUT p_auth_lname varchar(20))

it has three parameters (the first one in IN parameter, the second and third are OUT parameters).

Therefore the correct way to call it is:

    CallableStatement stat1 = con.prepareCall("{call authors(?,?,?)}");

            
    stat1.setString(1, "1");
    stat1.registerOutParameter(2,Types.VARCHAR);
    stat1.registerOutParameter(3,Types.VARCHAR);

You will also have to drop these lines:

/*
    int k = stat1.getInt(1);

    if(k == 0)
    {
        System.out.println("Customer id Not FOund");
        System.exit(0);
    }
*/

because the parameter 1 is an IN-only parameter (and your stored procedure doesn’t return any other value besides the OUT-parameters).

To read the result from the OUT-parameters you also have to adapt the parameter numbers:

System.out.println(stat1.getString(2));
System.out.println("   ");
System.out.println(stat1.getString(3));

---

Unfortunately I don’t know enough about stored procedures to tell you how you can find out if there was any row found at all.

I could imagine that if the query in your stored procedure didn’t find any rows then both OUT parameters would return null.