/******************************************************************************
**
** Source File Name = spserver.sqc
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 2000
** All Rights Reserved.
**
** US Government Users Restricted Rights - Use, duplication or
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
**
**
** PURPOSE: This sample program demonstrates stored procedures.
**
** BUILDING THE SHARED LIBRARY:
** 1. Ensure the Database Manager Configuration file has the keyword KEEPDARI
** set to "no". This allows unloading shared libraries while developing stored
** procedures. You can view the file's settings by entering the command:
** "db2 get dbm cfg". You can set KEEPDARI to "no" with this command:
** "db2 update dbm cfg using KEEPDARI no".
** NOTE: Setting KEEPDARI to "no" causes a performance cost when accessing the stored
** procedures because they have to be reloaded into memory each time they are called.
** If this is a consideration, use the KEEPDARI "yes" setting, and stop and restart
** DB2 before building the shared library, by entering "db2stop" followed by "db2start".
** This forces DB2 to unload shared libraries and enables the build file or the makefile
** to delete a previous version of the shared library from the "function" directory.
** 2. To build the shared library, enter "bldsrv spserver" (UNIX & OS/2), "make
** spserver" (UNIX), "nmake spserver" (OS/2 & Windows), or for the Microsoft Visual
** C++ compiler on Windows, enter "bldmsrv spserver", or for the VisualAge C++ compiler
** on Windows, enter "bldvsrv spserver".
** 3. To catalog the stored procedures in the shared library, first connect to the
** database by entering: "db2 connect to sample".
** 4. If the stored procedures were previously cataloged for a different language,
** uncatalog them by entering: "db2 -td@ -vf spdrop.db2".
** 5. Then, catalog the stored procedures by entering: "db2 -td@ -vf spcreate.db2".
**
** EXTERNAL DEPENDENCIES:
** - This program must be built on a DB2 server.
** - Ensure existence of the sample database.
** - Precompile with the SQL precompiler (PREP in DB2)
** - Bind to a database (BIND in DB2)
** - Compile and link loop with the compiler supported
** on your platform.
**
** For more information about the samples refer to the README file.
**
** For more information on programming in C, refer to the:
** - "Programming in C and C++" section of the Application Development Guide
** For more information on Building C Applications, refer to the:
** - "Building C Applications" section of the Application Building Guide.
**
** For more information on the SQL language refer to the SQL Reference.
**
**
******************************************************************************/
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlda.h>
#include <sqlca.h>
#include <sqludf.h>
#include <sql.h>
#include <memory.h>
/* Declare function prototypes for this stored procedure library */
SQL_API_RC SQL_API_FN outlanguage (char *);
SQL_API_RC SQL_API_FN out_param (double *, sqlint32 *, char *); /* :rk.1:erk. */
SQL_API_RC SQL_API_FN in_params (double *, double *, double *,
char *, sqlint32 *, char *);
SQL_API_RC SQL_API_FN inout_param (double *inoutMedian, sqlint32 *, char *, sqlint16 *);
SQL_API_RC SQL_API_FN one_result_set_to_client (double *, sqlint32 *);
SQL_API_RC SQL_API_FN two_result_sets (double *, sqlint32 *);
SQL_API_RC SQL_API_FN extract_from_clob (char *, char *, sqlint32 * );
SQL_API_RC SQL_API_FN all_data_types (sqlint16 *, sqlint32 *, sqlint64 *, float *,
double *, char *, char *, char *, char *, char *,
sqlint32 *, char *);
SQL_API_RC SQL_API_FN db2sql_example (char *, double *, sqlint16 *, char *, char *,
char *, char *);
SQL_API_RC SQL_API_FN dbinfo_example (char *, double *, char *, char *, sqlint32 *, struct sqludf_dbinfo *);
SQL_API_RC SQL_API_FN main_example (int, char **);
struct sqlca sqlca;
/* Declare host variables for basic error-handling */
EXEC SQL BEGIN DECLARE SECTION;
sqlint32 out_sqlcode;
char buffer[33];
/* Declare host variables used by multiple stored procedures */
sqlint16 numRecords;
double medianSalary;
EXEC SQL END DECLARE SECTION;
SQL_API_RC SQL_API_FN outlanguage (char language[9])
{
struct sqlca sqlca;
EXEC SQL BEGIN DECLARE SECTION;
char out_lang[9];
EXEC SQL END DECLARE SECTION;
/* Initialize strings used for output parameters to NULL */
memset(language, '\0', 9);
EXEC SQL SELECT language INTO :out_lang
FROM sysibm.sysprocedures
WHERE procname = 'OUT_LANGUAGE';
strcpy(language, out_lang);
return (0);
} /* end outlanguage function */
SQL_API_RC SQL_API_FN out_param (double *outMedianSalary,
sqlint32 *out_sqlerror, char buffer[33])
{
struct sqlca sqlca;
EXEC SQL WHENEVER SQLERROR GOTO return_error;
int counter;
/* Initialize strings used for output parameters to NULL */
memset(buffer, '\0', 33);
counter = 0;
*out_sqlerror = 0;
strcpy(buffer, "DECLARE c1");
EXEC SQL DECLARE c1 CURSOR FOR /* :rk.2:erk. */
SELECT CAST(salary AS DOUBLE) FROM staff
ORDER BY salary;
strcpy(buffer, "SELECT");
EXEC SQL SELECT COUNT(*) INTO :numRecords FROM staff; /* :rk.3:erk. */
strcpy(buffer, "OPEN");
EXEC SQL OPEN c1;
strcpy(buffer, "FETCH");
while (counter < (numRecords / 2 + 1))
{
EXEC SQL FETCH c1 INTO :medianSalary; /* :rk.4:erk. */
counter = counter + 1;
}
/* Set value of OUT parameter to host variable */
*outMedianSalary = medianSalary; /* :rk.5:erk. */
strcpy(buffer, "CLOSE c1");
EXEC SQL CLOSE c1;
return (0); /* :rk.6:erk. */
/* Copy SQLCODE to OUT parameter if SQL error occurs */
return_error:
{
*out_sqlerror = SQLCODE;
EXEC SQL WHENEVER SQLERROR CONTINUE;
return (0);
}
} /* end out_param function */
SQL_API_RC SQL_API_FN in_params (double *inlowsal,
double *inmedsal,
double *inhighsal,
char indept[4],
sqlint32 *out_sqlerror, char buffer[33])
{
struct sqlca sqlca;
EXEC SQL WHENEVER SQLERROR GOTO return_error;
EXEC SQL BEGIN DECLARE SECTION;
double lowsal;
double medsal;
double highsal;
double salary;
char department[4];
EXEC SQL END DECLARE SECTION;
/* Initialize strings used for output parameters to NULL */
memset(buffer, '\0', 33);
*out_sqlerror = 0;
lowsal = *inlowsal;
medsal = *inmedsal;
highsal = *inhighsal;
strcpy(department, indept);
strcpy(buffer, "DECLARE");
EXEC SQL DECLARE c2 CURSOR FOR
SELECT CAST(salary AS DOUBLE)
FROM employee
WHERE workdept = :department
FOR UPDATE;
strcpy(buffer, "OPEN c2");
EXEC SQL OPEN c2;
strcpy(buffer, "FETCH (1)");
EXEC SQL FETCH c2 INTO :salary;
if (SQLCODE == 100)
{
/* Fetch returned no data, so exit the stored procedure */
*out_sqlerror = SQLCODE;
strcpy (buffer, "NO DATA FOUND");
/* Close cursor before exiting */
EXEC SQL CLOSE c2;
return (0);
}
while (*out_sqlerror == 0)
{
if (lowsal > salary)
{
strcpy(buffer, "UPDATE (1)");
EXEC SQL UPDATE employee
SET salary = :lowsal
WHERE CURRENT OF c2;
}
else if (medsal > salary)
{
strcpy(buffer, "UPDATE (2)");
EXEC SQL UPDATE employee
SET salary = :medsal
WHERE CURRENT OF c2;
}
else if (highsal > salary)
{
strcpy(buffer, "UPDATE (3)");
EXEC SQL UPDATE employee
SET salary = :highsal
WHERE CURRENT OF c2;
}
else
{
strcpy(buffer, "UPDATE (4)");
EXEC SQL UPDATE employee
SET salary = :salary * 1.10
WHERE CURRENT OF c2;
}
strcpy(buffer, "FETCH (2)");
EXEC SQL FETCH c2 INTO :salary;
/* Catch NOT FOUND condition */
*out_sqlerror = SQLCODE;
}
strcpy(buffer, "CLOSE c2");
EXEC SQL CLOSE c2;
/* Return final value of SQLCODE */
*out_sqlerror = SQLCODE;
return (0);
/* Copy SQLCODE to OUT parameter if SQL error occurs */
return_error:
{
*out_sqlerror = SQLCODE;
EXEC SQL WHENEVER SQLERROR CONTINUE;
return (0);
}
} /* end in_params function */
SQL_API_RC SQL_API_FN inout_param (double *inoutMedian, sqlint32 *out_sqlerror,
char buffer[33], sqlint16 nullinds[3])
{
struct sqlca sqlca;
EXEC SQL WHENEVER SQLERROR GOTO return_error;
int counter;
/* Initialize strings used for output parameters to NULL */
memset(buffer, '\0', 33);
*out_sqlerror = 0;
if (nullinds[0] < 0)
{
/* NULL value was received as input, so return NULL output */
nullinds[0] = -1;
nullinds[1] = -1;
nullinds[2] = -1;
}
else
{
counter = 0;
*out_sqlerror = 0;
medianSalary = *inoutMedian;
strcpy(buffer, "DECLARE inout CURSOR");
EXEC SQL DECLARE inout CURSOR FOR
SELECT CAST(salary AS DOUBLE) FROM staff
WHERE salary > :medianSalary
ORDER BY salary;
nullinds[1] = 0;
nullinds[2] = 0;
strcpy(buffer, "SELECT COUNT INTO numRecords");
EXEC SQL SELECT COUNT(*) INTO :numRecords
FROM staff
WHERE salary > :medianSalary;
if (numRecords != 0)
/* At least one record was found */
{
strcpy(buffer, "OPEN inout");
EXEC SQL OPEN inout USING :medianSalary;
strcpy(buffer, "FETCH inout");
while (counter < (numRecords / 2 + 1))
{
EXEC SQL FETCH inout INTO :medianSalary;
counter = counter + 1;
}
/* Set value of INOUT parameter to host variable */
*inoutMedian = medianSalary;
strcpy(buffer, "CLOSE inout");
EXEC SQL CLOSE inout;
}
else /* No records were found */
{
/* Return 100 to indicate NOT FOUND error */
*out_sqlerror = 100;
}
}
return (0);
/* Copy SQLCODE to OUT parameter if SQL error occurs */
return_error:
{
*out_sqlerror = SQLCODE;
EXEC SQL WHENEVER SQLERROR CONTINUE;
return (0);
}
} /* end inout_param function */
SQL_API_RC SQL_API_FN one_result_set_to_client (double *insalary, sqlint32 *out_sqlerror)
{
struct sqlca sqlca;
EXEC SQL WHENEVER SQLERROR GOTO return_error;
EXEC SQL BEGIN DECLARE SECTION;
double l_insalary;
EXEC SQL END DECLARE SECTION;
l_insalary = *insalary;
*out_sqlerror = 0;
EXEC SQL DECLARE c3 CURSOR FOR
SELECT name, job, CAST(salary AS DOUBLE)
FROM staff
WHERE salary > :l_insalary
ORDER BY salary;
EXEC SQL OPEN c3;
/* Leave cursor open to return result set */
return (0);
/* Copy SQLCODE to OUT parameter if SQL error occurs */
return_error:
{
*out_sqlerror = SQLCODE;
EXEC SQL WHENEVER SQLERROR CONTINUE;
return (0);
}
} /* end one_result_set_to_client function */
SQL_API_RC SQL_API_FN two_result_sets (double *inMedianSalary, sqlint32 *out_sqlerror)
{
struct sqlca sqlca;
EXEC SQL WHENEVER SQLERROR GOTO return_error;
medianSalary = *inMedianSalary;
*out_sqlerror = 0;
EXEC SQL DECLARE r1 CURSOR FOR
SELECT name, job, CAST(salary AS DOUBLE)
FROM staff
WHERE salary > :medianSalary
ORDER BY salary;
EXEC SQL DECLARE r2 CURSOR FOR
SELECT name, job, CAST(salary AS DOUBLE)
FROM staff
WHERE salary < :medianSalary
ORDER BY salary DESC;
EXEC SQL OPEN r1;
EXEC SQL OPEN r2;
/* Leave cursors open to return result sets */
return (0);
/* Copy SQLCODE to OUT parameter if SQL error occurs */
return_error:
{
*out_sqlerror = SQLCODE;
EXEC SQL WHENEVER SQLERROR CONTINUE;
return (0);
}
} /* end two_result_sets function */
SQL_API_RC SQL_API_FN extract_from_clob (
char emp_number[7], /* IN parameter for employee number */
char out_dept[1001], /* OUT parameter for department information */
sqlint32 *out_sqlerror /* OUT parameter for SQLCODE */
)
{
struct sqlca sqlca;
EXEC SQL BEGIN DECLARE SECTION;
char in_number[7];
char out_resume[1001];
sqlint32 deptInfoBeginLoc;
sqlint32 deptInfoEndLoc;
SQL TYPE IS CLOB_LOCATOR resume;
SQL TYPE IS CLOB_LOCATOR deptBuffer;
sqlint16 lobind;
EXEC SQL END DECLARE SECTION;
EXEC SQL WHENEVER SQLERROR GOTO return_error;
char error_buffer[200];
/* Initialize strings used for output parameters to NULL */
memset(out_resume, '\0', 1001);
memset(out_dept, '\0', 1001);
*out_sqlerror = 0;
strcpy(in_number, emp_number);
strcpy(error_buffer, "An SQL error or warning occurred in the stored procedure.");
strcat(error_buffer, "Problem Location: Selecting CLOB from database");
EXEC SQL SELECT resume INTO :resume :lobind
FROM emp_resume
WHERE resume_format='ascii' AND empno = :in_number;
if (lobind < 0)
{
strcpy (error_buffer, "A NULL value was indicated for the requested LOB.\n");
}
else
{
/* Locate the beginning of "Department Information" section */
strcpy(error_buffer, "An SQL error or warning occurred in the stored procedure.\n");
strcat(error_buffer, "Problem Location: Locating beginning of Department Information in CLOB");
EXEC SQL VALUES (POSSTR(:resume, 'Department Information'))
INTO :deptInfoBeginLoc;
/* Locate the beginning of "Education" section (end of "Dept.Info" */
strcpy(error_buffer, "An SQL error or warning occurred in the stored procedure.\n");
strcat(error_buffer, "Problem Location: Locating beginning of Department Information in CLOB");
EXEC SQL VALUES (POSSTR(:resume, 'Education'))
INTO :deptInfoEndLoc;
/* Obtain ONLY the "Department Information" section by using SUBSTR */
strcpy(error_buffer, "An SQL error or warning occurred in the stored procedure.\n");
strcat(error_buffer, "Problem Location: Locating beginning of Department Information in CLOB");
EXEC SQL VALUES(SUBSTR(:resume, :deptInfoBeginLoc, :deptInfoEndLoc - :deptInfoBeginLoc))
INTO :deptBuffer;
/* Append the "Department Information" section to the :out_resume var. */
strcpy(error_buffer, "An SQL error or warning occurred in the stored procedure.\n");
strcat(error_buffer, "Problem Location: Locating beginning of Department Information in CLOB");
EXEC SQL VALUES(:out_resume || :deptBuffer)
INTO :out_resume;
strcpy(out_dept, out_resume);
} /* endif */
strcpy(error_buffer, "An SQL error or warning occurred in the stored procedure.\n");
strcat(error_buffer, "Problem Location: freeing the CLOB locator");
EXEC SQL FREE LOCATOR :resume, :deptBuffer;
return (0);
/* Copy SQLCODE to OUT parameter if SQL error occurs */
return_error:
{
*out_sqlerror = SQLCODE;
/* Copy error_buffer to out_dept OUT parameter if SQL error occurs */
strcpy(out_dept, error_buffer);
EXEC SQL WHENEVER SQLERROR CONTINUE;
return (0);
}
} /* end extract_from_clob procedure */
SQL_API_RC SQL_API_FN all_data_types (sqlint16 *smallInOut, sqlint32 *intInOut, sqlint64 *bigInOut,
float *realInOut, double *doubleInOut,
char *charOut, char charsOut[16], char varcharOut[13],
char dateOut[11], char timeOut[9], sqlint32 *out_sqlerror,
char buffer[33])
{
struct sqlca sqlca;
EXEC SQL WHENEVER SQLERROR GOTO return_error;
EXEC SQL BEGIN DECLARE SECTION;
char hvcharOut; /* CHAR(1) host variable */
char hvcharsOut[16]; /* CHAR(15) */
char hvvarcharOut[13]; /* VARCHAR(12) */
char hvdateOut[11]; /* DATE */
char hvtimeOut[9]; /* TIME */
EXEC SQL END DECLARE SECTION;
/* Initialize strings used for output parameters to NULL */
memset(charsOut, '\0', 16);
memset(varcharOut, '\0', 13);
memset(buffer, '\0', 33);
*out_sqlerror = 0;
if (*smallInOut == 0) { *smallInOut = 1; }
else { *smallInOut = (*smallInOut / 2); }
if (*intInOut == 0) { *intInOut = 1; }
else { *intInOut = (*intInOut / 2); }
if (*bigInOut == 0) { *bigInOut = 1; }
else { *bigInOut = (*bigInOut / 2); }
if (*realInOut == 0) { *realInOut = 1; }
else { *realInOut = (*realInOut / 2); }
if (*doubleInOut == 0) { *doubleInOut = 1; }
else { *doubleInOut = (*doubleInOut / 2); }
strcpy(buffer, "SELECT midinit");
EXEC SQL SELECT midinit INTO :hvcharOut
FROM employee
WHERE empno = '000180';
/* Treat CHAR(1) as a numeric data type */
*charOut = hvcharOut;
strcpy(buffer, "SELECT lastname");
EXEC SQL SELECT lastname INTO :hvcharsOut
FROM employee
WHERE empno = '000180';
strcpy(charsOut, hvcharsOut);
strcpy(buffer, "SELECT firstnme");
EXEC SQL SELECT firstnme INTO :hvvarcharOut
FROM employee
WHERE empno = '000180';
strcpy(hvvarcharOut, varcharOut);
strcpy(buffer, "VALUES CURRENT DATE");
EXEC SQL VALUES CURRENT DATE INTO :hvdateOut;
strcpy(dateOut, hvdateOut);
strcpy(buffer, "VALUES CURRENT TIME");
EXEC SQL VALUES CURRENT TIME INTO :hvtimeOut;
strcpy(timeOut, hvtimeOut);
return (0);
/* Copy SQLCODE to OUT parameter if SQL error occurs */
return_error:
{
*out_sqlerror = SQLCODE;
EXEC SQL WHENEVER SQLERROR CONTINUE;
return (0);
}
} /* end all_data_types procedure */
SQL_API_RC SQL_API_FN db2sql_example (
char injob[9], /* Input - CHAR(8) */
double *salary, /* Output - DOUBLE */
sqlint16 nullinds[2], /* Inout - Array[n] of null indicators
where n = number of parameters
declared by user */
char sqlst[6], /* Output - SQLSTATE as CHAR(5) */
char qualname[28], /* Input - qualified name as CHAR(27) */
char specname[19], /* Input - specific name as CHAR(18) */
char diagmsg[71] /* Output - diagnostic message as CHAR(70) */
)
{
struct sqlca sqlca;
EXEC SQL BEGIN DECLARE SECTION;
char db2sql_injob[9];
double db2sql_outsalary;
sqlint16 db2sql_outsalaryind;
EXEC SQL END DECLARE SECTION;
if (nullinds[0] < 0)
{
/* NULL value was received as input, so return NULL output */
nullinds[1] = -1;
/* Set custom SQLSTATE to return to client. */
strcpy(sqlst, "38100");
/* Set custom message to return to client. Note that although the
OUT parameter is declared as CHAR(70), DB2 prepends the
procedure name and shared library entry point to the message.
Keep the custom message short to avoid truncation. */
strcpy(diagmsg, "Received null input.");
}
else
{
strcpy(db2sql_injob, injob);
EXEC SQL SELECT (CAST(AVG(salary) AS DOUBLE))
INTO :db2sql_outsalary INDICATOR :db2sql_outsalaryind
FROM employee
WHERE job = :db2sql_injob;
*salary = db2sql_outsalary;
nullinds[1] = db2sql_outsalaryind;
}
return (0);
} /* end db2sql_example function */
SQL_API_RC SQL_API_FN dbinfo_example (
char injob[9], /* Input - CHAR(8) */
double *salary, /* Output - DOUBLE */
char dbname[129], /* Output - database name from DBINFO */
char dbversion[9], /* Output - database version from DBINFO */
sqlint32 *out_sqlerror,
struct sqludf_dbinfo *dbinfo /* Pointer to DBINFO structure */)
{
struct sqlca sqlca;
EXEC SQL WHENEVER SQLERROR GOTO return_error;
EXEC SQL BEGIN DECLARE SECTION;
char dbinfo_injob[9];
double dbinfo_outsalary;
sqlint16 dbinfo_outsalaryind;
EXEC SQL END DECLARE SECTION;
/* Initialize strings used for output parameters to NULL */
memset(dbname, '\0', 129);
memset(dbversion, '\0', 9);
*out_sqlerror = 0;
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.
*/
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:
{
*out_sqlerror = SQLCODE;
EXEC SQL WHENEVER SQLERROR CONTINUE;
return (0);
}
} /* end dbinfo_example function */
SQL_API_RC SQL_API_FN main_example (int argc, char **argv)
{
struct sqlca sqlca;
EXEC SQL BEGIN DECLARE SECTION;
char main_injob[9];
double main_outsalary;
EXEC SQL END DECLARE SECTION;
/* argv[0] contains the procedure name, so parameters start at argv[1] */
strcpy (main_injob, (char *)argv[1]);
EXEC SQL SELECT AVG(salary)
INTO :main_outsalary
FROM employee
WHERE job = :main_injob;
memcpy ((double *)argv[2], (double *)&main_outsalary, sizeof(double));
memcpy ((sqlint32 *)argv[3], (sqlint32 *)&SQLCODE, sizeof(sqlint32));
return (0);
} /* end main_example function */