www.pudn.com > dynamicsql.rar > TestAllMethods.pc

/*********************************************************** 
 * TestAllMethods.pc 
 * created by Caoqing on 2003.12.24 
 * 
 * This project is used to realize dynamic sql statments used     
 * four methods. 
 *   
 *   
 * 
 ************************************************************/ 
 
#include  
#include  
#include  
#include  
 
#include  
#include  
#include  
 
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; 
}