home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
OS/2 Shareware BBS: 18 REXX
/
18-REXX.zip
/
SQLDPN.ZIP
/
SQLDPND.SQC
< prev
Wrap
Text File
|
1991-06-27
|
13KB
|
382 lines
/****************************************************************************/
/* */
/* MODULE NAME: SQLDPND.SQC */
/* */
/* DESCRIPTIVE NAME: Display Dependencies for an SQL Statement */
/* */
/* DEPENDENCIES: */
/* Program must be precompiled with the following options (creating */
/* .C and .BND files): */
/* */
/* sqlprep sqldpnd.sqc dbname /b */
/* */
/* To run sqldpnd.exe, the .BND file from the pre-compilation must */
/* be in the same subdirectory as sqldpnd.exe. */
/* */
/* Changes: */
/* - 5/23/89: Add the clause 'and dcreator = user' to select against */
/* the system catalog 'sysibm.sysplandep' */
/* */
/****************************************************************************/
/****************************************************************************/
/* INCLUDE FILES */
/****************************************************************************/
#include <malloc.h> /* Include files for C built-in functions */
#include <memory.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
/* The following #define says "include base os/2 calls" -- used in os2.h */
#define INCL_BASE
#include <doscalls.h> /* Include file for OS/2 function calls */
#include <os2.h>
#include <sql.h> /* Database Manager System constants */
#include <sqlca.h> /* sqlca */
#include <sqlcodes.h> /* SQL return code constants */
#include <sqlenv.h> /* Database Environment Command interfaces */
/****************************************************************************/
/* EXTERNAL DECLARATIONS */
/****************************************************************************/
EXEC SQL INCLUDE SQLCA; /* Define an sqlca for SQL return codes */
EXEC SQL BEGIN DECLARE SECTION;
char sql_stmt[2000]; /* String to hold SQL statements */
struct {
short int length;
char data[20];
} bname;
char bcreator[9];
char btype;
short int bname_len;
EXEC SQL END DECLARE SECTION;
int rc = 0; /* Initialize return code to 0 */
int ok; /* flag for prompting for SQL stmt */
unsigned short usrc = 0; /* Unsigned short return code */
int numrows; /* # of rows read from the table */
char dbname[20]; /* Database name to connect to */
char getpswd[9]; /* String to retrieve password */
char pw_ptr[9] = "\0\0\0\0\0\0\0\0\0"; /* Database password; init to null */
char stmtfile[20]; /* file to contain SQL statement */
FILE *ptr_output; /* output file pointer */
unsigned char pgmname[30]; /* Parameters for sqlabind call */
unsigned char objnamebuf[20]; /* Parameters for DosExecPgm */
unsigned int objbuflen = 80;
unsigned int execflags = 0;
unsigned char arg_string[40];
unsigned char pgm_string[13];
unsigned char env[20];
struct rtnstruct {
unsigned short termcode;
unsigned short exitcode;
};
struct rtnstruct returncodes;
char deptype[10]; /* String to hold 'dependency type' */
char blanks[25]=" "; /* blanks for output */
short int numblanks; /* number of blanks to output */
#define BUFSIZE 512 /* Buffer size for error messages */
unsigned char msgbuf[BUFSIZE]; /* Message buffer */
#define LONG_DISP_LEN 4000; /* String Length used to display
Long Varchar */
/****************************************************************************/
/* FUNCTION DECLARATIONS */
/****************************************************************************/
int main();
static int fetch(void);
/****************************************************************************/
/* MAIN PROCEDURE */
/****************************************************************************/
main()
{
/*
** Install Ctrl+Break signal handler which ensures Ctrl+Break is
** processed correctly.
*/
sqleisig(&sqlca);
/*
** Prompt for Database name and Database Password
*/
printf("Enter database name: ");
gets(dbname);
printf("Enter database password; (null 'Enter' for none): ");
gets(pw_ptr);
/*
** Start Using Database
** If there was an error, print a message and exit program.
*/
sqlestrd(dbname, pw_ptr, 'S', &sqlca);
if (sqlca.sqlcode != 0)
{
printf("\nStart DB Error: sqlcode = %ld.", sqlca.sqlcode);
rc = sqlaintp(msgbuf,BUFSIZE,0,&sqlca);
if (rc < 0) /* message retrieve err*/
{
printf ("\nSQLAINTP ERROR. Return code = %d",rc);
}
if (rc > 0) /* error message return*/
{
printf ("%s\n",msgbuf);
}
}
/*
** No errors from "Start Using Database".
*/
else
{
/*
** Bind this program to the user's database
** (Assume that the .bnd file for this program is in the current directory)
*/
strcpy ( pgmname, "sqldpnd.bnd" );
rc = sqlabind ( pgmname, dbname, pw_ptr, "$$err.msg","0", &sqlca );
if (rc < 0)
{
printf ("\nError binding program sqldpnd.bnd. Sqlcode = %ld\n",
sqlca.sqlcode);
goto exit;
}
/*
** Prompt for the first SQL statement
*/
do
{
ok = 1;
printf("\n\nSQL stmt: ");
gets(sql_stmt);
if ( sql_stmt[0] != 's' && sql_stmt[0] != 'S' && sql_stmt[0] != 0 )
{
printf("SQL statement must be a SELECT -- try again.");
ok = 0;
}
} while ( ok != 1 );
/*
** Go into a loop where an SQL statement is prepared
** and executed and another statement is read in.
** Loop as long as the user keys in more SQL statements.
*/
while (sql_stmt[0] != 0)
{
/*
** Write the SQL statement into a file -- in the form of a
** DECLARE CURSOR statement.
*/
strcpy ( stmtfile, "$$stmt$$.sqc" );
if ((ptr_output = fopen(stmtfile,"w")) == NULL)
{
printf ("\nUnable to open output file.");
goto exit;
}
fprintf (ptr_output,
"EXEC "
"SQL "
"DECLARE C1 CURSOR FOR \n"
" %s;\n",
sql_stmt );
fclose (ptr_output);
/*
** Precompile the "stmt" file:
** - set up the parameters for DosExecPgm
** - execute the 'sqlprep' command
*/
objnamebuf[0] = '\0';
strcpy ( arg_string, "sqlprep.exe" );
arg_string[11] = '\0';
sprintf ( &arg_string[12],
"$$STMT$$.SQC %s",
dbname );
env[0] = '\0';
strcpy ( pgm_string, "sqlprep.exe" );
usrc = DosExecPgm ( objnamebuf,
objbuflen,
execflags,
(char *) arg_string,
env,
(PRESULTCODES) &returncodes,
pgm_string );
if (usrc < 0 || returncodes.termcode != 0)
{
printf ("\nError from DosExecPgm:"
"\n rc = %d\n termcode = %d\n resultcode = %d",
usrc,
returncodes.termcode,
returncodes.exitcode );
goto exit;
}
/*
** Retrieve the dependences for this SQL statement from the
** SYSIBM.SYSPLANDEP table.
*/
EXEC SQL
DECLARE depcur CURSOR FOR
SELECT BCREATOR, BNAME, BTYPE, LENGTH(BNAME)
FROM SYSIBM.SYSPLANDEP
WHERE DNAME = '$$STMT$$'
AND DCREATOR = USER;
printf("\nThe SQL statement is dependent on:");
printf("\n\nAuthid Object_Name Object Type");
printf("\n---------------------------------------");
numrows = 0;
EXEC SQL OPEN depcur;
if (sqlca.sqlcode == 0)
{
for (sqlca.sqlcode = 0; sqlca.sqlcode == 0; )
{
EXEC SQL
FETCH depcur INTO :bcreator, :bname, :btype, :bname_len;
if (sqlca.sqlcode == 0)
{
switch (btype)
{
case 'I':
strcpy (deptype, "Index");
break;
case 'T':
strcpy (deptype, "Table");
break;
case 'V':
strcpy (deptype, "View");
break;
}
numrows = numrows + 1;
numblanks = 18 - bname_len;
printf( "\n%-.8s %-.*s%-.*s %s",
bcreator,
bname.length, bname.data,
numblanks, blanks,
deptype );
} /* end: no error from fetch */
} /* end: fetch loop */
} /* end: no error from open cursor */
if (sqlca.sqlcode == SQL_RC_W100)
{
if(numrows == 0)
{
printf( "\nSorry Charlie ... there are no records "
"that satisfy this query.\n");
}
EXEC SQL CLOSE depcur;
}
/*
** If the return code from executing the SQL statement
** indicated an error, print a message.
*/
if (sqlca.sqlcode != 0)
{
printf("\nError: sqlcode = %ld.\n", sqlca.sqlcode);
rc = sqlaintp(msgbuf,BUFSIZE,0,&sqlca);
if (rc < 0) /* message retrieve err*/
{
printf ("SQLAINTP ERROR. Return code = %d\n",rc);
}
if (rc > 0) /* error message return*/
{
printf ("%s\n",msgbuf);
}
}
/*
** Commit the above actions to release locks on access plan name.
*/
EXEC SQL COMMIT;
if (sqlca.sqlcode != 0)
{
printf("\nError on COMMIT -- sqlcode = %ld", sqlca.sqlcode);
}
/*
** Prompt for another SQL statement.
*/
do
{
ok = 1;
printf("\n\n\nSQL stmt: ");
gets(sql_stmt);
if ( sql_stmt[0] != 's' && sql_stmt[0] != 'S' && sql_stmt[0] != 0 )
{
printf("SQL statement must be a SELECT -- try again.");
ok = 0;
}
} while ( ok != 1 && sql_stmt[0] !=0 );
/*
** End WHILE(another SQL statement) loop
*/
}
/*
** Drop the $$stmt$$ program and delete the
** $$stmt$$.sqc, $$stmt$$.c and $$err.msg files.
*/
EXEC SQL DROP PROGRAM $$STMT$$;
if (sqlca.sqlcode != 0)
{
printf("\nCould not drop access plan '$$stmt$$' - sqlcode = %ld",
sqlca.sqlcode );
}
if ((rc = unlink("$$stmt$$.sqc")) != 0 )
{
printf("\nCould not delete file: $$stmt$$.sqc");
}
if ((rc = unlink("$$stmt$$.c")) != 0 )
{
printf("\nCould not delete file: $$stmt$$.c");
}
/*
** Exit program if error.
*/
exit:
/*
** Stop Using Database and return.
*/
sqlestpd(&sqlca);
}
return(rc);
}