oradb.c

一个简单的数据库管理工具

/* oradb.c - Oracle Interface for SQLweb
/*
/* Copyright (c) 1995-1999 Applied Information Technologies, Inc.
/* All Rights Reserved.
/*  
/* Distributed uder the GNU General Public License which was included in
/* the file named "LICENSE" in the package that you recieved.
/* If not, write to:
/* The Free Software Foundation, Inc.,
/* 675 Mass Ave, Cambridge, MA 02139, USA.
 */

#include <stdlib.h>
#include <ctype.h>
#include <string.h>
#include <malloc.h>
#include "sqlweb.h"

/*
/* Oracle INCLUDES.  These are defined in the "${ORACLE_HOME}/rdbms/demo"
/* directory for copyright purposes, these files must be included from your
/* local distribution.
 */
#include "oratypes.h"	/* Oracle Datatypes */
#include "ocidfn.h"	/* Oracle CDA and LDA structs */
#include "ociapr.h"	/* Oracle function defs */

/*
/* The DBINFO struct.  
 */
typedef struct {
    sb2 ind;		/* Oracle Indicator Variable BIND & SELECT */
    ub2 rlen;		/* Oracle Return Length of Variable */
    ub2 rcode;		/* Oracle "Return Code" for SELECT variables */
    char *pValue;	/* Another pointer to the SYMBOL Value */
    sword iLen;		/* Symbol pValue's (Malloced) Length */
} DbInfo_t;

/*
/* Internal/Private Functions
 */
static eBoolean_t DescribeBindVars  (SQLWEB_CURSOR *pCursor);
static eBoolean_t DescribeSelectVars(SQLWEB_CURSOR *pCDA);
static eBoolean_t GetCursorIndex(int *piCursorIndex);
static void       PushDBErr(Lda_Def *pLDA,Cda_Def *pCDA,char *pErrMsg);
static eBoolean_t SetDbNulls(DbInfo_t *pDbInfo);
static DbInfo_t * NewDbInfo();
static eBoolean_t FreeDbInfo(DbInfo_t *pDbInfo);


/*
/* oparse flags
 */
#define PARSE_DEFER	0	/* defflg */
#define PARSE_NO_DEFER	1	/* defflg */
#define LNG_FL_V6	0	/* lngflg */
#define LNG_FL_SRV	1	/* lngflg */
#define LNG_FL_V7	2	/* lngflg */

#define MAX_ITEM_BUFFER_SIZE    33
#define MAX_SQL_IDENTIFIER      31

#define VARCHAR2_TYPE            1
#define NUMBER_TYPE              2
#define INT_TYPE                 3
#define FLOAT_TYPE               4
#define STRING_TYPE              5
#define ROWID_TYPE              11
#define DATE_TYPE               12
#define LONG_TYPE		 8
/* #define LONGRAW_TYPE		24

/*  ORACLE error codes used in demonstration programs */
#define VAR_NOT_IN_LIST       1007
#define NO_DATA_FOUND         1403
#define NULL_VALUE_RETURNED   1405

/*  some SQL and OCI function codes */
#define FT_INSERT		3
#define FT_SELECT		4
#define FT_UPDATE		5
#define FT_DELETE		9
#define FC_OOPEN		14
#define FC_OCLOSE		16

/*
/* Global Variables
 */
char 
     *gpIfExpr = "SELECT 1 SQLWEB_IF_EXPR FROM DUAL WHERE"
    ,*gpNullSelect = "SELECT 1 FROM DUAL WHERE 1=2"
    ;

static LIST *glDbInfoList=NULL_LIST;

/*
/* THE LDA Array....
 */
Lda_Def gaLDA[MAX_CONNECTIONS];
#if (defined(__osf__) && defined(__alpha)) || defined(CRAY) || defined(KSR)
ub1     gaHDA[MAX_CONNECTIONS][512];
#else
ub1     gaHDA[MAX_CONNECTIONS][256];
#endif

/*
/* THE CURSOR Array
 */
Cda_Def gaCDA[MAX_CURSORS];

/*
/* MAIN for testing only
#define MAIN_TEST
 */
