Skip to content
≡

Administrator

Developer

Tools

External Tools

2. How to Use External Procedures#

This chapter describes how to use external procedures in Altibase.

First, it describes the C / C ++ data types and database objects that are supported for external procedures, and describes how to write and build dynamic libraries that the Altibase server can recognize.

Data Types and Database Objects#

This section introduces data types supported for writing external procedures, and external library objects and external procedure objects provided for using external procedures.

Data Type#

The following table concatenates valid PSM data types of arguments for the creation of external procedure objects, and valid data types of user-defined C/C++ function arguments. The user should note that pointer types can vary for user-defined C/C++ functions depending on the input/output mode of arguments.

PSM Data Type IN INOUT/OUT RETURN REMARK
BIGINT long long long long * long long
BOOLEAN char char * char In the case of BOOLEAN, the following two values ​​are allowed.
0: FALSE
1: TRUE
SMALLINT int int * int
INTEGER
REAL float float * float
DOUBLE double double * double
CHAR char * char * char *
VARCHAR
NCHAR
NVARCHAR
NUMERIC double double * double Data loss may occur in the process of converting an argument with high precision to a double type.
DECIMAL
NUMBER
FLOAT
DATE SQL_TIMESTAMP_STRUCT SQL_TIMESTAMP_STRUCT * SQL_TIMESTAMP_STRUCT
INTERVAL SQL_TIMESTAMP_STRUCT SQL_TIMESTAMP_STRUCT * SQL_TIMESTAMP_STRUCT

External Library Objects#

It is necessary for user-defined functions or, in other words, the dynamic library file in which the external procedure resides to be made identifiable by the Altibase server. For this purpose, Altibase provides the external library object, which is a database object, and SQL statements for its management.

To use an external procedure as a stored procedure, the user must first create an external library object and save the related dynamic library file in the $ALTIBASE_HOME/lib directory. It is possible to create an external procedure object without the dynamic library file. However, the existence of a related dynamic library file is a prerequisite for the execution of an external procedure.

External Procedure/Function Objects#

Altibase differentiates a stored procedure that registered an external procedure from a regular stored procedure and defines it as an external procedure object. An external procedure object is a database object that corresponds to a user-defined function on a oneto-one basis. User-defined functions(external procedures) are executed through external procedure objects. All information on related library objects and user-defined functions must be specified when creating external procedure objects.

Altibase provides SQL statements for the management of external procedure objects.

Basic Instruction#

The general steps in using an external procedure is as follows:

  • Writing User-Defined Funcitons
  • Writing Entry Functions
  • Creating External Library Objects
  • Creating External Procedure/Function Objects
  • Creating the Dynamic Library
  • Calling External Procedures

This section offers an explanation of the above steps, along with examples.

Writing User-Defined Functions#

The user can write C/C++ functions for external procedures in the same manner as when creating regular C/C++ functions.

This section provides an example of a user-defined function which corresponds to a stored procedure and a function with the following arguments and return type.

procedure str_uppercase_proc( a1 in char(30), a2 out char(30) )
function str_uppercase_func_int( a1 in char(30), a2 out char(30) ) return int
function str_uppercase_func_char( a1 in char(30), a2 out char(30) ) return
char(30)
The user-defined function, str_uppercase, which corresponds to str_uppercase_proc requires three arguments, and the function prototype is as follows.

extern "C" void str_uppercase( char\* str1, long long str1_len, char\* str2 );

Here, the second argument is used to pass the length of the string, to be input to the first argument, to the user-defined function.

The prototypes of the C/C++ user-defined functions corresponding to str_uppercase_func_int and str_uppercase_func_char are as follows. 

extern "C" int str_uppercase_count( char* str1, long long str1_len, char* str2 );
extern "C" char* str_uppercase_return( char* str1, long long str1_len, char* str2 );
The following is an example code of user-defined functions.

void str_uppercase(char *str1, long long str1_len, char * str2)
{
    for( int i=0; i < str1_len; i++ )
    {
        str2[i] = toupper(str1[i]);
    }
}

int str_uppercase_count(char *str1, long long str1_len, char * str2)
{
    int capCount = 0;
    int i        = 0;

    while( i < str1_len )
    {   
        if( str1[i] >= 'A' && str1[i] <= 'Z' )
        {
            capCount++; 
        }

        i++;
    }   

    return capCount;
}

