How to Call a Java Stored Function/Procedure in C#?

Overview

The CUBRID Database support Java Stored Function/Procedure, for more information:

http://www.cubrid.org/manual/843/en/Java Stored Function/Procedure.

Check the cubrid.conf file

By default, the java_stored_procedure is set to no in the cubrid.conf file. To use a Java stored function/procedure, this value must be changed to yes. For details on this value, see Other Parameters in Database Server Configuration.

Write and compile the Java source code

public class SpCubrid{
     public static String HelloCubrid() {
         return "Hello, Cubrid !!";
     }
     public static int SpInt(int i) {
         return i + 1;
     }
     public static void outTest(String[] o) {
         o0 = "Hello, CUBRID";
     }
 }

%javac SpCubrid.java

Load the compiled Java class into CUBRID

Load the compiled Java class into CUBRID.

% loadjava demodb

Publish the loaded Java class

Create a CUBRID stored function and publish the Java class as shown below.

create function hello() return string
as language java
name 'SpCubrid.HelloCubrid() return java.lang.String';

How to calling?

You can call the Java stored functions/procedures by using a CALL statement, from SQL statements or Java applications. The following shows how to call them by using the CALL statement. The name of the Java stored function/procedure called from a CALL statement is not case sensitive. The CUBRID ADO.NET Provider suppot CALL statmen and SQL statments.

Example:

call Hello() into :HELLO;
call Sp_int(3) into :i;
call phone_info('Tom','016-111-1111');

Using Java Stored Function/Procedure in C#

In a C# applicaton, you can using the CUBRIDCommand class to create, call or drop a Java Stored Function/Procedure.

Create Java Stored Function

using CUBRID.Data.CUBRIDClient;
 
 namespace Sample
 {
     class ProcedureSample
     {
         /* conection string */
         /* Please modify before using. */
         static readonly string _connString = "server=127.0.0.1;database=demodb;port=33000;user=public;password=";
 
                 /*
         * create a new java stored function
         */
        public void create_function()
        {
            using (CUBRIDConnection conn = new CUBRIDConnection())
            {
                conn.ConnectionString = ProcedureSample._connString;
                conn.Open();

                string sql = "CREATE FUNCTION unit_hello(a int) RETURN int AS LANGUAGE JAVA NAME " +
                    "'SpCubrid.SpInt(int) return java.lang.Integer;'";
                using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }

        /*
         * create a new java stored procedure
         */
        public void create_procedure()
        {
            using (CUBRIDConnection conn = new CUBRIDConnection())
            {
                conn.ConnectionString = ProcedureSample._connString;
                conn.Open();


                string sql = "create PROCEDURE UNIT_PHONE_INFO(name varchar, phoneno varchar)" +
                             "as language java name 'SpCubrid.AddPhone(java.lang.String,java.lang.String)';";
                using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }
     }
 }

Call Java Stored Function/Procedure

using System.Data;
using CUBRID.Data.CUBRIDClient;

namespace Sample
{
    class ProcedureSample
    {
        /* conection string */
        /* Please modify before using. */
        static readonly string _connString = "server=127.0.0.1;database=demodb;port=33000;user=public;password=";

        /*
         * call procedure 
         */ 
        public void call_procedure()
        {
            using (CUBRIDConnection conn = new CUBRIDConnection())
            {
                conn.ConnectionString = ProcedureSample._connString;
                conn.Open();

                string sql = "CALL UNIT_PHONE_INFO('Tom', '03556315315');";
                using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }

        /*
         * call function, the function has a return value 
         */
        public void call_function()
        {
            using (CUBRIDConnection conn = new CUBRIDConnection())
            {
                conn.ConnectionString = ProcedureSample._connString;
                conn.Open();

                string sql = "? = CALL unit_hello(10)";
                using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    CUBRIDParameter p1 = new CUBRIDParameter("?p1", CUBRIDDataType.CCI_U_TYPE_INT);
                    p1.Direction = ParameterDirection.Output;   // output
                    cmd.Parameters.Add(p1);

                    cmd.ExecuteNonQuery();

                    // do something with p1.value
                }
            }
        }
    }
}

Drop Java Stored Function/Procedure

using CUBRID.Data.CUBRIDClient;

namespace Sample
{
    class ProcedureSample
    {
        /* conection string */
        /* Please modify before using. */
        static readonly string _connString = "server=127.0.0.1;database=demodb;port=33000;user=public;password=";

        /*
         * drop java stored function
         */
        public void drop_function()
        {
            using (CUBRIDConnection conn = new CUBRIDConnection())
            {
                conn.ConnectionString = ProcedureSample._connString;
                conn.Open();

                string sql = "drop function unit_hello";
                using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }

        /*
         * drop java stored procedure
         */ 
        public void drop_procedure()
        {
            using (CUBRIDConnection conn = new CUBRIDConnection())
            {
                conn.ConnectionString = ProcedureSample._connString;
                conn.Open();

                string sql = "DROP PROCEDURE UNIT_PHONE_INF";
                using (CUBRIDCommand cmd = new CUBRIDCommand(sql, conn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}