/****************************************************************************
** (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: basecase.sqc
**
** SAMPLE: To call the UPDATE_SALARY SQL procedure
**
** There are two parts to this program:
** 1. the basecase executable (placed on the client)
** 2. the UPDATE_SALARY SQL procedure (created on the
** server with the basecase.db2 CLP script)
**
** basecase calls the UPDATE_SALARY 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 :empno:empnoind, :rating:ratingind;
**
** When the CALL with Host Variable is used,
** the precompiler allocates and initializes an internal one
** variable SQLDA for both input and output.
**
** The UPDATE_SALARY procedure raises the salary of an
** employee identified by the "empno" IN parameter
** in the "staff" table of the "sample" database.
** The procedure determines the raise according to a CASE
** statement that uses the "rating" IN parameter.
**
** 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] = "UPDATE_SALARY";
/* Declare a statement string to call the procedure dynamically */
char stmt[1200];
/* Declare local variables for holding returned values */
double sal = 0;
double bon = 0;
/* Declare Local Variables for Passing Data to SQL Procedure */
char empno[7] = "000100";
sqlint16 empnoind = 0;
sqlint32 rating = 1;
sqlint16 ratingind = 0;
EXEC SQL END DECLARE SECTION;
/* Declare the output SQLDA */
struct sqlda *inout_sqlda = (struct sqlda *)
malloc(SQLDASIZE(1));
/* Declare the SQLCA */
struct sqlca sqlca;
char eBuffer[1024]; /* error message buffer */
if (argc != 4) {
printf ("\nUSAGE: basecase 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 employee info before issuing CALL statement *
\********************************************************/
EXEC SQL DECLARE c1 CURSOR FOR
SELECT salary, bonus
FROM employee
WHERE empno = :empno
FOR READ ONLY;
EXEC SQL OPEN c1;
EMB_SQL_CHECK("OPEN CURSOR");
EXEC SQL FETCH c1 INTO :sal, :bon;
EXEC SQL CLOSE c1;
printf("\nEmployee number %s before CALL: salary = %9.2f, bonus = %9.2f\n", empno, sal, bon);
/********************************************************\
* Call the Remote Procedure via CALL with Host Variables *
\********************************************************/
printf("Use CALL with Host Variables to invoke the Server Procedure "
"named %s\n", procname);
sprintf(stmt, "CALL %s (?,?)", procname);
EXEC SQL prepare st from :stmt;
EMB_SQL_CHECK("PREPARECALL STATEMENT");
EXEC SQL execute st USING :empno:empnoind, :rating:ratingind;
EMB_SQL_CHECK("EXECUTECALL STATEMENT");
/********************************************************\
* Display the employee info after issuing CALL statement *
\********************************************************/
EXEC SQL OPEN c1;
EMB_SQL_CHECK("OPEN CURSOR");
EXEC SQL FETCH c1 INTO :sal, :bon;
EXEC SQL CLOSE c1;
printf("\nEmployee number %s after CALL: salary = %9.2f, bonus = %9.2f\n", empno, sal, bon);
/* COMMIT or ROLLBACK the transaction */
if (SQLCODE == 0)
{ /* Successful, but rollback the changes to the database */
EXEC SQL ROLLBACK;
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 : basecase.sqc */