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

Question

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?

a quick guess is cs.getString("1"); – Mohammadreza Khatami Jul 31 '16 at 13:05 — Mohammadreza Khatami, Jul 31 '16 at 13:05

score 2 · Accepted Answer · edited Aug 01 '16 at 00:32

2

I think I was getting the SQLException because I am using jdk1.8.xx in which the syntax of calling the stored function is different. The problem was solved by replacing statement

CallableStatement cs = conn.prepareCall("{call ?:=getBalance1(?)}");

in the code with

CallableStatement cs = conn.prepareCall("{? = call getBalance1(?)}");

The syntax of calling the function in the prepareCall() method as parameter is here.

edited Aug 01 '16 at 00:32

Mick Mnemonic

7,808
2
26
30

answered Jul 31 '16 at 15:31

user404

117
1
10

I don't think the syntax has changed in any way in Java 8. You simply had a typo (extra colon + misplaced `call` keyword) in your original statement. – Mick Mnemonic Aug 01 '16 at 00:30

score 1 · Answer 2 · edited May 23 '17 at 12:06

1

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

edited May 23 '17 at 12:06

Community

1
1

answered Jul 31 '16 at 13:32

Gord Thompson

116,920
32
215
418

I am getting what you have written here.But then what would the CallableStatement interface be used for if I used PreparedStatement instead?? I am trying to get the results using CallableStatement as I may get dozens of problems to face then. – user404 Jul 31 '16 at 15:24
1

The `CallableStatement` is JDBC's way of running Stored Procedures, which are created in MySQL using `CREATE PROCEDURE` and invoked in MySQL using `CALL procedurename`. You have used `CREATE FUNCTION` to create a user-defined function, which is normally invoked by including it in an expression, e.g., as part of a `SELECT` statement. – Gord Thompson Jul 31 '16 at 16:50
Actually, you can also call SQL `FUNCTION`s with `CallableStatement`s. The JDBC syntax for this is `myConnection.prepareCall("{? = call my_function(?)}");`, as is exemplified in the answer authored by the OP. – Mick Mnemonic Aug 01 '16 at 00:29

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

2 Answers2