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, DOUBLE,

    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.

csql> select * from db_stored_procedure
csql> ;xrun

=== <Result of SELECT Command in Line 2> ===
 
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'
 
3 rows selected.
 
Current transaction has been committed.
 
1 command(s) successfully processed.
 
csql> select * from db_stored_procedure_args
csql> xrun
 
=== < Result of SELECT Command in Line 1> ===
 
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'
 
5 rows selected.

Current transaction has been committed.

1 command(s) successfully processed. 

  • 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 Phone_Info