testallmethods.pc

proc写的动态sql调用方法

/***********************************************************
 * TestAllMethods.pc
 * created by Caoqing on 2003.12.24
 *
 * This project is used to realize dynamic sql statments used    
 * four methods.
 *  
 *  
 *
 ************************************************************/

#include <stdio.h>
#include <stdlib.h>
#include <setjmp.h>
#include <assert.h>

#include <sqlca.h>
#include <sqlda.h>
#include <sqlcpr.h>

EXEC SQL INCLUDE sqlca;
EXEC SQL INCLUDE sqlda; 

#define MAX_SELECT_ITEMS 40
#define MAX_VNAME_LEN 64
#define MAX_INAME_LEN 64

typedef struct
{
	int precision;
	int scale;
}SELDA_LEN;

jmp_buf restore_err;

void testMethod1();
void testMethod2();
void testMethod3();
void testMethod4();
void AllocateDescriptors(SQLDA * sel_desc, SQLDA * bind_desc, int maxsize);
void UnAllocateDescriptors(SQLDA * sel_desc, SQLDA * bind_desc, int maxsize);
short getInputStmt(char * stmtarr);
void errorProc();
void QueryProcess(SQLDA * sel_desc, SQLDA * bind_desc);

main()
{
EXEC SQL BEGIN DECLARE SECTION;
	char username[15] = "schoolfee";
	char passwd[15] = "school";
	char dbstring[25] = "syntong";	
EXEC SQL END DECLARE SECTION;

	/* Connect database. */
	EXEC SQL WHENEVER SQLERROR GOTO conn_err;
	EXEC SQL CONNECT :username IDENTIFIED BY :passwd USING :dbstring;
	printf("connect success.\n");

	EXEC SQL WHENEVER SQLERROR CONTINUE;
	
	testMethod1();
	testMethod2();
	testMethod3();
	testMethod4();

	EXEC SQL COMMIT RELEASE;
	printf("Testing is Ok.\n");
	exit(0);
		
conn_err:
	printf("connect database failed!\n");
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	EXEC SQL ROLLBACK RELEASE;
	exit(1);
sql_err:
	printf("implements sql statements failed!\n");
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	EXEC SQL ROLLBACK RELEASE;
	exit(1);
}

void testMethod1()
{
	EXEC SQL BEGIN DECLARE SECTION;
		char sqlstmt1[1024];
	EXEC SQL END DECLARE SECTION;
	int opt,ch;
	int i;

	printf("Please input e exit the test method 1,input others continue.\n");
	fflush(stdin);
	while((opt = getchar()) != 'e')
	{		
		fflush(stdin);
		printf("please input sql statement for dynamic method1:\n");
		memset(sqlstmt1, 0, sizeof(sqlstmt1));
		for(i = 0; (i < 1024) && ((ch=getchar()) != EOF) && (ch != '\n'); i++)
			sqlstmt1[i] = ch;

		/* implements operation. */
		printf("start run sql :%s.\n",sqlstmt1);
		
		EXEC SQL EXECUTE IMMEDIATE :sqlstmt1;
		if (sqlca.sqlcode < 0)
		{
			printf("%70s\n", sqlca.sqlerrm.sqlerrmc);
			printf("Implement sql failed!\n");
			EXEC SQL ROLLBACK;
		}		
		printf("Implement Success.affective %d recordsets.\n", sqlca.sqlerrd[2]);
		EXEC SQL COMMIT;

		fflush(stdin);
		printf("Please input e exit the test method 1,input others continue.\n");
	}
}

void testMethod2()
{
	EXEC SQL BEGIN DECLARE SECTION;
		char sqlstmt2[1024];
		int ccode, dcode, ecode;
	EXEC SQL END DECLARE SECTION;

	int opt;

	strcpy(sqlstmt2, "update employee set ccode=:v1, dcode=:v2 where ecode=:v3");
	EXEC SQL PREPARE UPDATE_EMPLOYEE FROM :sqlstmt2;

	fflush(stdin);
	printf("Please input e exit the test method 2,input others continue.\n");
	while((opt = getchar()) != 'e')
	{
		fflush(stdin);
		printf("please input ecode:");scanf("%d",&ecode);
		printf("please input ccode:");scanf("%d",&ccode);
		printf("please input dcode:");scanf("%d",&dcode);
		printf("start run sql :update employee set ccode=%d, dcode=%d where ecode=%d.\n",ccode,dcode,ecode);
		
		/* implements operation. */
		EXEC SQL EXECUTE UPDATE_EMPLOYEE USING :ccode, :dcode, :ecode;
		if (sqlca.sqlcode < 0)
		{
			printf("Implement sql failed!\n%70s\n", sqlca.sqlerrm.sqlerrmc);
			EXEC SQL ROLLBACK;
		}
		else
		{
			printf("Implement Success.affective %d recordsets.\n", sqlca.sqlerrd[2]);
			EXEC SQL COMMIT;
		}

		fflush(stdin);
		printf("Please input e exit the test method 2,input others continue.\n");
	}
}

