/*外部說明部分*/
#include<stdio.h>
#include "operate.h"
/*說明段，說明SQL變量*/
EXEC SQL BEGIN DECLARE SECTION;
	VARCHAR username[20];
	VARCHAR tns[20];
	VARCHAR password[20];
EXEC SQL END DECLARE SECTION;
/*SQL通訊區說明*/
EXEC SQL INCLUDE sqlca;
/*C函數說明，錯誤處理*/
void getconn();
//main()
//{
//	insert(222222222,"xiaowu","男",24,"山東，濟南",9000);
//	int res = query(222222222);
	//if(res==-1) printf("沒有該用戶");
//	else printf("余額為:%d\n",res);
	//update(222222222,1000);
//	res = query(222222222);
//	if(res==-1) printf("沒有該用戶");
//	else printf("余額為:%d\n",res);
//	delete(222222222);
//}
/*程序體*/
void insert(int idd,char *pname,char *psex,int agee,char *paddress,int sall){
/*內部說明段，說明局部SQL變量*/
	EXEC SQL BEGIN DECLARE SECTION;
		int id;
		VARCHAR name[30];
		VARCHAR sex[10];
		int age;
		VARCHAR address[100];
		int sal;
	EXEC SQL END DECLARE SECTION;

	/*建立與數據庫的連接*/
	getconn();
			
			id=idd;
			strcpy(name.arr,pname);
			name.len=(unsigned short)strlen((char *)name.arr);
			strcpy(sex.arr,psex);
			sex.len=(unsigned short)strlen((char *)sex.arr);
			age=agee;
			strcpy(address.arr,paddress);
			address.len=(unsigned short)strlen((char *)address.arr);
			sal=sall;
			EXEC SQL INSERT INTO info VALUES(:id,:name,:sex,:age,:address,:sal);
			EXEC SQL COMMIT WORK;
		EXEC SQL COMMIT WORK RELEASE;
}

int query(int idd)
{
	/*內部說明段，說明局部SQL變量*/
	EXEC SQL BEGIN DECLARE SECTION;
		int id;
		int sal;
	EXEC SQL END DECLARE SECTION;
	
/*建立與數據庫的連接*/
	getconn();
			
			id=idd;
			/*查詢語句*/
			EXEC SQL WHENEVER NOT FOUND GOTO notfound;
			EXEC SQL SELECT sal INTO :sal FROM info WHERE id=:id;
			/*輸出查詢結果*/
			return sal;
			notfound:
				return -1;
		EXEC SQL COMMIT WORK RELEASE;
}

void update(int idd, int sall)
{
	/*內部說明段，說明局部SQL變量*/
	EXEC SQL BEGIN DECLARE SECTION;
		int id;
		int sal;
	EXEC SQL END DECLARE SECTION;	
	
	/*建立與數據庫的連接*/
	getconn();
			id=idd;
			sal=sall;
			EXEC SQL WHENEVER NOT FOUND GOTO notfound;
			EXEC SQL UPDATE  info SET sal=:sal WHERE id=:id;
			notfound:
				;
			EXEC SQL COMMIT WORK;
		EXEC SQL COMMIT WORK RELEASE;
}

void delete(int idd)
{
	/*內部說明段，說明局部SQL變量*/
	EXEC SQL BEGIN DECLARE SECTION;
		int id;
	EXEC SQL END DECLARE SECTION;	
	
/*建立與數據庫的連接*/
	getconn();
		
			id=idd;
			EXEC SQL WHENEVER NOT FOUND GOTO notfound;
			EXEC SQL DELETE FROM info WHERE id=:id;
			notfound:
				;
			EXEC SQL COMMIT WORK;
		EXEC SQL COMMIT WORK RELEASE;
}

/*建立連接*/
void getconn()
{
	/*登錄到oracle*/
	strcpy(username.arr,"scott");//用戶名
	username.len=strlen(username.arr);
	strcpy(password.arr,"tiger"); //密碼
	password.len=strlen(password.arr);
	strcpy(tns.arr,"stu");//監聽名
	tns.len=(unsigned short)strlen((char *)tns.arr);
	
	/*錯誤處理語句*/
	EXEC SQL WHENEVER SQLERROR DO sqlerror();
	/*連接到oracle*/
	EXEC SQL CONNECT :username IDENTIFIED BY :password USING :tns;
			printf("connect to oracle as user:%s\n",username.arr);	
}


/*錯誤處理*/
void sqlerror(){
   printf("\n%.70s \n",sqlca.sqlerrm.sqlerrmc);
}