您的位置: 首页  >  IT文章  >  LUA C库 Luasql OCI8增多带参数存储过程调用支持

LUA C库 Luasql OCI8增多带参数存储过程调用支持

分类: IT文章 • 2023-12-06 10:15:37
LUA C库 Luasql OCI8增加带参数存储过程调用支持
     Luasql原始的oci8中对于存储过程只能支持无参数的存储过程的调用，现在我在里边加入一个stmt结构来支持带参数尤其是对cursor输出,的支持,需要做的就是实现一个statement的userdata来完成需要的工作bind_number, bind_string, bind_cursor：
用到的主要数据结构：
typedef union {
	int     i;
	char   *s;
	double  d;
	OCIStmt *c; //add by alien
} column_value; 

/*****************
author:alien
date:2011-11-27
an SQL statement
******************/
typedef struct {
	ub2				type;    /* database type */
	OCIBind			*bind;  /* define handle */
	int				direction;
	column_value	val;
} bind_data;

typedef struct{
	short       closed;
	int			conn;
	int			numpars;
	bind_data	*parvals;
	char		*text;	
	OCIStmt		*stmthp;
	OCIError	*errhp; /* !!! */
} stmt_data;


在connection metatable中增加prepare方法来返回stmt环境：
/************************************************************
author:alien
date:2011-11-27
create an SQL statement
*************************************************************/
static int create_statement (lua_State *L, int o, conn_data *conn, OCIStmt *stmthp, const char *text, int numparam){
	env_data *env;
	stmt_data *stmt = (stmt_data *)lua_newuserdata(L, sizeof(stmt_data));
	luasql_setmeta (L, LUASQL_STATEMENT_OCI8);
	/* fill in structure */
	stmt->closed = 0;
	stmt->numpars = numparam;
	stmt->stmthp = stmthp;
	stmt->errhp = NULL;
	stmt->parvals = NULL;
	stmt->text = strdup (text);
	lua_pushvalue (L, o);
	stmt->conn = luaL_ref (L, LUA_REGISTRYINDEX);
	/* error handler */
	lua_rawgeti (L, LUA_REGISTRYINDEX, conn->env);
	env = lua_touserdata (L, -1);
	lua_pop (L, 1);
	ASSERT (L, OCIHandleAlloc((dvoid *) env->envhp,
		(dvoid **) &(stmt->errhp), (ub4) OCI_HTYPE_ERROR, (size_t) 0,
		(dvoid **) 0), conn->errhp);
	stmt->parvals = (bind_data *)malloc(sizeof(bind_data)*stmt->numpars);
	/* C array indices ranges from 0 to numcols-1 */
	return 1;
}
/************************************************************
author:alien
date:2011-11-27
prapare an SQL statement
*************************************************************/
static int conn_prepare(lua_State* L){
	env_data *env;
	conn_data *conn = getconnection (L);
	const char *statement = luaL_checkstring (L, 2);
	const int numparam = getparameternum(statement);
	OCIStmt *stmthp = NULL;

	/* get environment */
	lua_rawgeti (L, LUA_REGISTRYINDEX, conn->env);
	if (!lua_isuserdata (L, -1))
		luaL_error(L,LUASQL_PREFIX"invalid environment in connection!");
	env = (env_data *)lua_touserdata (L, -1);
	/* statement handle */
	ASSERT (L, OCIHandleAlloc ((dvoid *)env->envhp, (dvoid **)&stmthp,
		OCI_HTYPE_STMT, (size_t)0, (dvoid **)0), conn->errhp);
	ASSERT (L, OCIStmtPrepare (stmthp, conn->errhp, (text *)statement,
		(ub4) strlen(statement), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT),
		conn->errhp);
	return create_statement (L, 1, conn, stmthp, statement, numparam);
}