void testMethod3()
{
	EXEC SQL BEGIN DECLARE SECTION;
		char sqlstmt3[1024];
		int ccode;
		char ename[30];
		char duty[64];
		char cname[30];
		char dname[30];
	EXEC SQL END DECLARE SECTION;
	int opt;

	strcpy(sqlstmt3, "select emp.ename, cmp.cname, edp.dname, emp.duty from employee emp,company cmp,edepartment edp where emp.ccode=:v1 and cmp.ccode=emp.ccode and edp.dcode=emp.dcode");
	EXEC SQL PREPARE select_emp FROM :sqlstmt3;

	fflush(stdin);
	printf("Please input e exit the test method 3,input others continue.\n");
	while((opt = getchar()) != 'e')
	{
		fflush(stdin);
		printf("please input ccode:");scanf("%d",&ccode);
		printf("start run sql...\n");
		
		/* implements operation. */
		EXEC SQL DECLARE empcus CURSOR FOR select_emp;
		EXEC SQL OPEN empcus USING :ccode;

		EXEC SQL WHENEVER NOT FOUND DO break;
		printf("%-30s %-30s %-30s %-60s \n","employee name","company","department","duty");
		while(1)
		{
			EXEC SQL FETCH	empcus INTO :ename, :cname, :dname, :duty;			
			printf("%-30s %-30s %-30s %-60s \n", ename, cname, dname, duty);
		}
		EXEC SQL WHENEVER SQLERROR CONTINUE;
		EXEC SQL CLOSE empcus;

		if (sqlca.sqlcode < 0)
		{
			printf("Implement sql failed!\n%70s\n", sqlca.sqlerrm.sqlerrmc);
			EXEC SQL ROLLBACK;
		}
		else
		{
			printf("Implement Success.affective %d recordsets.\n", sqlca.sqlerrd[2]);
			EXEC SQL COMMIT;
		}

		fflush(stdin);
		printf("Please input e exit the test method 3,input others continue.\n");
	}
}