char* str_uppercase_return(char *str1, long long str1_len, char * str2)
{
    for( int i=0; i < str1_len; i++ )
    {
        str2[i] = toupper(str1[i]);
    }
    return str2;
}

Note: The extern "C" keyword must be written in the declaration of a user-defined function.

Writing Entry Functions#

Apart from user-defined functions, standardized functions to execute user-defined functions are required. These functions are called entry functions.

When the Agent Process calls an entry function, the entry function calls a user-defined function and returns the result. Thus, entry functions must exist in the dynamic library.

A routine that actually calls user-defined functions must be included in an entry function.

The following is an example of an entry function that calls the above user-defined functions, str_uppercase, str_uppercase_count and str_uppercase_return.

extern "C" void entryfunction(char* func_name, int arg_count, void ** args, void ** returnArg);

void entryfunction(char* func_name, int arg_count, void ** args, void ** returnArg)
{
    // User definition
    if(strcmp(func_name, "str_uppercase") == 0)
    {   
        str_uppercase((char*)args[0], *((long long *)args[1]), (char*)args[2]);
    }
    else if(strcmp(func_name, "str_uppercase_count") == 0)
    {
        if( *returnArg != NULL ) // Returns int type
        {
            **(int**)returnArg = str_uppercase_count ((char*)args[0], *((long long*)args[1]), (char*)args[2]);
        }
    } 
    else if(strcmp(func_name, "str_uppercase_return") == 0)
    {
        if( returnArg != NULL ) // Returns char* type
        {
            *(char**)returnArg = str_uppercase_return ((char*)args[0], *((long long*)args[1]), (char*)args[2]);
        }
    } 
}
The following are explanations for each argument of entry functions.

  • func_name: The string which indicates the function name.
  • arg_count: The number of arguments.
  • args: The array of arguments. The order specified in the external procedure must be followed. The user should note that each mapped C data type differs, depending on the input/output mode of arguments and PSM data types.
  • returnArg: The argument for the return value. NULL is always returned for external procedures having no return values, and the pointer pointing to the return value is returned for external functions.

Notes#

  • The user must not discretionally change the name of the entry function(entryfunction) and the data types of arguments within the dynamic library.
  • If two or more user-defined functions are contained in one library, the source code must be written as to make separate calls to each user-defined function within one entry function.
  • If a user-defined function in an external procedure, instead of an external function, is called and its return value is assigned to the returnArg variable, there is a possibility of the Agent Process terminating abnormally. For external functions, therefore, whether or not the returnArg is NULL must be checked and afterwards, the return value should be typecasted according to the function return type.
  • As for user-defined functions, the extern "C" keyword must also be written in the declaration of entry functions.
  • The user should take note of the code in the above example, where the user-defined function is being processed. The code checks whether or not the returnArg variable(the last argument of the entry function) which is to be assigned the return value is NULL, and differs, depending on the function return type

if( *returnArg != NULL ) // When int type is returned
...
if( returnArg != NULL )  // When char* type is returned

Type-casting the Return Value#

After making a call to a user-defined function corresponding to an external function, it is necessary to typecast the return value to the appropriate return type of the function.

Return Type Type-casting in Code
char **(char**)returnArg = ...
int **( int**)returnArg = ...
long long **( long long**)returnArg = ...
float **( float**)returnArg = ...
double **( double**)returnArg = ...
char * *(char**)returnArg = ...
SQL_TIMESTAMP_STRUCT **( SQL_TIMESTAMP_STRUCT**)returnArg = ...

Creating External Library Objects#

For the Altibase server to identify the dynamic library file, the library object must be created in the database first. For example, if the user wishes to use the dynamic library file shlib.so, the external library object can be created in the database as below.

CREATE OR REPLACE LIBRARY lib1 AS 'shlib.so';

Now Altibase can identify the dynamic library file shlib.so, as the external library object lib1.

Creating External Procedure/Function Objects#

An external procedure object which registers a user-defined function must be created in the database before executing an external procedure(C/C++ user-defined function).

An external procedure object can be created as shown below.

create or replace procedure str_uppercase_proc( a1 in char(30), a2 out char(30) )
as
language c
library lib1
name "str_uppercase"
parameters( a1, a1 LENGTH, a2 )
;
/
The return type must be specified for external functions. The following is an example of a SQL statement which creates an external function which returns an int type and a char array. 
create or replace function str_uppercase_func_int( a1 in char(30), a2 out char(30) ) return int
as
language c
library lib1
name "str_uppercase_count"
parameters( a1, a1 LENGTH, a2 )
;
/

