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:aliendate:2011-11-27an SQL statement******************/typedef struct {ub2type; /* database type */OCIBind*bind; /* define handle */intdirection;column_valueval;} bind_data;typedef struct{short closed;intconn;intnumpars;bind_data*parvals;char*text;OCIStmt*stmthp;OCIError*errhp; /* !!! */} stmt_data;/************************************************************author:aliendate:2011-11-27create 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:aliendate:2011-11-27prapare 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:aliendate:2011-11-27dispose 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:aliendate:2011-11-27bind 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:aliendate:2011-11-27bind 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:aliendate:2011-11-27bind 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:aliendate:2011-11-27handle 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:aliendate:2011-11-27execute 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;elseiters = 1;if (conn->auto_commit)mode = OCI_COMMIT_ON_SUCCESS;elsemode = OCI_DEFAULT;// Bind data to SQLStatementfor ( 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 dataif ( 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;}}#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 alienluasql_createmeta (L, LUASQL_STATEMENT_OCI8, statement_methods);