oraquery.pc

oracle下利用proc在 vc++开发的一个小例子

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <setjmp.h>
#include "oracle.h"

EXEC SQL INCLUDE sqlca;
EXEC SQL INCLUDE sqlda;

extern "C"{
	extern SQLDA*sqlald(int,int,int);
	extern void sqlnul(short*,short*,int*);
	extern void sqlprc(int*,int*,int*);
	extern void sqlclu(SQLDA*);
}

int oracle_connect();
int get_sql_statement(char*);

jmp_buf jmp_continue;

char* dml_commands[]={
		"SELECT","select",
		"INSERT","insert",
		"UPDATE","update",
		"DELETE","delete"};

FILE* ORAin=stdin;
FILE* ORAout=stdout;
int   ORAresult=0;









class ORAquery{
public:
	virtual int set(int,int,int);
	virtual int alloc();
	virtual int get_statement();
	virtual int set_statement(const char*);
	virtual int execute();
	void bind_variables();
	void select_list();
	virtual int free();
	virtual void error();

	EXEC SQL BEGIN DECLARE SECTION;
		char _sql[1024];
		EXEC SQL VAR _sql IS STRING(1024);
	EXEC SQL END DECLARE SECTION;
	SQLDA *bind;
	SQLDA *select;
	int nitem,vname,iname;
	int flag;
};

inline int ORAquery::set(int nn,int vv,int ii){
	nitem=nn;
	vname=vv;
	iname=ii;
	return 0;
}

int ORAquery::alloc(){
	int k;
	if((bind=sqlald(nitem,vname,iname))==(SQLDA*)0)return -1;
	if((select=sqlald(nitem,vname,iname))==(SQLDA*)0){sqlclu(bind);return -1;}
	for(k=0;k<nitem;k++){
		bind->I[k]=new short;
		bind->V[k]=new char[vname];
		select->I[k]=new short;
		select->V[k]=new char[vname];
	}		
	return 0;
}

int ORAquery::get_statement(){
	if(get_sql_statement(_sql)!=0)return -1;
	return 0;
}

inline int ORAquery::set_statement(const char* sql){
	if(sql==0)return -1;
	strcpy(_sql,sql);
	return 0;
}

int ORAquery::execute(){
	fprintf(ORAout,"\n%s\n",_sql);
	EXEC SQL WHENEVER SQLERROR DO error();

	flag=1;
	EXEC SQL PREPARE SS FROM :_sql;
	flag=0;

	EXEC SQL DECLARE CC CURSOR FOR SS;
	bind_variables();

	EXEC SQL OPEN CC USING DESCRIPTOR bind;

	select_list();

	for(int k=0;k<8;k++){
		if(strncmp(_sql,dml_commands[k],6)==0){
			fprintf(ORAout,"\n\n%d row%c processed.\n",
				sqlca.sqlerrd[2],sqlca.sqlerrd[2]==1? '\0':'s');
			break;
		}
	}
	return 0;
}

void ORAquery::bind_variables(){
	int k,len;
	char bind_var[64];
	EXEC SQL WHENEVER SQLERROR DO error();
	bind->N=nitem;
	EXEC SQL DESCRIBE BIND VARIABLES FOR SS INTO bind;

	if(bind->F<0){
		fprintf(ORAout,"\nToo many bind variables(%d),maximum is %d\n",-(bind->F),nitem);
		return;
	}

	bind->N=bind->F;
	for(k=0;k<bind->F;k++){
		fprintf(ORAout,"\nEnter value for bind variable %.*s:",
			(int)bind->C[k],bind->S[k]);
		fgets(bind_var,sizeof(bind_var),stdin);
		len=strlen(bind_var)-1;
		bind->L[k]=len;
		delete[] bind->V[k];
		bind->V[k]=new char[bind->L[k]+1];
		strncpy(bind->V[k],bind_var,len);
		if((strncmp(bind->V[k],"NULL",4)==0)
			||(strncmp(bind->V[k],"null",4)==0))
			*bind->I[k]=-1;
		else 
			*bind->I[k]=0;
		bind->T[k]=1;
	}
}

void ORAquery::select_list(){
	int k,null_ok,precision,scale;
	if((strncmp(_sql,"SELECT",6)!=0)
		&&(strncmp(_sql,"select",6)!=0)){
		select->F=0;
		return;
	}

	select->N=nitem;
	EXEC SQL DESCRIBE SELECT LIST FOR SS INTO select;
	if(select->F<0){
		fprintf(ORAout,"\nToo many select_list items(%d),maximum is %d\n",
			-(select->F),nitem);
		return;
	}
	
	select->N=select->F;
	fprintf(ORAout,"\n");
	for(k=0;k<select->F;k++){
		sqlnul(&(select->T[k]),&(select->T[k]),&null_ok);
		switch(select->T[k]){
			case 1:break;
			case 2:
				sqlprc(&select->L[k],&precision,&scale);
				if(precision==0)precision=40;
				select->L[k]=precision+2;
				break;
			case 8:
				select->L[k]=240;
				break;
			case 11:
				select->L[k]=18;
				break;
			case 12:
				select->L[k]=9;
				break;
			case 23:
				select->L[k]=240;
				break;
			}
			delete [] select->V[k];
			select->V[k]=new char[select->L[k]+1];
			if(select->T[k]==2)
				fprintf(ORAout,"%.*s",select->L[k],select->S[k]);
			else
				fprintf(ORAout,"%-.*s",select->L[k],select->S[k]);
			if(select->T[k]!=24)
				select->T[k]=1;
	}
	fprintf(ORAout,"\n\n");
	EXEC SQL WHENEVER NOT FOUND GOTO end_select_loop;
	for(;;){
		EXEC SQL FETCH CC USING DESCRIPTOR select;
		for(k=0;k<select->F;k++){
			if(*select->I[k]==0)
				fprintf(ORAout,"%-.*s",(int)select->L[k],select->V[k]);
			else
				fprintf(ORAout,"%-.*s",(int)select->L[k],select->V[k]);
		}
		fprintf(ORAout,"\n");
	}
	end_select_loop:
	return;
}

