oracle.c

用oci实现的对oracle的完全操作 有详细的注释

#include "oracle.h"

/*
**	1, if error
**	0, if succes
*/
int oci_init_env(OCI_ENV *env, char *ip, int port, char *user, char *pass, char *sid)
{
	OCIEnv *envhp;
	OCIError *errhp;
	OCISvcCtx *svchp;
	OCIStmt *stmthp;
	if(env==NULL || env->user==NULL || env->pass==NULL || env->sid==NULL || env->ip==NULL)
		return 1;
	snprintf(env->ip, 32, "%s", ip);
	snprintf(env->user, 32, "%s", user);
	snprintf(env->pass, 32, "%s", pass);
	snprintf(env->sid, 32, "%s", sid);
	env->port = port;
	if(OCIInitialize((ub4) OCI_DEFAULT, (dvoid *)0, (dvoid * (*)(dvoid *, size_t))0, (dvoid * (*)(dvoid *, dvoid *, size_t))0, (void (*)(dvoid *, dvoid *)) 0))
		return 1;
	if(OCIEnvInit((OCIEnv **) &envhp, OCI_DEFAULT, (size_t) 0, (dvoid **) 0))
		return 1;

	if(OCIHandleAlloc((dvoid *) envhp, (dvoid **) &errhp, OCI_HTYPE_ERROR,(size_t) 0,(dvoid **) 0))
		return 1;

	if(OCIHandleAlloc((dvoid *) envhp, (dvoid **)&svchp, OCI_HTYPE_SVCCTX,(size_t)NULL, (dvoid **)NULL))
	{
		OCIHandleFree((dvoid *) errhp, OCI_HTYPE_ERROR);
		OCIHandleFree((dvoid *) envhp, OCI_HTYPE_ENV);
		return 1;
	}
	if(OCILogon(envhp, errhp, &svchp, env->user, strlen(env->user), env->pass, strlen(env->pass),env->sid, strlen(env->sid)))
	{
		OCIHandleFree((dvoid *) svchp, OCI_HTYPE_SVCCTX);
		OCIHandleFree((dvoid *) errhp, OCI_HTYPE_ERROR);
		OCIHandleFree((dvoid *) envhp, OCI_HTYPE_ENV);
		return 1;
	} 
	if(OCIHandleAlloc((dvoid *)envhp, (dvoid **)&stmthp, OCI_HTYPE_STMT,(size_t)NULL, (dvoid **)NULL))
	{
		OCILogoff(svchp, errhp);
		OCIHandleFree((dvoid *) svchp, OCI_HTYPE_SVCCTX);
		OCIHandleFree((dvoid *) errhp, OCI_HTYPE_ERROR);
		OCIHandleFree((dvoid *) envhp, OCI_HTYPE_ENV);
		return 1;
	}

	env->envhp = envhp;
	env->errhp = errhp;
	env->svchp = svchp;
	env->stmthp = stmthp;
	env->defhp = NULL;
	env->bindhp = NULL;
	return 0;
}

/*1, if error
0, if success
*/
int oci_insert(OCI_ENV *env, char *query)
{
	if(OCIStmtPrepare(env->stmthp, env->errhp, query, (ub4)strlen(query), OCI_NTV_SYNTAX, OCI_DEFAULT))
		return 1;
	if(OCIStmtExecute(env->svchp, env->stmthp, env->errhp, (ub4) 1, (ub4) 0,(CONST OCISnapshot *) NULL,(OCISnapshot *) NULL, OCI_DEFAULT | OCI_COMMIT_ON_SUCCESS))
		return 1;
	return 0;
}

/* 1, if error
0, if success
*/
int oci_query(OCI_ENV *env, char *query)
{ 
	if(OCIStmtPrepare(env->stmthp, env->errhp, query, (ub4)strlen(query), OCI_NTV_SYNTAX, OCI_DEFAULT))
		return 1; 
	if(OCIStmtExecute(env->svchp, env->stmthp, env->errhp, (ub4) 1, (ub4) 0,(CONST OCISnapshot *) NULL,(OCISnapshot *) NULL, OCI_DEFAULT | OCI_COMMIT_ON_SUCCESS))
		return 1;
	return 0;
}


