testallmethods.c

proc写的动态sql调用方法

		bind_desc->N = MAX_SELECT_ITEMS;
		/* EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_desc; */ 

{
  struct sqlexd sqlstm;
  sqlstm.sqlvsn = 10;
  sqlstm.arrsiz = 4;
  sqlstm.sqladtp = &sqladt;
  sqlstm.sqltdsp = &sqltds;
  sqlstm.iters = (unsigned int  )1;
  sqlstm.offset = (unsigned int  )339;
  sqlstm.cud = sqlcud0;
  sqlstm.sqlest = (unsigned char  *)&sqlca;
  sqlstm.sqlety = (unsigned short)256;
  sqlstm.occurs = (unsigned int  )0;
  sqlstm.sqhstv[0] = (         void  *)bind_desc;
  sqlstm.sqhstl[0] = (unsigned int  )0;
  sqlstm.sqhsts[0] = (         int  )0;
  sqlstm.sqindv[0] = (         void  *)0;
  sqlstm.sqinds[0] = (         int  )0;
  sqlstm.sqharm[0] = (unsigned int  )0;
  sqlstm.sqadto[0] = (unsigned short )0;
  sqlstm.sqtdso[0] = (unsigned short )0;
  sqlstm.sqphsv = sqlstm.sqhstv;
  sqlstm.sqphsl = sqlstm.sqhstl;
  sqlstm.sqphss = sqlstm.sqhsts;
  sqlstm.sqpind = sqlstm.sqindv;
  sqlstm.sqpins = sqlstm.sqinds;
  sqlstm.sqparm = sqlstm.sqharm;
  sqlstm.sqparc = sqlstm.sqharc;
  sqlstm.sqpadto = sqlstm.sqadto;
  sqlstm.sqptdso = sqlstm.sqtdso;
  sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
  if (sqlca.sqlcode < 0) errorProc();
}


		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; */ 

{
   struct sqlexd sqlstm;
   sqlstm.sqlvsn = 10;
   sqlstm.arrsiz = 4;
   sqlstm.sqladtp = &sqladt;
   sqlstm.sqltdsp = &sqltds;
   sqlstm.stmt = "";
   sqlstm.iters = (unsigned int  )1;
   sqlstm.offset = (unsigned int  )358;
   sqlstm.selerr = (unsigned short)1;
   sqlstm.cud = sqlcud0;
   sqlstm.sqlest = (unsigned char  *)&sqlca;
   sqlstm.sqlety = (unsigned short)256;
   sqlstm.occurs = (unsigned int  )0;
   sqlstm.sqhstv[0] = (         void  *)bind_desc;
   sqlstm.sqhstl[0] = (unsigned int  )0;
   sqlstm.sqhsts[0] = (         int  )0;
   sqlstm.sqindv[0] = (         void  *)0;
   sqlstm.sqinds[0] = (         int  )0;
   sqlstm.sqharm[0] = (unsigned int  )0;
   sqlstm.sqadto[0] = (unsigned short )0;
   sqlstm.sqtdso[0] = (unsigned short )0;
   sqlstm.sqphsv = sqlstm.sqhstv;
   sqlstm.sqphsl = sqlstm.sqhstl;
   sqlstm.sqphss = sqlstm.sqhsts;
   sqlstm.sqpind = sqlstm.sqindv;
   sqlstm.sqpins = sqlstm.sqinds;
   sqlstm.sqparm = sqlstm.sqharm;
   sqlstm.sqparc = sqlstm.sqharc;
   sqlstm.sqpadto = sqlstm.sqadto;
   sqlstm.sqptdso = sqlstm.sqtdso;
   sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
   if (sqlca.sqlcode < 0) errorProc();
}



			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; */ 

{
 struct sqlexd sqlstm;
 sqlstm.sqlvsn = 10;
 sqlstm.arrsiz = 4;
 sqlstm.sqladtp = &sqladt;
 sqlstm.sqltdsp = &sqltds;
 sqlstm.iters = (unsigned int  )1;
 sqlstm.offset = (unsigned int  )377;
 sqlstm.cud = sqlcud0;
 sqlstm.sqlest = (unsigned char  *)&sqlca;
 sqlstm.sqlety = (unsigned short)256;
 sqlstm.occurs = (unsigned int  )0;
 sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
}


	/* EXEC SQL COMMIT WORK RELEASE; */ 