主要实现：
/************************************************************
author:alien
date:2011-11-27
dispose an SQL statement
*************************************************************/
static int stmt_close(lua_State* L){
	int i, ret;
	conn_data *conn;
	stmt_data *stmt = (cur_data *)luaL_checkudata (L, 1, LUASQL_STATEMENT_OCI8);
	luaL_argcheck (L, stmt != NULL, 1, LUASQL_PREFIX"statement expected");
	if (stmt->closed) {
		lua_pushboolean (L, 0);
		return 1;
	}

	/* Deallocate buffers. */
	for (i = 0; i <= stmt->numpars; i++) {
		if( stmt->parvals[i].type == STRING )
			free(stmt->parvals[i].val.s);
		/*else if( stmt->parvals[i].type == CURSOR ){
			lua_rawgeti(L, LUA_REGISTRYINDEX, stmt->parvals[i].val.c);
			cur_close(L);
		}*/
	}
	free(stmt->parvals);
	free(stmt->text);

	/* Nullify structure fields. */
	stmt->closed = 1;
	if (stmt->stmthp)
		OCIHandleFree ((dvoid *)stmt->stmthp, OCI_HTYPE_STMT);
	if (stmt->errhp)
		OCIHandleFree ((dvoid *)stmt->errhp, OCI_HTYPE_ERROR);
	/* Decrement cursor counter on connection object */
	lua_rawgeti (L, LUA_REGISTRYINDEX, stmt->conn);
	conn = lua_touserdata (L, -1);
	conn->cur_counter--;
	luaL_unref (L, LUA_REGISTRYINDEX, stmt->conn);

	lua_pushboolean (L, 1);
	return 1;

}

/************************************************************
author:alien
date:2011-11-27
bind integer to an SQL statement
*************************************************************/
static int stmt_bind_number(lua_State* L){
	stmt_data *stmt = getstatement (L);
	const int pos = luaL_checknumber(L, 2) - 1;
	const int num = luaL_checknumber(L, 3);
	const int direction = luaL_checknumber(L, 4);

	stmt->parvals[pos].direction = direction;
	stmt->parvals[pos].type = NUMBER;
	stmt->parvals[pos].val.i = num;

	/*ASSERT (L, OCIBindByPos(stmt->stmthp, &stmt->parvals[pos].bind, stmt->errhp, pos, &(stmt->parvals[pos].val.i), sizeof(stmt->parvals[pos].val.i),
		SQLT_NUM, NULL, NULL, NULL, 0, NULL, (ub4)OCI_DEFAULT), stmt->errhp);*/
	/*printf("pos: %d statement: %s bind_number: %d\n", pos, stmt->text, stmt->parvals[pos].val.i);
	error_proc(stmt->errhp, OCIBindByPos(stmt->stmthp, &(stmt->parvals[pos].bind), stmt->errhp, (ub4)pos, &(num), sizeof(num),
		SQLT_NUM, NULL, NULL, NULL, 0, NULL, (ub4)OCI_DEFAULT));*/

	lua_pushboolean (L, 1);
	return 1;
}

/************************************************************
author:alien
date:2011-11-27
bind string to an SQL statement
*************************************************************/
static int stmt_bind_string(lua_State* L){
	stmt_data *stmt = getstatement (L);
	const int pos = luaL_checknumber(L, 2) - 1;
	const char* text = luaL_checkstring(L, 3);
	const int direction = luaL_checknumber(L, 4);

	stmt->parvals[pos].direction = direction;
	stmt->parvals[pos].type = STRING;
	stmt->parvals[pos].val.s = (char*)calloc(strlen(text)+1, sizeof(char));
	strcpy(stmt->parvals[pos].val.s, text);

	/*ASSERT (L, OCIBindByPos(stmt->stmthp, &stmt->parvals[pos].bind, stmt->errhp, pos, &(stmt->parvals[pos].val.s), strlen(stmt->parvals[pos].val.s) + 1,
		SQLT_NUM, NULL, NULL, NULL, 0, NULL, (ub4)OCI_DEFAULT), stmt->errhp);*/
	/*printf("bind_string>> stmthp: %d\n", stmt->stmthp);
	error_proc(stmt->errhp, OCIBindByPos(stmt->stmthp, &(stmt->parvals[pos-1].bind), stmt->errhp, (ub4)pos + 1, &(stmt->parvals[pos].val.s), strlen(stmt->parvals[pos].val.s) + 1,
		SQLT_STR, NULL, NULL, NULL, 0, NULL, (ub4)OCI_DEFAULT));*/

	lua_pushboolean (L, 1);
	return 1;
}