#ifdef MAIN_TEST
eBoolean_t gbCookFlag;
main(){
    char sBuf[BUFSIZ];
    FRMSym("name","steve");
    FRMSym("nameb","so");
    FRMSym("V2PGN","55");
    (void)x();
    while( MsgPop(sBuf) ) {
	printf("%s\n",sBuf);
    }
}
int x(){
    SQLWEB_LDA lda;
    SQLWEB_CURSOR cda;
    char *p1 = "SELECT \n\
             pi.pi_level \n\
            ,pi.page_gen_nbr pgn \n\
            ,nvl(pi.pi_seq_nbr,'0') pi_seq_nbr \n\
            ,pi.pi_gen_nbr \n\
            ,pi.tag_name \n\
            ,nvl(pi.page_gen_nbr_link,'') page_gen_nbr_link \n\
            ,pi.pi_contents \n\
    FROM html_V_page_items pi \n\
    WHERE pi.page_gen_nbr = :V2PGN ";
    char *pSql1 = "select to_char(sysdate,'mm/dd/yyyy hh24:mi') da_dat \n\
		,t.*\n\
		from html_pages t\n\
		where :name='steve' and :nameb='so'";
    char *pDaDat, *pPageName, *pPageTitle;
    RETeFalse(DbConnect("sqlweb/sqlweb",&lda),"Connect Failed");
    RETeFalse(DbOpenCursor(&lda,p1,&cda),"Open Failed");
    GetSymbolValueREF("PI_CONTENTS",&pDaDat);
    GetSymbolValueREF("PI_SEQ_NBR",&pPageName);
    GetSymbolValueREF("TAG_NAME",&pPageTitle);
    do{
	RETeFalse(DbFetchCursor(&cda,eFalse),"Fetch Failed");
	fprintf(stderr,"%s %s C(%s)\n",pPageName,pPageTitle,pDaDat);
    } while(cda.bFound==eTrue);
    RETeFalse(DbDisconnect(&lda,eFalse),"Disconnect Failed");

    return(0);
}
#endif /* MAIN_TEST */

/*
/* Connect to the ORACLE DATABASE.
/* The pConnect parameter should be passed as (char *)0 to instruct
/*     the DBI to use the ORACLE_CONNECT Symobl.  The pConnect is
/*	only used to Override the ORACLE_CONNECT symbol.
/* usage:
/*	SQLWEB_LDA lLda;
/*	RETeFalse( DbConnect((char*)0     ,&lLda), "Connect Failed");
/*	RETeFalse( DbConnect("scott/tiger",&lLda), "Connect Failed");
 */
eBoolean_t
DbConnect(char *pConnect	/* Database Specific Connect String */
	 ,SQLWEB_LDA *pLDA	/* This Function must Fills-in
				/* the SQLWEB_LDA structure
				 */
	 )
{
    static int siConnectIndex;	/* Connections are NOT re-used. */
    char sEnvBuf[MAX_TOKVAL_SIZE]
	,*pBuf
	;

    if( siConnectIndex > MAX_CONNECTIONS ){
	MsgPush("Exceeded Maximum Connections");
	return(eFalse);
    }

    /*
    /* Prepare to Connect to ORACLE
     */

    /*
    /* Must be Sure ORACLE_HOME is in the Environment!
     */
    pBuf = getenv("ORACLE_HOME");
	/* The Environment overrides the SYMBOL TABLE
	 */
    if(!pBuf) {
	RETeFalse(GetSymbolValueREF("ORACLE_HOME",&pBuf)
		 ,"Can't find ORACLE_HOME"
		 );
	sprintf(sEnvBuf,"ORACLE_HOME=%s",pBuf);
	putenv(strdup(sEnvBuf));
    }

    /*
    /* Check for ORACLE_SID, this is NOT REQUIRED because the
    /* connect string (remote) databases don't use ORACLE_SID
     */
    pBuf = getenv("ORACLE_SID");
	/* Again, Environment overrides the SYMBOL Table
	 */
    if(!pBuf) {
	if(ISeTrue(GetSymbolValueREF("ORACLE_SID",&pBuf))) {
	    sprintf(sEnvBuf,"ORACLE_SID=%s", pBuf);
	    putenv(strdup(sEnvBuf));
	}
    }

    /*
    /* pConnect Parameter overrides the ORACLE_CONNECT env variable
     */
    if( pConnect ) {
	pBuf=pConnect;
    } else {
	RETeFalse(GetSymbolValueREF("ORACLE_CONNECT",&pBuf)
		 ,"Can't find ORACLE_CONNECT"
		 );
    }

    /*
    /* Update the Generic DBI LDA Struct
     */
    pLDA->iLdaIndex = siConnectIndex;
    pLDA->bConnected= eFalse;
    pLDA->pConnect  = pBuf;

    /*
    /* DO IT!
     */
    if(orlon(&gaLDA[pLDA->iLdaIndex]	/* LDA Array,supports multiple con's */
	    ,gaHDA[pLDA->iLdaIndex]	/* HDA Array, " */
	    ,(text*)pLDA->pConnect	/* Username/Password@Host */
	    ,-1				/* pConnect is NULL Terminated */
	    , 0				/* NULL Password, uses pConnect */
	    ,-1				/* NULL Password */
	    ,-1				/* No longer used */
	    ))
    {
	/* Actual Database Login Failed
	 */
	PushDBErr(&gaLDA[pLDA->iLdaIndex]
		 ,(Cda_Def *)&gaLDA[pLDA->iLdaIndex]	/* no cursor, yet */
		 ,"Connect Failed."
		 );
	return(eFalse);
    }
   
    siConnectIndex++; 		/* Bump the Connection Index */
    pLDA->bConnected = eTrue;	/* Set Connected Flag */
    return(eTrue);		/* Oh, sweet success! */
}

