#LuaJIT(OpenResty)通过ffi调用ODBC连接Access数据库
**注：后期对该文章的一些想法进行了一些修正，OpenResty想通过LuaSQL库连接ODBC的请参考这篇文章[《通过编译luaSQL调用ODBC连接Access以及给OpenResty(LuaJIT)编写简单的C扩展示例》](../LuaJIT_C_Extend_LuaSQL_complier_connect_Access/detail.html "《通过编译luaSQL调用ODBC连接Access以及给OpenResty(LuaJIT)编写简单的C扩展示例》")**
[上回书说道](../LuaJIT_ffi_iconv_convert_code/detail.html "上回书说道")，要把一个`asp`小项目弄到`OpenResty`平台上，转码问题解决了，之后当然就遇上最核心的问题了：**如何用 LuaJIT 连接 Access 数据库？**
`《C专家编程》`里面有句话，对于程絮媛们来说，最兴奋的是两件事：一件事是用软件来操纵硬件来获得成就感，第二件事是对一些古老的过时的东西进行利用，组合出新的东西来……连接`Access`当然要用上古神器`ODBC`库了……
当然，这种库我还是没找到有现成的，好用的，还是得我自己动手……然后我有点后悔入了`ODBC`的坑……这个坑真是堪称天坑啊……`ODBC`的函数系统其实特别繁杂，干啥的都有……幸好，我找到了一个较好的指导文档[《ODBC API开发教程》](http://wenku.baidu.com/view/dd28f04af111f18582d05a0a.html "《ODBC API开发教程》")，但是由于精力有限，再加上调试很繁琐，而且不得其法，再加上是业余时间抽空弄……一年多过去了……我只是实现了其中我需要的部分……话说为啥一直以来不愿意分享出来，就是因为一来代码很乱，都是不同时候写的，二来所引出的`API`不全，并不能将`ODBC`的所有部分都引出来……但是毕竟花费了我不少精力，所以，我还是将其公布出来吧……
另外，这回还要说的一个关于`ODBC`的坑就是关于`32位` `ODBC`的`dll`与`64位ODBC`的`dll`的坑……在这个坑上面我也是花费了不少精力的……
- 首先对于`win7`来说，`ODBC`的库有`32位`和`64位`两种，`64位`的`ODBC驱动`很少（更确切的说是几乎没有），`32位`的默认驱动不少跟`XP`中看到的差不多……如果用的是`32位`的`LuaJIT`默认连接的就是`32位`的`dll`，否则，连接的就是`64位`的`dll`……当然，为了保证兼容性，为了这些默认的`ODBC`还是选择`32位`的好……
- 程絮媛起名何苦要为难程絮媛啊……- 然后就是关于`ODBC`的`dll`所在的位置，一个在系统盘`system32`中，一个在系统盘`sysWOW64`中……而我，恰好被这两个文件名迷惑的好几晚上都没睡好觉……其实`system32`里面装的是`64位`的`ODBC库`，而`sysWOW64`文件夹里面装的是`32位`的`ODBC库`……**程絮媛起名何苦要为难程絮媛啊……**
于是最早引出的ODBC接口是这样的,`odbc_h.lua`文件：
```Lua
--odbc32 by yimengqiannian
local ffi = require'ffi'
local SQL={}
SQL.NULL=0
SQL.FALSE=0
SQL.TRUE=1
SQL.ODBCVER=0x0351
SQL.SQL_NULL_HDBC=0
SQL.SQL_NULL_HENV=0
SQL.SQL_NULL_HSTMT=0
SQL.SQL_NULL_DATA=-1
SQL.SQL_HANDLE_ENV=1
SQL.SQL_HANDLE_DBC=2
SQL.SQL_HANDLE_STMT=3
SQL.SQL_HANDLE_DESC=4
SQL.SQL_SUCCESS=0
SQL.ODBC_ADD_DSN=1
SQL.SQL_NO_DATA=100
SQL.SQL_OV_ODBC3=3
SQL.SQL_ATTR_ODBC_VERSION=200
SQL.SQL_IS_INTEGER=(-6)
SQL.SQL_NTS=(-3)
SQL.SQL_PARAM_INPUT=1
SQL.SQL_PARAM_INPUT_OUTPUT=2
SQL.SQL_PARAM_OUTPUT=4
SQL.SQL_MAX_CATALOG_NAME_LEN=34
SQL.SQL_MAX_COLUMN_NAME_LEN=30
SQL.SQL_MAX_COLUMNS_IN_GROUP_BY=97
SQL.SQL_MAX_COLUMNS_IN_INDEX=98
SQL.SQL_MAX_COLUMNS_IN_ORDER_BY=99
SQL.SQL_MAX_COLUMNS_IN_SELECT=100
SQL.SQL_MAX_COLUMNS_IN_TABLE=101
SQL.SQL_MAX_CURSOR_NAME_LEN=31
SQL.SQL_MAX_INDEX_SIZE=102
SQL.SQL_MAX_MESSAGE_LENGTH=255
SQL.SQL_MAX_ROW_SIZE=104
SQL.SQL_MAX_SCHEMA_NAME_LEN=32
SQL.SQL_MAX_STATEMENT_LEN=105
SQL.SQL_MAX_TABLE_NAME_LEN=35
SQL.SQL_MAX_TABLES_IN_SELECT=106
SQL.SQL_MAX_USER_NAME_LEN=107
SQL.SQL_MAXIMUM_CATALOG_NAME_LENGTH=SQL_MAX_CATALOG_NAME_LEN
SQL.SQL_MAXIMUM_COLUMN_NAME_LENGTH=SQL_MAX_COLUMN_NAME_LEN
SQL.SQL_MAXIMUM_COLUMNS_IN_GROUP_BY=SQL_MAX_COLUMNS_IN_GROUP_BY
SQL.SQL_MAXIMUM_COLUMNS_IN_INDEX=SQL_MAX_COLUMNS_IN_INDEX
SQL.SQL_MAXIMUM_COLUMNS_IN_ORDER_BY=SQL_MAX_COLUMNS_IN_ORDER_BY
SQL.SQL_MAXIMUM_COLUMNS_IN_SELECT=SQL_MAX_COLUMNS_IN_SELECT
SQL.SQL_MAXIMUM_CURSOR_NAME_LENGTH=SQL_MAX_CURSOR_NAME_LEN
SQL.SQL_MAXIMUM_INDEX_SIZE=SQL_MAX_INDEX_SIZE
SQL.SQL_MAXIMUM_ROW_SIZE=SQL_MAX_ROW_SIZE
SQL.SQL_MAXIMUM_SCHEMA_NAME_LENGTH=SQL_MAX_SCHEMA_NAME_LEN
SQL.SQL_MAXIMUM_STATEMENT_LENGTH=SQL_MAX_STATEMENT_LEN
SQL.SQL_MAXIMUM_TABLES_IN_SELECT=SQL_MAX_TABLES_IN_SELECT
SQL.SQL_MAXIMUM_USER_NAME_LENGTH=SQL_MAX_USER_NAME_LEN
SQL.SQL_BIGINT=(-5)
SQL.SQL_BINARY=(-2)
SQL.SQL_BIT=(-7)
SQL.SQL_CHAR=1
SQL.SQL_DATE=9
SQL.SQL_DOUBLE=8
SQL.SQL_REAL=7
SQL.SQL_INTEGER=4
SQL.SQL_SMALLINT=5
SQL.SQL_NUMERIC=2
SQL.SQL_VARCHAR=12
SQL.SQL_TIMESTAMP=11
SQL.SQL_TINYINT=(-6)
SQL.SQL_DATE=91
SQL.SQL_TIME=92
SQL.SQL_TIMESTAMP=93
SQL.SQL_SIGNED_OFFSET=(-20)
SQL.SQL_UNSIGNED_OFFSET=(-22)
SQL.SQL_C_DEFAULT=99
SQL.SQL_C_BINARY=SQL.SQL_BINARY
SQL.SQL_C_BIT=SQL.SQL_BIT
SQL.SQL_C_LONG=SQL.SQL_INTEGER
SQL.SQL_C_ULONG=(SQL.SQL_C_LONG+SQL.SQL_UNSIGNED_OFFSET)
SQL.SQL_C_BOOKMARK=SQL.SQL_C_ULONG
SQL.SQL_C_CHAR=SQL.SQL_CHAR
SQL.SQL_C_DATE=SQL.SQL_DATE
SQL.SQL_C_DOUBLE=SQL.SQL_DOUBLE
SQL.SQL_C_FLOAT=SQL.SQL_REAL
SQL.SQL_C_SHORT=SQL.SQL_SMALLINT
SQL.SQL_C_SLONG=(SQL.SQL_C_LONG+SQL.SQL_SIGNED_OFFSET)
SQL.SQL_C_SSHORT=(SQL.SQL_C_SHORT+SQL.SQL_SIGNED_OFFSET)
SQL.SQL_C_STINYINT=(SQL.SQL_TINYINT+SQL.SQL_SIGNED_OFFSET)
SQL.SQL_C_TIME=SQL.SQL_TIME
SQL.SQL_C_TIMESTAMP=SQL.SQL_TIMESTAMP
SQL.SQL_C_TINYINT=SQL.SQL_TINYINT
SQL.SQL_C_USHORT=(SQL.SQL_C_SHORT+SQL.SQL_UNSIGNED_OFFSET)
SQL.SQL_C_UTINYINT=(SQL.SQL_TINYINT+SQL.SQL_UNSIGNED_OFFSET)
SQL.SQL2C = {
[SQL.SQL_CHAR] = SQL.SQL_C_CHAR,
[SQL.SQL_VARCHAR] = SQL.SQL_C_CHAR,
[SQL.SQL_BIT] = SQL.SQL_C_BIT,
[SQL.SQL_TINYINT] = SQL.SQL_C_TINYINT,
--[SQL.SQL_BIGINT] = SQL.SQL_C_BIGINT,
[SQL.SQL_SMALLINT] = SQL.SQL_C_SHORT,
[SQL.SQL_INTEGER] = SQL.SQL_C_LONG,
[SQL.SQL_REAL] = SQL.SQL_C_FLOAT,
[SQL.SQL_DOUBLE] = SQL.SQL_C_DOUBLE,
[SQL.SQL_NUMERIC] = SQL.SQL_C_DOUBLE,
[SQL.SQL_BINARY] = SQL.SQL_C_BINARY,
[SQL.SQL_DATE] = SQL.SQL_C_DATE,
[SQL.SQL_TIME] = SQL.SQL_C_DATE,
[SQL.SQL_TIMESTAMP] = SQL.SQL_C_TIMESTAMP
};
SQL.C_TYPE_MAP = {
[SQL.SQL_C_CHAR] = {"unsigned char[?]",true,SQL.SQL_CHAR},
[SQL.SQL_C_SHORT] = {"short[1]",false,SQL.SQL_SMALLINT},
[SQL.SQL_C_SSHORT] = {"short[1]",false,SQL.SQL_SMALLINT},
[SQL.SQL_C_USHORT] = {"unsigned short[1]",false,SQL.SQL_SMALLINT},
[SQL.SQL_C_LONG] = {"long[1]",false,SQL.SQL_INTEGER},
[SQL.SQL_C_SLONG] = {"long[1]",false,SQL.SQL_INTEGER},
[SQL.SQL_C_ULONG] = {"unsigned long[1]",false,SQL.SQL_INTEGER},
[SQL.SQL_C_FLOAT] = {"float[1]",false,SQL.SQL_REAL},
[SQL.SQL_C_DOUBLE] = {"double[1]",false,SQL.SQL_DOUBLE},
[SQL.SQL_C_BIT] = {"unsigned char[1]",false,SQL.SQL_BIT},
[SQL.SQL_C_TINYINT] = {"char[1]",false,SQL.SQL_TINYINT},
[SQL.SQL_C_STINYINT] = {"signed char[1]",false,SQL.SQL_TINYINT},
[SQL.SQL_C_UTINYINT] = {"unsigned char[1]",false,SQL.SQL_TINYINT},
--[SQL.SQL_C_BIGINT] = {"int64_t[1]",false,SQL.SQL_BIGINT},
--[SQL.SQL_C_SBIGINT] = {"int64_t[1]",false,SQL.SQL_BIGINT},
--[SQL.SQL_C_UBIGINT] = {"uint64_t[1]",false,SQL.SQL_BIGINT},
[SQL.SQL_C_BINARY] = {"unsigned char[?]",true,SQL.SQL_BINARY},
[SQL.SQL_C_BOOKMARK] = {"unsigned long[1]",false,SQL.SQL_BINARY},
--[SQL.SQL_C_VARBOOKMARK] = {"unsigned char[?]",true},
[SQL.SQL_C_DATE] = {"DATE_STRUCT",false,SQL.SQL_DATE},
[SQL.SQL_C_TIME] = {"TIME_STRUCT",false,SQL.SQL_TIME},
[SQL.SQL_C_TIMESTAMP] = {"TIMESTAMP_STRUCT",false,SQL.SQL_TIMESTAMP}
};
ffi.cdef[[
typedef short SQLSMALLINT;
typedef unsigned short SQLUSMALLINT;
typedef unsigned long SQLUINTEGER;
typedef struct tagDATE_STRUCT {
SQLSMALLINT year;
SQLUSMALLINT month;
SQLUSMALLINT day;
} DATE_STRUCT;
typedef struct tagTIME_STRUCT {
SQLUSMALLINT hour;
SQLUSMALLINT minute;
SQLUSMALLINT second;
} TIME_STRUCT;
typedef struct tagTIMESTAMP_STRUCT {
SQLSMALLINT year;
SQLUSMALLINT month;
SQLUSMALLINT day;
SQLUSMALLINT hour;
SQLUSMALLINT minute;
SQLUSMALLINT second;
SQLUINTEGER fraction;
} TIMESTAMP_STRUCT;
short SQLAllocHandle(int,int,int*);
short SQLSetEnvAttr(int,int,int,int);
short SQLConfigDataSource(int *, unsigned short,const char *,const char*);
short SQLConnect(int,const char*,int,int*,int,int*,int);
short SQLGetDiagRec(int,int,int,unsigned char*,long*,unsigned char*,int,short*);
short SQLExecDirect(int,const char*,int);
short SQLPrepare(int,const char*,int);
short SQLBindParameter(int,unsigned short,unsigned short,unsigned short,unsigned short,unsigned long,unsigned short,void *,unsigned long,unsigned long *);
short SQLExecute(int);
short SQLNumResultCols (int,unsigned short *);
short SQLDescribeCol(int,unsigned short,const char *,short,short *,short*,unsigned long*,short*,short*);
short SQLBindCol(int,unsigned short,unsigned short,void *,unsigned long,unsigned long*);
short SQLFetch(int);
short SQLDisconnect(int);
short SQLFreeHandle(int,int);
]];
return SQL;
```
`odbc.lua`文件：
```Lua
--odbc binding
local ffi = require'ffi';
local SQL = require'lua.odbc_h';
local odbc = ffi.load"odbc32.dll";
local odbccp = ffi.load"odbccp32";
--create an Access DSN by file path
local function creatMdbDsn(dsnName,filePath)
local retcode = odbccp.SQLConfigDataSource(nil,SQL.ODBC_ADD_DSN,"Microsoft Access Driver (*.mdb)\0","DSN="..dsnName.."\0DBQ="..filePath.."\0\0");
return retcode ~= SQL.FALSE
end
local function getErrorMsg(typeId,hd)
local SqlState=ffi.new("char[6]",{});
local Msg=ffi.new("char[?]",SQL.SQL_MAX_MESSAGE_LENGTH,{});
local NativeError = ffi.new("long[1]",{});
local MsgLen = ffi.new("short[1]",{});
local i=1;
local msgs={};
repeat
local retcode = odbc.SQLGetDiagRec(typeId,hd, i, SqlState, NativeError,Msg, SQL.SQL_MAX_MESSAGE_LENGTH, MsgLen)
if(retcode == SQL.SQL_NO_DATA) then break end
table.insert(msgs, "["..NativeError[0].."]:["..ffi.string(SqlState).."]"..ffi.string(Msg,MsgLen[0]));
i=i+1;
until (retcode == SQL.SQL_NO_DATA)
--'['..typeId..']'..table.concat(msgs, "\n")
return msgs;
end
local function checkError(status,typeId,hd)
if status == SQL.SQL_NO_DATA then
return status,hd,{'no rows effect!'}
else if status ~= SQL.SQL_SUCCESS then
local msgs=getErrorMsg(typeId,hd);
return status,hd,msgs;
else
return status,hd,{}
end end
end
--Allocate the ODBC Environment and set version
local function openEnv()
--Allocate the ODBC Environment and save handle.
local henv = ffi.new("int[1]", {SQL.SQL_NULL_HENV})
local retcode = odbc.SQLAllocHandle(SQL.SQL_HANDLE_ENV, henv[0], henv);
if(retcode ~= SQL.SQL_SUCCESS)then
return checkError(retcode,SQL.SQL_HANDLE_ENV, henv[0]);
end
--Notify ODBC that this is an ODBC 3.0 application.
retcode = odbc.SQLSetEnvAttr(henv[0],SQL.SQL_ATTR_ODBC_VERSION,
SQL.SQL_OV_ODBC3, SQL.SQL_IS_INTEGER);
return checkError(retcode,SQL.SQL_HANDLE_ENV, henv[0]);
end
--Allocate an ODBC connection and connect.
local function openConn(henv,dsnName)
local hdbc1 = ffi.new("int[1]", {SQL.SQL_NULL_HDBC})
local retcode = odbc.SQLAllocHandle(SQL.SQL_HANDLE_DBC, henv, hdbc1);
if(retcode ~= SQL.SQL_SUCCESS)then
return checkError(retcode,SQL.SQL_HANDLE_DBC, hdbc1[0]);
end
retcode = odbc.SQLConnect(hdbc1[0],dsnName,SQL.SQL_NTS,nil,SQL.SQL_NTS,nil,SQL.SQL_NTS);
return checkError(retcode,SQL.SQL_HANDLE_DBC, hdbc1[0]);
end
--Allocate a statement handle.
local function createStatement(hdbc1)
local hstmt1 = ffi.new("long[1]", {SQL.SQL_NULL_HSTMT})
local retcode = odbc.SQLAllocHandle(SQL.SQL_HANDLE_STMT, hdbc1, hstmt1);
return checkError(retcode,SQL.SQL_HANDLE_STMT, hstmt1[0]);
end
--Execute an SQL statement directly on the statement handle.
local function executeSQL(hstmt1,sql)
local retcode = odbc.SQLExecDirect(hstmt1,sql, SQL.SQL_NTS);
return checkError(retcode,SQL.SQL_HANDLE_STMT, hstmt1);
end
--prepare an SQL statement
local function prepareSQL(hstmt1,sql)
retcode = odbc.SQLPrepare(hstmt1,sql, SQL.SQL_NTS);
return checkError(retcode,SQL.SQL_HANDLE_STMT, hstmt1);
end
--Set Prepare SQL statment Number Type
local function setNumberType(hstmt1,index,value)
local intParam=ffi.new("int[1]",value);
local rnum=ffi.new("int[1]",SQL.SQL_NTS);
retcode = odbc.SQLBindParameter(hstmt1,index, SQL.SQL_PARAM_INPUT,
SQL.SQL_C_SLONG, SQL.SQL_NUMERIC, 8, 0,
intParam, ffi.sizeof("int"), rnum);
if retcode < 0 then
return retcode,'set prepare SQL number parame error';
end
return SQL.SQL_SUCCESS,intParam
end
--Set Prepare SQL statment String Type
local function setStringType(hstmt1,index,value)
local length=string.len(value)
local szName=ffi.new("char[?]",length,value);
local rnum=ffi.new("int[1]",SQL.SQL_NTS);
retcode = odbc.SQLBindParameter(hstmt1,index, SQL.SQL_PARAM_INPUT,
SQL.SQL_C_CHAR, SQL.SQL_VARCHAR, length, 0,
szName, length, rnum);
if retcode < 0 then
return retcode,'set prepare SQL string parame error';
end
return SQL.SQL_SUCCESS,szName
end
--Set num value
local function setNumber(intParam,num)
intParam[0]=num;
end
--Set String value
local function setString(szName,str)
ffi.copy(szName,str);
end
--excute prepare SQL statment
local function executeStmt(hstmt1)
local retcode = odbc.SQLExecute(hstmt1);
return checkError(retcode,SQL.SQL_HANDLE_STMT, hstmt1);
end
--get column number from SQL statment
local function getColNum(hstmt1)
local pnum=ffi.new("unsigned short[1]");
retcode = odbc.SQLNumResultCols(hstmt1,pnum);
if retcode < 0 then
return retcode,'get column number from SQL statment error';
end
return SQL.SQL_SUCCESS,pnum[0]
end
--get result description of column
local function getColDescs(hstmt1)
local pColName=ffi.new("char[?]",SQL.SQL_MAX_COLUMN_NAME_LEN);
local pColNameLen=ffi.new("unsigned short[1]",0);
local pColType=ffi.new("unsigned short[1]",0);
local pColTypeLen=ffi.new("unsigned long[1]",0);
local pColDecimalLen=ffi.new("unsigned short[1]",0);
local pAllowNull=ffi.new("short[1]",0);
local status,colNum=getColNum(hstmt1);
if retcode<0 then
return retcode,colNum;
end
local colDescs={}
for i=1,colNum,1 do
status = odbc.SQLDescribeCol(hstmt1, i, pColName, SQL.SQL_MAX_COLUMN_NAME_LEN, pColNameLen, pColType, pColTypeLen, pColDecimalLen, pAllowNull);
if status < 0 then
return retcode,'Error on get the '..i..' column description!',i;
end
table.insert(colDescs,{
index=i,
name=ffi.string(pColName,pColNameLen[0]),
nameLen=pColNameLen[0],
typeId=pColType[0],
size=pColTypeLen[0],
decimal=pColDecimalLen[0],
isNull=pAllowNull[0]
});
end
return SQL.SQL_SUCCESS,colDescs,colNum
end
--bind column type by number
local function bindColNumberType(hstmt1,index)
local pnum=ffi.new("long[1]");
local pcolumnLen=ffi.new("unsigned long[1]",0);
retcode = odbc.SQLBindCol(hstmt1, index, SQL.SQL_C_SLONG,pnum,ffi.sizeof("long") , pcolumnLen);
if retcode < 0 then
return retcode,'Bind Col Number Type Error';
end
return SQL.SQL_SUCCESS,pnum,pcolumnLen[0]
end
--bind column by number Type
local function bindColStringType(hstmt1,index,length)
local pvalue=ffi.new("char[?]",length);
local pcolumnLen=ffi.new("unsigned long[1]",0);
local retcode = odbc.SQLBindCol(hstmt1, index, SQL.SQL_C_CHAR,pvalue,length , pcolumnLen);
if retcode < 0 then
return retcode,'Bind Col Number Type Error';
end
return SQL.SQL_SUCCESS,pvalue,pcolumnLen[0]
end
local function bindAllCols(hstmt1,colDescs)
local i=1;
local result = {};
for key,colDesc in pairs(colDescs) do
local ctype = SQL.SQL2C[colDesc.typeId];
if not ctype then
ctype = SQL.SQL_C_CHAR;
end
local ct = SQL.C_TYPE_MAP[ctype];
local pvalue= nil;
local length = nil
if ct[2] then
pvalue = ffi.new(ct[1],colDesc.size);
length = colDesc.size;
else
pvalue = ffi.new(ct[1]);
length = 0;
end
local pcolumnLen=ffi.new("unsigned long[1]",0);
local retcode = odbc.SQLBindCol(hstmt1, i, ctype, pvalue, length , pcolumnLen);
if retcode < 0 then
return retcode,'Bind Col Type Error On Bind Col:'..i;
end
table.insert(result,{pvalue=pvalue,length=pcolumnLen[0],index=i,ctype=ctype,maxSize=length,ct=ct});
i=i+1;
end
return SQL.SQL_SUCCESS,result,colDescs;
end
--fecth query
local function fecth(hstmt1)
local retcode = odbc.SQLFetch(hstmt1)
return retcode ~= SQL.SQL_NO_DATA
end
--fecth query
local function fecthRow(hstmt1,bindData)
local retcode = odbc.SQLFetch(hstmt1);
if retcode == SQL.SQL_NO_DATA then
return false;
end
local result = {}
for key,valueElem in pairs(bindData) do
if valueElem.ct[2] then
table.insert(result,ffi.string(valueElem.pvalue));
elseif valueElem.ctype == SQL.SQL_C_DATE then
local s = valueElem.pvalue;
table.insert(result,{
year = s.year,
month = s.month,
day = s.day
});
elseif valueElem.ctype == SQL.SQL_C_TIME then
local s = valueElem.pvalue;
table.insert(result,{
hour = s.hour,
minute = s.minute,
second = s.second
});
elseif valueElem.ctype == SQL.SQL_C_TIMESTAMP then
local s = valueElem.pvalue;
table.insert(result,{
year = s.year,
month = s.month,
day = s.day,
hour = s.hour,
minute = s.minute,
second = s.second,
fraction = s.fraction
});
else
table.insert(result,valueElem.pvalue[0]);
end
end
return true,result;
end
--get num value
local function iter_row(hstmt1,bindData)
return function()
local status,result = fecthRow(hstmt1,bindData);
if status then
return result;
end
end
end
--get num value
local function getNumber(pnum)
return pnum[0]
end
--get String value
local function getString(pvalue)
return ffi.string(pvalue);
end
--close statement
local function closeStmt(hstmt1)
local retcode = odbc.SQLFreeHandle(SQL.SQL_HANDLE_STMT, hstmt1);
return checkError(retcode,SQL.SQL_HANDLE_STMT, hstmt1);
end
--close connection
local function closeConnect(hdbc1)
local retcode = odbc.SQLDisconnect(hdbc1);
if retcode < 0 then
return checkError(retcode,SQL.SQL_HANDLE_DBC, hdbc1);
end
retcode = odbc.SQLFreeHandle(SQL.SQL_HANDLE_DBC, hdbc1);
return checkError(retcode,SQL.SQL_HANDLE_DBC, hdbc1);
end
--close Envaronment
local function closeEnv(henv)
local retcode = odbc.SQLFreeHandle(SQL.SQL_HANDLE_ENV, henv);
return checkError(retcode,SQL.SQL_HANDLE_ENV, henv);
end
return {
SQLFINALS = SQL,
ODBC=odbc,
SUCCESS=SQL.SQL_SUCCESS,
openEnv=openEnv,
creatMdbDsn=creatMdbDsn,
openConn=openConn,
createStatement=createStatement,
executeSQL=executeSQL,
prepareSQL=prepareSQL,
setNumberType=setNumberType,
setStringType=setStringType,
setNumber=setNumber,
setString=setString,
executeStmt=executeStmt,
getColNum=getColNum,
getColDescs=getColDescs,
bindColStringType=bindColStringType,
bindColNumberType=bindColNumberType,
bindAllCols = bindAllCols,
fecthRow = fecthRow,
iter_row = iter_row,
fecth=fecth,
getNumber=getNumber,
getString=getString,
closeStmt=closeStmt,
closeConnect=closeConnect,
closeEnv=closeEnv
}
```
别看两个文件加起来才五百来行，然而为了写出它们来，也是操心到蛋碎……
下面我们写个测试文件来测试一下，首先建一个`Access`数据库，`mdb文件`，起名叫`test.mdb`吧……`test.mdb`里面建一张表，叫`test表`吧，`test表`里有三个字段，`id(自动编号)`，`name(文本)`，`value(文本)`……
好了，够用来测试的了：
下面是测试文件`testOdbc.lua`
```Lua
local odbc = require"odbc"
local dsnName = "excuteSQL";
local mdbFile = "test.mdb";
if not odbc.creatMdbDsn(dsnName,mdbFile) then
print("create MDB DSN error!");
return -1;
end
local status,evn,msg=odbc.openEnv();
if status ~= odbc.SUCCESS then
print("odbc环境创建失败");
return -2;
end
local status,conn,msg=odbc.openConn(evn,dsnName)
if status ~= odbc.SUCCESS then
print("odbc连接创建失败");
odbc.closeEnv(evn);
return -3;
end
function excuteSQL(sql)
local status,st,msg=odbc.createStatement(conn);
if(status ~= odbc.SUCCESS) then
print("创建statement失败");
return -4;
end
local status,st,msg = odbc.executeSQL(st,sql);
odbc.closeStmt(st);
if status~=odbc.SUCCESS then
print("运行SQL失败:"..table.concat(msg,"\n"));
return -5;
end;
print("执行SQL成功");
return 0;
end
function excutePrepareSQL(sql,name,value)
local status,st,msg=odbc.createStatement(conn);
if(status ~= odbc.SUCCESS) then
print("创建statement失败");
return -4;
end
odbc.prepareSQL(st,sql);
odbc.setStringType(st,1,name);
odbc.setNumberType(st,2,value);
local status,st,msg = odbc.executeStmt(st,sql);
odbc.closeStmt(st);
if status~=odbc.SUCCESS then
print("执行准备SQL失败:"..table.concat(msg,"\n"));
return -5;
end
print("执行准备SQL成功");
return 0;
end
function excuteQuery(sql)
local status,st,msg=odbc.createStatement(conn);
if(status ~= odbc.SUCCESS) then
print("创建statement失败");
return -4;
end
local status,st,msg = odbc.executeSQL(st,sql);
if status~=odbc.SUCCESS then
print("运行SQL失败:"..table.concat(msg,"\n"));
return -5;
end;
print("执行查询SQL成功");
local status,colDesc,colNum = odbc.getColDescs(st);
local status,bindData,colDesc = odbc.bindAllCols(st,colDesc);
local row = nil;
for row in odbc.iter_row(st,bindData) do
print(table.concat(row,","));
end
odbc.closeStmt(st);
return 0;
end
excuteSQL("insert into [test]([name],[value]) values('testOOO',101)");
excutePrepareSQL("insert into [test]([name],[value]) values(?,?)","tesooo0",23);
excuteQuery("select * from [test]");
odbc.closeConnect(conn);
odbc.closeEnv(evn);
return 0;
```
看一下运行结果：
```
>luajit testOdbc.lua
执行SQL成功
执行准备SQL成功
执行查询SQL成功
1,testOOO,101
2,tesooo0,23
```
运行效果不错~
这样就结束了？呃……当然没有……后来不是在写`iconv.lua`的时候发现了很好用的`gc机制`么……后来慢慢的学`lua的封装`，于是我觉得把这个`odbc`的库封装起来……
但是我实在是不想再动`ODBC API`了，于是，直接在`odbc.lua`外面又套了一层……起名叫`odbcConn.lua`
```Lua
local ffi = require"ffi"
local odbc = require"odbc"
local resultset = {
_statement = nil,
statement = nil,
desc = nil,
num = nil,
bindData = nil,
new = function(self,statement)
local _statement = statement._statement;
if _statement == nil or _statement[0] == -1 then
error("please open statement first!",2);
return false;
end
local o = nil;
if self._statement ~= nil then
error("please close itself first!", 2);
return false;
else
o = {statement = statement,_statement = _statement};
setmetatable(o, {__index = self});
end
local status,desc,num = odbc.getColDescs(o._statement[0]);
if status ~=odbc.SUCCESS then
error("Get Columns Description Error on Create Resultset Processing");
return false;
end;
o.desc = desc;
o.num = num;
local status,bindData,colDesc = odbc.bindAllCols(o._statement[0],desc);
if status ~=odbc.SUCCESS then
error("Bind Columns Type Error on Create Resultset Processing");
return false;
end;
o.bindData = bindData;
return o;
end,
getColNum = function(self)
return self.num;
end,
getColDescs = function(self)
return self.desc;
end,
iterator = function(self)
return odbc.iter_row(self._statement[0],self.bindData);
end,
close = function(self)
if self._statement == nil or self._statement[0] == -1 then
return false,"please open it first!";
end
self._statement = nil;
self.statement = nil;
self.desc = nil;
self.num = nil;
self.bindData = nil;
return true,self;
end
}
local statement = {
conn = nil,
_statement = nil,
new = function(self,conn)
local _conn = conn._conn;
if _conn == nil or _conn[0] == -1 then
error("please open connect first!",2);
return false;
end
local o = nil;
if self._statement ~= nil then
if self._statement[0] ~= -1 then
error("please close it first!", 2);
return false;
end
o = self;
else
o = {_statement = ffi.new("long[1]",-1)};
setmetatable(o, {__index = self});
end
local status,st,msg=odbc.createStatement(_conn[0])
o._statement[0] = st;
if(status == odbc.SUCCESS) then
o._conn = _conn;
ffi.gc(o._statement,o.__gc);
return o;
else
o._statement[0] = -1;
error(msg)
return false
end
end,
executeSQL = function(self,sql)
if type(sql) ~= "string" then
error("paramater error,please input a string!", 2);
return false;
end
local status,st,msg = odbc.executeSQL(self._statement[0],sql);
if status==odbc.SUCCESS then
return true,self;
else
return false,msg;
end;
end,
prepareSQL = function(self,sql)
if type(sql) ~= "string" then
error("paramater error,please input a string!", 2);
return false;
end
local status,st,msg = odbc.prepareSQL(self._statement[0],sql);
if status==odbc.SUCCESS then
return true,self;
else
return false,msg;
end;
end,
setString = function(self,index,str)
if type(index) ~= "number" then
error("paramater 1 error,please input a number!", 2);
return false;
end
if type(str) ~= "string" then
error("paramater 2 error,please input a string!", 2);
return false;
end
local status,msg = odbc.setStringType(self._statement[0],index,str);
if status==odbc.SUCCESS then
return true,self;
else
return false,msg;
end;
end,
setNumber = function(self,index,num)
if type(index) ~= "number" or type(num) ~= "number" then
error("paramater error,please input two number!", 2);
return false;
end
local status,msg = odbc.setNumberType(self._statement[0],index,num);
if status==odbc.SUCCESS then
return true,self;
else
return false,msg;
end;
end,
execute = function(self)
local status,st,msg = odbc.executeStmt(self._statement[0],sql);
if status==odbc.SUCCESS then
return true,self;
else
return false,msg;
end;
end,
getResultset = function(self)
return resultset:new(self);
end,
__gc = function(_statement)
--print("gc running!");
if _statement ~= nil and _statement[0] ~= -1 then
odbc.closeStmt(_statement[0]);
end
end,
close = function(self)
if self._statement == nil or self._statement[0] == -1 then
return false,"please open it first!";
end
local status,i,msg=odbc.closeStmt(self._statement[0])
if(status ~= odbc.SUCCESS) then
return false,'['..i..":"..status..']'..table.concat(msg, "\n");
end
self._statement[0] = -1;
return true,self;
end
}
local conn = {
env = nil,
_conn = nil,
open = function(self,env,dsnName)
local _env = env._env;
if _env == nil or _env[0] == -1 then
error("please open env first!",2);
return false;
end
local o = nil;
if self._conn ~= nil then
if self._conn[0] ~= -1 then
error("please close it first!", 2);
return false;
end
o = self;
else
o = {_conn = ffi.new("int[1]",-1)};
setmetatable(o, {__index = self});
end
local status,c,msg=odbc.openConn(_env[0],dsnName);
o._conn[0] = c;
if(status == odbc.SUCCESS) then
o._env = _env;
ffi.gc(o._conn,o.__gc);
return o;
else
o._conn[0] = -1;
error(msg)
return false
end
end,
createStatement = function(self)
return statement:new(self);
end,
__gc = function(_conn)
--print("gc running!");
if _conn ~= nil and _conn[0] ~= -1 then
odbc.closeConnect(_conn[0]);
end
end,
close = function(self)
if self._conn == nil or self._conn[0] == -1 then
return false,"please open it first!";
end
local status,i,msg=odbc.closeConnect(self._conn[0])
if(status ~= odbc.SUCCESS) then
return false,'['..i..":"..status..']'..table.concat(msg, "\n");
end
self._conn[0] = -1;
return true,self;
end
}
local env = {
_env = nil,
createDsn =function(dsnName,mdbFile)
if type(dsnName) ~= "string" or type(mdbFile) ~= "string" then
return false,"paramater error,please input two string!";
end
if(odbc.creatMdbDsn(dsnName,mdbFile))then
return true,dsnName;
else
return false,"create MDB DSN error!";
end
end,
open = function(self)
local o = nil;
if self._env ~= nil then
if self._env[0] ~= -1 then
error("please close it first!", 2);
return false;
end
o = self;
else
o = {_env = ffi.new("int[1]",-1)};
setmetatable(o, {__index = self});
end
local status,e,msg=odbc.openEnv();
o._env[0] = e;
if(status == odbc.SUCCESS) then
ffi.gc(o._env,o.__gc);
return o;
else
o._env[0] = -1;
error(msg);
return false
end
end,
createConn = function(self,dsnName)
if type(dsnName) ~= "string" then
error("paramater error,please input a string!", 2);
return false;
end
return conn:open(self,dsnName);
end,
__gc = function(_env)
--print("gc running!");
if _env ~= nil and _env[0] ~= -1 then
odbc.closeEnv(_env[0]);
end
end,
close = function(self)
if self._env == nil or self._env[0] == -1 then
return false,"please open it first!";
end
local status,i,msg=odbc.closeEnv(self._env[0])
if(status ~= odbc.SUCCESS) then
return false,'['..i..":"..status..']'..table.concat(msg, "\n");
end
self._env[0] = -1;
return true,self;
end
}
return env;
```
然后，重新再写个测试程序`testOdbcConn.lua`
```Lua
local ENV = require"odbcConn"
local env = ENV:open();
local dsnName = "excuteSQL";
local dsnFile = "test.mdb";
local status,msg = ENV.createDsn(dsnName,dsnFile);
if not status then
print(msg);
return -1;
end
if not env then
print("创建odbc调用环境失败");
return -2;
end
local conn = env:createConn(dsnName);
if not conn then
print("创建odbc连接失败");
env:close();
return -3;
end
function excuteSQL(sql)
local statement = conn:createStatement()
if not statement then
print("创建statement失败");
return -4;
end
local result,msg = statement:executeSQL(sql);
statement:close();
if not result then
print("运行SQL失败:"..table.concat(msg,"\n"));
return -5;
end
print("执行SQL成功");
return 0;
end
function excutePrepareSQL(sql,name,value)
local statement = conn:createStatement()
if not statement then
print("创建statement失败");
return -4;
end
statement:prepareSQL(sql);
statement:setString(1,name);
statement:setNumber(2,value);
local result,msg = statement:execute();
statement:close();
if not result then
print("执行准备SQL失败:"..table.concat(msg,"\n"));
return -5;
end
print("执行准备SQL成功");
return 0;
end
function excuteQuery(sql)
local statement = conn:createStatement()
if not statement then
print("创建statement失败");
return -4;
end
local result,msg = statement:executeSQL(sql);
if not result then
print("运行查询SQL失败:"..table.concat(msg,"\n"));
return -5;
end
print("执行查询SQL成功");
local resultset = statement:getResultset();
local row = nil;
for row in resultset:iterator() do
print(table.concat(row,","));
end
resultset:close();
statement:close();
return 0;
end
excuteSQL("insert into [test]([name],[value]) values('testodbcConn1',100)");
excutePrepareSQL("insert into [test]([name],[value]) values(?,?)","tesodbcConntp1",2333);
excuteQuery("select * from [test]");
conn:close();
env:close();
return 0;
```
然后……执行一下试试：
```
>luajit testOdbcConn.lua
执行SQL成功
执行准备SQL成功
执行查询SQL成功
1,testOOO,101
2,tesooo0,23
3,testodbcConn1,100
4,tesodbcConntp1,2333
```
呃……貌似跟没封装的`odbc`也没啥不同嘛……虽然封装代码一堆堆的冗余还没来得及优化，当然，水平还不够，以后有机会慢慢优化……
当前只能这样了，先分享出来吧……
鲁公网安备 37010202001027号