The dbinfo structure as C or C++ routine parameter
The dbinfo structure is a structure that
contains database and routine information that can be passed to and
from a routine implementation as an extra argument if and only if
the DBINFO clause is included in the CREATE statement for the routine.
The dbinfo structure
is supported in LANGUAGE C routines by using the sqludf_dbinfo structure. The dbinfo C structure is defined in
the sqludf.h file that is located in the sqllib\include directory.
The
sqludf_dbinfo structure is defined as follows:
SQL_STRUCTURE sqludf_dbinfo
{
unsigned short dbnamelen; /* Database name length */
unsigned char dbname[SQLUDF_MAX_IDENT_LEN]; /* Database name */
unsigned short authidlen; /* Authorization ID length */
unsigned char authid[SQLUDF_MAX_IDENT_LEN]; /* Authorization ID */
union db_cdpg codepg; /* Database code page */
unsigned short tbschemalen; /* Table schema name length */
unsigned char tbschema[SQLUDF_MAX_IDENT_LEN]; /* Table schema name */
unsigned short tbnamelen; /* Table name length */
unsigned char tbname[SQLUDF_MAX_IDENT_LEN]; /* Table name */
unsigned short colnamelen; /* Column name length */
unsigned char colname[SQLUDF_MAX_IDENT_LEN]; /* Column name */
unsigned char ver_rel[SQLUDF_SH_IDENT_LEN]; /* Database version/release */
unsigned char resd0[2]; /* Alignment */
sqluint32 platform; /* Platform */
unsigned short numtfcol; /* # of entries in TF column*/
/* List array */
unsigned char resd1[2]; /* Reserved */
sqluint32 procid; /* Current procedure ID */
unsigned char resd2[32]; /* Reserved */
unsigned short *tfcolumn; /* Tfcolumn to be allocated */
/* dynamically if a table */
/* function is defined; */
/* else a NULL pointer */
char *appl_id; /* Application identifier */
sqluint32 dbpartitionnum; /* Database partition number*/
/* where routine executed */
sqluint32 numdbpartitions; /* number of entries in */
/* dbpartitions array */
sqluint32 *dbpartitions; /* allocated dynamically if */
/* routine is processed in */
/* parallel. Otherwise, this*/
/* will be a null pointer. */
unsigned char resd3[16]; /* Reserved */
};dbinfo structure
might be useful within a routine, several of the values in the structure
fields might be useful when formulating diagnostic error message information.
For example, if an error occurs within a routine, it might be useful
to return the database name, database name length, the database code
page, the current authorization ID, and the length of the current
authorization ID.To reference the
sqludf_dbinfo structure in a
LANGUAGE C routine implementation: - Add the DBINFO clause to the CREATE statement that defines the routine.
- Include the sqludf.h header file at the top of the file containing the routine implementation.
- Add a parameter of type
sqludf_dbinfoto the routine signature in the position specified by the parameter style used.
Example of a C procedure
using the dbinfo structure
The following
example of a C procedure with PARAMETER STYLE GENERAL demonstrates
the use of the dbinfo structure.
Here is the CREATE PROCEDURE statement for the procedure. The procedure implementation is located
in a library file named
spserver that contains a C function named
DbinfoExample, as specified by the EXTERNAL NAME clause: CREATE PROCEDURE DBINFO_EXAMPLE (IN job CHAR(8),
OUT salary DOUBLE,
OUT dbname CHAR(128),
OUT dbversion CHAR(8),
OUT errorcode INTEGER)
DYNAMIC RESULT SETS 0
LANGUAGE C
PARAMETER STYLE GENERAL
DBINFO
FENCED NOT THREADSAFE
READS SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'spserver!DbinfoExample'@/***************************************************************
Routine: DbinfoExample
IN: inJob - a job type, used in a SELECT predicate
OUT: salary - average salary of employees with job injob
dbname - database name retrieved from DBINFO
dbversion - database version retrieved from DBINFO
outSqlError - sqlcode of error raised (if any)
sqludf_dbinfo - pointer to DBINFO structure
Purpose: This routine takes in a job type and returns the
average salary of all employees with that job, as
well as information about the database (name,
version of database). The database information
is retrieved from the dbinfo object.
Shows how to:
- define IN/OUT parameters in PARAMETER STYLE GENERAL
- declare a parameter pointer to the dbinfo structure
- retrieve values from the dbinfo structure
*****************************************************************/
SQL_API_RC SQL_API_FN DbinfoExample(char inJob[9],
double *salary,
char dbname[129],
char dbversion[9],
sqlint32 *outSqlError,
struct sqludf_dbinfo * dbinfo
)
{
/* Declare a local SQLCA */
struct sqlca sqlca;
EXEC SQL WHENEVER SQLERROR GOTO return_error;
/* SQL host variable declaration section */
/* Each host variable names must be unique within a code
file, or the precompiler raises SQL0307 error */
EXEC SQL BEGIN DECLARE SECTION;
char dbinfo_injob[9];
double dbinfo_outsalary;
sqlint16 dbinfo_outsalaryind;
EXEC SQL END DECLARE SECTION;
/* Initialize output parameters - se strings to NULL */
memset(dbname, '\0', 129);
memset(dbversion, '\0', 9);
*outSqlError = 0;
/* Copy input parameter into local host variable */
strcpy(dbinfo_injob, inJob);
EXEC SQL SELECT AVG(salary) INTO:dbinfo_outsalary
FROM employee
WHERE job =:dbinfo_injob;
*salary = dbinfo_outsalary;
/* Copy values from the DBINFO structure into the output parameters
You must explicitly null-terminate the strings.
Information such as the database name, and the version of the
database product can be found in the DBINFO structure as well as
other information fields. */
strncpy(dbname, (char *)(dbinfo->dbname), dbinfo->dbnamelen);
dbname[dbinfo->dbnamelen] = '\0';
strncpy(dbversion, (char *)(dbinfo->ver_rel), 8);
dbversion[8] = '\0';
return 0;
/* Copy SQLCODE to OUT parameter if SQL error occurs */
return_error:
{
*outSqlError = SQLCODE;
EXEC SQL WHENEVER SQLERROR CONTINUE;
return 0;
}
} /* DbinfoExample function */