/*
/* Disconnect from the DATABASE.
/* bCommit Flag:  bCommit=eTrue  means essentially "COMMIT RELEASE"
/*		  bCommit=eFalse means essentially "ROLLBACK RELEASE"
/* usage:
/*	SQLWEB_LDA lLda;
/*	... connect ...
/*	RETeFalse( DbDisonnect(&lLda,eTrue), "Connect Failed"); -- Commit
/*	RETeFalse( DbDisonnect(&lLda,eFalse),"Connect Failed"); -- Rollback
 */
eBoolean_t
DbDisconnect(SQLWEB_LDA *pLDA	/* LDA struct from DbConnect() */
	    ,eBoolean_t bCommit	/* Commit/Rollback Flag */
	    )
{
    /* Perform the COMMIT/ROLLBACK
     */
    if(ISeTrue(bCommit)) (void)DbCommit(pLDA);
    else		 (void)DbRollback(pLDA);

    if(ologof( &gaLDA[pLDA->iLdaIndex])) {
	PushDBErr(&gaLDA[pLDA->iLdaIndex]
		 ,(Cda_Def *)&gaLDA[pLDA->iLdaIndex]	/* use LDA as CDA! */
		 ,"Disconnect Failed."
		 );
	return(eFalse);
    }
    return(eTrue);
}

/*
/* Commit an Oracle Transaction
 */
eBoolean_t
DbCommit(SQLWEB_LDA *pLDA)	/* LDA struct from DbConnect() */
{
    if(ocom(&gaLDA[pLDA->iLdaIndex])){
	PushDBErr(&gaLDA[pLDA->iLdaIndex]
		 ,(Cda_Def *)&gaLDA[pLDA->iLdaIndex]	/* Use LDA for CDA */
		 ,"Commit Failed."
		 );
	return(eFalse);
    }
    return(eTrue);
}

/*
/* Rollback a Transaction.
 */
eBoolean_t
DbRollback(SQLWEB_LDA *pLDA)	/* LDA struct ftom DbConnect() */
{
    if(orol(&gaLDA[pLDA->iLdaIndex])){
	PushDBErr(&gaLDA[pLDA->iLdaIndex]
		 ,(Cda_Def *)&gaLDA[pLDA->iLdaIndex]	/* Use LDA for CDA */
		 ,"Rollback Failed."
		 );
	return(eFalse);
    }
    return(eTrue);
}

/*
/* Open a CURSOR. Must deal with SELECT and non-SELECT statements.
/* All BIND variables are taken from the SYMBOL TABLE
/* All SELECT-LIST variables are installed into the SYMBOL TABLE
/* usage:
/*	SQLWEB_LDA lLda;
/*	SQLWEB_CURSOR cCur;
/*	char *pSQL = "select ename from emp where ename like :SYM_NAME";
/*	... connect ...
/*	RETeFalse( DbOpenCursor(&lLda,pSQL,&cCur), "Open Failed");
 */
