oradbutil.pc

使用Pro*C封装的访问数据库的源码

/********************************************************************
Author:WISEKING           Email:[e-mail]
MODIFY TIME: 2003-12
PROGRAM NAME: OraDBUtil.pc                                       
程序说明：这个程序是ORACLE的动态方法四的数据库存取程序的封装
   程序员可以方便调用该文件所提供的execQuery(), execUpdate()方法，进行数据库操作
   而不必掌握复杂的Pro*C语句
*********************************************************************/
#include <stdio.h>
#include <string.h>
#include <setjmp.h>
#include <stdlib.h>
#include <ctype.h>
#include "OraDBUtil.h"

EXEC SQL INCLUDE sqlda;
EXEC SQL INCLUDE sqlca;
EXEC SQL INCLUDE oraca;
EXEC SQL INCLUDE sqlcpr;

char* upperStr(char* p)
{
  char *ptr;

  ptr = p;
  while( *ptr != 0 )
  {
    if( *ptr>='a' && *ptr<='z' )
      *ptr = 'A' + *ptr - 'a';
      ptr ++ ;
  }

  return p;
}

/**
注意问题：对你malloc或relloc的指针不要轻易重定向，否则在释放中会出现问题！！！
教训：
LRTrim时吧指针p的位置偏移
*/
char* LRTrim(char *p)
{
        char *ptr;
        char *ptr2;

        ptr = p;
	while( *ptr == 0x20 )
	   ptr ++;
        ptr2 = ptr;

        ptr = ptr2 + strlen(ptr2);
        ptr--;
        while(ptr >= ptr2 && *ptr == 0x20)
                ptr--;

        *(ptr + 1) = '\0';
        memcpy( p, ptr2, strlen(ptr2)+1);
        return p;
}


int execUpdate(char* dySql)
{
	EXEC SQL BEGIN DECLARE SECTION;
		char userid[12] = "busi/busi";
	EXEC SQL END DECLARE SECTION;
	EXEC SQL CONNECT :userid;
	
	EXEC SQL PREPARE updateCur FROM :dySql;
	EXEC SQL EXECUTE updateCur ;
	if (sqlca.sqlcode != 0){
	  printf("ORA-%d",abs(sqlca.sqlcode));
	  EXEC SQL ROLLBACK WORK ;
	  return -1;
	}
	EXEC SQL COMMIT WORK RELEASE;
	return 0;

}

