Background Image

DOCUMENTATION

?

Shortcut

PrevPrev Article

NextNext Article

Larger Font Smaller Font Up Down Go comment Print Attachment

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 anything 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 is 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 into more details about these steps here, because the online/offline documentation already covers this topic in details – see https://www.cubrid.org/manual/en/10.2/sql/jsp.html#how-to-write-java-stored-function-procedure

Some very interesting information about CUBRID Java stored procedures can be found here:
https://www.cubrid.org/manual/en/10.2/sql/jsp.html#caution

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”

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

cubrid_sp_function.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:

loadjava.png

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

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

admin_sp.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 Admin:

admin_sp2.png

…Last step – use it from SQL!

There are various ways to execute it:

select tcount('athlete');

or

call tcount('athlete');

result_sp.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:

result_sp2.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 also other stored procedures as well.

information_schema.java

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

admin_sp3.png

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:

sp_result.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:

  • 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

https://www.cubrid.org/manuals/

CUBRID Stored Procedures

https://www.cubrid.org/manual/en/10.2/sql/jsp.html

JDBC general information

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

CUBRID JDBC & Java Programming with CUBRID

https://www.cubrid.org/manual/en/10.2/api/jdbc.html#jdbc-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!


List of Articles
No. Category Subject Date
37 Interfaces Analyzing JDBC Logs with LOG4JDBC 2020.05.21
36 Tools CSQL - Command Line SQL Interpreter 2020.05.12
35 Server CUBRID Backup & Restore – Part I (Backup) file 2020.06.22
34 Server CUBRID Backup & Restore – Part II (Restore) file 2020.06.22
33 Server CUBRID Configuration Guide to Launch Services 2020.06.24
32 Server CUBRID GRANT ALL TABLES file 2020.07.17
31 Server CUBRID HA (High Availability) Starting Guide 2020.05.22
30 Interfaces CUBRID JDBC Tutorial 2020.05.12
» Server CUBRID Java Stored Procedures file 2020.06.24
28 Server CUBRID Log Files file 2020.06.10
Board Pagination Prev 1 2 3 4 Next
/ 4

Join the CUBRID Project on