Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

CUBRID Java Stored Procedures

The scope of this tutorial is to introduce you some of the CUBRID features regarding database stored procedures.

Overview

One of the most powerful CUBRID features is the built-in support for stored procedures. If you are not familiar with the concept of “stored procedures”, we recommend that you first read about it, before continuing further with this tutorial; one great place to start is, for example,  http://en.wikipedia.org/wiki/Stored_procedure.

Note: CUBRID supports only Java stored procedures; it does not support SQL, T-SQL or other programming languages or SQL dialects.

Why use Stored Procedures?

There are many reasons why most of the databases, both commercial and open-source, implement support for stored procedures:

Do things which are not available in “plain” SQL. For example, using Java stored procedures you can:

  • Get access to the file system
  • Get access to web/web services
  • Implement complex business logic
  • …and, generally speaking, do whatever things you can do using a powerful development language like Java, for example.

Share the code/Share the features/Code reuse! While a client-side application is hardly available to another user without some extra-effort, a database stored procedure is a great way to share functionalities. Stored procedures are available on the database server-side, so sharing them is just a matter of providing access to the database.

Enhance CUBRID with new features! Whenever you need a new feature in CUBRID, a great way to start can be through developing a stored procedure. Of course, you could always choose to contribute with your code to the CUBRID engine, but developing a Java stored procedure is much simpler.

And these are not the only reasons to use stored procedures – think about performance and usability; all these are valid reasons to enhance your CUBRID database with Java stored procedures.

“How-To” CUBRID Java Stored Procedures

To create a CUBRID stored procedure, the mandatory steps are:

We will not go here into more details about these steps, because the online/offline documentation already covers this topic in details – see http://www.cubrid.org/manual/840/en/How%20to%20Write%20Java%20Stored%20Function|Procedure.

Some very interesting information about CUBRID Java stored procedures can be found here: http://www.cubrid.org/manual/840/en/Note

In CUBRID, there are two types of database stored procedures:

  • Procedures; these are stored procedures which do not return a value
  • Functions; these are stored procedures which return a value back to the “caller”

image001.png

For Functions, the valid data types which can be returned are any of the CUBRID SQL data types (except BLOB/CLOB), or the CURSOR data type (which is used for when the code returns a Java ResultSet):

image002.png

Beside this difference regarding the return value, there are no other differences that separate procedures and functions. Therefore, unless when explicitly specified, whenever we will refer to “stored procedures” in this document, it means that it applies to both types.

Important! There are 2 fundamental types of stored procedures in CUBRID:

Stored procedures which do not access the CUBRID database

Stored procedures which have access the CUBRID database – so-called “server-side JDBC driver stored procedures

As the scope of developing a stored procedure in a database is generally associated with the need of accessing the data in the database, we will refer in this document only to the second type of stored procedures – which have access to the database.

Connecting to the database

As a general rule, because a stored procedure is nothing else but Java code, all the rules about connecting and accessing a database via JDBC do apply.

In particular, because a stored procedure is already “attached” to a database, to connect to the “current” database, all you need to do to get a database connection in code is:

Connection conn = DriverManager.getConnection("jdbc:default:connection:"); 

Of course, you can connect to any other database as well from the stored procedures code, CUBRID or not, there are no limitations – it is nothing else but standard JDBC programming.

Recommendation: The next thing to do, after successfully connected, is to setup the connection encoding:

((CUBRIDConnection) conn).setCharset("UTF-8"); 

In the next sections, we will go through some concrete example of developing Java stored procedures.

A simple stored procedure example

Let’s create a stored procedure, which replicates the COUNT() SQL function; it will get as an input parameter the name of the table and it will return the number of records in that table.

Here is the (simplified) Java code:

public static int count(String table_name) throws  Exception { 
String sql = "select count(*) from " + table_name; 
int ret = 0; 
 
try { 
Class.forName("cubrid.jdbc.driver.CUBRIDDriver"); 
Connection conn = DriverManager.getConnection("jdbc:default:connection:"); 
((CUBRIDConnection) conn).setCharset("UTF-8"); 
  
PreparedStatement pstmt = conn.prepareStatement(sql); 
ResultSet resultSet = pstmt.executeQuery(); 
if (resultSet.next()) { 
ret = resultSet.getInt(1); 
} 
resultSet.close(); 
pstmt.close(); 
conn.commit(); 
conn.close(); 
} catch (SQLException e) { 
System.err.println(e.getMessage()); 
} 

return ret; 
}

After compiling the code, we will load the compiled class in the database, using the loadjava utility:

image003.png

 

Then, we need to create the call interface, so we can access the procedure from SQL.

If you are using CUBRID Manager, you can take advantage of the built-in UI interface:

image004.png

Or you can just use plain SQL:

CREATE FUNCTION "tcount"("table_name" STRING) RETURN INTEGER 
AS LANGUAGE JAVA
NAME 'information_schema.count(java.lang.String) return java.lang.Int' 

