Creating C External Procedures

This chapter describes how to configure the basic environment for C external procedures and how to create C external procedures.

Basic Environment Configuration

C external procedures are executed by the tbEPA, as mentioned earlier. The binary used to execute the tbEPA process exists in the $TB_HOME/client/bin directory.

This section describes how to specify the basic environment configuration needed to execute tbEPA processes, before creating the external C procedure.

Initialization Parameters

When first executed, the tbEPA process checks the user-specified initialization parameters. The initialization parameter is specified in the tbepa.cfg file located under the $TB_HOME/client/tbepa directory. If no initialization parameter is specified, the default value is used.

The initialization parameters that users can define for the tbEPA process are as follows:

<<tbepa.cfg>>

LOG_DIR=/tmp/epa_log 
LOG_LVL=2 
MAX_LOG_SIZE=20k

초기화 파라미터	설명
LOG_DIR	tPath to the tbEPA log file. (Default value: $TB_HOME/client/tbepa)
LOG_LVL	Logging level. (Default value: 2)
MAX_LOG_SIZE	Maximum size of the log file. (Default value: 0, unit: byte) If set to 0: Log file size is unlimited. If the log file size exceeds the specified maximum size, the log file is backed up.
MAX_LOG_BACKUP_SIZE	Maximum total size of log files in the backup directory. (Default value: 0, unit: byte) If set to 0: Total size of the log files is unlimited. If the total size of the log files exceeds the specified maximum size, the oldest one-third of the log files are removed.

LOG_LVL is an initialization parameter in the tbepa.cfg file and allows users to choose a logging level between 1-5.

The following table details each logging level.

Logging Level	Log Level Name	Description
1	ERROR	Error related events.
2	WARNING	Warning related events.
3	INFO	The basic trace events.
4	DEBUG	Detail level trace events to debug applications.
5	TRACE	The most verbose logging level.

---

Creating C External Procedures

Users must take the following steps to create an external C procedure.

1. Create a user shared library

2. Register the library object

3. Map the function (external procedure) to a PSM

4. Execute the function

1. Creating User Shared Libraries

Create a user shared library which can be dynamically called in C.

A user shared library is created in the following order.

(1) Write a C function

<<extproc.c>>

long find_max(long x, long y)
{
    if (x >= y) return x;
    else return y;
}

(2) Perform compilation

The following example performs compilation in the UNIX series (Linux included) platform.

cc -g -fpic -shared -o libextproc.so extproc.c

When the compilation completes, a user shared library named libextproc.so is created.

2. Registering Library Objects

Library Object registers a user shared library file in Tibero. It is a type of schema object.

The following is the syntax used to register a library object.

CREATE LIBRARY [schema_name.]library_name
{IS | AS} 'file_path';

Item	Description
schema_name	Name of the schema object.
library_name	Name of the library object.
file_path	Absolute path to the user shared library file.

Executing the CREATE LIBRARY statement requires the CREATE LIBRARY privilege.

The following example registers the library object named 'extproc' using a created user shared library file.

CREATE LIBRARY extproc
IS '/usr/mylib/libextproc.so';
/

3. Function and PSM Object Mapping

When a function within the library object is mapped to a PSM object after the library object is registered, an external C procedure is created. Users can call the function in the same way as they call general PSMs.

The following syntax defines a PSM.

CREATE OR REPLACE {FUCTION | PROCEDURE | PACKAGE}
…
{IS | AS} LANGUAGE C
LIBRARY library_name
[NAME c_string_literal_name] 
[WITH CONTEXT]
[PARAMETERS (external_parameter[, external_parameter]...)];

Item	Description
library_name	Name of the library object.
c_string_literal_name	Function to be called.
WITH CONTEXT	Indicates that a context pointer will be passed to the C external procedure. (For detailed information, refer to “External C Procedure Utility”, “Callback Service”.)
PARAMETERS (external_parameter)	Parameters mapped to PSM parameters. (For detailed information about external_parameter, refer to “ Parameter Mapping”.)

The following example maps a specific function (find_max) within the library object to the PSM.

CREATE OR REPLACE FUNCTION ext_find_max(num1 BINARY_INTEGER, num2 BINARY_INTEGER)
  RETURN BINARY_INTEGER 
  AS LANGUAGE C
  LIBRARY extproc
  NAME "find_max"
  PARAMETERS(num1 int, num2 int);

4. Executing Functions

A function is mapped to a PSM in Tibero, thus users can use the PSM syntax to execute the function.

The following example executes an external C procedure using the PSM syntax.

SQL> CREATE TABLE TBL (COL1 NUMBER, COL2 NUMBER);
Table 'TBL' created.

SQL> INSERT INTO TBL VALUES(1, 2);
1 row inserted.

SQL> INSERT INTO TBL VALUES(5, 3);
1 row inserted.

SQL> INSERT INTO TBL VALUES(7, 9);
1 row inserted.

SQL> select col1, col2, ext_find_max(col1, col2) as max from tbl; 
      col1	  col2	       max
-----------  ---------- -----------
         1           2           2
         5           3           5
         7           9           9