/******************************************************************************
**
** Source File Name = recursql.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
** "Recursive Queries" of "Using SQL - Advanced"
** WITH
** REACH (SOURCE, DESTINATION, COST, STOPS) AS
** ( SELECT SOURCE, DESTINATION, COST, CAST(0 AS SMALLINT)
** FROM FLIGHTS
** WHERE SOURCE = 'Paris'
** UNION ALL
** SELECT R.SOURCE, F.DESTINATION, CAST(R.COST+F.COST AS SMALLINT),
** CAST(R.STOPS+1 AS SMALLINT)
** FROM REACH R, FLIGHTS F
** WHERE R.DESTINATION=F.SOURCE
** AND R.STOPS < 5)
** SELECT DESTINATION, COST, STOPS FROM REACH");
**
** 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[]) {
EXEC SQL BEGIN DECLARE SECTION;
char userid[9];
char passwd[19];
char statement[1000];
EXEC SQL END DECLARE SECTION;
printf( "\nSample C program: RECURSQL\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: recursql [userid passwd]\n\n");
return 1;
} /* endif */
EXEC SQL CREATE TABLE flights (source VARCHAR(8), destination VARCHAR(8),
d_time INTEGER, a_time INTEGER, cost SMALLINT, airline VARCHAR(8));
EMB_SQL_CHECK("CREATE TABLE flights");
strcpy (statement, "INSERT INTO flights VALUES "
"('Paris', 'Detroit', null,null,700,'KLM'), "
"('Paris', 'New York', null,null,600,'KLM'), "
"('Paris', 'Toronto', null,null,750,'AC'), "
"('Detroit', 'San Jose', null,null,400,'AA'), "
"('New York','Chicago', null,null,200,'AA'), "
"('Toronto', 'Chicago', null,null,275,'AC'), "
"('Chicago', 'San Jose', null,null,300,'AA')");
EXEC SQL EXECUTE IMMEDIATE :statement;
EMB_SQL_CHECK("INSERT");
printf ("\nRECURSIVE QUERY EXAMPLE\n");
printf ("^^^^^^^^^^^^^^^^^^^^^^^\n");
strcpy (statement, "WITH "
"REACH (SOURCE, DESTINATION, COST, STOPS) AS "
"( SELECT SOURCE, DESTINATION, COST, CAST(0 AS SMALLINT) "
"FROM FLIGHTS "
"WHERE SOURCE = 'Paris' "
"UNION ALL "
"SELECT R.SOURCE, F.DESTINATION, CAST(R.COST+F.COST AS SMALLINT), "
"CAST(R.STOPS+1 AS SMALLINT) "
"FROM REACH R, FLIGHTS F "
"WHERE R.DESTINATION=F.SOURCE "
"AND R.STOPS < 5) "
"SELECT DESTINATION, COST, STOPS FROM REACH");
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;
if (SQLCODE != 347) 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);
/* Drop the table when we are done. */
EXEC SQL DROP TABLE flights;
EMB_SQL_CHECK("DROP TABLE");
free_da(sqldaPointer);
return 0;
}
/* end of program : recursql.sqc */