home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
OS/2 Shareware BBS: Product
/
Product.zip
/
DBDEMO.ZIP
/
DEMOFLS.ZIP
/
STAT_DYN.SQC
< prev
next >
Wrap
Text File
|
1991-06-30
|
8KB
|
240 lines
/************************************************************************/
/* DATABASE PERFORMANCE CONCEPTS AND TECHNIQUES DEMONSTRATION PROGRAM */
/* MODULE: STATIC VS. DYNAMIC */
/* source file: STAT_DYN.SQC */
/************************************************************************/
/************************************************************************/
/* HEADER FILES - USER DEFINED */
/************************************************************************/
#include "db.h"
/************************************************************************/
/* FUNCTION PROTOTYPES */
/************************************************************************/
short far stat_dyn(void);
short stat(void);
short dyn(void);
double q1time, q2time;
struct timeb dbeg, dend;
/***************************************************************************/
/* static query */
/***************************************************************************/
/*************************************************/
/* SQL HOST VARIABLE DECLARATIONS */
/*************************************************/
EXEC SQL BEGIN DECLARE SECTION;
char st_cu_ssn[12];
char st_cu_name[31];
double s_bal;
char c_accnumb[9];
char st_cu_addr1[26];
char st_cu_addr2[26];
char st_cu_zip[6];
EXEC SQL END DECLARE SECTION;
short num_rows;
/************************************************************************/
/* FUNCTION stat() */
/* Runs static query */
/************************************************************************/
short stat(void)
{
q1time = 0.0;
num_rows = 0;
/*** SQL error handling ***/
EXEC SQL WHENEVER SQLERROR GO TO error;
EXEC SQL WHENEVER NOT FOUND GO TO ext;
/****** DECLARE CURSOR *********/
ftime (&dbeg);
EXEC SQL DECLARE CSTAT CURSOR FOR
SELECT CU_NAME, CU_SSN, S_BAL, C_ACCNUMB
FROM BMCHUGH.CUSTOMER, BMCHUGH.SAVINGS, BMCHUGH.CHECKING
WHERE CU_SSN = S_SSN AND CU_SSN > '000-00-0000'
AND CU_SSN < '999-99-9999' AND S_BAL > 499
AND S_BAL < 20000 AND C_STARTBAL > 0
AND C_STARTBAL < 10000 AND C_ENDBAL > 0
AND C_ENDBAL < 20000 AND C_STARTBAL <= C_ENDBAL
AND CU_ZIP BETWEEN '11111' AND '99999'
AND CU_NAME LIKE 'B%Vald%'
AND (CU_SSN = C_SSN1 OR CU_SSN = C_SSN2);
ftime(&dend);
q1time += delta(dbeg.time, dend.time, dbeg.millitm, dend.millitm);
// get_error( &sqlca, " declare cursor");
/****** OPEN CURSOR *********/
ftime (&dbeg);
EXEC SQL OPEN CSTAT;
ftime(&dend);
q1time += delta(dbeg.time, dend.time, dbeg.millitm, dend.millitm);
// get_error( &sqlca, " open cursor");
/****** FETCH TILL NO MORE ROWS LEFT *********/
while (sqlca.sqlcode == 0) {
ftime (&dbeg);
EXEC SQL FETCH CSTAT
INTO :st_cu_ssn, :st_cu_name, :s_bal, :c_accnumb;
ftime(&dend);
q1time += delta(dbeg.time, dend.time, dbeg.millitm, dend.millitm);
if ((sqlca.sqlcode == 0) ) num_rows++;
}/*end while more rows */
ext:
/****** CLOSE CURSOR *********/
ftime (&dbeg);
EXEC SQL CLOSE CSTAT;
ftime(&dend);
q1time += delta(dbeg.time, dend.time, dbeg.millitm, dend.millitm);
/* printf("\nstat time = %7.3f", q1time);
*/
return(0);
error:
error_sql = sqlca.sqlcode; // copy the sql code into external variable
// to be printed on user screen by dbdemo
return(-1);
}/* end stat */
/************************************************************************/
/* FUNCTION dyn() */
/* runs the same query as stat() dynamically. It uses the simplest form*/
/* of dynamic query, assuming that */
/* 1. The query is a SELECT */
/* 2. The table name and the columns fetched are known */
/************************************************************************/
short dyn()
{
/* the same queries executed dynamically */
/*************************************************/
/* SQL HOST VARIABLE DECLARATIONS */
/*************************************************/
EXEC SQL BEGIN DECLARE SECTION;
char qstr[600];
EXEC SQL END DECLARE SECTION;
/*** SQL error handling ***/
EXEC SQL WHENEVER SQLERROR GO TO error;
EXEC SQL WHENEVER NOT FOUND GO TO ext;
/****** DECLARE CURSOR *********/
EXEC SQL DECLARE CDYN CURSOR FOR SDYN;
q2time= 0.0;
num_rows = 0;
strcpy(qstr, "SELECT CU_NAME, CU_SSN, S_BAL, C_ACCNUMB" );
strcat(qstr , " FROM BMCHUGH.CUSTOMER, BMCHUGH.SAVINGS, BMCHUGH.CHECKING");
strcat(qstr , " WHERE CU_SSN = S_SSN AND CU_SSN > '000-00-0000'" );
strcat(qstr , " AND CU_SSN < '999-99-9999' AND S_BAL > 499" );
strcat(qstr , " AND S_BAL < 20000 AND C_STARTBAL > 0" );
strcat(qstr , " AND C_STARTBAL < 10000 AND C_ENDBAL > 0" );
strcat(qstr , " AND C_ENDBAL < 20000 AND C_STARTBAL <= C_ENDBAL" );
strcat(qstr , " AND CU_ZIP BETWEEN '11111' AND '99999'" );
strcat(qstr , " AND CU_NAME LIKE 'B%Vald%'" );
strcat(qstr , " AND (CU_SSN = C_SSN1 OR CU_SSN = C_SSN2)" );
/****** PREPARE CURSOR SINCE DYNAMIC QUERY *********/
// in more complex situation DESCRIBE and SQLDA may have to be used
ftime (&dbeg);
EXEC SQL PREPARE SDYN FROM :qstr ;
ftime(&dend);
q2time += delta(dbeg.time, dend.time, dbeg.millitm, dend.millitm);
// get_error( &sqlca, " prepare cursor");
/****** OPEN CURSOR *********/
ftime (&dbeg);
EXEC SQL OPEN CDYN;
ftime(&dend);
q2time += delta(dbeg.time, dend.time, dbeg.millitm, dend.millitm);
// get_error( &sqlca, " open cursor");
/****** FETCH TILL NO MORE ROWS LEFT *********/
while (sqlca.sqlcode == 0) {
ftime (&dbeg);
EXEC SQL FETCH CDYN
INTO :st_cu_ssn, :st_cu_name, :s_bal, :c_accnumb;
ftime(&dend);
q2time += delta(dbeg.time, dend.time, dbeg.millitm, dend.millitm);
if ((sqlca.sqlcode == 0) ) num_rows++;
}/*end while more rows */
ext:
/****** CLOSE CURSOR *********/
ftime (&dbeg);
EXEC SQL CLOSE CDYN;
ftime(&dend);
q2time += delta(dbeg.time, dend.time, dbeg.millitm, dend.millitm);
/* printf("\ndyn time = %7.3f", q2time);
*/
return(0);
error:
error_sql = sqlca.sqlcode; // copy the sql code into external variable
// to be printed on user screen by dbdemo
return(-1);
}/* end dyn */
/************************************************************************/
/* FUNCTION stat_dyn() */
/************************************************************************/
short far stat_dyn()
{
short i;
q1avg=0.0;
q2avg=0.0;
// SET THE NUMBER OF DEMOS FOR THIS SECTION 1 OR 2.. 2 IS MAX!!!!
Timetext[0].test_num = 2;
for (i=1; i<=num_runs; i++)
{
if (stat())
return(3);
if (i!=1)
q1avg =q1avg + q1time;
}/*end for */
if (num_runs==1)
q1avg = q1time;
else
q1avg = q1avg/(num_runs-1);
// FILL UP TIMETEXT STRUCTURE FOR DEMO 1
strcpy(Timetext[0].demoname,"Static SQL ");
Timetext[0].demotime = q1avg;
Timetext[0].rows = num_rows;
for (i=1; i<=num_runs; i++)
{
if (dyn())
return(3);
if (i!=1)
q2avg = q2avg+ q2time;
}/*end for */
if (num_runs==1)
q2avg = q2time;
else
q2avg = q2avg/(num_runs-1);
// FILL UP TIMETEXT STRUCTURE FOR DEMO 2
strcpy(Timetext[1].demoname,"Dynamic SQL ");
Timetext[1].demotime = q2avg;
Timetext[1].rows = num_rows;
return(0);
}/* end main */