Note: Because “count” is a reserved CUBRID keyword, when we declared the access interface, we will named it “tcount”. However, this restriction applies only to the SQL call interface, in the Java code we can use the “count” name for the function – there are no restrictions.

Once created, you can see the stored procedure in CUBRID Manager:

image005.png

…Last step – use it from SQL!

There are various ways to execute it:

select tcount('athlete'); 

or

call tcount('athlete'); 

 

image006.png

Note: There are no verifications for the correct data type for the input parameters!

For example, calling with a numeric argument, will simply output 0, without any error messages:

image007.png

Remember:

CUBRID stored procedure names are not case sensitive.

The maximum name length is 256

The maximum number of parameters a stored procedure can have is 64.

Another example: Accessing the file system

As we mentioned before, using Java stored procedures gives access to a whole new world of functionalities which are not available from SQL by default - you can use almost all the Java capabilities right from within CUBRID.

In particular, one important benefit that worth mentioning is getting access from the database to the file system.

We will illustrate this with an example of a stored procedure which will output data in a file. In particular, because CUBRID is missing an export-to-xml function or a similar built-in tool, let’s create a stored procedure which will take as an input argument a SQL SELECT statement and will output the result set in an XML format.

We will keep things simple, no XML parsers and a quite simple schema; of course, following this example, you can extend it to create more complex functionality.

Here is a part of the Java code:

public static String save_to_xml(String sql, String filename)
throws Exception { 
try { 
Class.forName("cubrid.jdbc.driver.CUBRIDDriver"); 
Connection conn = DriverManager.getConnection("jdbc:default:connection:"); 
((CUBRIDConnection) conn).setCharset("UTF-8"); 
  
File target = new File(filename); 
  
FileWriter fstream = new FileWriter(filename); 
BufferedWriter out = new BufferedWriter(fstream); 
out.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>"); 
out.newLine(); 
  
… 
PreparedStatement pstmt = conn.prepareStatement(sql); 
ResultSet rs = pstmt.executeQuery(); 
  
ResultSetMetaData rsm = rs.getMetaData(); 
out.write("<metadata>"); 
out.newLine(); 
for (int i = 1; i <= rsm.getColumnCount(); i++) { 
      out.write(" " + "<column>"); 
      out.newLine(); 
      out.write("  " + "<name>"); 
      out.newLine(); 
… 

The complete code is available for download on this page. You will find not only the examples used in this tutorial, but other stored procedures as well.

After compiling, loading in the database and creating the interface we will be able to use this stored procedure from SQL:

image008.jpg

Let’s dump the data from the code table in the file:

call save_to_xml('select * from code', 'c:\out.xml'); 

And here are the results:

image009.png

Tip:  If you want to provide some feedback to the user, you can always choose to return a string value (use a function instead of a procedure). For example, you can return a confirmation that the operation has completed ok, or an error message.

 

Summary

As you can see, there are so many new things you can accomplish when you combine the power of Java with a CUBRID database! …And there is nothing complicated or hard to do!

So when you need a new functionality in your application, first consider if a store procedure is a good solution approach.

Also, you should know that stored procedures are not a universal solution to any problem - they have their own disadvantages:

Are very hard to debug.

They can “hide” the business logic, making hard to understand how they work/what do they do exactly.

There are limitations when combining SQL and Java, especially when you need to deal with result sets.

Links & Resources

CUBRID Online Manual

http://www.cubrid.org/webmanual/3.1/

CUBRID Stored Procedures

http://www.cubrid.org/manual/840/en/How%20to%20Write%20Java%20Stored%20Function|Procedure

JDBC general information

http://en.wikipedia.org/wiki/Java_Database_Connectivity

CUBRID JDBC & Java Programming with CUBRID

http://www.cubrid.org/cubrid_java_programming

General information about stored procedures

http://en.wikipedia.org/wiki/Stored_procedure

 

 

This concludes the first CUBRID Java Stored Procedures tutorial. In the next tutorial about CUBRID Stored Procedures, we will see how to use stored procedures which return a result set. Please let us know your feedback and remember to periodically check the CUBRID web site – www.cubrid.org/tutorials - for more tutorials and resources.

Thank you!

See also

Tutorial :: Clj-DBCP Clojure Library for Connecting to CUBRID Database

Clj-DBCP is a Java-6/Clojure library to create a database connection pool, which is known to be a more efficient way to connect to multiple databases ...

Tutorial :: Store Java Logs to Database Using log4j

log4j is one of the most powerful and commonly used logging system used when programming in Java. There are many different ways how you can utilize i...

CUBRID JDBC Tutorial (for CUBRID 8.3.1 and below)

You may write CUBRID powered applications in a variety of languages. The languages that probably most people use to write CUBRID applications are PHP ...

Tutorials :: Analyzing JDBC Logs with LOG4JDBC

First, let's clarify what exactly this log4jdbc is useful for. Assume you work on a project written in Java which communicates with CUBRID JDBC (or an...




You are either using a very old browser or a browser that is not supported.
In order to browse cubrid.org you need to have one of the following browsers:



Internet Explorer: Mozilla Firefox: Google Chrome: