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.

1. Create an environment handle using the SQLAllocEnv function.

2. Create a connection handle using the SQLAllocConnect function.

3. Connect to the DBMS server using the SQLConnect function.

4. Create a statement handle using the SQLAllocStmt function.

5. 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.

For more information, refer to Tibero tbCLI Guide.

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;

As shown in the previous example, a statement handle must be created manually using SQLAllocHandle(), and freed via SQLFreeHandle() as required.

For the environment handle and the connection handle that are automatically created via the SQLGetExtProcConnect() function, neither SQLDisconnect() nor SQLFreeHandle() is used.

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;

}