/************************************************************
author:alien
date:2011-11-27
bind an cursor to an SQL statement
*************************************************************/
static int stmt_bind_cursor(lua_State* L){	
	env_data *env;
	conn_data *conn;
	stmt_data *stmt = getstatement (L);
	const int pos = luaL_checknumber(L, 2) - 1;
	const int direction = luaL_checknumber(L, 3);

	stmt->parvals[pos].type = CURSOR;
	stmt->parvals[pos].direction = direction;
	stmt->parvals[pos].val.c = NULL;
	
	lua_rawgeti (L, LUA_REGISTRYINDEX, stmt->conn);
	conn = lua_touserdata (L, -1);
	lua_rawgeti (L, LUA_REGISTRYINDEX, conn->env);
	env = lua_touserdata (L, -1);
	lua_pop(L, 2);
	ASSERT(L, OCIHandleAlloc((dvoid*)env->envhp, (dvoid**)&(stmt->parvals[pos].val.c), OCI_HTYPE_STMT, 0, (dvoid**)0), stmt->errhp);
	
	/*ASSERT(L, OCIBindByPos(stmt->stmthp, &stmt->parvals[pos].bind, stmt->errhp, pos, &(stmt->parvals[pos].val.i), sizeof(stmt->parvals[pos].val.i),
		SQLT_NUM, NULL, NULL, NULL, 0, NULL, (ub4)OCI_DEFAULT), stmt->errhp);*/
	/*printf("bind_cursor>> stmthp: %d cursor: %d\n", stmt->stmthp, stmt->parvals[pos].val.c);
	error_proc(stmt->errhp, OCIBindByPos(stmt->stmthp, &stmt->parvals[pos].bind, stmt->errhp, pos + 1, &(stmt->parvals[pos].val.c), sizeof(stmt->parvals[pos].val.c),
		SQLT_RSET, NULL, NULL, NULL, 0, NULL, (ub4)OCI_DEFAULT));*/
	/*error_proc(stmt->errhp, OCIBindByPos(stmt->stmthp, &(stmt->parvals[i].bind), stmt->errhp, i + 1, &(stmt->parvals[i].val.c), sizeof(stmt->parvals[i].val.c),
		SQLT_RSET, NULL, NULL, NULL, 0, NULL, (ub4)OCI_DEFAULT));*/
	lua_pushboolean (L, 1);
	return 1;
}