eBoolean_t
DbOpenCursor(SQLWEB_LDA *pLDA	/* SQLWEB LDA struct created by DbConnect() */
	    ,char *pStmt	/* SQL Statement, All BIND Variables will
				/* be extracted from the SYMBOL TABLE and
				/* The SELECT LIST must be INSTALLED into
				/* The SYMBOL TABLE
				 */
	    ,SQLWEB_CURSOR *pCursor
				/* This Function must Populate
				/* The SQLWEB_CURSOR structure
				 */
	    )
{
    int iCursorIndex;
    ub2 sql_ft;

    if(pStmt==(char*)0)			return(eTrue);
    if(pLDA==(SQLWEB_LDA*)0)		return(eTrue);
    if(pCursor==(SQLWEB_CURSOR*)0)	return(eTrue);


    RETeFalse(GetCursorIndex(&iCursorIndex),"GetCursorIndex Failed");

    DebugHTML(__FILE__,__LINE__,2,"DbOpenCursor(%d)",iCursorIndex);

    /*
    /* Initialize the SQLWEB_CURSOR
     */
    pCursor->pLDA	 = pLDA;
    pCursor->iCursorIndex= iCursorIndex;
    pCursor->bOpen	 = eFalse;
    pCursor->bFound	 = eFalse;
    pCursor->pStmt	 = pStmt;
    pCursor->lSelect	 = l_create("INORDER");	/* Indicator Variables */
    pCursor->lBind	 = l_create("INORDER");	/* cleanup */

    /*
    /* Open the CURSOR
     */
    if(oopen(&gaCDA[pCursor->iCursorIndex]
	    ,&gaLDA[pLDA->iLdaIndex]
	    ,(text*)0
	    ,-1
	    ,-1
	    ,(text*)0
	    ,-1
	    ))
    {
	PushDBErr(&gaLDA[pLDA->iLdaIndex]
		 ,&gaCDA[pCursor->iCursorIndex]
		 ,"oopen failed."
		 );
	return(eFalse);
    }

    /*
    /* Parse the SQL Statement
     */
    if(oparse(&gaCDA[pCursor->iCursorIndex]
	     ,(text*)pStmt
	     ,-1
	     ,PARSE_NO_DEFER	/* see 4-70 in OCI manual */
	     ,LNG_FL_V7
	     ))
    {
	PushDBErr(&gaLDA[pLDA->iLdaIndex]
		 ,&gaCDA[pCursor->iCursorIndex]
		 ,"oparse failed."
		 );
	return(eFalse);
    }
    sql_ft=gaCDA[pCursor->iCursorIndex].ft;


    /*
    /* Describe the BIND variables, (no thanks to Oracle!)
     */
    RETeFalse(DescribeBindVars(pCursor),"Describe Bind Variables Failed");

    if(sql_ft==FT_SELECT) {
	/*
	/* For SELECT Statements, describe the SELECT Variables.
	 */
	RETeFalse(DescribeSelectVars(pCursor)
		,"Describe Select Variables Failed"
		);
    }

    /*
    /* Execute the SQL Statement
     */
    if(oexec(&gaCDA[pCursor->iCursorIndex])){
	PushDBErr(&gaLDA[pLDA->iLdaIndex]
		 ,&gaCDA[pCursor->iCursorIndex]
		 ,"oexec failed."
		 );
	return(eFalse);
    }

    if(sql_ft==FT_SELECT) {
	/* Update the SQLWEB_CURSOR status for SELECT STATEMENTS ONLY
	/* This CURSOR is OPEN for BUSINESS!
	 */
	pCursor->bOpen = eTrue;
    } else {
	/* NON-SELECT statements get CLOSED right away
	 */
	if(oclose(&gaCDA[pCursor->iCursorIndex])) {
	    PushDBErr(&gaLDA[pCursor->pLDA->iLdaIndex]
		     ,&gaCDA[pCursor->iCursorIndex]
		     ,"oclose failed."
		     );
	    return(eFalse);
	}
    }

    /*
    /* Alls well that end well!
     */
    return(eTrue);
}