int execQuery(char* querySQL, DataSet *retDataSet, char* errMsg)
{
	EXEC SQL BEGIN DECLARE SECTION;
	    char    dyn_statement[1024];
	    EXEC SQL VAR dyn_statement IS STRING(1024);/*利用这个语句可以去掉dyn_statement多余的空格*/
	EXEC SQL END DECLARE SECTION;
	
	SQLDA *bind_dp=NULL;
	SQLDA *select_dp=NULL;
	int i, null_ok, precision, scale;
	char title[MAX_VNAME_LEN];
	char*** tmpCtx=NULL;
	char**  tmpRow=NULL;
	char*   tmpField=NULL;
	long	lRowCount=0;
        char tmpNum[50];

	EXEC SQL BEGIN DECLARE SECTION;
		char userid[12] = "busi/busi";
	EXEC SQL END DECLARE SECTION;
	/*连接数据库，未加入出错处理，请自行加入*/
	EXEC SQL CONNECT :userid;
	

	strcpy(dyn_statement,querySQL);
	printf("dyn_statement=%s\n",dyn_statement);


	/* Only support SELECT statement,程序只支持选择语句 */
	if ((strncmp(dyn_statement, "SELECT", 6) != 0) &&
	(strncmp(dyn_statement, "select", 6) != 0))
	{
		sprintf(errMsg, "'%s' isn't a SELECT statement\n",dyn_statement);
		return -1;
	}
	
	/* Allocate memory for the select and bind descriptors.
	分配内存为选择和绑定描述符*/

    if ((bind_dp = sqlald(MAX_ITEMS, MAX_VNAME_LEN, MAX_INAME_LEN)) == (SQLDA *) 0)
    {
        printf("Cannot allocate memory for bind descriptor.");
        return -1;  /* Have to exit in this case. */
    }

    if ((select_dp =
        sqlald (MAX_ITEMS, MAX_VNAME_LEN, MAX_INAME_LEN)) == (SQLDA *) 0)
    {
        printf("Cannot allocate memory for select descriptor.");
        return -1;
    }
    select_dp->N = MAX_ITEMS;
    for (i = 0; i < MAX_ITEMS; i++) {
        bind_dp->I[i] = (short *) malloc(sizeof(short));
        select_dp->I[i] = (short *) malloc(sizeof(short));
        bind_dp->V[i] = (char *) malloc(1);
        select_dp->V[i] = (char *) malloc(1);
    }


	EXEC SQL WHENEVER SQLERROR GOTO sql_error;


	/* Prepare the statement and declare a cursor.
	声明游标
	EXEC SQL WHENEVER SQLERROR DO sql_error(errMsg);
	*/
	
	EXEC SQL PREPARE S FROM :dyn_statement;
	
	EXEC SQL DECLARE C CURSOR FOR S;

	/* Describe any bind variables (input host variables)
	声明绑定变量*/
	
	bind_dp->N = MAX_ITEMS;  /* Initialize count of array elements.初始化最大绑定变量个数 */
	EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_dp;
	
	/* Don't support bind variable */
	if (bind_dp->F != 0)
	{
		printf ("\n Don't support bind variable\n");
		return -1;
	}
	bind_dp->N=0;

	/* Open the cursor and execute the statement.
	* If the statement is not a query (SELECT), the
	* statement processing is completed after the
	* OPEN.
	*/
	 /* 打开游标和执行该语句，如果该语句是SELECT，则在OPEN之后处理结合*/

	EXEC SQL OPEN C USING DESCRIPTOR bind_dp;
	
	/* Call the function that processes the select-list.
	* If the statement is not a query, this function
	* just returns, doing nothing.
	*/
	 /*调用函数process_select_list()来处理选择表项，如果该语句不是SELECT，则不作任何操作*/
		select_dp->N = MAX_ITEMS;
	
	/*EXEC SQL WHENEVER SQLERROR DO sql_error(errMsg);
	*/
	EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;

	if (select_dp->F < 0)
	{
	printf("\nToo many select-list items (%d), maximum is %d\n",	-(select_dp->F), MAX_ITEMS);
	return -1;
	}

	select_dp->N = select_dp->F;
	retDataSet->iCols = select_dp->F;

    retDataSet->fields = (char**)malloc(sizeof(char*)*retDataSet->iCols );
    for (i = 0; i < select_dp->F; i++)
    {
        /* Turn off high-order bit of datatype (in this example,
           it does not matter if the column is NOT NULL). */
        sqlnul ((unsigned short *)&(select_dp->T[i]), (unsigned short *)&(select_dp->T[i]), &null_ok);

        switch (select_dp->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 sqlprc() to
                         extract precision and scale. */
                sqlprc ((unsigned long*)&(select_dp->L[i]), &precision, &scale);
                      /* Allow for maximum size of NUMBER. */
                if (precision == 0) precision = 40;
                      /* Also allow for decimal point and
                         possible sign. */
                /* convert NUMBER datatype to FLOAT if scale > 0,
                   INT otherwise. */
                if (scale > 0)
                    select_dp->L[i] = 80; /* wiseking edit it sizeof(float);*/
                else
                    select_dp->L[i] = 80; /* wiseking edit it sizeof(int);*/
                break;

            case  8 : /* LONG datatype */
                select_dp->L[i] = 240;
                break;

            case 11 : /* ROWID datatype */
            case 104 : /* Universal ROWID datatype */
                select_dp->L[i] = 18;
                break;

            case 12 : /* DATE datatype */
                select_dp->L[i] = 19;					
		/*SunSulin Modified 2001.12.5*/
                break;

            case 23 : /* RAW datatype */
                break;

            case 24 : /* LONG RAW datatype */
                select_dp->L[i] = 240;
                break;
        } /* end switch  */


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


	memset(title, ' ', MAX_VNAME_LEN);
	sprintf(title,"%.*s",select_dp->C[i],select_dp->S[i]);
	tmpField = (char*)malloc(sizeof(char)*(select_dp->C[i]+1));
	strcpy(tmpField, title );
	upperStr(tmpField);
	retDataSet->fields[i] = tmpField;

        /* Coerce ALL datatypes except for LONG RAW and NUMBER to
           character.除了LONG RAW and NUMBER外，所有的数据类型都转换成为字符型 */
        if (select_dp->T[i] != 24 && select_dp->T[i] != 2)
            select_dp->T[i] = 1;

        /* Coerce the datatypes of NUMBERs to float or int depending on
           the scale. */
        if (select_dp->T[i] == 2)
          if (scale > 0)
             select_dp->T[i] = 1;  /*wiseking edit all is varchar 4 float */
          else
             select_dp->T[i] = 1;  /*wiseking edit all is varchar 3 int */

    }/* end for */

    /* FETCH each row selected and print the column values.
	返回每一行的值，并将值放入到缓冲区中*/
    EXEC SQL WHENEVER NOT FOUND GOTO end_select_loop;
    

    for (;;)
    {
        EXEC SQL FETCH C USING DESCRIPTOR select_dp;
        tmpRow = (char**)malloc(sizeof(char*)*retDataSet->iCols );
        for (i = 0; i < select_dp->F; i++)
	{
	/* 向缓冲区SndBuf中写入EMP表的字段值 */
		if (*select_dp->I[i]==-1) /*为空*/
		{
			tmpField = (char*)malloc(sizeof(char)*5);