CallableStatement Interface

To call the stored procedures and functions, CallableStatement interface is used.

We can have business logic on the database by the use of stored procedures and functions that will make the performance better because these are precompiled.

Suppose you need the get the age of the employee based on the date of birth, you may create a function that receives date as the input and returns age of the employee as the output.

---

What is the difference between stored procedures and functions.

The differences between stored procedures and functions are given below:

Stored Procedure	Function
is used to perform business logic.	is used to perform calculation.
must not have the return type.	must have the return type.
may return 0 or more values.	may return only one values.
We can call functions from the procedure.	Procedure cannot be called from function.
Procedure supports input and output parameters.	Function supports only input parameter.
Exception handling using try/catch block can be used in stored procedures.	Exception handling using try/catch can't be used in user defined functions.

---

How to get the instance of CallableStatement?

The prepareCall() method of Connection interface returns the instance of CallableStatement. Syntax is given below:

1. public CallableStatement prepareCall("{ call procedurename(?,?...?)}");  

The example to get the instance of CallableStatement is given below:

1. CallableStatement stmt=con.prepareCall("{call myprocedure(?,?)}");  

It calls the procedure myprocedure that receives 2 arguments.

---

Full example to call the stored procedure using JDBC

To call the stored procedure, you need to create it in the database. Here, we are assuming that stored procedure looks like this.

1. create or replace procedure "INSERTR"  
2. (id IN NUMBER,  
3. name IN VARCHAR2)  
4. is  
5. begin  
6. insert into user420 values(id,name);  
7. end;  
8. /     

The table structure is given below:

1. create table user420(id number(10), name varchar2(200));  

In this example, we are going to call the stored procedure INSERTR that receives id and name as the parameter and inserts it into the table user420. Note that you need to create the user420 table as well to run this application.

