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
– Mohammadreza Khatami

2016-07-31 13:05:41 +00:00
Commented Jul 31, 2016 at 13:05 — Mohammadreza Khatami
– Mohammadreza Khatami, Commented Jul 31, 2016 at 13:05

Mick Mnemonic · Accepted Answer · 2016-08-01 00:32:25Z

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 1, 2016 at 0:32

Mick Mnemonic

7,9562 gold badges29 silver badges35 bronze badges

answered Jul 31, 2016 at 15:31

user404

1151 silver badge10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mick Mnemonic Over a year ago

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.

Community · Accepted Answer · 2017-05-23 12:06:46Z

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, 2017 at 12:06

CommunityBot

11 silver badge

answered Jul 31, 2016 at 13:32

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

3 Comments

user404 Over a year ago

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.

Gord Thompson Over a year ago

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.

Mick Mnemonic Over a year ago

Actually, you can also call SQL FUNCTIONs with CallableStatements. The JDBC syntax for this is myConnection.prepareCall("{? = call my_function(?)}");, as is exemplified in the answer authored by the OP.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related