Can we call functions using Callable Statements? Explain with an example in JDBC?

Like procedures, you can also create function in a database and store them.

Syntax

Following is the syntax of creating a function in a(MySQL) database:

CREATE FUNCTION Function_Name(input_arguments) RETURNS output_parameter
BEGIN
   declare variables;
   statements . . . . . . . . . .
   return data_type;
   END

Example

Suppose we have a table named Emp in the database with the following content:

+--------+------------+----------------+
| Name   | DOB        | Location      |
+--------+------------+----------------+
| Amit   | 1970-01-08 | Hyderabad      |
| Sumith | 1970-01-08 | Vishakhapatnam |
| Sudha  | 1970-01-05 | Vijayawada     |
+--------+------------+----------------+

Below Given is an example of creating a function. Here, we are creating a function named getDob() which accepts the name of the employee, retrieves and returns the value of DOB column(s).

CREATE FUNCTION getDob(emp_name VARCHAR(50)) RETURNS DATE
BEGIN
declare dateOfBirth DATE;
select DOB into dateOfBirth from EMP where Name = emp_name;
return dateOfBirth;
END

Calling a function using JDBC

You can call a function using CallableStatement object just like stored procedures, to call a function using JDBC program you need to.

• Connect to the database.

• Create a PreparedStatement object and to its constructor pass the function call in String format.

• Set values to the place holders.

• Execute the Callable statement.

Following is the query to call a function from JDBC:

{? = call getDob(?)}

As you observe the query contains place holders (?) just like prepared and callable statements.

In the above query the first place holder represents the return value of the function and the second placeholder represents the input parameter.

You need to register the place holder which represents return value, as an output parameter using the registerOutParameter() method(of the CallableStatement interface). To this method you need to pass an integer value representing the position of the place holder and, an integer variable representing the sql type (of the parameter)

cstmt.registerOutParameter(1, Types.DATE);

Set the value to the input parameter using the setString() method. (since getDoc() function accepts a value of VARCHAR type).

Example

Following JDBC program executes the function getDob and retrieves the results:

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
public class CallingFunctionsUsingCallable2 {
   public static void main(String args[]) throws SQLException {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());

      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/sampleDB";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");

      //Preparing a CallableStatement
      CallableStatement cstmt = con.prepareCall("{? = call getDob(?)}");

      cstmt.registerOutParameter(1, Types.DATE);
      cstmt.setString(2, "Amit");
      cstmt.execute();

      System.out.print("Date of birth: "+cstmt.getDate(1));
   }
}

Output

Connection established......
Date of birth: 1970-01-08