/******************************************************************************
**
** Source File Name = tabsql.sqc
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 1995, 2000
** All Rights Reserved.
**
** US Government Users Restricted Rights - Use, duplication or
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
**
**
** PURPOSE : an example of using advanced SQL statements.
** The advanced SQL statement to be executed and displayed is:
** The following example is described thoroughly in the
** "Common Table Expressions" of "Using SQL - Advanced"
** WITH
** PAYLEVEL AS (SELECT EMPNO, YEAR(HIREDATE) AS HIREYEAR, EDLEVEL,
** SALARY+BONUS+COMM AS TOTAL_PAY FROM EMPLOYEE
** WHERE EDLEVEL > 16),
** PAYBYED (EDUC_LEVEL, YEAR_OF_HIRE, AVG_TOTAL_PAY) AS
** (SELECT EDLEVEL, HIREYEAR, AVG(TOTAL_PAY) FROM PAYLEVEL
** GROUP BY EDLEVEL, HIREYEAR)
** SELECT EMPNO, EDLEVEL, YEAR_OF_HIRE, TOTAL_PAY, AVG_TOTAL_PAY
** FROM PAYLEVEL, PAYBYED
** WHERE EDLEVEL=EDUC_LEVEL
** AND HIREYEAR = YEAR_OF_HIRE
** AND TOTAL_PAY < AVG_TOTAL_PAY;
**
** The second example is described in the "Nested Table
** Expressions" section of "Using SQL - Advanced".
** SELECT EDLEVEL, HIREYEAR, AVG(TOTAL_PAY)
** FROM (
** SELECT EMPNO, YEAR(HIREDATE) AS HIREYEAR, EDLEVEL,
** SALARY+BONUS+COMM AS TOTAL_PAY FROM EMPLOYEE
** WHERE EDLEVEL > 16
** ) AS PAY_LEVEL
** GROUP BY EDLEVEL, HIREYEAR
**
**
** STRUCTURES USED :
** sqlca
** sqlda
**
** APIs USED :
**
** FUNCTIONS DECLARED :
** 'C' COMPILER LIBRARY :
** stdio.h - printf
** string.h - strncpy
**
** OTHER :
** external : [in the file UTIL.C]
** check_error : Checks for SQLCODE error, and prints out any
** related information available.
** init_da: Initialiaze the memory space for an SQLDA.
** alloc_host_vars: Allocate memory to be filled with SQLDA
** data.
** free_da: Frees up memory which has been allocated
** as an SQLDA structure.
** display_col_titles: Display column titles.
** display_da: Display row information.
**
** EXTERNAL DEPENDANCIES :
** - Ensure existence of database for precompile purposes.
** - Precompile with the SQL precompiler (PREP in DB2)
** - Bind to a database (BIND in DB2)
** - Compile and link with the IBM Cset++ compiler (AIX and OS/2)
** or the Microsoft Visual C++ compiler (Windows)
** or the compiler supported on your platform.
**
** For more information about these samples see the README file.
**
** For more information on programming in C, see the:
** - "Programming in C and C++" section of the Application Development Guide
** For more information on Building C Applications, see the:
** - "Building C Applications" section of the Application Building Guide.
**
** For more information on the SQL language see the SQL Reference.
**
*******************************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlda.h>
#include "utilemb.h"
EXEC SQL INCLUDE SQLCA;
int output_statement (char [1000]);
int main(int argc, char *argv[]) {
char statement[1000];
EXEC SQL BEGIN DECLARE SECTION;
char userid[9];
char passwd[19];
EXEC SQL END DECLARE SECTION;
printf( "\nSample C program: TABSQL\n");
if (argc == 1) {
EXEC SQL CONNECT TO sample; /* :rk.5:erk. */
EMB_SQL_CHECK("CONNECT TO SAMPLE"); /* :rk.6:erk. */
}
else if (argc == 3) {
strcpy (userid, argv[1]);
strcpy (passwd, argv[2]);
EXEC SQL CONNECT TO sample USER :userid USING :passwd; /* :rk.5:erk. */
EMB_SQL_CHECK("CONNECT TO SAMPLE"); /* :rk.6:erk. */
}
else {
printf ("\nUSAGE: tabsql [userid passwd]\n\n");
return 1;
} /* endif */
printf ("\nCOMMON TABLE EXPRESSIONS EXAMPLE\n");
printf ("^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n");
strcpy (statement, "WITH "
"PAYLEVEL AS (SELECT EMPNO, YEAR(HIREDATE) AS HIREYEAR, EDLEVEL, "
"(SALARY+BONUS+COMM) AS TOTAL_PAY FROM EMPLOYEE "
"WHERE EDLEVEL > 16), "
"PAYBYED (EDUC_LEVEL, YEAR_OF_HIRE, AVG_TOTAL_PAY) AS "
"(SELECT EDLEVEL, HIREYEAR, AVG(TOTAL_PAY) FROM PAYLEVEL "
"GROUP BY EDLEVEL, HIREYEAR) "
"SELECT EMPNO, EDLEVEL, YEAR_OF_HIRE, TOTAL_PAY, AVG_TOTAL_PAY "
"FROM PAYLEVEL, PAYBYED "
"WHERE EDLEVEL=EDUC_LEVEL "
"AND HIREYEAR = YEAR_OF_HIRE "
"AND TOTAL_PAY < AVG_TOTAL_PAY");
output_statement (statement);
printf ("\nNESTED TABLE EXPRESSIONS EXAMPLE\n");
printf ("^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^\n");
printf ("QUESTION: What is the average total pay, by education level \n");
printf (" and year of hire, for those with an education \n");
printf (" level greater than 16?\nANSWER:\n");
strcpy (statement, "SELECT EDLEVEL, HIREYEAR, AVG(TOTAL_PAY) "
"AS AVG_TOTAL_PAY "
"FROM ( "
"SELECT EMPNO, YEAR(HIREDATE) AS HIREYEAR, EDLEVEL, "
"SALARY+BONUS+COMM AS TOTAL_PAY FROM EMPLOYEE "
"WHERE EDLEVEL > 16 "
") AS PAY_LEVEL "
"GROUP BY EDLEVEL, HIREYEAR");
output_statement (statement);
EXEC SQL CONNECT RESET;
EMB_SQL_CHECK("CONNECT RESET");
return 0;
}
int output_statement (char sqlInput[1000]) {
int counter = 0;
struct sqlda *sqldaPointer;
EXEC SQL BEGIN DECLARE SECTION;
char sqlStatement[1000];
EXEC SQL END DECLARE SECTION;
strcpy (sqlStatement, sqlInput);
init_da (&sqldaPointer, 1);
EXEC SQL PREPARE statement1 from :sqlStatement;
EMB_SQL_CHECK("PREPARE");
/* obtain the initial SQLDA, to get the number of columns */
EXEC SQL DESCRIBE statement1 INTO :*sqldaPointer;
/* need to reassign the SQLDA with the correct number of columns to
the SQL statement */
init_da (&sqldaPointer, sqldaPointer->sqld);
EXEC SQL DESCRIBE statement1 INTO :*sqldaPointer;
EMB_SQL_CHECK("DESCRIBE");
/* allocating the proper amount of memory space needed for the
variables */
alloc_host_vars (sqldaPointer);
EXEC SQL DECLARE pcurs CURSOR FOR statement1;
EXEC SQL OPEN pcurs;
EMB_SQL_CHECK("OPEN");
EXEC SQL FETCH pcurs USING DESCRIPTOR :*sqldaPointer;
EMB_SQL_CHECK("FETCH");
/* if the FETCH is successful, obtain data from SQLDA */
/* display the column titles */
display_col_titles (sqldaPointer);
/* display the rows that are fetched */
while (SQLCODE == 0) {
counter++;
display_da (sqldaPointer);
EXEC SQL FETCH pcurs USING DESCRIPTOR :*sqldaPointer;
} /* endwhile */
EXEC SQL CLOSE pcurs;
EMB_SQL_CHECK("CLOSE CURSOR");
printf ("\n %d record(s) selected\n\n", counter);
free_da(sqldaPointer);
return 0;
}
/* end of program : tabsql.sqc */