/****************************************************************************
** (c) Copyright IBM Corp. 2007 All rights reserved.
**
** The following sample of source code ("Sample") is owned by International
** Business Machines Corporation or one of its subsidiaries ("IBM") and is
** copyrighted and licensed, not sold. You may use, copy, modify, and
** distribute the Sample in any form without payment to IBM, for the purpose of
** assisting you in the development of your applications.
**
** The Sample code is provided to you on an "AS IS" basis, without warranty of
** any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR
** IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF
** MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do
** not allow for the exclusion or limitation of implied warranties, so the above
** limitations or exclusions may not apply to you. IBM shall not be liable for
** any damages you suffer as a result of using, copying, modifying or
** distributing the Sample, even if IBM has been advised of the possibility of
** such damages.
*****************************************************************************
**
** SOURCE FILE NAME: nestif.sqc
**
** SAMPLE: To call the BUMP_SALARY_IF SQL procedure
**
** There are two parts to this program:
** 1. the nestif executable (placed on the client)
** 2. the BUMP_SALARY_IF SQL procedure (created on the
** server with the nestif.db2 CLP script)
**
** nestif calls the BUMP_SALARY_IF SQL procedure by preparing
** and executing a dynamic CALL statement:
**
** sprintf(stmt, "CALL %s (?)", procname);
** EXEC SQL prepare st from :stmt;
** EXEC SQL execute st USING :dept:deptind;
**
** When the CALL with Host Variable is used,
** the precompiler allocates and initializes an internal one
** variable SQLDA for both input and output.
**
** The BUMP_SALARY procedure uses nested IF statements to raise
** the salaries of employees in a department identified by
** the deptnum IN parameter from the "staff" table of the
** "sample" database.
**
** A cursor fetches rows from the "staff" table until the
** "not_found" (end of file) condition is met and the handler
** sets the value of the "at_end" variable to 1, ending the
** WHILE loop.
**
** The first IF statement provides three IF conditions:
**
** Condition 1: Employee salary is less than 2000 * years of service
** Result: Employee salary is updated to 2000 * years of service
**
** Condition 2: Employee salary is less than 5000 * years of service
** (Note that an employee that satisfies condition 1 cannot also
** satisfy condition 2, because the IF statement exits after the
** first condition was matched.)
** Result: Call nested IF statement 2.
**
** Condition 3: Employee salary does not match condition 1 or 2.
** Result: Update employee job to 'PREZ'. Note that any employee
** with a NULL salary would have their job updated to 'PREZ'.
**
** SQL STATEMENTS USED:
** CONNECT
** DECLARE CURSOR
** OPEN
** FETCH
** CLOSE
** CALL
**
**
*****************************************************************************
**
** For more information on the sample programs, see the README file.
**
** For information on creating SQL procedures and developing C applications,
** see the Developing SQL and External Routines book.
**
** For information on using SQL statements, see the SQL Reference.
**
** For the latest information on programming, building, and running DB2
** applications, visit the DB2 Information Center:
** http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
****************************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <sql.h>
#include <sqlda.h>
#include <sqlca.h>
#include <string.h>
#include "utilemb.h"
int main(int argc, char *argv[]) {
EXEC SQL BEGIN DECLARE SECTION;
char database[9];
char userid[9];
char passwd[19];
/* Declare a Local Variable for Holding the Procedure's Name */
char procname[254] = "BUMP_SALARY_IF";
/* Declare a statement strings to call the procedure dynamically */
char stmt[1200];
/* Declare local variables for cursor statements */
char name[10];
sqlint16 dept_c = 0;
sqlint16 years = 0;
double salary = 0;
char job[6];
/* Declare Local Variables for Holding Returned Data */
sqlint16 deptnum = 20;
sqlint16 deptind = 0;
EXEC SQL END DECLARE SECTION;
/* Declare the output SQLDA */
struct sqlda *inout_sqlda = (struct sqlda *)
malloc(SQLDASIZE(1)); /* :rk.2:erk. */
/* Declare the SQLCA */
struct sqlca sqlca;
char eBuffer[1024]; /* error message buffer */
if (argc != 4) {
printf ("\nUSAGE: nestif remote_database userid passwd\n\n");
return 1;
}
strcpy (database, argv[1]);
strcpy (userid, argv[2]);
strcpy (passwd, argv[3]);
/* Connect to Remote Database */
printf("CONNECT TO Remote Database.\n");
EXEC SQL CONNECT TO :database USER :userid USING :passwd;
EMB_SQL_CHECK("CONNECT TO RSAMPLE");
/********************************************************\
* Display the contents of STAFF table before CALL *
\********************************************************/
EXEC SQL DECLARE c1 CURSOR FOR
SELECT name, dept, years, salary, job FROM staff
WHERE dept = :deptnum
FOR READ ONLY;
EXEC SQL OPEN c1;
EMB_SQL_CHECK("OPEN CURSOR");
printf("\nDepartment %d before calling %s:\n", deptnum, procname);
printf("\n%-9s %6s %5s %10s %-5s\n", "NAME", "DEPT", "YEARS", "SALARY", "JOB");
do {
EXEC SQL FETCH c1 INTO :name, :dept_c, :years, :salary, :job;
if (SQLCODE != 0) break;
printf( "%-9s %6d %5d %10.2f %-5s\n", name, dept_c, years, salary, job );
} while ( 1 );
EXEC SQL CLOSE c1;
EMB_SQL_CHECK("CLOSE CURSOR");
/********************************************************\
* Call the Remote Procedure via CALL with Host Variables *
\********************************************************/
printf("\nUse CALL with Host Variable to invoke the Server Procedure "
"named %s\n", procname);
sprintf(stmt, "CALL %s (?)", procname);
EXEC SQL prepare st from :stmt;
EMB_SQL_CHECK("PREPATE CALL STATEMENT");
EXEC SQL execute st USING :deptnum:deptind;
EMB_SQL_CHECK("EXECUTECALL STATEMENT");
/* COMMIT or ROLLBACK the transaction */
if (SQLCODE == 0)
{
EXEC SQL OPEN c1;
EMB_SQL_CHECK("OPEN CURSOR");
printf("\nDepartment %d after calling %s:\n", deptnum, procname);
printf("%-9s %6s %5s %10s %-5s\n", "NAME", "DEPT", "YEARS", "SALARY", "JOB");
do {
EXEC SQL FETCH c1 INTO :name, :dept_c, :years, :salary, :job;
if (SQLCODE != 0) break;
printf("%-9s %6d %5d %10.2f %-5s\n", name, dept_c, years, salary, job );
} while ( 1 );
EXEC SQL CLOSE c1;
EMB_SQL_CHECK("CLOSE CURSOR");
EXEC SQL ROLLBACK;
/* to preserve the sample database, changes are not committed;
to commit the changes, you should use:
EXEC SQL COMMIT */
printf("Server Procedure Complete.\n");
}
else
{ /* print the error message, roll back the transaction and return */
sqlaintp (eBuffer, 1024, 80, &sqlca);
printf("\n%s\n", eBuffer);
EXEC SQL ROLLBACK;
printf("Server Procedure Transaction Rolled Back.\n\n");
return 1;
}
/* Free allocated memory */
free( inout_sqlda );
/* Disconnect from Remote Database */
EXEC SQL CONNECT RESET;
EMB_SQL_CHECK("CONNECT RESET");
return 0;
}
/* end of program : nestif.sqc */