Creating C and C++ routines

Procedures and functions that reference a C or C++ library are created in a similar way to external routines with other implementations. This task comprises a few steps including the formulation of the CREATE statement for the routine, the coding of the routine implementation, precompilation, compilation and linking of code, and the deployment of source code.

Before you begin

  • Knowledge of C and C++ routine implementation. To learn about C and C++ routines in general see:
  • The IBM® data server client which includes application development support must be installed on the client computer.
  • The database server must be running an operating system that supports a C or C++ compiler that is supported by the database.
  • The required compilers must be installed on the database server.
  • Authority to execute the CREATE statement for the external routine. For the privileges required to execute the CREATE PROCEDURE statement or the CREATE FUNCTION statement, see the documentation for the statement.

About this task

You would choose to implement a C or C++ routine if:
  • You want to encapsulate complex logic into a routine that accesses the database or that performs an action outside of the database.
  • You require the encapsulated logic to be invoked from any of: multiple applications, the CLP, another routine (procedure, function (UDF), or method), or a trigger.
  • You are most comfortable coding this logic using an embedded SQL programming language such as C or C++.

Procedure

  1. Code the routine logic in the chosen programming language: C or C++.
    • Include any C or C++ header files that are required for additional C functionality and the database header files for C or C++ that are required for SQL data type and SQL execution support. Include the following header files:
      • memory.h
      • sql.h
      • sqlca.h
      • sqlda.h
      • sqludf.h
    • A routine parameter signature must be implemented using one of the supported parameter styles. It is strongly recommended that parameter style SQL be used for all C and C++ routines. Scratchpads and dbinfo structures are passed into C and C++ routines as parameters. For more on parameter signatures and parameter implementations see:
    • Declare host variables and parameter markers in the same manner as is done for embedded SQL C and C++ applications. Be careful to correctly use data types that map to SQL data types. For more on data type mapping between SQL and C or C++ data types refer to:
    • Include routine logic. Routine logic can consist of any code supported in the C or C++ programming language. It can also include the execution of embedded SQL statements which is implemented in the same way as for embedded SQL applications.
      For more on executing SQL statements in embedded SQL see:
    • If the routine is a procedure and you want to return a result set to the caller of the routine, you do not require any parameters for the result set. For more on returning result sets from routines:
    • Set a routine return value at the end of the routine.
  2. Build your code to produce a library file.
    For information on how to build embedded SQL C and C++ routines, see:
  3. Copy the library into the database function directory on the database server. It is recommended that you store libraries associated with routines in the function directory. To find out more about the function directory, see the EXTERNAL clause of either of the following statements: CREATE PROCEDURE or CREATE FUNCTION.

    You can copy the library to another directory on the server, but to successfully invoke the routine you must note the fully qualified path name of your library as you will require it for the next step.

  4. Execute either dynamically or statically the appropriate SQL language CREATE statement for the routine type: CREATE PROCEDURE or CREATE FUNCTION.
    • Specify the LANGUAGE clause with value: C
    • Specify the PARAMETER STYLE clause with the name of the supported parameter style that was implemented in the routine code. It is strongly recommended that PARAMETER STYLE SQL be used.
    • Specify the EXTERNAL clause with the name of the library to be associated with the routine using one of the following values:
      • the fully qualified path name of the routine library
      • the relative path name of the routine library relative to the function directory.

      By default the database manager looks for the library in the function directory unless a fully qualified or relative path name for it is specified in the EXTERNAL clause.

    • Specify DYNAMIC RESULT SETS with a numeric value if your routine is a procedure and it will return one or more result sets to the caller.
    • Specify any other non-default clause values in the CREATE statement to be used to characterize the routine.

Results

To invoke your C or C++ routine, see Invoking routines.