SQL Guide : solidDB® SQL statements : CREATE PROCEDURE (external)
  
CREATE PROCEDURE (external)
CREATE PROCEDURE procedure_name
   [(parameter_definition [, parameter_definition ...])]
   [RETURNS (output_column_definition
          [, output_column_definition ...])]
   [SQL_data_access_indication]
   LANGUAGE C
   EXTERNAL NAME ’external_procedure_library_identifier’
parameter_definition ::= [parameter_mode]
   parameter_name data_type
parameter_mode ::= IN | OUT | INOUT
output_column_definition::= column_name column_type
SQL_data_access_indication::= CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA | NO SQL
Usage
The CREATE PROCEDURE (external) statement defines an external procedure written in C programming language.
The external procedures are accessed using the standard dynamic library interface as provided by the operating system (shared libraries in Linux and UNIX, DLLs in Windows). To enable use of external stored procedures, you must link your application dynamically to solidDB® using shared memory access (SMA) or linked library access (LLA).
The external procedures are called with the same CALL statement syntax as SQL stored procedures; the control of the thread is passed to the external library routine until it returns. Furthermore, the database interface of the external procedures is similar to that of an ODBC application, except that the connection handle (hdbc) is passed to the external routine, and it can immediately start using it without establishing the connection.
The output of the external procedure can be returned as a result set of a single row or as output parameters.
Parameters
procedure_name
Identifies the procedure name. The procedure name can also include the catalog name and schema:
[catalog_name[.schema].]procedure_name
The name must be a valid SQL identifier. The procedure name must be unique within the schema.
parameter_definition ::= [parameter_mode]
      parameter_name data_type
Identifies the parameters of the procedure, and specifies the mode, optional parameter name, and data type of the parameter. One entry in the list must be specified for each parameter that the procedure will expect.
parameter_mode::= IN | OUT | INOUT
Identifies the parameter mode.
IN
Identifies the parameter as an input parameter to the procedure. Any changes made to the parameter within the procedure are not available to the calling SQL application when control is returned. The default is IN.
OUT
Identifies the parameter as an output parameter for the procedure.
INOUT
Identifies the parameter as both an input and output parameter for the procedure.
Note If the RETURNS keyword is used, the parameter mode can only be IN.
parameter_name
Specifies the name of the parameter. The parameter name must be unique for the procedure.
data_type
Specifies the data type of the parameter. The data type must be a valid solidDB® data_type.
RETURNS (output_column_definition)
Identifies the output of the procedure.
output_column_definition::= column_name column_type
column_name
Specifies the name of the output column.
column_type
Specifies the data type of the output column. The data type must be a valid solidDB® data_type.
Note If the RETURNS keyword is used, the parameter mode can only be IN.
SQL_data_access_indication
Defines whether the procedure issues any SQL statements and, if so, what type.
CONTAINS SQL
Indicates that the procedure contains SQL statements but these statements do not read or modify SQL data.
READS SQL DATA
Indicates that the procedure contains SQL statements that read SQL data but do not modify any data.
MODIFIES SQL DATA
Indicates that the procedure contains SQL statements modify SQL data .
NO SQL
Indicates that the procedure does not execute any SQL statements.
Note The validity of the contents of the procedure are not checked against the declared data access indication.
LANGUAGE C
Indicates that the CREATE PROCEDURE statement is being used to register a new procedure based on code written in C programming language.
EXTERNAL NAME 'external_procedure_library_identifier'
Identifies the name of the user-written code which implements the procedure being defined. The name must be enclosed by single quotation marks. Extraneous blanks are not permitted.
external_procedure_library_identifier::=
    [path] library_name!C_routine_name
[path]library_name
Identifies the full path (optional) and name of the procedure. If you specify an absolute path, you need to append the file extension of the library file. If you do not specify the file extension, you might need to add the location of the library to the LD_LIBRARY_PATH or LIBPATH (Linux and UNIX) or PATH (Windows) environment variable.
For example:
▪On UNIX systems, '/u/jchui/mylib/myproc.so' would cause solidDB® to look in /u/jchui/mylib for the myproc.so library.
▪On Windows systems, 'd:\mylib\myproc.dll' would cause solidDB® to load the file myproc.dll from the d:\mylib directory.
Tip: If you do not specify the file extension, you can port and use the library on several platforms.
!C_routine_name
Identifies the entry point name of the C routine (procedure) to be invoked. The exclamation point (!) serves as a delimiter between the library name and the routine name.
'!proc8' would direct solidDB® to look for the library in the location specified by [path]library_name, and to use entry point proc8 within that library.
Note On Windows 32-bit systems, use the calling convention _cdecl for calling the C routines. This is to allow checking and handling of varying argument counts. Also, ensure that your DLL exports the routines you want to call: for example, include the routines in the corresponding .def file in your DLL project.
Note The library (and the procedure within the library) does not need to exist when the CREATE PROCEDURE statement is issued. However, when the procedure is called, the library and procedure within the library must exist and be accessible.
Return values
▪SQL_ERROR
▪SQL_SUCCESS
Restrictions
▪The call interface of the external procedures is assumed to be non-fenced and thread-safe.
▪All procedures are assumed non-deterministic; the procedure depends on some state values that affect the results.
▪You should not start new threads that execute in parallel with the called procedure; the provided connection handle is not multithread safe in the same manner as normal ODBC connection handle is. If new threads are launched, they cannot access the connection concurrently or after the original external procedure has returned.
Example
Tip: For more examples, see the samples/procedures directory in your solidDB® installation directory.
The following example registers an external stored procedure EXTPROCEXAMPLE1.
CREATE PROCEDURE EXTPROCEXAMPLE1(IN P1 INTEGER,
      INOUT P2 INTEGER, OUT P3 CHAR(10))