create or replace function str_uppercase_func_char( a1 in char(30), a2 out char(30) ) return char(30)
as
language c
library lib1
name "str_uppercase_return"
parameters( a1, a1 LENGTH, a2 )
;
/

Creating the Dynamic Library#

If the source file of one or more user-defined functions and entry functions is prepared, it can be compiled to create the dynamic library. The *.so file for UNIX-like operating systems is the dynamic library files.

The following example shows how to create the dynamic library by compiling the source file on UNIX-like operation systems.

$ g++ -g -fPIC -shared -o shlib.so extproc.cpp

If the user-defined functions and entry point functions are composed of multiple files, each source file can be compiled, and then linked, as shown below.

$ g++ -fPIC -c extproc.cpp
$ g++ -fPIC -c entry.cpp
$ g++ -shared -o shlib.so entry.o extproc.o

The created dynamic library file must be moved to a location which is identifiable by the Altibase server. The location is $ALTIBASE_HOME/lib; you cannot change this location at your discretion.

$ mv shlib.so $ALTIBASE_HOME/lib/

Calling External Procedures#

The following example shows the execution of an external procedure on iSQL. 

var var1 char(30);
var var2 char(30);

exec :var1 := 'hello world';
exec str_uppercase_proc( :var1, :var2 );

iSQL> print var;
[ HOST VARIABLE ]
-------------------------------------------------------
NAME                 TYPE                 VALUE
-------------------------------------------------------
VAR1                 CHAR(30)             hello world
VAR2                 CHAR(30)             HELLO WORLD

extproc.cpp#

The following source file, extproc.cpp, has written the user-defined functions used as an the example in the previous section.

#include <string.h>
#include <ctype.h>

extern "C" void str_uppercase( char* str1, long long str1_len, char* str2 );
extern "C" int str_uppercase_count( char* str1, long long str1_len, char* str2 );
extern "C" char* str_uppercase_return( char* str1, long long str1_len, char* str2 );


extern "C" void entryfunction(char* func_name, int arg_count, void ** args, void ** returnArg);

void entryfunction(char* func_name, int arg_count, void ** args, void ** returnArg)
{
    if(strcmp(func_name, "str_uppercase") == 0)
    {
        str_uppercase((char*)args[0], *((long long*)args[1]), (char*)args[2]);
    }
    else if(strcmp(func_name, "str_uppercase_count") == 0)
    {
        if( *returnArg != NULL ) // return int type
        {
            **(int**)returnArg = str_uppercase_count ((char*)args[0], *((long long*)args[1]), (char*)args[2]);
        }
    }
    else if(strcmp(func_name, "str_uppercase_return") == 0)
    {
        if( returnArg != NULL ) // return char* type
        {
            *(char**)returnArg = str_uppercase_return ((char*)args[0], *((long long*)args[1]), (char*)args[2]);
        }
    }
}

void str_uppercase(char *str1, long long str1_len, char * str2)
{
    for( int i=0; i < str1_len; i++ )
    {
        str2[i] = toupper(str1[i]);
    }
}

int str_uppercase_count(char *str1, long long str1_len, char * str2)
{
    int capCount = 0;
    int i        = 0;

    while( i < str1_len )
    {
        if( str1[i] >= 'A' && str1[i] <= 'Z' )
        {
            capCount++;
        }

        i++;
    }

    return capCount;
}

char* str_uppercase_return(char *str1, long long str1_len, char * str2)
{
    for( int i=0; i < str1_len; i++ )
    {
        str2[i] = toupper(str1[i]);
    }
    return str2;
}

Related meta table

  • SYS_LIBRARIES_

    This is the meta table that contains information about external library objects.

Related performance views

  • V$EXTPROC_AGENT

    This view shows information about the Agent Process created for the execution of external procedures

  • V$LIBRARY

    This view shows information on the dynamic libraries loaded directly within the database.

  • V$PROCINFO

    This view shows the current external procedure mode of the database.

For more detailed information on each meta table and performance view, please refer to General Reference.

The following are properties related to agent operations for external procedures. Internal mode, which does not create agent processes, is not affected.

  • EXTPROC_AGENT_CONNECT_TIMEOUT

  • EXTPROC_AGENT_CALL_RETRY_COUNT

  • EXTPROC_AGENT_IDLE_TIMEOUT

  • EXTPROC_AGENT_SOCKET_FILEPATH

For more detailed information on each property, please refer to General Reference.