Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.2.1 |  CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

Call Specifications

To use a Java stored function/procedure in CUBRID, you must write call specifications. With call specifications, Java function names, parameter types, return values and their types can be accessed by SQL statements or Java applications. To write call specifications, use CREATE FUNCTION or CREATE PROCEDURE statement. Java stored function/procedure names are not case sensitive. The maximum number of characters a Java stored function/procedure can have is 256. The maximum number of parameters a Java stored function/procedure can have is 64.

Syntax

CREATE {PROCEDURE procedure_name[(param[, param]...] | FUNCTION function_name[(param[, param]...] RETURN sql_type }

{IS | AS} LANGUAGE JAVA

NAME 'method_fullname (java_type_fullname[,java_type_fullname]... [return java_type_fullname]';

 

parameter_name [IN|OUT|IN OUT|INOUT] sql_type

   (default IN)

If the parameter of a Java stored function/procedure is set to OUT, it will be passed as a one-dimensional array whose length is 1. Therefore, a Java method must store its value to pass in the first space of the array.

Example

CREATE FUNCTION Hello() RETURN VARCHAR

AS LANGUAGE JAVA

NAME 'SpCubrid.HelloCubrid() return java.lang.String';

CREATE FUNCTION Sp_int(i int) RETURN int

AS LANGUAGE JAVA

NAME 'SpCubrid.SpInt(int) return int';

 

CREATE PROCEDURE Phone_Info(name varchar, phoneno varchar)

AS LANGUAGE JAVA

NAME 'PhoneNumber.Phone(java.lang.String, java.lang.String)';

When a Java stored function/procedure is published, it is not checked whether the return definition of the Java stored function/procedure coincides with the one in the declaration of the Java file. Therefore, the Java stored function/procedure follows the sql_type return definition provided at the time of registration. The return definition in the declaration is significant only as user-defined information.

Data Type Mapping

In call specifications, the data types SQL must correspond to the data types of Java parameter and return value. The following table shows SQL/Java data types allowed in CUBRID.

Data Type Mapping

SQL Type

Java Type

CHAR, VARCHAR

java.lang.String, java.sql.Date, java.sql.Time, java.sql.Timestamp, java.lang.Byte, java.lang.Short, java.lang.Integer, java.lang.Long, java.lang.Float, java.lang.Double, java.math.BigDecimal, byte, short, int, long, float, double

NUMERIC, SHORT, INT, FLOAT, DOUBEL, CURRENCY

java.lang.Byte, java.lang.Short, java.lang.Integer, java.lang.Long, java.lang.Float, java.lang.Double, java.math.BigDecimal, java.lang.String, byte, short, int, long, float, double

DATE, TIME, TIMESTAMP

java.sql.Date, java.sql.Time, java.sql.Timestamp, java.lang.String

SET, MULTISET, SEQUENCE

java.lang.Object[], java primitive type array, java.lang.Integer[] ...

OBJECT

cubrid.sql.CUBRIDOID

CURSOR

cubrid.jdbc.driver.CUBRIDResultSet

Checking the Published Java Stored Function/Procedure Information

You can check the information on the published Java stored function/procedure The db_stored_procedure system virtual table provides virtual table and the db_stored_procedure_args system virtual table. The db_stored_procedure system virtual table provides the information on stored names and types, return types, number of parameters, Java class specifications, and the owner. The db_stored_procedure_args system virtual table provides the information on parameters used in the stored function/procedure.

SELECT * from db_stored_procedure;
sp_name     sp_type   return_type    arg_count
sp_name               sp_type               return_type             arg_count  lang target                owner
================================================================================
'hello'               'FUNCTION'            'STRING'                        0  'JAVA''SpCubrid.HelloCubrid() return java.lang.String'  'DBA'
 
'sp_int'              'FUNCTION'            'INTEGER'                       1  'JAVA''SpCubrid.SpInt(int) return int'  'DBA'
 
'athlete_add'         'PROCEDURE'           'void'                          4  'JAVA''Athlete.Athlete(java.lang.String, java.lang.String, java.lang.String, java.lang.String)'  'DBA'

SELECT * from db_stored_procedure_args;
sp_name   index_of  arg_name  data_type      mode
=================================================
 'sp_int'                        0  'i'                   'INTEGER'             'IN'
 'athlete_add'                   0  'name'                'STRING'              'IN'
 'athlete_add'                   1  'gender'              'STRING'              'IN'
 'athlete_add'                   2  'nation_code'         'STRING'              'IN'
 'athlete_add'                   3  'event'               'STRING'              'IN'

Deleting Java Stored Functions/Procedures

You can delete published Java stored functions/procedures in CUBRID. To delete a Java function/procedure, use the DROP FUNCTION function_name or DROP PROCEDURE procedure_name statement. Also, you can delete multiple Java stored functions/procedures at a time with several function_names or procedure_names separated by a comma (,).

A Java stored function/procedure can be deleted only by the user who published it or by DBA members. For example, if a PUBLIC user published the 'sp_int' Java stored function, only the PUBLIC or DBA members can delete it.

drop function hello[, sp_int]

drop procedure Athlete_Add