/************************************************************
author:alien
date:2011-11-27
handle an SQL statement
*************************************************************/
static int data_handle(lua_State *L, stmt_data* stmt){
	int top = lua_gettop(L), i, res = 0;
	env_data *env = NULL;
	conn_data *conn = NULL;
	cur_data *cur = NULL;
	OCIStmt* curstmt = NULL;

	//debug
	//printf("handle>> top: %d\n", top);
	for (i =  0; i < stmt->numpars; i++) {
		//printf("handle>> %d> io: %d type: %d res: %d\n", i, stmt->parvals[i].direction, stmt->parvals[i].type, res);
		if ( OUT == stmt->parvals[i].direction ) {
			if ( NUMBER == stmt->parvals[i].type ){
				//printf("handle>> number: %u\n", stmt->parvals[i].val.i);
				lua_pushnumber( L, stmt->parvals[i].val.i );
				res++;
			}
			if ( STRING == stmt->parvals[i].type ){
				//printf("handle>> string: %s\n", stmt->parvals[i].val.s);
				lua_pushlstring( L, stmt->parvals[i].val.s, strlen(stmt->parvals[i].val.s) );
				res++;
			}
			if ( CURSOR == stmt->parvals[i].type ){
				curstmt = stmt->parvals[i].val.c;
				cur = (cur_data *)lua_newuserdata(L, sizeof(cur_data));
				luasql_setmeta (L, LUASQL_CURSOR_OCI8);

				//printf("handle>> cursor: %d\n", stmt->parvals[i].val.c);
				lua_rawgeti (L, LUA_REGISTRYINDEX, stmt->conn);
				conn = lua_touserdata (L, -1);
				cur->conn = luaL_ref(L, LUA_REGISTRYINDEX);
				lua_rawgeti (L, LUA_REGISTRYINDEX, conn->env);
				env = lua_touserdata (L, -1);
				lua_pop(L, 1);

				conn->cur_counter++;
				/* fill in structure */
				cur->closed = 0;
				cur->numcols = 0;
				cur->colnames = LUA_NOREF;
				cur->coltypes = LUA_NOREF;
				cur->curr_tuple = 0;
				cur->stmthp = curstmt;
				cur->errhp = NULL;
				cur->cols = NULL;
				cur->text = strdup (stmt->text);

				//debug
				/*top = lua_gettop(L);
				printf("handle>> top: %d\n", top);*/

				ASSERT (L, OCIHandleAlloc((dvoid *) env->envhp,
					(dvoid **) &(cur->errhp), (ub4) OCI_HTYPE_ERROR, (size_t) 0,
					(dvoid **) 0), conn->errhp);
				/* get number of columns */
				ASSERT (L, OCIAttrGet ((dvoid *)curstmt, (ub4)OCI_HTYPE_STMT,
					(dvoid *)&cur->numcols, (ub4 *)0, (ub4)OCI_ATTR_PARAM_COUNT,
					cur->errhp), cur->errhp);
				//debug
				//printf("handle>> numcols: %d\n", cur->numcols );

				cur->cols = (column_data *)malloc (sizeof(column_data) * cur->numcols);
				/* define output variables */
				/* Oracle and Lua column indices ranges from 1 to numcols */
				/* C array indices ranges from 0 to numcols-1 */
				for (i = 1; i <= cur->numcols; i++) {
					int ret = alloc_column_buffer (L, cur, i);
					if (ret)
						return ret;
				}
				res++;
			}
		}	
	}
	return res;	
}

