Executing C External Procedures
This chapter describes how to map between C and PSM parameters and use the callback service.
Parameter Mapping
When a function in C language within the library object is mapped to a PSM, the PSM parameter is supposed to be passed to the function 'as is'. However, users sometimes want to send only some part of the PSM parameters or change the order of the PSM parameters. There are sometimes errors when exchanging data between PSM and C. For example, PSM variables can be null but C variables have no equivalent concept.
To address such requirements, the external C procedure provides the PARAMETERS clause. Parameter mapping is possible between a function and a PSM.
This chapter explains the syntax of the PARAMETERS clause and how to use it.
The PARAMETERS Clause
The PARAMETERS clause allows user to define parameters and their properties which are subsequently passed to a user-defined function. PARAMETERS clause is omissible.
The following uses the syntax of the PARAMETERS clause.
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]...)];
The PARAMETERS clause lists external_parameter which indicates each parameter to be sent.
The following is the parameter entry of the external_parameter.
{ CONTEXT
| {parameter_name | RETURN} [property] [BY REFERENCE] [external_datatype]
}CONTEXT
For further information, refer to “C External Procedure Utility”.
PSM parameter
Specifies the data types of parameters to be passed to the function and the preferred parameter passing method (by value or by reference).
When specifying datatypes, users must not pass parameters to an external procedure directly but specify the PSM datatype for the parameter. Each PSM datatype maps to a corresponding external datatype, which in turn maps to a C datatype, and then datatype conversions are accomplished.
The following details each entry of the PSM parameter.
parameter_name parameter_name specifies the PSM to be passed to the function.
The following example specifies the parameter name of PSM (num2) to be passed to the function (get_sqrt).
CREATE OR REPLACE FUNCTION ext_get_sqrt (num1 BINARY_INTEGER,
num2 BINARY_INTEGER)
RETURN BINARY_DOUBLE AS LANGUAGE C
LIBRARY extproc
NAME "get_sqrt"
PARAMETERS(num2 int);property Specifies each possible property in a PARAMETERS clause.
Unlike a PSM data type, a C type data does not specify the maximum length of character strings or whether to allow null values. To address these problems, the following parameter properties are supported in the external C procedure.
Property
C Data Type
Description
INDICATOR
short
Indicator allowing null value in data.
NULL: -1
Not Null: 0
LENGTH
int
Current length of the character string data. Used for IN
and IN OUT parameters.
MAXLEN
int
Maximum length of data. Used for IN, INOUT, and
RETURN parameters.
The C data type indicates the data type of the parameters in the function.
BY REFERENCE The BY REFERENCE clause passes the parameter by reference. It indicates the preferred parameter passing method (by value or by reference).
For example, C datatype cannot contain the value of character strings, thus it must be sent in char*. The OUT parameters are always passed by reference because the value must be changed after the function has been executed. The following is an example of writing a C function. <<module.c>>
void swap(short *x, short *y)
{
short *z;
z = x;
x = y;
y = z;
}The external procedure allows the C function to be written as shown in the following PSM function. When the BY REFERENCE clause is defined to create a PSM function, the tbEPA process passes the parameter via a pointer to the function as follows:
SQL> CREATE OR REPLACE PROCEDURE ext_swap (x PLS_INTEGER, y PLS_INTEGER)
IS
LANGUAGE C
LIBRARY libextproc
NAME "swap"
PARAMETERS(x BY REFERENCE, y BY REFERENCE);external_datatype external_datatype converts a PSM data type to a desired C data type. If omitted, the default value is used.
For example, when the PSM data type is passed to a valid C data type, the tbEPA process converts it to an external type, then sends it to the function.
The following table shows the mapping of PSM and C datatypes.
PSM Data Type
C Data Type (Default)
Allowed C Data Type
BINARY_INTEGER
PLS_INTEGE
INT
[UNSIGNED] CHAR, SHORT, INT, LONG
FLOAT
FLOAT
FLOAT
REAL
BINARY_DOUBLE
DOUBLE
DOUBLE
CHAR VARCHAR
STRING
STRING, TBSTRING
DOUBLE
TBNUMBER
TBNUMBER
DATE
TBDATE
TBDATE
TIMESTAMP
TBDateTime
TBDateTime
BLOB CLOB
TBLOBLOCATOR
TBLOBLOCATOR
Example
The following example maps the parameters of a C function to a PSM function, using the PARAMETERS clause.
1. Write a C function.
<<find_max.c>>
short find_max_short(short x, short y)
{
if (x >= y)
return x;
else
return y;
}
2. Map the parameters of a C function to a PSM function using the PARAMETERS clause.
SQL> CREATE OR REPLACE FUNCTION ext_find_max_short
(x PLS_INTEGER, y PLS_INTEGER) ...(1)...
RETURN PLS_INTEGER IS
LANGUAGE C
LIBRARY libextproc
NAME "find_max_short" ...(2)...
PARAMETERS(x short, y short);(1) Calls the PSM function named ext_find_max_short.
(2) The tbEPA process converts, the 'pls_integer type' passed by the server, to the sort type then sends it to the parameter of the user shared library function 'find_max_short'. The sort type return value is converted again to the PLS_INTEGER then sent to the server.
Callback Service
Users can write an application program using defferent interfaces such as tbCLI. Also, it is possible to access the DBMS server to create user shared libraries, which can then be accessed via a C external procedure, using queries or DML operations.
The C external procedure allows call backs into the database from a function, instead of using a tbCLI program which communicates with a server by establishing a new connection.
This chapter describes how to create tbCLI and execute a callback service provided by an external C procedure.
Creating an Application Program using tbCLI
In general, users must take the following steps to create an application program using tbCLI.
Create an environment handle using the SQLAllocEnv function.
Create a connection handle using the SQLAllocConnect function.
Connect to the DBMS server using the SQLConnect function.
Create a statement handle using the SQLAllocStmt function.
Specify the job to be handled in the allocated statement handle, then perform the SQLExecute function.
In steps 1, 2 and 3, a user-created application program is connected to the server. In steps 4 and 5, queries and DML operations are performed.
Using a Callback Service with External C Procedures
When an external C procedure is called after a user shared library is created as shown in “Creating an Application Program using tbCLI”, the application creates a new session on the server to execute the user shared library. In this case, the user shared library cannot be executed in the same transaction that invoked the external C procedure.
The following example uses the SQLGetExtProcConnect.
SQLRETURN SQL_API
SQLGetExtProcConnect(ExtProcContext *epCtx, SQLHENV *henv, SQLHDBC *hdbc,
SQLSMALLINT *errHdlType, SQLHANDLE *errHdl);*epCtx is a pointer to the ExtProcContext struct. henv indicates an environment handle used to get a return value, and hdbc indicates a connection handle. Error handle type (errHdlType) and error handle (errHdl) are defined to deal with errors.
The SQLGetExtProcConnect function performs steps 1 through 3 at once. Steps 4 and 5 are performed in the same way as for tbCLI.
DMLs and queries within the user shared library are performed in the same transaction that called the user library.
The following shows an example.
SQLRETURN rc;
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;
SQLSMALLINT errHdlType;
SQLHANDLE errHdl;
char *sql = "insert into tbl values('Tibero')";
SQLINTEGER cnt;
rc = SQLGetExtProcConnect(epc, &henv, &hdbc, &errHdlType, &errHdl);
if (rc != 0) return rc;
rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
if (rc != 0) return rc;
rc = SQLExecDirect(hstmt, sql, SQL_NTS);
if (rc != 0) {
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
return rc;
}
rc = SQLRowCount(hstmt, &cnt);
if (rc != 0) {
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
return rc;
}
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
return rc;
Example
The following is an example of how to write an application program using a callback service.
int connect(ExtProcContext *epc)
{
SQLRETURN rc;
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;
SQLSMALLINT errHdlType;
SQLHANDLE errHdl;
char *sql = "insert into psm_test_tab_002 values('TEST')";
SQLINTEGER cnt;
rc = SQLGetExtProcConnect(epc, &henv, &hdbc, &errHdlType, &errHdl);
if (rc != 0) return rc;
rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
if (rc != 0) return rc;
rc = SQLExecDirect(hstmt, sql, SQL_NTS);
if (rc != 0) {
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
return rc;
}
rc = SQLRowCount(hstmt, &cnt);
if (rc != 0) {
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
return rc;
}
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
if (cnt != 1) return 1;
return 0;
}Last updated