/*1, if has more data
0, if has no data any more
*/
int oci_fetch_data(OCI_ENV *env, char *query, OCI_DATA *data, BASE_DATA *pdata)
{
		//ROW_DATA * row_data;
	int		num_col;
//	int		len;
	char**  buf_col;
//	int		length[30] = {0};
	int*    len_col;
//	char* p= NULL;
//	char* pTemp = NULL;						//起始地址
//	char** pa = NULL;
	int i/*j,k*/;
	sword	swResult;
	OCIParam* colhp;
	OCIDefine** defhp;
/*
	pp = (char **)malloc(sizeof(char *)*20); 
	for (i = 0; i < 20; i++){ 
		pp[i]=(char *)malloc(20*sizeof(char));
	} 
8*/
//	p = (char *)malloc(sizeof(char) * char);
	if(env == NULL || query == NULL || data == NULL)
		return 0;
	if(data->tag == NOT_BIND)
	{
		num_col = 0;
		if(OCIStmtPrepare(env->stmthp, env->errhp, query, (ub4)strlen(query), OCI_NTV_SYNTAX, OCI_DEFAULT) > 0)
			return 0;
		if(OCIStmtExecute(env->svchp, env->stmthp, env->errhp, (ub4) 0, (ub4) 0,
			(CONST OCISnapshot *) NULL,(OCISnapshot *) NULL, OCI_DESCRIBE_ONLY) > 0)
			return 0;
		if(OCIAttrGet(env->stmthp, OCI_HTYPE_STMT, &num_col, 0, OCI_ATTR_PARAM_COUNT, env->errhp))
			return 0;
		if(num_col == 0)
			return 0;
		len_col = (int *)malloc(sizeof(int) * num_col);
		if(len_col == NULL)
			return 0;
		buf_col = (char **)malloc(sizeof(char *) * num_col);
		if(buf_col == NULL)
			return 0;
		defhp = (OCIDefine **)malloc(sizeof(OCIDefine *) * num_col);
		if(defhp == NULL)
			return 0;
		data->num_column = num_col;
		data->num_row = 0;
		data->buf = buf_col;
		data->buf_len = len_col;

		memset(len_col, 0, sizeof(int) * num_col);
		memset(buf_col, 0, sizeof(char *) * num_col);
		for(i = 1; i <= num_col; i++)
		{
			OCIParamGet(env->stmthp, OCI_HTYPE_STMT, env->errhp, (void **)&colhp, i);
			OCIAttrGet(colhp, OCI_DTYPE_PARAM, (len_col+i-1), 0, OCI_ATTR_DATA_SIZE, env->errhp);
			*(len_col+i-1) = *(len_col+i-1)+1;
			*(buf_col+i-1) = (char *)malloc((int)(*(len_col+i-1)));
			if(*(buf_col+i-1) == NULL)
				return 0;
			memset(*(buf_col+i-1), 0, (int)(*(len_col+i-1)));

			if(OCIDefineByPos(env->stmthp, (defhp+i-1), env->errhp, i, (ub1 *)(*(buf_col+i-1)), *(len_col+i-1), 
				SQLT_STR, NULL, (dvoid *)0, (ub2 *)0, OCI_DEFAULT))
				return 0;
		}
		if (OCIStmtExecute(env->svchp, env->stmthp, env->errhp, (ub4) 0, (ub4) 0,(CONST OCISnapshot *) NULL,(OCISnapshot *) NULL, OCI_DEFAULT) > 0)
			return 0;
		data->tag = ALREADY_BIND;
	}
	for(i = 0; i < data->num_column; i++)
		memset(data->buf[i], 0, data->buf_len[i]);
	
	i = 0;
	while (OCIStmtFetch(env->stmthp, env->errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT) != OCI_NO_DATA)
	{
	//	length[i] = strlen(data->buf[1]);
		strcpy(pdata->plot[i][0],data->buf[0]);
		strcpy(pdata->plot[i][1],data->buf[1]);
		strcpy(pdata->plot[i][2],data->buf[2]);
		data->num_row++;
		i++;
	}
	/*
	len = strlen(temp);
	i = j =0;
	k = (data->num_row - 1);
	while(k + 1)
	{
		i = i + length[k];
		p = &temp[len - i];
		printf("p %s\n",p);
		pa = &p;
		for (j = 0;j < length[k] ;j++)
			temp[len - i + j] = '\0';
		k--;
	}
	*/
	if ((data->num_row) > 0)
		return 1;
	else
	{
		data->tag = NOT_BIND;
		return 0;
	}
}

/*1, if error
0, if success
*/
int oci_free_result(OCI_DATA *data)
{
	int i;
	if(data == NULL)
		return 1;
	if(data->buf_len)
		free(data->buf_len);
	if(data->buf)
	{
		for(i=0; i<data->num_column; i++)
		{
			if(*(data->buf+i))
				free(*(data->buf+i));
		}
		free(data->buf);
	}
	return 0;
}

int oci_end(OCI_ENV *env)
{
	OCILogoff(env->svchp, env->errhp);
	OCIHandleFree((dvoid *) env->svchp, OCI_HTYPE_SVCCTX);
	OCIHandleFree((dvoid *) env->errhp, OCI_HTYPE_ERROR);
	OCIHandleFree((dvoid *) env->envhp, OCI_HTYPE_ENV);
	return 0;
}