Amir Hasan Amir Hasan - 4 months ago 7
SQL Question

Error in JDBC code calling a stored function which returns a value

I'm trying to print the returned value of a MySQL stored function from the JDBC code which is as follows (I am using MySQL Server 5.7.13):

package jdbc;

import java.sql.DriverManager;
import java.sql.*;
import java.util.Scanner;


public class CallableStatementsCallingFunctions {
public static void main(String... syrt)
{
try
{
try
{
Class.forName("com.mysql.jdbc.Driver");
}
catch(ClassNotFoundException e)
{
System.out.println("Error(class): "+ e);
}
try
{
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/collablestatement","root","mysql") ;
CallableStatement cs = conn.prepareCall("{call ?:=getBalance1(?)}");
String s = new Scanner(System.in).nextLine();
cs.registerOutParameter(1,Types.INTEGER);
cs.setInt(2,Integer.parseInt(s));
cs.execute();
System.out.println("Account number :" + cs.getInt(1));
conn.close();
}
catch(SQLException e)
{
System.out.println("Error(SQL) : "+e);
}
}
catch(Exception e)
{
System.out.println("Error(Fro outer try) : "+ e);
}
}


}

the stored function
getBalance1(acno)
is shown here

getBalance(acno numeric)

my code output is shown here

jdbc output

I am getting the output from the SQL command but in JDBC I am getting and
SQLException
saying that


parameter 1 is not an output parameter


I know that parameter 1 has been used as the placeholder of the returned value from the function in jdbc code. In
prepareCall
I also tried the syntax -
{?:= call getBalance1(?)}
, but even then getting the same Exception.

Why am I getting the exception?

Answer

getBalance1() is a MySQL FUNCTION, not a PROCEDURE, so I wouldn't expect using a JDBC CallableStatement to be applicable.

Even in your MySQL console test you are using

select getBalance1(103)

so you simply need to do the same thing in your Java code using a PreparedStatement:

PreparedStatement ps = conn.prepareStatement("select getBalance1(?)");
ps.setInt(1) = 103;
ResultSet rs = ps.executeQuery();
rs.next();
Double bal = rs.getDouble(1);

(It should be noted that since "balance" apparently refers to "money", REAL is not a good choice for the column type; details here.)

Comments