/****************************************************************************
** (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: tbread.sqC
**
** SAMPLE: How to read tables
**
** SQL STATEMENTS USED:
** DECLARE CURSOR
** OPEN
** FETCH
** CLOSE
** PREPARE
** DESCRIBE
** SELECT
**
**
*****************************************************************************
**
** For more information on the sample programs, see the README file.
**
** For information on developing embedded SQL applications see the Developing Embedded SQL Applications book.
**
** For information on using SQL statements, see the SQL Reference.
**
** For the latest information on programming, compiling, and running DB2
** applications, visit the DB2 Information Center at
** http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp
****************************************************************************/
#include <string.h>
#include <sqlenv.h>
#include <sqlcodes.h>
#include <sqlutil.h>
#include "utilemb.h"
#if ((__cplusplus >= 199711L) && !defined DB2HP && !defined DB2AIX) || \
(DB2LINUX && (__LP64__ || (__GNUC__ >= 3)) )
#include <iomanip>
#include <iostream>
using namespace std;
#else
#include <iomanip.h>
#include <iostream.h>
#endif
EXEC SQL BEGIN DECLARE SECTION;
short deptnumb;
short maxDeptnumb;
char deptname[15];
char orgDeptname[15];
short manager;
char division[11];
char location[14];
char deptno[4];
char departmentDeptname[30];
char mgrno[7];
short mgrnoInd;
char admrdept[4];
char departmentLocation[17];
short departmentLocationInd;
char job[9];
short jobInd;
short edlevel;
short edlevelInd;
double comm;
double commSum;
short commSumInd;
char rcteDeptno[4];
char rcteDeptname[30];
char rcteAdmrdept[4];
char strStmt[100];
double avgSalary;
EXEC SQL END DECLARE SECTION;
class TbRead
{
public:
// methods to perform SELECT
int TbSelectUsingFetchIntoHostVariables();
int TbSelectUsingFetchIntoSQLDA();
int TbSelectUsingDeclareCursor();
int TbSelectUsingPrepareDeclareCursor();
int TbSelectUsingPrepareDescribeDeclareCursor();
private:
// the support functions for TbSelectUsingPrepareDescribeDeclareCursor
int SqldaInit(struct sqlda **, int);
int ColTitlesDisplay(struct sqlda *);
short DisplayDataLenGet(short, short);
int RowDataMemoryAlloc(struct sqlda *);
int RowDataDisplay(struct sqlda *);
int CellDataDisplay(char *, short, short *, short, short);
int RowDataMemoryFree(struct sqlda *);
public:
// SELECT types
int TbMostSimpleSubselect();
int TbBasicSubselect();
int TbGroupBySubselect();
int TbSubselect();
int TbRowSubselect();
int TbFullselect();
int TbSelectStatement();
int TbBasicSubselectFromMultipleTables();
int TbBasicSubselectFromJoinedTable();
int TbBasicSubselectUsingSubquery();
int TbBasicSubselectUsingCorrelatedSubquery();
int TbSubselectUsingGroupingSets();
int TbSubselectUsingRollup();
int TbSubselectUsingCube();
int TbSelectStatementUsingRecursiveCommonTableExpression();
int TbSelectUsingQuerySampling();
private:
// support functions for SELECT types
int OrgTbContentDisplay();
int DepartmentTbContentDisplay();
int EmployeeTbPartialContentDisplay();
};
int TbRead::TbSelectUsingFetchIntoHostVariables()
{
int rc = 0;
struct sqlca sqlca;
cout << "\n-----------------------------------------------------------";
cout << "\nUSE THE SQL STATEMENTS:" << endl;
cout << " DECLARE CURSOR" << endl;
cout << " OPEN" << endl;
cout << " FETCH" << endl;
cout << " CLOSE" << endl;
cout << "TO PERFORM SELECT USING FETCH INTO HOST VARIABLES." << endl;
cout << "\n Perform:" << endl;
cout << " SELECT deptnumb, deptname FROM org" << endl;
cout << "\n Results:" << endl;
cout << " DEPTNUMB DEPTNAME" << endl;
cout << " -------- --------------" << endl;
// declare cursor
EXEC SQL DECLARE c0 CURSOR FOR SELECT deptnumb, deptname FROM org;
// open cursor
EXEC SQL OPEN c0;
EMB_SQL_CHECK("cursor -- open");
// fetch cursor
EXEC SQL FETCH c0 INTO :deptnumb, :deptname;
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
cout.setf(ios::right, ios::adjustfield);
cout << " " << setw(8) << deptnumb;
cout.setf(ios::left, ios::adjustfield);
cout << " " << setw(14) << deptname << endl;
EXEC SQL FETCH c0 INTO :deptnumb, :deptname;
EMB_SQL_CHECK("cursor -- fetch");
}
// close cursor
EXEC SQL CLOSE c0;
EMB_SQL_CHECK("cursor -- close");
return 0;
} //TbRead::TbSelectUsingFetchIntoHostVariables
int TbRead::TbSelectUsingFetchIntoSQLDA()
{
int rc = 0;
struct sqlca sqlca;
struct sqlda *pSqlda = NULL;
short deptnumbInd;
char deptnameInd;
cout << "\n-----------------------------------------------------------";
cout << "\nUSE THE SQL STATEMENTS:" << endl;
cout << " DECLARE CURSOR" << endl;
cout << " OPEN" << endl;
cout << " FETCH" << endl;
cout << " CLOSE" << endl;
cout << "TO PERFORM SELECT USING FETCH INTO SQLDA." << endl;
cout << "\n Perform:" << endl;
cout << " SELECT deptnumb, deptname FROM org" << endl;
cout << "\n Results:" << endl;
cout << " DEPTNUMB DEPTNAME" << endl;
cout << " -------- --------------" << endl;
// declare cursor
EXEC SQL DECLARE c1 CURSOR FOR SELECT deptnumb, deptname FROM org;
// open cursor
EXEC SQL OPEN c1;
EMB_SQL_CHECK("cursor -- open");
// initialize SQLDA structure
pSqlda = new sqlda[2]; // two variables
strncpy(pSqlda->sqldaid, "SQLDA ", sizeof(pSqlda->sqldaid));
pSqlda->sqldabc = (sqlint32) SQLDASIZE(2);
pSqlda->sqln = 2; // two variables
pSqlda->sqld = 2;
pSqlda->sqlvar[0].sqltype = SQL_TYP_NSMALL;
pSqlda->sqlvar[0].sqllen = sizeof(short);
pSqlda->sqlvar[0].sqldata = (char *)&deptnumb;
pSqlda->sqlvar[0].sqlind = (short *)&deptnumbInd;
pSqlda->sqlvar[1].sqltype = SQL_TYP_NCSTR;
pSqlda->sqlvar[1].sqllen = 15;
pSqlda->sqlvar[1].sqldata = (char *)&deptname;
pSqlda->sqlvar[1].sqlind = (short *)&deptnameInd;
// fetch cursor
EXEC SQL FETCH c1 USING DESCRIPTOR :*pSqlda;
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
cout.setf(ios::right, ios::adjustfield);
cout << " " << setw(8) << deptnumb;
cout.setf(ios::left, ios::adjustfield);
cout << " " << setw(14) << deptname << endl;
EXEC SQL FETCH c1 USING DESCRIPTOR :*pSqlda;
EMB_SQL_CHECK("cursor -- fetch");
}
// close cursor
EXEC SQL CLOSE c1;
EMB_SQL_CHECK("cursor -- close");
// release memory allocated
delete [] pSqlda;
return 0;
} //TbRead::TbSelectUsingFetchIntoSQLDA
int TbRead::TbSelectUsingDeclareCursor()
{
int rc = 0;
struct sqlca sqlca;
cout << "\n-----------------------------------------------------------";
cout << "\nUSE THE SQL STATEMENTS:" << endl;
cout << " DECLARE CURSOR" << endl;
cout << " OPEN" << endl;
cout << " FETCH" << endl;
cout << " CLOSE" << endl;
cout << "TO PERFORM SELECT USING DECLARE CURSOR." << endl;
cout << "\n Perform:" << endl;
cout << " SELECT deptnumb, deptname FROM org" << endl;
cout << "\n Results:" << endl;
cout << " DEPTNUMB DEPTNAME" << endl;
cout << " -------- --------------" << endl;
// declare cursor
EXEC SQL DECLARE c2 CURSOR FOR SELECT deptnumb, deptname FROM org;
// open cursor
EXEC SQL OPEN c2;
EMB_SQL_CHECK("cursor -- open");
// fetch cursor
EXEC SQL FETCH c2 INTO :deptnumb, :deptname;
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
cout.setf(ios::right, ios::adjustfield);
cout << " " << setw(8) << deptnumb;
cout.setf(ios::left, ios::adjustfield);
cout << " " << setw(14) << deptname << endl;
EXEC SQL FETCH c2 INTO :deptnumb, :deptname;
EMB_SQL_CHECK("cursor -- fetch");
}
// close cursor
EXEC SQL CLOSE c2;
EMB_SQL_CHECK("cursor -- close");
return 0;
} //TbRead::TbSelectUsingDeclareCursor
int TbRead::TbSelectUsingPrepareDeclareCursor()
{
int rc = 0;
struct sqlca sqlca;
cout << "\n-----------------------------------------------------------";
cout << "\nUSE THE SQL STATEMENTS:" << endl;
cout << " PREPARE" << endl;
cout << " DECLARE CURSOR" << endl;
cout << " OPEN" << endl;
cout << " FETCH" << endl;
cout << " CLOSE" << endl;
cout << "TO PERFORM SELECT USING PREPARE-DECLARE CURSOR." << endl;
cout << "\n Perform:" << endl;
cout << " SELECT deptnumb, deptname FROM org" << endl;
cout << "\n Results:" << endl;
cout << " DEPTNUMB DEPTNAME" << endl;
cout << " -------- --------------" << endl;
strcpy(strStmt, "SELECT deptnumb, deptname FROM org");
EXEC SQL PREPARE stmt1 FROM :strStmt;
EMB_SQL_CHECK("statement -- prepare");
// declare cursor
EXEC SQL DECLARE c3 CURSOR FOR stmt1;
// open cursor
EXEC SQL OPEN c3;
EMB_SQL_CHECK("cursor -- open");
// fetch cursor
EXEC SQL FETCH c3 INTO :deptnumb, :deptname;
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
cout.setf(ios::right, ios::adjustfield);
cout << " " << setw(8) << deptnumb;
cout.setf(ios::left, ios::adjustfield);
cout << " " << setw(14) << deptname << endl;
EXEC SQL FETCH c3 INTO :deptnumb, :deptname;
EMB_SQL_CHECK("cursor -- fetch");
}
// close cursor
EXEC SQL CLOSE c3;
EMB_SQL_CHECK("cursor -- close");
return 0;
} //TbRead::TbSelectUsingPrepareDeclareCursor
int TbRead::TbSelectUsingPrepareDescribeDeclareCursor()
{
int rc = 0;
struct sqlca sqlca;
struct sqlda *pSqlda = NULL;
int numCols;
cout << "\n-----------------------------------------------------------";
cout << "\nUSE THE SQL STATEMENTS:" << endl;
cout << " PREPARE" << endl;
cout << " DESCRIBE" << endl;
cout << " DECLARE CURSOR" << endl;
cout << " OPEN" << endl;
cout << " FETCH" << endl;
cout << " CLOSE" << endl;
cout << "TO PERFORM SELECT USING PREPARE-DESCRIBE-DECLARE CURSOR." << endl;
strcpy(strStmt, "SELECT * FROM staff WHERE id <= 30");
cout << "\n Perform:" << endl;
cout << " " << strStmt << endl << endl;
// -------------------------------------
// detetmine number of output columns
// -------------------------------------
EXEC SQL PREPARE stmt2 FROM :strStmt;
EMB_SQL_CHECK("statement -- prepare");
rc = SqldaInit(&pSqlda, 1);
if (rc != 0)
{
return rc;
}
EXEC SQL DESCRIBE stmt2 INTO :*pSqlda;
if (sqlca.sqlcode != 0 &&
sqlca.sqlcode != SQL_RC_W236 &&
sqlca.sqlcode != SQL_RC_W237 &&
sqlca.sqlcode != SQL_RC_W238 && sqlca.sqlcode != SQL_RC_W239)
{
EMB_SQL_CHECK("statement -- describe");
}
numCols = (int)pSqlda->sqld;
delete [] pSqlda;
// -------------------
// invoke statement
// -------------------
if (numCols > 0)
{
if (sqlca.sqlcode == 0 || sqlca.sqlcode == SQL_RC_W236)
{
rc = SqldaInit(&pSqlda, numCols);
if (rc != 0)
{
return rc;
}
}
if (sqlca.sqlcode == SQL_RC_W237 ||
sqlca.sqlcode == SQL_RC_W238 || sqlca.sqlcode == SQL_RC_W239)
{
rc = SqldaInit(&pSqlda, numCols * 2);
if (rc != 0)
{
return rc;
}
}
EXEC SQL DESCRIBE stmt2 INTO :*pSqlda;
EMB_SQL_CHECK("statement -- describe");
rc = ColTitlesDisplay(pSqlda);
EXEC SQL DECLARE c4 CURSOR FOR stmt2;
EXEC SQL OPEN c4;
EMB_SQL_CHECK("cursor -- open");
rc = RowDataMemoryAlloc(pSqlda);
if (rc != 0)
{
return rc;
}
EXEC SQL FETCH c4 USING DESCRIPTOR :*pSqlda;
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
rc = RowDataDisplay(pSqlda);
EXEC SQL FETCH c4 USING DESCRIPTOR :*pSqlda;
EMB_SQL_CHECK("cursor -- fetch");
}
EXEC SQL CLOSE c4;
EMB_SQL_CHECK("cursor -- close");
rc = RowDataMemoryFree(pSqlda);
delete [] pSqlda;
}
return 0;
} //TbRead::TbSelectUsingPrepareDescribeDeclareCursor
int TbRead::SqldaInit(struct sqlda **ppSqlda, int size)
{
int rc = 0;
*ppSqlda = new sqlda[size];
if (*ppSqlda == NULL)
{
return 1;
}
memcpy((*ppSqlda)->sqldaid, "SQLDA ", 8);
(*ppSqlda)->sqldabc = (sqlint32) SQLDASIZE(size);
(*ppSqlda)->sqln = size;
(*ppSqlda)->sqld = 0;
return 0;
} //TbRead::SqldaInit
int TbRead::ColTitlesDisplay(struct sqlda *pSqlda)
{
int rc = 0;
short colNb;
short numCols;
short colDataLen;
short colTitleLen;
short colWidth;
char colTitle[129];
numCols = pSqlda->sqld;
for (colNb = 0; colNb < numCols; colNb++)
{
colDataLen = DisplayDataLenGet(pSqlda->sqlvar[colNb].sqltype,
pSqlda->sqlvar[colNb].sqllen);
colTitleLen = pSqlda->sqlvar[colNb].sqlname.length;
colWidth = max(colDataLen, colTitleLen);
strcpy(colTitle, pSqlda->sqlvar[colNb].sqlname.data);
cout << setw(colWidth) << colTitle;
cout << " | ";
}
cout << endl;
return 0;
} //TbRead::ColTitlesDisplay
short TbRead::DisplayDataLenGet(short sqlDataType, short sqlDataSize)
{
short displayDataLen;
switch (sqlDataType)
{
case SQL_TYP_DATE:
case SQL_TYP_NDATE:
case SQL_TYP_TIME:
case SQL_TYP_NTIME:
case SQL_TYP_STAMP:
case SQL_TYP_NSTAMP:
case SQL_TYP_VARCHAR:
case SQL_TYP_NVARCHAR:
case SQL_TYP_CHAR:
case SQL_TYP_NCHAR:
case SQL_TYP_LONG:
case SQL_TYP_NLONG:
case SQL_TYP_CSTR:
case SQL_TYP_NCSTR:
case SQL_TYP_LSTR:
case SQL_TYP_NLSTR:
displayDataLen = sqlDataSize;
break;
case SQL_TYP_FLOAT:
case SQL_TYP_NFLOAT:
// 3 chars for every byte +
// 1 char for sign +
// 1 digit for decimal point
displayDataLen = (short)(3 * sqlDataSize + 1 + 1);
break;
case SQL_TYP_INTEGER:
case SQL_TYP_NINTEGER:
case SQL_TYP_SMALL:
case SQL_TYP_NSMALL:
// 3 chars for every byte +
// 1 char for sign
displayDataLen = (short)(3 * sqlDataSize + 1);
break;
case SQL_TYP_DECIMAL:
case SQL_TYP_NDECIMAL:
displayDataLen = (short)(((char *)&sqlDataSize)[0]) + // precision
(short)1 + // decimal point
(short)1; // sign
break;
case SQL_TYP_CGSTR:
case SQL_TYP_NCGSTR:
case SQL_TYP_VARGRAPH:
case SQL_TYP_NVARGRAPH:
case SQL_TYP_GRAPHIC:
case SQL_TYP_NGRAPHIC:
case SQL_TYP_LONGRAPH:
case SQL_TYP_NLONGRAPH:
displayDataLen = 30;
break;
case SQL_TYP_BLOB:
case SQL_TYP_NBLOB:
case SQL_TYP_CLOB:
case SQL_TYP_NCLOB:
case SQL_TYP_DBCLOB:
case SQL_TYP_NDBCLOB:
displayDataLen = 30;
break;
default:
displayDataLen = 0;
break;
}
return displayDataLen;
} //TbRead::DisplayDataLenGet
int TbRead::RowDataMemoryAlloc(struct sqlda *pSqlda)
{
int rc = 0;
short colNb;
short numCols;
unsigned int memSize;
short precision;
numCols = pSqlda->sqld;
for (colNb = 0; colNb < numCols; colNb++)
{
// allocate sqlind memory
if (pSqlda->sqlvar[colNb].sqltype & 1)
{
pSqlda->sqlvar[colNb].sqlind = new short;
if (pSqlda->sqlvar[colNb].sqlind == NULL)
{
return 1;
}
memset(pSqlda->sqlvar[colNb].sqlind, '\0', sizeof(short));
}
// allocate sqldata memory
switch (pSqlda->sqlvar[colNb].sqltype)
{
case SQL_TYP_DATE:
case SQL_TYP_NDATE:
case SQL_TYP_TIME:
case SQL_TYP_NTIME:
case SQL_TYP_STAMP:
case SQL_TYP_NSTAMP:
case SQL_TYP_VARCHAR:
case SQL_TYP_NVARCHAR:
case SQL_TYP_CHAR:
case SQL_TYP_NCHAR:
case SQL_TYP_LONG:
case SQL_TYP_NLONG:
case SQL_TYP_CSTR:
case SQL_TYP_NCSTR:
case SQL_TYP_LSTR:
case SQL_TYP_NLSTR:
pSqlda->sqlvar[colNb].sqltype = SQL_TYP_NCSTR;
memSize = (unsigned int)pSqlda->sqlvar[colNb].sqllen;
break;
case SQL_TYP_FLOAT:
case SQL_TYP_NFLOAT:
case SQL_TYP_INTEGER:
case SQL_TYP_NINTEGER:
case SQL_TYP_SMALL:
case SQL_TYP_NSMALL:
memSize = (unsigned int)pSqlda->sqlvar[colNb].sqllen;
break;
case SQL_TYP_DECIMAL:
case SQL_TYP_NDECIMAL:
precision = (short)(((char *)&(pSqlda->sqlvar[colNb].sqllen))[0]);
memSize = (unsigned int)(precision / 2 + 1);
break;
case SQL_TYP_CGSTR:
case SQL_TYP_NCGSTR:
case SQL_TYP_VARGRAPH:
case SQL_TYP_NVARGRAPH:
case SQL_TYP_GRAPHIC:
case SQL_TYP_NGRAPHIC:
case SQL_TYP_LONGRAPH:
case SQL_TYP_NLONGRAPH:
memSize = (unsigned int)(2 * pSqlda->sqlvar[colNb].sqllen);
break;
case SQL_TYP_BLOB:
case SQL_TYP_NBLOB:
case SQL_TYP_CLOB:
case SQL_TYP_NCLOB:
memSize = GETSQLDALONGLEN(pSqlda, colNb);
break;
case SQL_TYP_DBCLOB:
case SQL_TYP_NDBCLOB:
memSize = 2 * GETSQLDALONGLEN(pSqlda, colNb);
break;
default:
memSize = 0;
break;
}
pSqlda->sqlvar[colNb].sqldata = new char[memSize];
if (pSqlda->sqlvar[colNb].sqldata == NULL)
{
return 1;
}
memset(pSqlda->sqlvar[colNb].sqldata, '\0', memSize);
}
return 0;
} //TbRead::RowDataMemoryAlloc
int TbRead::RowDataDisplay(struct sqlda *pSqlda)
{
int rc = 0;
short colNb;
short numCols;
short colDataLen;
short colTitleLen;
short colWidth;
numCols = pSqlda->sqld;
for (colNb = 0; colNb < numCols; colNb++)
{
colDataLen = DisplayDataLenGet(pSqlda->sqlvar[colNb].sqltype,
pSqlda->sqlvar[colNb].sqllen);
colTitleLen = pSqlda->sqlvar[colNb].sqlname.length;
colWidth = max(colDataLen, colTitleLen);
rc = CellDataDisplay(pSqlda->sqlvar[colNb].sqldata,
pSqlda->sqlvar[colNb].sqltype,
pSqlda->sqlvar[colNb].sqlind,
pSqlda->sqlvar[colNb].sqllen, colWidth);
cout << " | ";
}
cout << endl;
return 0;
} //TbRead::RowDataDisplay
int TbRead::CellDataDisplay(char *sqldata,
short sqltype,
short *sqlind, short sqllen, short colWidth)
{
int rc = 0;
char buf[100];
char data[10];
struct lstr
{
char len;
char *data;
}
*pLstr;
int i;
short precision;
short scale;
short numBytes;
short top;
short bottom;
short byteNb;
short digNb;
struct lob
{
sqlint32 len;
char *data;
}
*pLob;
if (sqltype & 1 && *sqlind < 0)
{
cout << setw(colWidth) << " -";
}
else
{
switch (sqltype)
{
case SQL_TYP_DATE:
case SQL_TYP_NDATE:
case SQL_TYP_TIME:
case SQL_TYP_NTIME:
case SQL_TYP_STAMP:
case SQL_TYP_NSTAMP:
case SQL_TYP_VARCHAR:
case SQL_TYP_NVARCHAR:
case SQL_TYP_CHAR:
case SQL_TYP_NCHAR:
case SQL_TYP_LONG:
case SQL_TYP_NLONG:
case SQL_TYP_CSTR:
case SQL_TYP_NCSTR:
cout.setf(ios::left, ios::adjustfield);
memset(data, '\0', sizeof(data));
strncpy(data, sqldata, sqllen);
cout << setw(colWidth) << data;
break;
case SQL_TYP_LSTR:
case SQL_TYP_NLSTR:
pLstr = (struct lstr *)sqldata;
for (i = 0; i < (int)pLstr->len; i++)
{
cout << (pLstr->data)[i];
}
while (i < colWidth)
{
cout << " ";
i = i + 1;
}
break;
case SQL_TYP_FLOAT:
case SQL_TYP_NFLOAT:
cout << setw(colWidth) << *((double *)sqldata);
break;
case SQL_TYP_INTEGER:
case SQL_TYP_NINTEGER:
cout << setw(colWidth) << *((long *)sqldata);
break;
case SQL_TYP_SMALL:
case SQL_TYP_NSMALL:
cout.setf(ios::right, ios::adjustfield);
cout << setw(colWidth) << *((short *)sqldata);
break;
case SQL_TYP_DECIMAL:
case SQL_TYP_NDECIMAL:
precision = (short)(((char *)&sqllen)[0]);
scale = (short)(((char *)&sqllen)[1]);
// adjust precision to odd value
if ((precision % 2) == 0)
{
precision = precision + 1;
}
// calculate numBytes
numBytes = (short)(precision + 1) / 2;
// determine sign using bottom of the last byte
bottom = *(sqldata + numBytes - 1) & 0x000F;
i = 0;
if (bottom == 0x000D || bottom == 0x000B)
{
buf[i] = '-';
i++;
}
else
{
buf[i] = ' ';
i++;
}
// prepare decimal number
digNb = 0;
if (digNb == precision - scale)
{
buf[i] = '.';
i++;
}
// (top + bottom) from first (numBytes - 1) bytes ...
for (byteNb = 0; byteNb < numBytes - 1; byteNb = byteNb + 1)
{
top = *(sqldata + byteNb) & 0x00F0;
top = top >> 4;
bottom = *(sqldata + byteNb) & 0x000F;
buf[i] = top + '0';
i++;
digNb++;
if (digNb == precision - scale)
{
buf[i] = '.';
i++;
}
buf[i] = bottom + '0';
i++;
digNb++;
if (digNb == precision - scale)
{
buf[i] = '.';
i++;
}
}
// ... and top of the last byte(bottom is the sign)
top = *(sqldata + byteNb) & 0x00F0;
top = top >> 4;
buf[i] = top + '0';
i++;
digNb++;
if (digNb == precision - scale)
{
buf[i] = '.';
i++;
}
// display decimal number
buf[i] = '\0';
cout.setf(ios::left, ios::adjustfield);
cout << setw(colWidth) << buf;
break;
case SQL_TYP_CGSTR:
case SQL_TYP_NCGSTR:
case SQL_TYP_VARGRAPH:
case SQL_TYP_NVARGRAPH:
case SQL_TYP_GRAPHIC:
case SQL_TYP_NGRAPHIC:
case SQL_TYP_LONGRAPH:
case SQL_TYP_NLONGRAPH:
//cout << setw(colWidth);
cout << "Graphic data length: " << setw(5) << sqllen;
break;
case SQL_TYP_BLOB:
case SQL_TYP_NBLOB:
case SQL_TYP_CLOB:
case SQL_TYP_NCLOB:
case SQL_TYP_DBCLOB:
case SQL_TYP_NDBCLOB:
pLob = (struct lob *)sqldata;
//cout << setw(colWidth);
if (*sqlind == 0)
{
cout << "LOB length: " << setw(10) << (long)pLob->len;
}
else
{
cout << "Truncated LOB length: " << setw(10) << (long)pLob->len;
}
break;
default:
cout << setw(colWidth) << "?";
break;
}
}
return 0;
} //TbRead::CellDataDisplay
int TbRead::RowDataMemoryFree(struct sqlda *pSqlda)
{
int rc = 0;
short colNb;
for (colNb = 0; colNb < pSqlda->sqld; colNb++)
{
delete [] pSqlda->sqlvar[colNb].sqldata;
if (pSqlda->sqlvar[colNb].sqltype & 1)
{
delete pSqlda->sqlvar[colNb].sqlind;
}
}
return 0;
} //TbRead::RowDataMemoryFree
int TbRead::TbMostSimpleSubselect()
{
int rc = 0;
struct sqlca sqlca;
cout << "\n-----------------------------------------------------------";
cout << "\nUSE THE SQL STATEMENTS:" << endl;
cout << " DECLARE CURSOR" << endl;
cout << " OPEN" << endl;
cout << " FETCH" << endl;
cout << " CLOSE" << endl;
cout << "TO PERFORM A SIMPLE SUBSELECT." << endl;
// display ORG table content
rc = OrgTbContentDisplay();
cout << "\n Perform:" << endl;
cout << " SELECT deptnumb, deptname FROM org" << endl;
cout << "\n Results:" << endl;
cout << " DEPTNUMB DEPTNAME" << endl;
cout << " -------- --------------" << endl;
// declare cursor
EXEC SQL DECLARE c5 CURSOR FOR SELECT deptnumb, deptname FROM org;
// open cursor
EXEC SQL OPEN c5;
EMB_SQL_CHECK("cursor -- open");
// fetch cursor
EXEC SQL FETCH c5 INTO :deptnumb, :deptname;
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
cout.setf(ios::right, ios::adjustfield);
cout << " " << setw(8) << deptnumb;
cout.setf(ios::left, ios::adjustfield);
cout << " " << setw(14) << deptname << endl;
EXEC SQL FETCH c5 INTO :deptnumb, :deptname;
EMB_SQL_CHECK("cursor -- fetch");
}
// close cursor
EXEC SQL CLOSE c5;
EMB_SQL_CHECK("cursor -- close");
return 0;
} //TbRead::TbMostSimpleSubselect
int TbRead::TbBasicSubselect()
{
int rc = 0;
struct sqlca sqlca;
cout << "\n-----------------------------------------------------------";
cout << "\nUSE THE SQL STATEMENTS:" << endl;
cout << " DECLARE CURSOR" << endl;
cout << " OPEN" << endl;
cout << " FETCH" << endl;
cout << " CLOSE" << endl;
cout << "TO PERFORM A SUBSELECT." << endl;
// display content of ORG table content
rc = OrgTbContentDisplay();
cout << "\n Perform:" << endl;
cout << " SELECT deptnumb, deptname FROM org";
cout << " WHERE deptnumb < 30" << endl;
cout << "\n Results:" << endl;
cout << " DEPTNUMB DEPTNAME" << endl;
cout << " -------- --------------" << endl;
// declare cursor
EXEC SQL DECLARE c6 CURSOR FOR
SELECT deptnumb, deptname FROM org WHERE deptnumb < 30;
// open cursor
EXEC SQL OPEN c6;
EMB_SQL_CHECK("cursor -- open");
// fetch cursor
EXEC SQL FETCH c6 INTO :deptnumb, :deptname;
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
cout.setf(ios::right, ios::adjustfield);
cout << " " << setw(8) << deptnumb;
cout.setf(ios::left, ios::adjustfield);
cout << " " << setw(14) << deptname << endl;
EXEC SQL FETCH c6 INTO :deptnumb, :deptname;
EMB_SQL_CHECK("cursor -- fetch");
}
// close cursor
EXEC SQL CLOSE c6;
EMB_SQL_CHECK("cursor -- close");
return 0;
} //TbRead::TbBasicSubselect
int TbRead::TbGroupBySubselect()
{
int rc = 0;
struct sqlca sqlca;
cout << "\n-----------------------------------------------------------";
cout << "\nUSE THE SQL STATEMENTS:" << endl;
cout << " DECLARE CURSOR" << endl;
cout << " OPEN" << endl;
cout << " FETCH" << endl;
cout << " CLOSE" << endl;
cout << "TO PERFORM 'GROUP BY' SUBSELECT." << endl;
// display the content of ORG table
rc = OrgTbContentDisplay();
cout << "\n Perform:" << endl;
cout << " SELECT division, MAX(deptnumb) FROM org";
cout << " GROUP BY division" << endl;
cout << "\n Results:" << endl;
cout << " DIVISION MAX(DEPTNUMB)" << endl;
cout << " ---------- --------------" << endl;
// declare cursor
EXEC SQL DECLARE c7 CURSOR FOR
SELECT division, MAX(deptnumb) FROM org GROUP BY division;
// open cursor
EXEC SQL OPEN c7;
EMB_SQL_CHECK("cursor -- open");
// fetch cursor
EXEC SQL FETCH c7 INTO :division, :maxDeptnumb;
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
cout.setf(ios::left, ios::adjustfield);
cout << " " << setw(10) << division;
cout.setf(ios::right, ios::adjustfield);
cout << " " << setw(14) << maxDeptnumb << endl;
EXEC SQL FETCH c7 INTO :division, :maxDeptnumb;
EMB_SQL_CHECK("cursor -- fetch");
}
// close cursor
EXEC SQL CLOSE c7;
EMB_SQL_CHECK("cursor -- close");
return 0;
} //TbRead::TbGroupBySubselect
int TbRead::TbSubselect()
{
int rc = 0;
struct sqlca sqlca;
cout << "\n-----------------------------------------------------------";
cout << "\nUSE THE SQL STATEMENTS:" << endl;
cout << " DECLARE CURSOR" << endl;
cout << " OPEN" << endl;
cout << " FETCH" << endl;
cout << " CLOSE" << endl;
cout << "TO PERFORM SUBSELECT." << endl;
// display the content of ORG table
rc = OrgTbContentDisplay();
cout << "\n Perform:" << endl;
cout << " SELECT division, MAX(deptnumb)" << endl;
cout << " FROM org" << endl;
cout << " WHERE location NOT IN 'New York'" << endl;
cout << " GROUP BY division";
cout << " HAVING division LIKE '%ern'" << endl;
cout << "\n Results:" << endl;
cout << " DIVISION MAX(DEPTNUMB)" << endl;
cout << " ---------- --------------" << endl;
// declare cursor
EXEC SQL DECLARE c8 CURSOR FOR
SELECT division, MAX(deptnumb)
FROM org
WHERE location NOT IN 'New York'
GROUP BY division HAVING division LIKE '%ern';
// open cursor
EXEC SQL OPEN c8;
EMB_SQL_CHECK("cursor -- open");
// fetch cursor
EXEC SQL FETCH c8 INTO :division, :maxDeptnumb;
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
cout.setf(ios::left, ios::adjustfield);
cout << " " << setw(10) << division;
cout.setf(ios::right, ios::adjustfield);
cout << " " << setw(14) << maxDeptnumb << endl;
EXEC SQL FETCH c8 INTO :division, :maxDeptnumb;
EMB_SQL_CHECK("cursor -- fetch");
}
// close cursor
EXEC SQL CLOSE c8;
EMB_SQL_CHECK("cursor -- close");
return 0;
} //TbRead::TbSubselect
int TbRead::TbRowSubselect()
{
int rc = 0;
struct sqlca sqlca;
cout << "\n-----------------------------------------------------------";
cout << "\nUSE THE SQL STATEMENTS:" << endl;
cout << " DECLARE CURSOR" << endl;
cout << " OPEN" << endl;
cout << " FETCH" << endl;
cout << " CLOSE" << endl;
cout << "TO PERFORM 'ROW' SUBSELECT." << endl;
// display the content of ORG table
rc = OrgTbContentDisplay();
cout << "\n Perform:" << endl;
cout << " SELECT deptnumb, deptname INTO :deptnumb, :deptname" << endl;
cout << " FROM org" << endl;
cout << " WHERE location = 'New York'" << endl;
cout << "\n Results:" << endl;
cout << " DEPTNUMB DEPTNAME" << endl;
cout << " -------- --------------" << endl;
// declare cursor
EXEC SQL SELECT deptnumb, deptname INTO :deptnumb, :deptname
FROM org
WHERE location = 'New York';
cout.setf(ios::right, ios::adjustfield);
cout << " " << setw(8) << deptnumb;
cout.setf(ios::left, ios::adjustfield);
cout << " " << setw(14) << deptname << endl;
return 0;
} //TbRead::TbRowSubselect
int TbRead::TbFullselect()
{
int rc = 0;
struct sqlca sqlca;
cout << "\n-----------------------------------------------------------";
cout << "\nUSE THE SQL STATEMENTS:" << endl;
cout << " DECLARE CURSOR" << endl;
cout << " OPEN" << endl;
cout << " FETCH" << endl;
cout << " CLOSE" << endl;
cout << "TO PERFORM FULLSELECT." << endl;
// display ORG table content
rc = OrgTbContentDisplay();
cout << "\n Perform:" << endl;
cout << " SELECT deptnumb, deptname" << endl;
cout << " FROM org" << endl;
cout << " WHERE deptnumb < 20 UNION VALUES(7, 'New Deptname')"
<< endl;
cout << "\n Results:" << endl;
cout << " DEPTNUMB DEPTNAME" << endl;
cout << " -------- --------------" << endl;
// declare cursor
EXEC SQL DECLARE c9 CURSOR FOR
SELECT deptnumb, deptname
FROM org
WHERE deptnumb < 20 UNION VALUES(7, 'New Deptname');
// open cursor
EXEC SQL OPEN c9;
EMB_SQL_CHECK("cursor -- open");
// fetch cursor
EXEC SQL FETCH c9 INTO :deptnumb, :deptname;
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
cout.setf(ios::right, ios::adjustfield);
cout << " " << setw(8) << deptnumb;
cout.setf(ios::left, ios::adjustfield);
cout << " " << setw(14) << deptname << endl;
EXEC SQL FETCH c9 INTO :deptnumb, :deptname;
EMB_SQL_CHECK("cursor -- fetch");
}
// close cursor
EXEC SQL CLOSE c9;
EMB_SQL_CHECK("cursor -- close");
return 0;
} //TbRead::TbFullselect
int TbRead::TbSelectStatement()
{
int rc = 0;
struct sqlca sqlca;
cout << "\n-----------------------------------------------------------";
cout << "\nUSE THE SQL STATEMENTS:" << endl;
cout << " DECLARE CURSOR" << endl;
cout << " OPEN" << endl;
cout << " FETCH" << endl;
cout << " CLOSE" << endl;
cout << "TO PERFORM SELECT STATEMENT." << endl;
// display ORG table content
rc = OrgTbContentDisplay();
cout << "\n Perform:" << endl;
cout << " WITH new_org(new_deptnumb, new_deptname)" << endl;
cout << " AS(SELECT deptnumb, deptname" << endl;
cout << " FROM org" << endl;
cout << " UNION VALUES(7, 'New Dept 1')," << endl;
cout << " (77, 'New Dept 2')," << endl;
cout << " (777, 'New Dept 3'))" << endl;
cout << " SELECT new_deptnumb, new_deptname" << endl;
cout << " FROM new_org" << endl;
cout << " WHERE new_deptnumb > 70" << endl;
cout << " ORDER BY new_deptname" << endl;
cout << "\n Results:" << endl;
cout << " NEW_DEPTNUMB NEW_DEPTNAME" << endl;
cout << " ------------ ------------" << endl;
// declare cursor
EXEC SQL DECLARE c10 CURSOR FOR
WITH new_org(new_deptnumb, new_deptname)
AS(SELECT deptnumb, deptname
FROM org
UNION VALUES(7, 'New Dept 1'),
(77, 'New Dept 2'),
(777, 'New Dept 3'))
SELECT new_deptnumb, new_deptname
FROM new_org
WHERE new_deptnumb > 70
ORDER BY new_deptname;
// open cursor
EXEC SQL OPEN c10;
EMB_SQL_CHECK("cursor -- open");
// fetch cursor
EXEC SQL FETCH c10 INTO :deptnumb, :deptname;
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
cout.setf(ios::right, ios::adjustfield);
cout << " " << setw(12) << deptnumb;
cout.setf(ios::left, ios::adjustfield);
cout << " " << setw(18) << deptname << endl;
EXEC SQL FETCH c10 INTO :deptnumb, :deptname;
EMB_SQL_CHECK("cursor -- fetch");
}
// close cursor
EXEC SQL CLOSE c10;
EMB_SQL_CHECK("cursor -- close");
return 0;
} //TbRead::TbSelectStatement
int TbRead::TbBasicSubselectFromMultipleTables()
{
int rc = 0;
struct sqlca sqlca;
cout << "\n-----------------------------------------------------------";
cout << "\nUSE THE SQL STATEMENTS:" << endl;
cout << " DECLARE CURSOR" << endl;
cout << " OPEN" << endl;
cout << " FETCH" << endl;
cout << " CLOSE" << endl;
cout << "TO PERFORM A SUBSELECT FROM MULTIPLE TABLES." << endl;
// display ORG table content
rc = OrgTbContentDisplay();
// display DEPARTMENT table content
rc = DepartmentTbContentDisplay();
cout << "\n Perform:" << endl;
cout << " SELECT deptnumb, o.deptname, deptno, d.deptname" << endl;
cout << " FROM org o, department d" << endl;
cout << " WHERE deptnumb <= 15 AND deptno LIKE '%11'" << endl;
cout << "\n Results:" << endl;
cout << " DEPTNUMB ORG.DEPTNAME DEPTNO DEPARTMENT.DEPTNAME" << endl;
cout << " -------- -------------- ------ ---------------------" << endl;
// declare cursor
EXEC SQL DECLARE c11 CURSOR FOR
SELECT deptnumb, o.deptname, deptno, d.deptname
FROM org o, department d
WHERE deptnumb <= 15 AND deptno LIKE '%11';
// open cursor
EXEC SQL OPEN c11;
EMB_SQL_CHECK("cursor -- open");
// fetch cursor
EXEC SQL FETCH c11 INTO :deptnumb, :orgDeptname, :deptno,
:departmentDeptname;
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
cout.setf(ios::right, ios::adjustfield);
cout << " " << setw(8) << deptnumb;
cout.setf(ios::left, ios::adjustfield);
cout << " " << setw(14) << orgDeptname
<< " " << setw(6) << deptno
<< " " << setw(14) << departmentDeptname << endl;
EXEC SQL FETCH c11 INTO :deptnumb, :orgDeptname, :deptno,
:departmentDeptname;
EMB_SQL_CHECK("cursor -- fetch");
}
// close cursor
EXEC SQL CLOSE c11;
EMB_SQL_CHECK("cursor -- close");
return 0;
} //TbRead::TbBasicSelectFromMultipleTables
int TbRead::TbBasicSubselectFromJoinedTable()
{
int rc = 0;
struct sqlca sqlca;
cout << "\n-----------------------------------------------------------";
cout << "\nUSE THE SQL STATEMENTS:" << endl;
cout << " DECLARE CURSOR" << endl;
cout << " OPEN" << endl;
cout << " FETCH" << endl;
cout << " CLOSE" << endl;
cout << "TO PERFORM A SUBSELECT FROM A JOINED TABLE." << endl;
// display ORG table content
rc = OrgTbContentDisplay();
// display DEPARTMENT table content
rc = DepartmentTbContentDisplay();
cout << "\n Perform:" << endl;
cout << " SELECT deptnumb, manager, deptno, mgrno" << endl;
cout << " FROM org INNER JOIN department" << endl;
cout << " ON manager = INTEGER(mgrno)" << endl;
cout << " WHERE deptnumb BETWEEN 20 AND 100" << endl;
cout << "\n Results:" << endl;
cout << " DEPTNUMB MANAGER DEPTNO MGRNO" << endl;
cout << " -------- ------- ------ ------" << endl;
// declare cursor
EXEC SQL DECLARE c12 CURSOR FOR
SELECT deptnumb, manager, deptno, mgrno
FROM org INNER JOIN department
ON manager = INTEGER(mgrno)
WHERE deptnumb BETWEEN 20 AND 100;
// open cursor
EXEC SQL OPEN c12;
EMB_SQL_CHECK("cursor -- open");
// fetch cursor
EXEC SQL FETCH c12 INTO :deptnumb, :manager, :deptno, :mgrno;
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
cout.setf(ios::right, ios::adjustfield);
cout << " " << setw(8) << deptnumb << " " << setw(7) << manager;
cout.setf(ios::left, ios::adjustfield);
cout << " " << setw(6) << deptno << " " << setw(6) << mgrno << endl;
EXEC SQL FETCH c12 INTO :deptnumb, :manager, :deptno, :mgrno;
EMB_SQL_CHECK("cursor -- fetch");
}
// close cursor
EXEC SQL CLOSE c12;
EMB_SQL_CHECK("cursor -- close");
return 0;
} //TbRead::TbBasicSubselectFromJoinedTable
int TbRead::TbBasicSubselectUsingSubquery()
{
int rc = 0;
struct sqlca sqlca;
cout << "\n-----------------------------------------------------------";
cout << "\nUSE THE SQL STATEMENTS:" << endl;
cout << " DECLARE CURSOR" << endl;
cout << " OPEN" << endl;
cout << " FETCH" << endl;
cout << " CLOSE" << endl;
cout << "TO PERFORM A SUBSELECT USING SUBQUERY." << endl;
// display ORG table content
rc = OrgTbContentDisplay();
cout << "\n Perform:" << endl;
cout << " SELECT deptnumb, deptname" << endl;
cout << " FROM org" << endl;
cout << " WHERE deptnumb < (SELECT AVG(deptnumb) FROM org)" << endl;
cout << "\n Results:" << endl;
cout << " DEPTNUMB DEPTNAME" << endl;
cout << " -------- --------------" << endl;
// declare cursor
EXEC SQL DECLARE c13 CURSOR FOR
SELECT deptnumb, deptname
FROM org
WHERE deptnumb < (SELECT AVG(deptnumb) FROM org);
// open cursor
EXEC SQL OPEN c13;
EMB_SQL_CHECK("cursor -- open");
// fetch cursor
EXEC SQL FETCH c13 INTO :deptnumb, :deptname;
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
cout.setf(ios::right, ios::adjustfield);
cout << " " << setw(8) << deptnumb;
cout.setf(ios::left, ios::adjustfield);
cout << " " << setw(14) << deptname << endl;
EXEC SQL FETCH c13 INTO :deptnumb, :deptname;
EMB_SQL_CHECK("cursor -- fetch");
}
// close cursor
EXEC SQL CLOSE c13;
EMB_SQL_CHECK("cursor -- close");
return 0;
} //TbRead::TbBasicSubselectUsingSubquery
int TbRead::TbBasicSubselectUsingCorrelatedSubquery()
{
int rc = 0;
struct sqlca sqlca;
cout << "\n-----------------------------------------------------------";
cout << "\nUSE THE SQL STATEMENTS:" << endl;
cout << " DECLARE CURSOR" << endl;
cout << " OPEN" << endl;
cout << " FETCH" << endl;
cout << " CLOSE" << endl;
cout << "TO PERFORM A SUBSELECT USING CORRELATED SUBQUERY." << endl;
// display ORG table content
rc = OrgTbContentDisplay();
cout << "\n Perform:" << endl;
cout << " SELECT deptnumb, deptname" << endl;
cout << " FROM org o1" << endl;
cout << " WHERE deptnumb > (SELECT AVG(deptnumb)" << endl;
cout << " FROM org o2" << endl;
cout << " WHERE o2.division = o1.division)"
<< endl;
cout << "\n Results:" << endl;
cout << " DEPTNUMB DEPTNAME" << endl;
cout << " -------- --------------" << endl;
// declare cursor
EXEC SQL DECLARE c14 CURSOR FOR
SELECT deptnumb, deptname
FROM org o1
WHERE deptnumb > (SELECT AVG(deptnumb)
FROM org o2
WHERE o2.division = o1.division);
// open cursor
EXEC SQL OPEN c14;
EMB_SQL_CHECK("cursor -- open");
// fetch cursor
EXEC SQL FETCH c14 INTO :deptnumb, :deptname;
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
cout.setf(ios::right, ios::adjustfield);
cout << " " << setw(8) << deptnumb;
cout.setf(ios::left, ios::adjustfield);
cout << " " << setw(14) << deptname << endl;
EXEC SQL FETCH c14 INTO :deptnumb, :deptname;
EMB_SQL_CHECK("cursor -- fetch");
}
// close cursor
EXEC SQL CLOSE c14;
EMB_SQL_CHECK("cursor -- close");
return 0;
} //TbRead::TbBasicSubselectUsingCorrelatedSubquery
int TbRead::TbSubselectUsingGroupingSets()
{
int rc = 0;
struct sqlca sqlca;
cout << "\n-----------------------------------------------------------";
cout << "\nUSE THE SQL STATEMENTS:" << endl;
cout << " DECLARE CURSOR" << endl;
cout << " OPEN" << endl;
cout << " FETCH" << endl;
cout << " CLOSE" << endl;
cout << "TO PERFORM SUBSELECT USING GROUPING SETS." << endl;
rc = EmployeeTbPartialContentDisplay();
cout << "\n Perform:" << endl;
cout << " SELECT job, edlevel, SUM(comm)" << endl;
cout << " FROM employee" << endl;
cout << " WHERE job IN('DESIGNER', 'FIELDREP')" << endl;
cout << " GROUP BY GROUPING SETS((job, edlevel), (job))" << endl;
cout << "\n Results:" << endl;
cout << " JOB EDLEVEL SUM(COMM)" << endl;
cout << " -------- ------- ----------" << endl;
// declare cursor
EXEC SQL DECLARE c15 CURSOR FOR
SELECT job, edlevel, SUM(comm)
FROM employee
WHERE job IN('DESIGNER', 'FIELDREP')
GROUP BY GROUPING SETS((job, edlevel), (job));
// open cursor
EXEC SQL OPEN c15;
EMB_SQL_CHECK("cursor -- open");
// fetch cursor
EXEC SQL FETCH c15 INTO :job:jobInd, :edlevel:edlevelInd,
:commSum:commSumInd;
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
if (jobInd >= 0)
{
cout.setf(ios::left, ios::adjustfield);
cout << " " << setw(8) << job;
}
else
{
cout << " - ";
}
if (edlevelInd >= 0)
{
cout.setf(ios::right, ios::adjustfield);
cout << " " << setw(7) << edlevel;
}
else
{
cout << " -";
}
if (commSumInd >= 0)
{
cout.setf(ios::right, ios::adjustfield);
cout << " " << setw(10) << commSum;
}
else
{
cout << " - ";
}
cout << endl;
EXEC SQL FETCH c15 INTO :job:jobInd, :edlevel:edlevelInd,
:commSum:commSumInd;
EMB_SQL_CHECK("cursor -- fetch");
}
// close cursor
EXEC SQL CLOSE c15;
EMB_SQL_CHECK("cursor -- close");
return 0;
} //TbRead::TbSubselectUsingGroupingSets
int TbRead::TbSubselectUsingRollup()
{
int rc = 0;
struct sqlca sqlca;
cout << "\n-----------------------------------------------------------";
cout << "\nUSE THE SQL STATEMENTS:" << endl;
cout << " DECLARE CURSOR" << endl;
cout << " OPEN" << endl;
cout << " FETCH" << endl;
cout << " CLOSE" << endl;
cout << "TO PERFORM SUBSELECT USING ROLLUP." << endl;
rc = EmployeeTbPartialContentDisplay();
cout << "\n Perform:" << endl;
cout << " SELECT job, edlevel, SUM(comm)" << endl;
cout << " FROM employee" << endl;
cout << " WHERE job IN('DESIGNER', 'FIELDREP')" << endl;
cout << " GROUP BY ROLLUP(job, edlevel)" << endl;
cout << "\n Results:" << endl;
cout << " JOB EDLEVEL SUM(COMM)" << endl;
cout << " -------- ------- ----------" << endl;
// declare cursor
EXEC SQL DECLARE c16 CURSOR FOR
SELECT job, edlevel, SUM(comm)
FROM employee
WHERE job IN('DESIGNER', 'FIELDREP')
GROUP BY ROLLUP(job, edlevel);
// open cursor
EXEC SQL OPEN c16;
EMB_SQL_CHECK("cursor -- open");
// fetch cursor
EXEC SQL FETCH c16 INTO :job:jobInd, :edlevel:edlevelInd,
:commSum:commSumInd;
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
if (jobInd >= 0)
{
cout.setf(ios::left, ios::adjustfield);
cout << " " << setw(8) << job;
}
else
{
cout << " - ";
}
if (edlevelInd >= 0)
{
cout.setf(ios::right, ios::adjustfield);
cout << " " << setw(7) << edlevel;
}
else
{
cout << " -";
}
if (commSumInd >= 0)
{
cout.setf(ios::right, ios::adjustfield);
cout << " " << setw(10) << commSum;
}
else
{
cout << " - ";
}
cout << endl;
EXEC SQL FETCH c16 INTO :job:jobInd, :edlevel:edlevelInd,
:commSum:commSumInd;
EMB_SQL_CHECK("cursor -- fetch");
}
// close cursor
EXEC SQL CLOSE c16;
EMB_SQL_CHECK("cursor -- close");
return 0;
} //TbRead::TbSubselectUsingRollup
int TbRead::TbSubselectUsingCube()
{
int rc = 0;
struct sqlca sqlca;
cout << "\n-----------------------------------------------------------";
cout << "\nUSE THE SQL STATEMENTS:" << endl;
cout << " DECLARE CURSOR" << endl;
cout << " OPEN" << endl;
cout << " FETCH" << endl;
cout << " CLOSE" << endl;
cout << "TO PERFORM SUBSELECT USING CUBE." << endl;
rc = EmployeeTbPartialContentDisplay();
cout << "\n Perform:" << endl;
cout << " SELECT job, edlevel, SUM(comm)" << endl;
cout << " FROM employee" << endl;
cout << " WHERE job IN('DESIGNER', 'FIELDREP')" << endl;
cout << " GROUP BY CUBE(job, edlevel)" << endl;
cout << "\n Results:" << endl;
cout << " JOB EDLEVEL SUM(COMM)" << endl;
cout << " -------- ------- ----------" << endl;
// declare cursor
EXEC SQL DECLARE c17 CURSOR FOR
SELECT job, edlevel, SUM(comm)
FROM employee
WHERE job IN('DESIGNER', 'FIELDREP')
GROUP BY CUBE(job, edlevel);
// open cursor
EXEC SQL OPEN c17;
EMB_SQL_CHECK("cursor -- open");
// fetch cursor
EXEC SQL FETCH c17 INTO :job:jobInd, :edlevel:edlevelInd,
:commSum:commSumInd;
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
if (jobInd >= 0)
{
cout.setf(ios::left, ios::adjustfield);
cout << " " << setw(8) << job;
}
else
{
cout << " - ";
}
if (edlevelInd >= 0)
{
cout.setf(ios::right, ios::adjustfield);
cout << " " << setw(7) << edlevel;
}
else
{
cout << " -";
}
if (commSumInd >= 0)
{
cout.setf(ios::right, ios::adjustfield);
cout << " " << setw(10) << commSum;
}
else
{
cout << " - ";
}
cout << endl;
EXEC SQL FETCH c17 INTO :job:jobInd, :edlevel:edlevelInd,
:commSum:commSumInd;
EMB_SQL_CHECK("cursor -- fetch");
}
// close cursor
EXEC SQL CLOSE c17;
EMB_SQL_CHECK("cursor -- close");
return 0;
} //TbRead::TbSubselectUsingCube
int TbRead::TbSelectStatementUsingRecursiveCommonTableExpression()
{
int rc = 0;
struct sqlca sqlca;
cout << "\n-----------------------------------------------------------";
cout << "\nUSE THE SQL STATEMENTS:" << endl;
cout << " DECLARE CURSOR" << endl;
cout << " OPEN" << endl;
cout << " FETCH" << endl;
cout << " CLOSE" << endl;
cout << "TO PERFORM SELECT STATEMENT" << endl;
cout << "USING RECURSIVE COMMON TABLE EXPRESSION." << endl;
// display DEPARTMENT table content
rc = DepartmentTbContentDisplay();
cout << "\n Perform:";
cout << "\n WITH rcte_department(deptno, deptname, admrdept)";
cout << "\n AS(SELECT root.deptno, root.deptname, root.admrdept";
cout << "\n FROM department root";
cout << "\n WHERE root.deptname = 'SUPPORT SERVICES'";
cout << "\n UNION ALL";
cout << " SELECT child.deptno, child.deptname, child.admrdept";
cout << "\n ";
cout << " FROM department child, rcte_department parent";
cout << "\n WHERE child.admrdept = parent.deptno)";
cout << "\n SELECT * FROM rcte_department" << endl;
cout << "\n Results:" << endl;
cout << " DEPTNO DEPTNAME ADMRDEPT" << endl;
cout << " ------ ---------------------------- --------" << endl;
// display cursor
EXEC SQL DECLARE c18 CURSOR FOR
WITH rcte_department(deptno, deptname, admrdept)
AS(SELECT root.deptno, root.deptname, root.admrdept
FROM department root
WHERE root.deptname = 'SUPPORT SERVICES'
UNION ALL SELECT child.deptno, child.deptname, child.admrdept
FROM department child, rcte_department parent
WHERE child.admrdept = parent.deptno)
SELECT * FROM rcte_department;
// open cursor
EXEC SQL OPEN c18;
EMB_SQL_CHECK("cursor -- open");
// fetch cursor
EXEC SQL FETCH c18 INTO :rcteDeptno, :rcteDeptname, :rcteAdmrdept;
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
cout << " " << setw(6) << rcteDeptno
<< " " << setw(28) << rcteDeptname
<< " " << setw(8) << rcteAdmrdept << endl;
EXEC SQL FETCH c18 INTO :rcteDeptno, :rcteDeptname, :rcteAdmrdept;
EMB_SQL_CHECK("cursor -- fetch");
}
// close cursor
EXEC SQL CLOSE c18;
EMB_SQL_CHECK("cursor -- close");
return 0;
} //TbRead::TbSelectStatementUsingRecursiveCommonTableExpression
int TbRead::TbSelectUsingQuerySampling()
{
int rc = 0;
struct sqlca sqlca;
cout << "\n-----------------------------------------------------------";
cout << "\nUSE THE SQL STATEMENTS:\n";
cout << " DECLARE CURSOR\n";
cout << " OPEN\n";
cout << " FETCH\n";
cout << " CLOSE\n";
cout << "TO PERFORM A SELECT USING QUERY SAMPLING.\n";
cout << "\nCOMPUTING AVG(SALARY) WITHOUT SAMPLING \n";
cout << "\n Perform:\n";
cout << " SELECT AVG(salary) FROM employee \n";
cout << "\n Results:\n";
cout << " AVG SALARY\n";
cout << " ----------\n";
// declare cursor
EXEC SQL DECLARE c22 CURSOR FOR SELECT AVG(salary) FROM employee;
// open cursor
EXEC SQL OPEN c22;
EMB_SQL_CHECK("cursor -- open");
// fetch cursor
EXEC SQL FETCH c22 INTO :avgSalary;
EMB_SQL_CHECK("cursor -- fetch");
if (sqlca.sqlcode != 100)
{
cout.precision(2);
cout << " " << avgSalary << endl;
}
// close cursor
EXEC SQL CLOSE c22;
EMB_SQL_CHECK("cursor -- close");
cout << "\nCOMPUTING AVG(SALARY) WITH QUERY SAMPLING";
cout << "\n - ROW LEVEL SAMPLING ";
cout << "\n - BLOCK LEVEL SAMPLING \n";
cout << "\n ROW LEVEL SAMPLING : USE THE KEYWORD 'BERNOULLI'\n";
cout << "\nFOR A SAMPLING PERCENTAGE OF P, EACH ROW OF THE TABLE IS\n";
cout << "SELECTED FOR THE INCLUSION IN THE RESULT WITH A PROBABILITY\n";
cout << "OF P/100, INDEPENDENTLY OF THE OTHER ROWS IN T\n";
cout << "\n Perform:\n";
cout << " SELECT AVG(salary) FROM employee TABLESAMPLE BERNOULLI(25)";
cout << " REPEATABLE(5)\n";
cout << "\n Results:\n";
cout << " AVG SALARY\n";
cout << " ----------\n";
// declare cursor
EXEC SQL DECLARE c23 CURSOR FOR SELECT AVG(salary) FROM employee
TABLESAMPLE BERNOULLI(25) REPEATABLE(5);
// open cursor
EXEC SQL OPEN c23;
EMB_SQL_CHECK("cursor -- open");
// fetch cursor
EXEC SQL FETCH c23 INTO :avgSalary;
EMB_SQL_CHECK("cursor -- fetch");
if (sqlca.sqlcode != 100)
{
cout.precision(2);
cout << " " << avgSalary << endl;
}
// close cursor
EXEC SQL CLOSE c23;
EMB_SQL_CHECK("cursor -- close");
cout << "\n\n BLOCK LEVEL SAMPLING : USE THE KEYWORD 'SYSTEM'\n";
cout << "\nFOR A SAMPLING PERCENTAGE OF P, EACH ROW OF THE TABLE IS\n";
cout << "SELECTED FOR INCLUSION IN THE RESULT WITH A PROBABILITY\n";
cout << "OF P/100, NOT NECESSARILY INDEPENDENTLY OF THE OTHER ROWS\n";
cout << "IN T, BASED UPON AN IMPLEMENTATION-DEPENDENT ALGORITHM\n";
cout << "\n Perform:\n";
cout << " SELECT AVG(salary) FROM employee TABLESAMPLE SYSTEM(50)";
cout << " REPEATABLE(1234)\n";
cout << "\n Results:\n";
cout << " AVG SALARY\n";
cout << " ----------\n";
// declare cursor
EXEC SQL DECLARE c24 CURSOR FOR SELECT AVG(salary)FROM employee
TABLESAMPLE SYSTEM(50) REPEATABLE(1234);
// open cursor
EXEC SQL OPEN c24;
EMB_SQL_CHECK("cursor -- open");
// fetch cursor
EXEC SQL FETCH c24 INTO :avgSalary;
EMB_SQL_CHECK("cursor -- fetch");
if (sqlca.sqlcode != 100)
{
cout.precision(2);
cout << " " << avgSalary << endl;
}
// close cursor
EXEC SQL CLOSE c24;
EMB_SQL_CHECK("cursor -- close");
cout << "\nREPEATABLE CLAUSE ENSURES THAT REPEATED EXECUTIONS OF THAT\n";
cout << "TABLE REFERENCE WILL RETURN IDENTICAL RESULTS FOR THE SAME \n";
cout << "VALUE OF THE REPEAT ARGUMENT (IN PARENTHESIS). \n";
return 0;
} // TbRead::TbSelectUsingQuerySampling
int TbRead::OrgTbContentDisplay()
{
struct sqlca sqlca;
cout << "\n SELECT * FROM org" << endl;
cout << " DEPTNUMB DEPTNAME MANAGER DIVISION LOCATION" << endl;
cout << " -------- -------------- ------- ---------- --------------"
<< endl;
EXEC SQL DECLARE c19 CURSOR FOR SELECT * FROM org;
EXEC SQL OPEN c19;
EMB_SQL_CHECK("cursor -- open");
EXEC SQL FETCH c19 INTO :deptnumb, :deptname, :manager, :division,
:location;
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
cout.setf(ios::right, ios::adjustfield);
cout << " " << setw(8) << deptnumb;
cout.setf(ios::left, ios::adjustfield);
cout << " " << setw(14) << deptname;
cout.setf(ios::right, ios::adjustfield);
cout << " " << setw(7) << manager;
cout.setf(ios::left, ios::adjustfield);
cout << " " << setw(10) << division
<< " " << setw(14) << location << endl;
EXEC SQL FETCH c19 INTO :deptnumb, :deptname, :manager, :division,
:location;
EMB_SQL_CHECK("cursor -- fetch");
}
EXEC SQL CLOSE c19;
EMB_SQL_CHECK("cursor -- close");
return 0;
} // TbRead::OrgTableContentDisplay
int TbRead::DepartmentTbContentDisplay()
{
struct sqlca sqlca;
cout << "\n SELECT * FROM department" << endl;
cout << " DEPTNO DEPTNAME MGRNO ADMRDEPT LOCATION"
<< endl;
cout << " ------ ---------------------------- ------ -------- --------"
<< endl;
EXEC SQL DECLARE c20 CURSOR FOR SELECT * FROM department;
EXEC SQL OPEN c20;
EMB_SQL_CHECK("cursor -- open");
EXEC SQL FETCH c20 INTO :deptno, :departmentDeptname,
:mgrno:mgrnoInd, :admrdept,
:departmentLocation:departmentLocationInd;
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
cout.setf(ios::left, ios::adjustfield);
cout << " " << setw(6) << deptno
<< " " << setw(28) << departmentDeptname;
if (mgrnoInd >= 0)
{
cout << " " << setw(6) << mgrno;
}
else
{
cout << " - ";
}
cout << " " << setw(8) << admrdept;
if (departmentLocationInd >= 0)
{
cout << " " << setw(16) << departmentLocation;
}
else
{
cout << " - ";
}
cout << endl;
EXEC SQL FETCH c20 INTO :deptno, :departmentDeptname,
:mgrno:mgrnoInd, :admrdept,
:departmentLocation:departmentLocationInd;
EMB_SQL_CHECK("cursor -- fetch");
}
EXEC SQL CLOSE c20;
EMB_SQL_CHECK("cursor -- close");
return 0;
} //TbRead::DepartmentTbContentDisplay
int TbRead::EmployeeTbPartialContentDisplay()
{
int rc = 0;
struct sqlca sqlca;
cout << "\n Perform:" << endl;
cout << " SELECT job, edlevel, comm" << endl;
cout << " FROM employee" << endl;
cout << " WHERE job IN('DESIGNER', 'FIELDREP')" << endl;
cout << "\n Results:" << endl;
cout << " JOB EDLEVEL COMM" << endl;
cout << " -------- ------- ----------" << endl;
// declare cursor
EXEC SQL DECLARE c21 CURSOR FOR
SELECT job, edlevel, comm
FROM employee
WHERE job IN('DESIGNER', 'FIELDREP');
// open cursor
EXEC SQL OPEN c21;
EMB_SQL_CHECK("cursor -- open");
// fetch cursor
EXEC SQL FETCH c21 INTO :job, :edlevel, :comm;
EMB_SQL_CHECK("cursor -- fetch");
while (sqlca.sqlcode != 100)
{
cout.setf(ios::left, ios::adjustfield);
cout << " " << setw(8) << job;
cout.setf(ios::right, ios::adjustfield);
cout << " " << setw(7) << edlevel << " " << setw(10) << comm << endl;
EXEC SQL FETCH c21 INTO :job, :edlevel, :comm;
EMB_SQL_CHECK("cursor -- fetch");
}
// close cursor
EXEC SQL CLOSE c21;
EMB_SQL_CHECK("cursor -- close");
return 0;
} //TbRead::EmployeeTbPartialContentDisplay
int main(int argc, char *argv[])
{
int rc = 0;
CmdLineArgs check;
TbRead read;
DbEmb db;
// check the command line arguments
rc = check.CmdLineArgsCheck1(argc, argv, db);
if (rc != 0)
return rc;
cout.setf(ios::fixed, ios::floatfield);
cout.precision(2);
cout << "\nTHIS SAMPLE SHOWS HOW TO READ TABLES." << endl;
cout << "\n THIS SAMPLE SHOWS:" << endl;
cout << " - METHODS TO PERFORM SELECT" << endl;
cout << " - SELECT TYPES" << endl;
cout << " ALL METHODS APPLY TO ALL TYPES, EXCEPTING" << endl;
cout << " 'TbRowSubselect' TYPE WHERE THE FOLLOWING METHODS" << endl;
cout << " DESCRIBED IN dbuse SAMPLE APPLY:" << endl;
cout << " StaticStmtWithHostVarsInvoke" << endl;
cout << " CompoundStmtInvoke" << endl;
cout << "\n METHODS TO PERFORM SELECT." << endl;
cout << " SELECT HAS TWO IMPORTANT PARTS:" << endl;
cout << " -DECLARE CURSOR" << endl;
cout << " -FETCH CURSOR" << endl;
cout << " THERE ARE THREE METHODS TO DECLARE CURSOR:" << endl;
cout << " -SIMPLE DECLARE CURSOR" << endl;
cout << " -PREPARE-DECLARE CURSOR" << endl;
cout << " -PREPARE-DESCRIBE-DECLARE CURSOR" << endl;
cout << " THERE ARE TWO METHODS TO FETCH CURSOR:" << endl;
cout << " -FETCH INTO HOST VARIABLES" << endl;
cout << " -FETCH INTO SQLDA" << endl;
// connect to database
rc = db.Connect();
if (rc != 0)
{
return rc;
}
// methods to perform SELECT
cout << "\n***************************************" << endl;
cout << " METHODS TO PERFORM SELECT" << endl;
cout << "***************************************" << endl;
rc = read.TbSelectUsingFetchIntoHostVariables();
rc = read.TbSelectUsingFetchIntoSQLDA();
rc = read.TbSelectUsingDeclareCursor();
rc = read.TbSelectUsingPrepareDeclareCursor();
rc = read.TbSelectUsingPrepareDescribeDeclareCursor();
// SELECT types
cout << "\n***************************************" << endl;
cout << " SELECT TYPES" << endl;
cout << "**************************************" << endl;
rc = read.TbMostSimpleSubselect();
rc = read.TbBasicSubselect();
rc = read.TbGroupBySubselect();
rc = read.TbSubselect();
rc = read.TbRowSubselect();
rc = read.TbFullselect();
rc = read.TbSelectStatement();
rc = read.TbBasicSubselectFromMultipleTables();
rc = read.TbBasicSubselectFromJoinedTable();
rc = read.TbBasicSubselectUsingSubquery();
rc = read.TbBasicSubselectUsingCorrelatedSubquery();
rc = read.TbSubselectUsingGroupingSets();
rc = read.TbSubselectUsingRollup();
rc = read.TbSubselectUsingCube();
rc = read.TbSelectStatementUsingRecursiveCommonTableExpression();
rc = read.TbSelectUsingQuerySampling();
// disconnect from the database
rc = db.Disconnect();
if (rc != 0)
{
return rc;
}
return 0;
} // main