/************************************************************
author:alien
date:2011-11-27
execute an SQL statement
*************************************************************/
static int stmt_execute(lua_State* L){
	int i = 0;
	env_data *env;
	conn_data *conn;
	stmt_data *stmt = getstatement (L);
	sword status;
	ub4 prefetch = 0;
	ub4 iters;
	ub4 mode;
	ub2 type;

	/* get environment */
	lua_rawgeti(L, LUA_REGISTRYINDEX, stmt->conn);
	conn = (conn_data*)lua_touserdata(L, -1);
	lua_rawgeti (L, LUA_REGISTRYINDEX, conn->env);
	if (!lua_isuserdata (L, -1))
		luaL_error(L,LUASQL_PREFIX"invalid environment in connection!");
	env = (env_data *)lua_touserdata (L, -1);
	lua_pop(L, 2);

	//debug
	//printf("execute>> text: %s\n", stmt->text);

	/* statement handle */
	ASSERT (L, OCIAttrSet ((dvoid *)stmt->stmthp, (ub4)OCI_HTYPE_STMT,
		(dvoid *)&prefetch, (ub4)0, (ub4)OCI_ATTR_PREFETCH_ROWS,
		conn->errhp), conn->errhp);
	/* statement type */
	ASSERT (L, OCIAttrGet ((dvoid *)stmt->stmthp, (ub4) OCI_HTYPE_STMT,
		(dvoid *)&type, (ub4 *)0, (ub4)OCI_ATTR_STMT_TYPE, conn->errhp),
		conn->errhp);

	
	if (type == OCI_STMT_SELECT)
		iters = 0;
	else
		iters = 1;
	if (conn->auto_commit)
		mode = OCI_COMMIT_ON_SUCCESS;
	else
		mode = OCI_DEFAULT;

	// Bind data to SQLStatement
	for ( i = 0; i < stmt->numpars; i++)
	{
		//debug
		//printf("execute>> %d %s %d\n", i, stmt->parvals[0].val.s, stmt->numpars);
		switch(stmt->parvals[i].type){
			case NUMBER:
				ASSERT (L, OCIBindByPos(stmt->stmthp, &(stmt->parvals[i].bind), stmt->errhp, i + 1, &(stmt->parvals[i].val.i), sizeof(stmt->parvals[i].val.i),
					SQLT_INT, NULL, NULL, NULL, 0, NULL, (ub4)OCI_DEFAULT), stmt->errhp);
				break;
			case STRING:
				ASSERT (L, OCIBindByPos(stmt->stmthp, &(stmt->parvals[i].bind), stmt->errhp, i + 1, stmt->parvals[i].val.s, strlen(stmt->parvals[i].val.s) + 1,
					SQLT_STR, NULL, NULL, NULL, 0, NULL, (ub4)OCI_DEFAULT), stmt->errhp);
				break;
			case CURSOR:
				ASSERT (L, OCIBindByPos(stmt->stmthp, &(stmt->parvals[i].bind), stmt->errhp, i + 1, &(stmt->parvals[i].val.c), sizeof(stmt->parvals[i].val.c),
					SQLT_RSET, NULL, NULL, NULL, 0, NULL, (ub4)OCI_DEFAULT), stmt->errhp);
				break;
			default:
				break;
		}	
	}
	status = OCIStmtExecute (conn->svchp, stmt->stmthp, conn->errhp, iters,
		(ub4)0, (CONST OCISnapshot *)NULL, (OCISnapshot *)NULL, mode);
	//debug
	//printf("execute>> type: %d status: %d\n", type, status);
	
	if (status && (status != OCI_NO_DATA)) {
		//debug
		/*text errbuf[512]; 
		sb4 errcode; 
		(void)OCIErrorGet((dvoid *)conn->errhp,(ub4)1,NULL,&errcode, 
			errbuf,(ub4)sizeof(errbuf),OCI_HTYPE_ERROR); 
		printf("错误号：%d\n错误信息：%s\n",errcode,errbuf); */
		OCIHandleFree ((dvoid *)stmt->stmthp, OCI_HTYPE_STMT);
		return checkerr (L, status, conn->errhp);
	}
	//get data
	if ( OCI_STMT_SELECT == type) {
		/* create cursor */
		return create_cursor (L, 1, conn, stmt->stmthp, stmt->text);
	}
	else if ( OCI_STMT_BEGIN == type ){
		/* handle the bind data */
		return data_handle(L, stmt);
	}
	else {
		/* return number of rows */
		int rows_affected;
		ASSERT (L, OCIAttrGet ((dvoid *)stmt->stmthp, (ub4)OCI_HTYPE_STMT,
			(dvoid *)&rows_affected, (ub4 *)0,
			(ub4)OCI_ATTR_ROW_COUNT, conn->errhp), conn->errhp);
		OCIHandleFree ((dvoid *)stmt->stmthp, OCI_HTYPE_STMT);
		lua_pushnumber (L, rows_affected);
		return 1;
	}
}


LUA接口导出:
#define LUASQL_STATEMENT_OCI8 "Oracle statement"

struct luaL_reg connection_methods[] = {
		{"close", conn_close},
		{"prepare", conn_prepare}, //add
		{"execute", conn_execute},
		{"commit", conn_commit},
		{"rollback", conn_rollback},
		{"setautocommit", conn_setautocommit},
		{NULL, NULL},
	};

struct luaL_reg statement_methods[] = {
		{"close", stmt_close},
		{"bind_number", stmt_bind_number},
		{"bind_string", stmt_bind_string},
		{"bind_cursor", stmt_bind_cursor},
		{"execute", stmt_execute},
		{NULL, NULL}
	};	//add by alien

luasql_createmeta (L, LUASQL_STATEMENT_OCI8, statement_methods);

相关推荐