READS SQL DATA
LANGUAGE C
EXTERNAL NAME ’examplelib1!extprocexample1’
C program for the procedure
#include <solidodbc3.h>
/* UTF-8 sequence for a char can be up to 4 bytes */
#define CHARMAXBYTELEN(charlen) (4 * (charlen) + 1)
int extprocexample1(SQLHDBC hdbc, /* implicit input argument: ’hot’
                                     connection handle */
  char sqlst[6],      /* implicit output argument : sqlstate */
  char qualName[],    /* implicit input argument: procedure name */
  char diagMsg[71],   /* implicit output argument: diagnostics message */   SQLULEN nArgs,      /* implicit input: actual number of args */
  SQLLEN nullind[3],  /* implicit input/output: null/size indicator
                         variables
(when nArgs > 0) */ int* p1, int* p2, char p3[CHARMAXBYTELEN(10)])
                      /* explicit arguments */
{
  SQLHSTMT hstmt = SQL_NULL_STMT;
  SQLRETURN rc;
  int retcode;
  if (nArgs != 3) {
    goto argcount_failure;
  }
  nullind[2] = SQL_NULL_DATA;
  if (nullind[0] == SQL_NULL_DATA || nullind[1] == SQL_NULL_DATA) {
    goto null_input_detected;
  }
  rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
  if (rc != SQL_SUCCESS) {
    goto failure;
  }
  rc = SQLPrepare(hstmt, “SELECT P2,P3 FROM EXTPROCEXAMPLE1_TAB ”
                                “WHERE P1 = ? AND P2 > ?”, SQL_NTS);
  if (rc != SQL_SUCCESS) {
    goto failure; }
  rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG,
       SQL_INTEGER, 0, 0, p1, sizeof(*p1), NULL);
  if (rc != SQL_SUCCESS) {
    goto failure;
  }
  rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT,
       SQL_C_SLONG, SQL_INTEGER, 0, 0, p2, sizeof(*p2), NULL);
  if (rc != SQL_SUCCESS) {
    goto failure;
  }
  rc = SQLBindColumn(hstmt, 1, SQL_C_SLONG, p2, 0, NULL);
  if (rc != SQL_SUCCESS) {
    goto failure;
  }
  rc = SQLBindColumn(hstmt, 2, SQL_C_CHAR, p3, CHARMAXBYTELEN(10), NULL);
  if (rc != SQL_SUCCESS) {
    goto failure;
  }
  rc = SQLExecute(hstmt);
  if (rc != SQL_SUCCESS) {
    goto failure;
  }
  rc = SQLFetch(hstmt);
  if (rc == SQL_NO_DATA) {
    goto no_data_found;
  }
  if (rc != SQL_SUCCESS) {
    goto failure;
  }
  nullind[1] = sizeof(int); nullind[2] = sizeof(int);
  retcode = SQL_SUCCESS;
  cleanup:;
    if (hstmt != SQL_NULL_HANDLE) {
      SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
    }
    return retcode; null_input_detected:;
  no_data_found:;
    retcode = SQL_SUCCESS;
    nullind[1] = SQL_NULL_DATA;
    nullind[2] = SQL_NULL_DATA;
    goto cleanup;
  failure:;
    retcode = SQL_ERROR;
    if (hstmt != SQL_NULL_HANDLE) {
      rc = SQLError(SQL_NULL_HANDLE, SQL_NULL_HANDLE, hstmt,
                 sqlst, &nativeerror, diagMsg, 71, NULL
    );
    if (rc == SQL_NO_DATA) {
      goto generic_failure; } }
    else {
      rc = SQLError(SQL_NULL_HANDLE, hdbc, SQL_NULL_HANDLE,
          sqlst, &nativeerror, diagMsg, 71, NULL);
      if (rc == SQL_NO_DATA) {
        goto generic_failure;
      }
    }
    goto exit_cleanup;
  generic_failure:;
    memcpy(sqlst, “1C000”, 6);
    memcpy(diagMsg, “Generic error”, 14);
  goto exit_cleanup;
  argcount_failure:;
    memcpy(sqlst, “1C000”, 6);
    strncpy(diagMsg, “wrong number of arguments, 3 expected”, 71);
    goto exit_cleanup;
  }
Related reference
CREATE PROCEDURE
DROP PROCEDURE
See also
solidDB® SQL statements