int ORAquery::free(){
	int k;
	for(k=0;k<nitem;k++){
		if(bind->V[k]!=(char*)0) delete[] bind->V[k];
		delete bind->I[k];
		if(select->V[k]!=(char*)0) delete[] select->V[k];
		delete select->I[k];
	}
	sqlclu(bind);
	sqlclu(select);

	EXEC SQL WHENEVER SQLERROR CONTINUE;
	EXEC SQL CLOSE CC;
	EXEC SQL COMMIT WORK RELEASE;
	return 0;
}

void ORAquery::error(){
	fprintf(ORAout,"\n\n%.70s\n",sqlca.sqlerrm.sqlerrmc);
	if(flag)
		fprintf(ORAout,"Parse error at character offset %d in SQL statement.\n",sqlca.sqlerrd[4]);
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	EXEC SQL ROLLBACK WORK;
	ORAresult=-2;
	longjmp(jmp_continue,1);
}

ORAquery query;
ORAqueryBase* pQuery=(ORAqueryBase*)&query;









#ifdef TESTMAIN
void main(){
	if(oracle_connect()!=0)exit(1);
	query.set(40,30,30);
	if(query.alloc()!=0)exit(1);

	for(;;){
		setjmp(jmp_continue);
		if(query.get_statement()!=0)break;
		query.execute();
	}
	
	query.free();
	return;
}
#endif

int oracle_connect(){
	EXEC SQL BEGIN DECLARE SECTION;
		VARCHAR username[20];
		VARCHAR password[20];
	EXEC SQL END DECLARE SECTION;
	fprintf(ORAout,"\nEnter user name: ");
	fgets((char*)username.arr,20,ORAin);
	username.len=strlen((char*)username.arr);
	fprintf(ORAout,"\nEnter password: ");
	fgets((char*)password.arr,20,ORAin);
	password.len=strlen((char*)password.arr);
	EXEC SQL WHENEVER SQLERROR GOTO connect_error;
	EXEC SQL CONNECT :username IDENTIFIED BY :password;
	fprintf(ORAout,"\nConnected to ORACLE as user %s.\n",username.arr);
	return 0;
	connect_error:
	fprintf(ORAout,"Cannot connect to ORACLE as user %s \n",username.arr);
	return -1;
}

void help(){
	fprintf(ORAout,"\nEnter a SQL statement at the SQL prompt.\n");
	fprintf(ORAout,"Use standard (not embedded) SQL syntax.\n");
}

int get_sql_statement(char*s){
	char*cp,linebuf[256];
	int iter;
	for(iter=2;;){
		if(iter==2){
			fprintf(ORAout,"\nSQL");
			s[0]=0;
		}
		fgets(linebuf,sizeof(linebuf),ORAin);
		cp=strrchr(linebuf,'\n');
		if(cp&&cp!=linebuf)*cp=' ';
		else if(cp==linebuf)continue;
		if(strncmp(linebuf,"exit",4)==0
			||strncmp(linebuf,"EXIT",4)==0){
			return -1;
		}else if(linebuf[0]=='?'
			||strncmp(linebuf,"help",4)==0
			||strncmp(linebuf,"HELP",4)==0){
			help();
			iter=2;
			continue;
		}
		strcat(s,linebuf);
		if((cp=strrchr(s,';'))!=(char*)NULL){
			*cp=0;
			break;
		}else{
			fprintf(ORAout,"%3d",iter++);
		}
	}
	return 0;
}






void ORAerror(){
	EXEC SQL WHENEVER SQLERROR CONTINUE;
	fprintf(ORAout,"\nOracle error detected:\n");
	fprintf(ORAout,"\n%.70s\n\n",sqlca.sqlerrm.sqlerrmc);
	EXEC SQL ROLLBACK RELEASE;
	ORAresult=-1;
	longjmp(jmp_continue,1);
}

int ORAconnect(
	const char*const user
	,const char*const password){
	if(!user||!*user)return -1;
	if(!password||!*password)return false;

	EXEC SQL BEGIN DECLARE SECTION;
		VARCHAR un[30];
		VARCHAR pwd[30];
	EXEC SQL END DECLARE SECTION;

	strcpy((char*)un.arr,user);
	un.len=strlen(user);
	strcpy((char*)pwd.arr,password);
	pwd.len=strlen(password);

	EXEC SQL WHENEVER SQLERROR GOTO conn_err;
	EXEC SQL CONNECT :un IDENTIFIED BY :pwd;
	fprintf(ORAout,"\nConnected to ORACLE as user: %s\n",un.arr);
	return 0;

	conn_err:
	fprintf(ORAout,"\nCan not connect to ORACLE as user: %s\n",un.arr);
	return -1;
}

int ORAdisconnect(){
	EXEC SQL WHENEVER SQLERROR GOTO disc_err;
	EXEC SQL COMMIT WORK RELEASE;
	return 0;

	disc_err:
	fprintf(ORAout,"\nDisconnect from ORACLE\n");
	return -1;
}