{
 struct sqlexd sqlstm;
 sqlstm.sqlvsn = 10;
 sqlstm.arrsiz = 4;
 sqlstm.sqladtp = &sqladt;
 sqlstm.sqltdsp = &sqltds;
 sqlstm.iters = (unsigned int  )1;
 sqlstm.offset = (unsigned int  )392;
 sqlstm.cud = sqlcud0;
 sqlstm.sqlest = (unsigned char  *)&sqlca;
 sqlstm.sqlety = (unsigned short)256;
 sqlstm.occurs = (unsigned int  )0;
 sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
}


	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; */ 

{
 struct sqlexd sqlstm;
 sqlstm.sqlvsn = 10;
 sqlstm.arrsiz = 4;
 sqlstm.sqladtp = &sqladt;
 sqlstm.sqltdsp = &sqltds;
 sqlstm.iters = (unsigned int  )1;
 sqlstm.offset = (unsigned int  )407;
 sqlstm.cud = sqlcud0;
 sqlstm.sqlest = (unsigned char  *)&sqlca;
 sqlstm.sqlety = (unsigned short)256;
 sqlstm.occurs = (unsigned int  )0;
 sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
}


	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; */ 

{
 struct sqlexd sqlstm;
 sqlstm.sqlvsn = 10;
 sqlstm.arrsiz = 4;
 sqlstm.sqladtp = &sqladt;
 sqlstm.sqltdsp = &sqltds;
 sqlstm.iters = (unsigned int  )1;
 sqlstm.offset = (unsigned int  )422;
 sqlstm.cud = sqlcud0;
 sqlstm.sqlest = (unsigned char  *)&sqlca;
 sqlstm.sqlety = (unsigned short)256;
 sqlstm.occurs = (unsigned int  )0;
 sqlstm.sqhstv[0] = (         void  *)sel_desc;
 sqlstm.sqhstl[0] = (unsigned int  )0;
 sqlstm.sqhsts[0] = (         int  )0;
 sqlstm.sqindv[0] = (         void  *)0;
 sqlstm.sqinds[0] = (         int  )0;
 sqlstm.sqharm[0] = (unsigned int  )0;
 sqlstm.sqadto[0] = (unsigned short )0;
 sqlstm.sqtdso[0] = (unsigned short )0;
 sqlstm.sqphsv = sqlstm.sqhstv;
 sqlstm.sqphsl = sqlstm.sqhstl;
 sqlstm.sqphss = sqlstm.sqhsts;
 sqlstm.sqpind = sqlstm.sqindv;
 sqlstm.sqpins = sqlstm.sqinds;
 sqlstm.sqparm = sqlstm.sqharm;
 sqlstm.sqparc = sqlstm.sqharc;
 sqlstm.sqpadto = sqlstm.sqadto;
 sqlstm.sqptdso = sqlstm.sqtdso;
 sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
 if (sqlca.sqlcode < 0) errorProc();
}



	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; */ 

{
  struct sqlexd sqlstm;
  sqlstm.sqlvsn = 10;
  sqlstm.arrsiz = 4;
  sqlstm.sqladtp = &sqladt;
  sqlstm.sqltdsp = &sqltds;
  sqlstm.iters = (unsigned int  )1;
  sqlstm.offset = (unsigned int  )441;
  sqlstm.cud = sqlcud0;
  sqlstm.sqlest = (unsigned char  *)&sqlca;
  sqlstm.sqlety = (unsigned short)256;
  sqlstm.occurs = (unsigned int  )0;
  sqlstm.sqhstv[0] = (         void  *)sel_desc;
  sqlstm.sqhstl[0] = (unsigned int  )0;
  sqlstm.sqhsts[0] = (         int  )0;
  sqlstm.sqindv[0] = (         void  *)0;
  sqlstm.sqinds[0] = (         int  )0;
  sqlstm.sqharm[0] = (unsigned int  )0;
  sqlstm.sqadto[0] = (unsigned short )0;
  sqlstm.sqtdso[0] = (unsigned short )0;
  sqlstm.sqphsv = sqlstm.sqhstv;
  sqlstm.sqphsl = sqlstm.sqhstl;
  sqlstm.sqphss = sqlstm.sqhsts;
  sqlstm.sqpind = sqlstm.sqindv;
  sqlstm.sqpins = sqlstm.sqinds;
  sqlstm.sqparm = sqlstm.sqharm;
  sqlstm.sqparc = sqlstm.sqharc;
  sqlstm.sqpadto = sqlstm.sqadto;
  sqlstm.sqptdso = sqlstm.sqtdso;
  sqlcxt((void **)0, &sqlctx, &sqlstm, &sqlfpn);
  if (sqlca.sqlcode == 1403) goto end_select_loop;
  if (sqlca.sqlcode < 0) errorProc();
}


		/* 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;
}