Example: XML and XQuery support in C procedure
Once the basics of procedures, the essentials of C routines, XQuery and XML are understood, you can start creating and using C procedures with XML features.
The following example demonstrates a C procedure with parameters of type XML as well as how to update and query XML data.
- Prerequisites
- Before working with the C procedure example you might want to
read the following concept topic:
The following examples makes use of a table named
xmlDataTablethat is defined as follows:CREATE TABLE xmlDataTable ( num INTEGER, xdata XML ) INSERT INTO xmlDataTable VALUES (1, XMLPARSE(DOCUMENT '<doc> <type>car</type> <make>Pontiac</make> <model>Sunfire</model> </doc>' PRESERVE WHITESPACE)), (2, XMLPARSE(DOCUMENT '<doc> <type>car</type> <make>Mazda</make> <model>Miata</model> </doc>' PRESERVE WHITESPACE)), (3, XMLPARSE(DOCUMENT '<doc> <type>person</type> <name>Mary</name> <town>Vancouver</town> <street>Waterside</street> </doc>' PRESERVE WHITESPACE)), (4, XMLPARSE(DOCUMENT '<doc> <type>person</type> <name>Mark</name> <town>Edmonton</town> <street>Oak</street> </doc>' PRESERVE WHITESPACE)), (5, XMLPARSE(DOCUMENT '<doc> <type>animal</type> <name>dog</name> </doc>' PRESERVE WHITESPACE)), (6, NULL), (7, XMLPARSE(DOCUMENT '<doc> <type>car</type> <make>Ford</make> <model>Taurus</model> </doc>' PRESERVE WHITESPACE)), (8, XMLPARSE(DOCUMENT '<doc> <type>person</type> <name>Kim</name> <town>Toronto</town> <street>Elm</street> </doc>' PRESERVE WHITESPACE)), (9, XMLPARSE(DOCUMENT '<doc> <type>person</type> <name>Bob</name> <town>Toronto</town> <street>Oak</street> </doc>' PRESERVE WHITESPACE)), (10, XMLPARSE(DOCUMENT '<doc> <type>animal</type> <name>bird</name> </doc>' PRESERVE WHITESPACE)) - Procedure
- Use the following examples as references when making your own C procedures:
The C external code file
The example consists of two parts: the CREATE PROCEDURE statement and the external C code implementation of the procedure from which the associated assembly can be built.
The C source file that contains
the procedure implementations of the following examples is named
gwenProc.SQC and
has the following format:
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlda.h>
#include <sqlca.h>
#include <sqludf.h>
#include <sql.h>
#include <memory.h>
// C procedures
...
The file inclusions are indicated at the top of the file. There are no extra include files required for XML support in embedded SQL routines.
It is important to note the name of the file and the name of the function that corresponds to the procedure implementation. These names are important, because the EXTERNAL clause of the CREATE PROCEDURE statement for each procedure must specify this information so that the database manager can locate the library and entry point that corresponds to the C procedure.
Example 1: C parameter style SQL procedure with XML features
This example shows the following:
- CREATE PROCEDURE statement for a parameter style SQL procedure
- C code for a parameter style SQL procedure with XML parameters
inNum and is of type INTEGER.
The second input parameters is named inXML and is
of type XML. The values of the input parameters are used to insert
a row into the table xmlDataTable. Then an XML value
is retrieved using an SQL statement. Another XML value is retrieved
using an XQuery expression. The retrieved XML values are respectively
assigned to two output parameters, out1XML and out2XML.
No result sets are returned. CREATE PROCEDURE xmlProc1 ( IN inNUM INTEGER,
IN inXML XML as CLOB (1K),
OUT inXML XML as CLOB (1K),
OUT inXML XML as CLOB (1K)
)
LANGUAGE C
PARAMETER STYLE SQL
DYNAMIC RESULT SETS 0
FENCED
THREADSAFE
DETERMINISTIC
NO DBINFO
MODIFIES SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'gwenProc!xmlProc1' ; //*************************************************************************
// Stored Procedure: xmlProc1
//
// Purpose: insert XML data into XML column
//
// Parameters:
//
// IN: inNum -- the sequence of XML data to be insert in xmldata table
// inXML -- XML data to be inserted
// OUT: out1XML -- XML data returned - value retrieved using XQuery
// out2XML -- XML data returned - value retrieved using SQL
//*************************************************************************#ifdef __cplusplus
extern "C"
#endif
SQL_API_RC SQL_API_FN testSecA1(sqlint32* inNum,
SQLUDF_CLOB* inXML,
SQLUDF_CLOB* out1XML,
SQLUDF_CLOB* out2XML,
SQLUDF_NULLIND *inNum_ind,
SQLUDF_NULLIND *inXML_ind,
SQLUDF_NULLIND *out1XML_ind,
SQLUDF_NULLIND *out2XML_ind,
SQLUDF_TRAIL_ARGS)
{
char *str;
FILE *file;
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
sqlint32 hvNum1;
SQL TYPE IS XML AS CLOB(200) hvXML1;
SQL TYPE IS XML AS CLOB(200) hvXML2;
SQL TYPE IS XML AS CLOB(200) hvXML3;
EXEC SQL END DECLARE SECTION;
/* Check null indicators for input parameters */
if ((*inNum_ind < 0) || (*inXML_ind < 0)) {
strcpy(sqludf_sqlstate, "38100");
strcpy(sqludf_msgtext, "Received null input");
return 0;
}
/* Copy input parameters to host variables */
hvNum1 = *inNum;
hvXML1.length = inXML->length;
strncpy(hvXML1.data, inXML->data, inXML->length);
/* Execute SQL statement */
EXEC SQL
INSERT INTO xmlDataTable (num, xdata) VALUES (:hvNum1, :hvXML1);
/* Execute SQL statement */
EXEC SQL
SELECT xdata INTO :hvXML2
FROM xmlDataTable
WHERE num = :hvNum1;
sprintf(stmt5, "SELECT XMLQUERY('for $x in $xmldata/doc
return <carInfo>{$x/model}</carInfo>'
passing by ref xmlDataTable.xdata
as \"xmldata\" returning sequence)
FROM xmlDataTable WHERE num = ?");
EXEC SQL PREPARE selstmt5 FROM :stmt5 ;
EXEC SQL DECLARE c5 CURSOR FOR selstmt5;
EXEC SQL OPEN c5 using :hvNum1;
EXEC SQL FETCH c5 INTO :hvXML3;
exit:
/* Set output return code */
*outReturnCode = sqlca.sqlcode;
*outReturnCode_ind = 0;
return 0;
}