1. import java.sql.*;  
2. public class Proc {  
3. public static void main(String[] args) throws Exception{  
4.   
5. Class.forName("oracle.jdbc.driver.OracleDriver");  
6. Connection con=DriverManager.getConnection(  
7. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");  
8.   
9. CallableStatement stmt=con.prepareCall("{call insertR(?,?)}");  
10. stmt.setInt(1,1011);  
11. stmt.setString(2,"Amit");  
12. stmt.execute();  
13.   
14. System.out.println("success");  
15. }  
16. }  

Now check the table in the database, value is inserted in the user420 table.

---

Example to call the function using JDBC

In this example, we are calling the sum4 function that receives two input and returns the sum of the given number. Here, we have used the registerOutParameter method of CallableStatement interface, that registers the output parameter with its corresponding type. It provides information to the CallableStatement about the type of result being displayed.

The Types class defines many constants such as INTEGER, VARCHAR, FLOAT, DOUBLE, BLOB, CLOB etc.

Let's create the simple function in the database first.

1. create or replace function sum4  
2. (n1 in number,n2 in number)  
3. return number  
4. is   
5. temp number(8);  
6. begin  
7. temp :=n1+n2;  
8. return temp;  
9. end;  
10. /  

Now, let's write the simple program to call the function.

1. import java.sql.*;  
2.   
3. public class FuncSum {  
4. public static void main(String[] args) throws Exception{  
5.   
6. Class.forName("oracle.jdbc.driver.OracleDriver");  
7. Connection con=DriverManager.getConnection(  
8. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");  
9.   
10. CallableStatement stmt=con.prepareCall("{?= call sum4(?,?)}");  
11. stmt.setInt(2,10);  
12. stmt.setInt(3,43);  
13. stmt.registerOutParameter(1,Types.INTEGER);  
14. stmt.execute();  
15.   
16. System.out.println(stmt.getInt(1));  
17.           
18. }  
19. }  

Output: 53

Retrieve file from Oracle database

The getClob() method of PreparedStatement is used to get file information from the database.

Syntax of getClob method

1. public Clob getClob(int columnIndex){}  

Let's see the table structure of this example to retrieve the file.

1. CREATE TABLE  "FILETABLE"   
2.    (    "ID" NUMBER,   
3.     "NAME" CLOB  
4.    )  
5. /  

The example to retrieve the file from the Oracle database is given below.

1. import java.io.*;  
2. import java.sql.*;  
3.   
4. public class RetrieveFile {  
5. public static void main(String[] args) {  
6. try{  
7. Class.forName("oracle.jdbc.driver.OracleDriver");  
8. Connection con=DriverManager.getConnection(  
9. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");  
10.               
11. PreparedStatement ps=con.prepareStatement("select * from filetable");  
12. ResultSet rs=ps.executeQuery();  
13. rs.next();//now on 1st row  
14.               
15. Clob c=rs.getClob(2);  
16. Reader r=c.getCharacterStream();              
17.               
18. FileWriter fw=new FileWriter("d:\\retrivefile.txt");  
19.               
20. int i;  
21. while((i=r.read())!=-1)  
22. fw.write((char)i);  
23.               
24. fw.close();  
25. con.close();  
26.               
27. System.out.println("success");  
28. }catch (Exception e) {e.printStackTrace();  }  
29. }  
30. }  

Store file in Oracle database

The setCharacterStream() method of PreparedStatement is used to set character information into the parameterIndex.

Syntax:

1) public void setBinaryStream(int paramIndex,InputStream stream)throws SQLException

2) public void setBinaryStream(int paramIndex,InputStream stream,long length)throws SQLException

For storing file into the database, CLOB (Character Large Object) datatype is used in the table. For example:

1. CREATE TABLE  "FILETABLE"   
2.    (    "ID" NUMBER,   
3.     "NAME" CLOB  
4.    )  
5. /  

1. import java.io.*;  
2. import java.sql.*;  
3.   
4. public class StoreFile {  
5. public static void main(String[] args) {  
6. try{  
7. Class.forName("oracle.jdbc.driver.OracleDriver");  
8. Connection con=DriverManager.getConnection(  
9. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");  
10.               
11. PreparedStatement ps=con.prepareStatement(  
12. "insert into filetable values(?,?)");  
13.               
14. File f=new File("d:\\myfile.txt");  
15. FileReader fr=new FileReader(f);  
16.               
17. ps.setInt(1,101);  
18. ps.setCharacterStream(2,fr,(int)f.length());  
19. int i=ps.executeUpdate();  
20. System.out.println(i+" records affected");  
21.               
22. con.close();  
23.               
24. }catch (Exception e) {e.printStackTrace();}  
25. }  
26. }  

Retrieve image from Oracle database

By the help of PreparedStatement we can retrieve and store the image in the database.

The getBlob() method of PreparedStatement is used to get Binary information, it returns the instance of Blob. After calling the getBytes() method on the blob object, we can get the array of binary information that can be written into the image file.

Signature of getBlob() method of PreparedStatement

1. public Blob getBlob()throws SQLException  

Signature of getBytes() method of Blob interface

1. public  byte[] getBytes(long pos, int length)throws SQLException  

We are assuming that image is stored in the imgtable.

1. CREATE TABLE  "IMGTABLE"   
2.    (    "NAME" VARCHAR2(4000),   
3.     "PHOTO" BLOB  
4.    )  
5. /  

Now let's write the code to retrieve the image from the database and write it into the directory so that it can be displayed.

In AWT, it can be displayed by the Toolkit class. In servlet, jsp, or html it can be displayed by the img tag.

1. import java.sql.*;  
2. import java.io.*;  
3. public class RetrieveImage {  
4. public static void main(String[] args) {  
5. try{  
6. Class.forName("oracle.jdbc.driver.OracleDriver");  
7. Connection con=DriverManager.getConnection(  
8. "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");  
9.       
10. PreparedStatement ps=con.prepareStatement("select * from imgtable");  
11. ResultSet rs=ps.executeQuery();  
12. if(rs.next()){//now on 1st row  
13.               
14. Blob b=rs.getBlob(2);//2 means 2nd column data  
15. byte barr[]=b.getBytes(1,(int)b.length());//1 means first image  
16.               
17. FileOutputStream fout=new FileOutputStream("d:\\sonoo.jpg");  
18. fout.write(barr);  
19.               
20. fout.close();  
21. }//end of if  
22. System.out.println("ok");  
23.               
24. con.close();  
25. }catch (Exception e) {e.printStackTrace();  }  
26. }  
27. }  

Now if you see the d drive, sonoo.jpg image is created.