`
alienchang
  • 浏览: 31555 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
社区版块
存档分类
最新评论

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

    博客分类:
  • Lua
阅读更多
     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);
分享到:
评论

相关推荐

    易语言LUA支持库1.0#0版(第三方)

    LUAC函数操作(命令分类)。这是一组全局函数,当您在您的应用程序中实现一个能被LUA调用的函数时候,需要用到这些函数。主要是数据交换相关。包含 LUA取参数数目、LUA取参数类型、LUA取参数值、LUA加返回值、LUA...

    lua和c语言之间相互调用实例

    而C语言则是一种广泛使用的系统编程语言,具有高效、直接的内存访问和丰富的库支持。将两者结合,可以利用Lua的易用性和C的高性能。 **1. Lua调用C** Lua提供了一种称为"Lua C API"的接口,允许C代码直接与Lua...

    linux环境中c++调用lua动态链接库以及编译方法

    本文将深入探讨如何在C++程序中调用Lua动态链接库,并讲解其编译过程和环境配置。 首先,我们需要理解Lua的动态链接库(liblua.so)。Lua是一个轻量级的脚本语言,它的动态链接库允许C/C++程序直接嵌入和调用Lua...

    LUA脚本|LUA脚本支持库

    LUA脚本支持库则是为了扩展LUA的功能,提供更多的API和工具,使得开发者能够更高效、更方便地编写LUA代码。本文将深入探讨LUA脚本支持库的相关知识点。 1. LUA基础 LUA的核心特点是简洁和高效,它拥有简单的语法...

    Lua支持库.rar

    这个"Lua支持库.rar"压缩包很可能是提供了一系列的 Lua 库源码,帮助开发者扩展 Lua 的功能,使其在易语言环境中更好地运行和交互。 首先,我们要了解 Lua 的基本特性。Lua 是一种动态类型的语言,语法简洁,易于...

    Lua脚本支持库

    通过支持库,可以进一步强化这一特性,如luabind库可以方便地将C++类绑定到Lua,使得Lua可以直接调用C++的方法。 2. **数据序列化与持久化**:lua-serialize库提供了序列化和反序列化功能,可将Lua的数据结构转换为...

    lua支持库完美版终极版

    通过这个支持库,易语言用户可以直接在易语言环境中调用Lua的函数,实现复杂的游戏逻辑和动态内容。这大大提升了易语言在游戏开发中的应用潜力,使得非专业程序员也能参与到游戏的制作中来。 在游戏开发中,Lua支持...

    LUA扩展支持库

    总的来说,"LUA扩展支持库" 是为了提升Lua的功能性和实用性,让开发者能够更好地结合C/C++的性能优势和Lua的简洁语法,创建出功能强大且高效的软件解决方案。通过深入理解和熟练运用这些扩展技术,开发者可以将Lua的...

    支持lua的md5加密库

    编译后生成的`core.so`和`md5.lua`是动态链接库文件,`core.so`包含了实际的C/C++实现,而`md5.lua`则是Lua接口,使得Lua脚本可以直接调用MD5加密功能。另外,还提到了一个`des56.so`,这可能是DES(Data Encryption...

    lua 调用 c++ dll 示例

    在IT领域,有时候我们需要在不同的编程语言之间进行交互,例如使用脚本语言 Lua 调用 C++ 编写的动态链接库(DLL)。本示例是关于如何在 Lua 中调用由 Visual Studio 2010 编译的 C++ DLL 的教程。下面将详细介绍这...

    C语言调用Lua代码

    在Linux底下,从C语言调用Lua代码的一个简单例子。

    易语言Lua支持库

    易语言Lua支持库源码,Lua支持库,GetNewInf,lua_ProcessNotifyLib,建立全局信息,内部_附加功能主函数,函数改C调用约定,加载内存库,取内存DLL函数地址,调用内存函数,到子程序指针,取子程序地址,取指针地址,取文本指针,...

    易语言源码易语言Lua支持库源码.rar

    2. **Lua C API**:Lua是用C语言实现的,因此易语言需要通过Lua的C API来与Lua交互。这包括luaL_newstate、luaL_openlibs、lua_pcall等函数,以及注册函数、读写全局变量、调用函数等操作。 3. **线程安全**:如果...

    lua调用c函数库的demo

    lua调用c函数库的demo 其中实现了对一个 数组的管理。 注意lua参数与c函数的对应, 如:b=lua_dllb.getarray(a,1) static int getarray(lua_State* L) { NumArray * a=(NumArray*)lua_touserdata(L,1); //对应lua...

    lua实现调用webservice

    ### Lua 实现调用 WebService 的方法 #### 一、通过 gSOAP 实现 C/C++ 调用 Web Service gSOAP 是一个强大的工具包,它简化了使用 C/C++ 语言开发 ...这种方法不仅简化了调用过程,还提高了代码的灵活性和可移植性。

    Lua的最基本使用 C++与lua的互相调用

    Lua的参数和返回值都存储在栈上,函数的返回值数量通过`lua_gettop`获取。 三、示例代码 LuaExample中的示例可能包含一个简单的C++程序,该程序加载一个Lua脚本,定义一个C++函数,然后在Lua中调用这个函数。同时...

    Lua编程事例:调用Lua有参函数

    在本文中,我们将深入探讨如何在C++环境中,特别是使用Visual C++ 6.0,调用Lua脚本中的有参数函数。Lua是一种轻量级的脚本语言,常用于游戏开发、配置管理以及嵌入式系统中。通过C++与Lua的交互,我们可以将业务...

    Lua静态库,可以让Lua与C++进行互相调用

    Lua静态库,引用Lua静态库,可以让Lua与C++进行交互,这个游戏开发中是必要的基础。

Global site tag (gtag.js) - Google Analytics