void testMethod4()
{
	EXEC SQL BEGIN DECLARE SECTION;
		char sqlstmt4[1024];
	EXEC SQL END DECLARE SECTION;

	int i,loopflag,opt;
	char bindvar[MAX_VNAME_LEN];
	char * ptmp;

	SQLDA * bind_desc;
	SQLDA * sel_desc;

	/* initializes descriptors. */
	if ((sel_desc = sqlald((int)MAX_SELECT_ITEMS, (int)MAX_VNAME_LEN, (int)MAX_INAME_LEN)) == (SQLDA *)0)
	{
		printf("initializes select descriptor failed!\n");
		return;
	}

	if ((bind_desc = sqlald((int)MAX_SELECT_ITEMS, (int)MAX_VNAME_LEN, (int)MAX_INAME_LEN)) == (SQLDA *)0)
	{
		printf("initializes bind descriptor failed!\n");
		return;
	}

	/* allocates memory for descriptors. */
	AllocateDescriptors(sel_desc, bind_desc, MAX_SELECT_ITEMS);

	/* test4 is runing. */
	fflush(stdin);
	printf("Please input e exit the test method 4,input others continue.\n");
	while((opt = getchar()) != 'e')
	{
		(void)setjmp(restore_err);

		memset(sqlstmt4, 0, sizeof(sqlstmt4));
		getInputStmt(sqlstmt4);

		if ((strncmp(sqlstmt4, "exit", 4) == 0) || (strncmp(sqlstmt4, "EXIT", 4) == 0))
			goto rebegin;
				
		EXEC SQL WHENEVER SQLERROR DO errorProc();

		EXEC SQL PREPARE S FROM :sqlstmt4;
		EXEC SQL DECLARE C CURSOR FOR S;

		bind_desc->N = MAX_SELECT_ITEMS;
		EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_desc;
		if (bind_desc->F < 0)
		{
			printf("the count of bind variables is %d, don't be larger than MAX_SELECT_ITEMS %d.\n", 
				-(bind_desc->F), MAX_SELECT_ITEMS);
			continue;
		}
		bind_desc->N = bind_desc->F;

		for(loopflag = 1;loopflag; )
		{
			fflush(stdin);
			for(i = 0; i < bind_desc->F; i++)
			{
				/* input variable binding */
				printf("please input the value of %.*s:", (int)bind_desc->C[i], bind_desc->S[i]);
				fgets(bindvar, sizeof(bindvar), stdin);
				if (bindvar[0] == '\n')
				{
					loopflag = 0;
					break;
				}

				/* bind the variable with bind_desc */
				ptmp = (char *) realloc(bind_desc->V[i], bind_desc->L[i]+1);
				if (ptmp != NULL)
					bind_desc->V[i] = ptmp;
				strncpy(bind_desc->V[i], bindvar, strlen(bindvar)-1);
				bind_desc->L[i] = strlen(bindvar)-1;
				bind_desc->T[i] = 1;

				/* set the value of indicators. */ 
				if ((strncmp(bind_desc->V[i], "NULL", 4) == 0) || 
					(strncmp(bind_desc->V[i], "null", 4)== 0))
					*bind_desc->I[i] = -1;
				else
					*bind_desc->I[i] = 0;
					
			}
			if (!loopflag) break;
			else if (!i) loopflag = 0;

			/* open cursor */
			EXEC SQL OPEN C USING DESCRIPTOR bind_desc;

			if ((strncmp(sqlstmt4, "SELECT", 6) == 0) || (strncmp(sqlstmt4, "select", 6) == 0))
				QueryProcess(sel_desc, bind_desc);

		}
		
		rebegin:
			fflush(stdin);
			printf("Please input e exit the test method 4,input others continue.\n");
	}
	UnAllocateDescriptors(sel_desc, bind_desc, MAX_SELECT_ITEMS);
	sqlclu(bind_desc);
	sqlclu(sel_desc);
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	EXEC SQL CLOSE C;
	EXEC SQL COMMIT WORK RELEASE;
	exit(0);
}

void AllocateDescriptors(SQLDA * sel_desc, SQLDA * bind_desc, int maxsize)
{
	int i;
	for (i = 0; i < maxsize; i++)
	{
		sel_desc->V[i] = (char *)malloc(1);
		assert(sel_desc->V[i]);
		sel_desc->I[i] = (short *)malloc(sizeof(short));
		assert(sel_desc->I[i]);
		bind_desc->V[i] = (char *)malloc(1);
		assert(bind_desc->V[i]);
		bind_desc->I[i] = (short *)malloc(sizeof(short));
		assert(bind_desc->I[i]);
	}
}

void UnAllocateDescriptors(SQLDA * sel_desc, SQLDA * bind_desc, int maxsize)
{
	int i;
	for (i = 0; i < maxsize; i++)
	{
		free(sel_desc->V[i]);
		assert(sel_desc->V[i]);
		free(sel_desc->I[i]);
		assert(sel_desc->I[i]);
		free(bind_desc->V[i]);
		assert(bind_desc->V[i]);
		free(bind_desc->I[i]);
		assert(bind_desc->I[i]);
	}
}

short getInputStmt(char * stmtarr)
{
	int ch,i;
	fflush(stdin);
	printf("please input dynamic sql statement(input 'exit' to leave):\n");
	for(i = 0; (i < 1024) && ((ch=getchar()) != EOF) && (ch != '\n'); i++)
		stmtarr[i] = ch;
	return 1;
}

void errorProc()
{
	printf("Occurs an error :\n%s\n",sqlca.sqlerrm.sqlerrmc);
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	EXEC SQL ROLLBACK;
	longjmp(restore_err, 1);
}

void QueryProcess(SQLDA * sel_desc, SQLDA * bind_desc)
{
	int i, isnull, precision, scale;
	SELDA_LEN SeldaLen[MAX_SELECT_ITEMS];
	char ColName[MAX_VNAME_LEN+1];
	EXEC SQL WHENEVER SQLERROR DO errorProc();
	sel_desc->N = MAX_SELECT_ITEMS;
	EXEC SQL DESCRIBE SELECT LIST FOR S INTO sel_desc;

	if (sel_desc->F < 0)
	{
		printf("the count of bind variables is %d, don't be larger than MAX_SELECT_ITEMS %d.\n", 
				-(bind_desc->F), MAX_SELECT_ITEMS);
		return;
	}
	sel_desc->N = sel_desc->F;
	
	for(i = 0; i < sel_desc->F; i++)
	{
		sqlnul((unsigned short*)&sel_desc->T[i], (unsigned short*)&sel_desc->T[i], &isnull);
		switch (sel_desc->T[i])
		{
		case 1:	/* CHAR datatype: no change in length
				needed, except possibly for TO_CHAR
				conversions (not handled here). */
				break;
		case 2:	/* NUMBER datatype: use SQLNumberPrecV6() to
				extract precision and scale. */
			sqlprc(&(sel_desc->L[i]), &precision, &scale);
			if (precision == 0) precision = 38;
			if (scale > 0)
				sel_desc->L[i] = sizeof(float);
			else
				sel_desc->L[i] = sizeof(int);

			SeldaLen[i].precision = precision;
			SeldaLen[i].scale = scale;
			break;
		case 8 : /* LONG datatype */
				sel_desc->L[i] = 240;
				break;
		case 11 : /* ROWID datatype */
				sel_desc->L[i] = 18;
				break;
		case 12 : /* DATE datatype */
				sel_desc->L[i] = 9;
				break;
		case 23 : /* RAW datatype */
				break;
		case 24 : /* LONG RAW datatype */
				sel_desc->L[i] = 240;
				break;
		}

		if (sel_desc->T[i] != 2)
			sel_desc->V[i] = (char *) realloc(sel_desc->V[i],
				sel_desc->L[i] + 1);
		else
			sel_desc->V[i] = (char *) realloc(sel_desc->V[i],
				sel_desc->L[i]);

		/* get column name */
		memset(ColName, 0, MAX_VNAME_LEN);
		strncpy(ColName, sel_desc->S[i], sel_desc->C[i]);

		/* get the suitable len of displaying column and value. */
		if (sel_desc->T[i] == 2)
			if (scale > 0)
			{
				SeldaLen[i].precision = (precision > sel_desc->C[i])? precision : sel_desc->C[i];
			}
			else
			{
				SeldaLen[i].precision = (sel_desc->L[i] > sel_desc->C[i])? sel_desc->L[i] : sel_desc->C[i];
			}
		else
		{
			SeldaLen[i].precision = (sel_desc->L[i] > sel_desc->C[i])? sel_desc->L[i] : sel_desc->C[i];			
		}
		printf ("%-*s ", SeldaLen[i].precision, ColName);


		/* Coerce ALL datatypes except for LONG RAW and NUMBER to character. */
		if (sel_desc->T[i] != 24 && sel_desc->T[i] != 2)
			sel_desc->T[i] = 1;
		/* Coerce the datatypes of NUMBERs to float or int depending on the scale. */
		if (sel_desc->T[i] == 2)
			if (scale > 0)
				sel_desc->T[i] = 4; /* float */
			else
				sel_desc->T[i] = 3; /* int */	
	}
	printf("\n");

	EXEC SQL WHENEVER NOT FOUND GOTO end_select_loop;
	for(;;)
	{
		EXEC SQL FETCH C USING DESCRIPTOR sel_desc;
		/* Since each variable returned has been coerced to a
		character string, int, or float very little processing
		is required here. This routine just prints out the
		values on the terminal. */
		for (i = 0; i < sel_desc->F; i++)
		{
			if (*sel_desc->I[i] < 0)
				printf ("%-*c ", SeldaLen[i].precision, ' ');
			else
				if (sel_desc->T[i] == 3) /* int datatype */
					printf ("%*d ", SeldaLen[i].precision, *(int *)sel_desc->V[i]);
				else if (sel_desc->T[i] == 4) /* float datatype */
					printf ("%*.*f ", SeldaLen[i].precision, SeldaLen[i].scale, *(float *)sel_desc->V[i]);
				else /* character string */
					printf ("%-.*s ", SeldaLen[i].precision, sel_desc->V[i]);
		}
		printf ("\n");
	}
	end_select_loop:
		return;
}