lua 访问oracle,lua链接oracle解决方法与步骤

测试环境centos7

1) 使用luajit ,这个比较简单,下载luajit源码,编译即可

省略

以下是具体操作步骤:

参考文章:http://www.programgo.com/article/15452439520/

但是步骤写的有点快,很难解决问题,所以重新来搞一下:

2) 下载luasql,

git clone https://github.com/keplerproject/luasql.gi

3) 编译make odbc

需要修改源代码目录中的config文件

调整lua相关的路径

LUA_LIBDIR =

LUA_DIC = /usr/local/bin/luajit-2.0.4

LUA_INC = /usr/local/include/luajit-2.0/

make odbc,提示找不到sql.h文件

需要安装unixODBC

yum install unixODBC unixODBC-devel -y

编译之后,执行make install

odbc.so文件被复制到/luasql目录中

4) 需要编译oci8

make oci8   需要oracle中的开发包,

A: 需要下载:

版本号需要对应一下,下载链接地址,请移步: 需要登录

oracle-instantclient-basic-10.2.0.4-1.i386.zip

oracle-instantclient-devel-10.2.0.4-1.i386.zip     //这个是sdk 的，文件名上没有说明，特此说明

oracle-instantclient-sqlplus-10.2.0.4-1.i386.zip  (注意版本号)

1) base,2)devel 3) sqlplus ,下载rpm包  使用 rpm -ivh xxx.rpm来安装(注意下载与系统符合的x64)

B: 修复config文件修改

DRIVER_LIBS_oci8 ?= -L/usr/lib/oracle/12.1/client64/lib/ -lclntshcore -lclntsh -lipc1 -lmql1 -lnnz12 -locci -lociei -locijdbc12 -lons -loramysql12 -lsqlplusic -lsqlplus ###注意标色的,不然会出现下面的错误

DRIVER_INCS_oci8 ?= -I/usr/include/oracle/12.1/client64/

备注上面的路径为默认的安装路径,请对应好自己的路径

需要将oracle的so做链接,放到lib64下面,不然即使编译成功,lua库找不到,

ln -s /usr/lib/oracle/12.1/client64/lib/libclntsh.so.12.1 /lib64/libclntsh.so.12.1

ln -s /usr/lib/oracle/12.1/client64/lib/libipc1.so /lib64/libipc1.so

ln -s /usr/lib/oracle/12.1/client64/lib/libmql1.so /lib64/libmql1.so

ln -s /usr/lib/oracle/12.1/client64/lib/libnnz12.so /lib64/libnnz12.so

ln -s /usr/lib/oracle/12.1/client64/lib/libocci.so.12.1 /lib64/libocci.so.12.1

ln -s /usr/lib/oracle/12.1/client64/lib/libociei.so /lib64/libociei.so

ln -s /usr/lib/oracle/12.1/client64/lib/libocijdbc12.so /lib64/libocijdbc12.so

ln -s /usr/lib/oracle/12.1/client64/lib/libons.so /lib64/libons.so

ln -s /usr/lib/oracle/12.1/client64/lib/liboramysql12.so /lib64/liboramysql12.so

ln -s /usr/lib/oracle/12.1/client64/lib/libsqlplusic.so /lib64/libsqlplusic.so

ln -s /usr/lib/oracle/12.1/client64/lib/libsqlplus.so /lib64/libsqlplus.so

ln -s /usr/lib/oracle/12.1/client64/lib/libclntshcore.so.12.1 /lib64/libclntshcore.so.12.1

如果有缺少,继续添加.

make clean一下,然后重新生成

make oci8

make odbc

将生成的so文件存放到luasql目录里面.

代码分布如下

---

├── luasql

│   ├── oci8.so

│   └── odbc.so

└── test.lua

------------

test.lua文件内容如下

local driver = require "luasql.oci8"

print(driver)

local env = driver.oci8()

print(env)

local dbcon = assert (env:connect("10.10.10.1", "xxx", "sss"))

print( dbcon )

--------------执行结果:

table: 0x41984530

Oracle environment (0x41984910)

luajit: test.lua:11: LuaSQL: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

stack traceback:

[C]: in function 'assert'

test.lua:11: in main chunk

[C]: at 0x00404ac0

好像提示是有个配置文件不对了.目前至少driver和env能打印出对象出来

---------------------------------------------------------------

错误汇总:

如果出现符号链接找不到:

luajit test.lua

luajit: error loading module 'luasql.oci8' from file './luasql/oci8.so':

./luasql/oci8.so: undefined symbol: OCIAttrSet

stack traceback:

[C]: at 0x0044b8b0

[C]: in function 'require'

test.lua:1: in main chunk

[C]: at 0x00404ac0

说明编译参数有问题,没有链接到库,对应gcc的参数为-l 小写字母l

可以使用nm来证明:

ldd -r libluasql_oci8D.so

undefined symbol: lua_settop (./libluasql_oci8D.so)

undefined symbol: OCIAttrSet (./libluasql_oci8D.so)

undefined symbol: lua_toboolean (./libluasql_oci8D.so)

undefined symbol: OCIStmtFetch (./libluasql_oci8D.so)

undefined symbol: OCIDescriptorAlloc (./libluasql_oci8D.so)

undefined symbol: lua_touserdata (./libluasql_oci8D.so)

undefined symbol: luaL_argerror (./libluasql_oci8D.so)

undefined symbol: lua_pushcclosure ( linux-vdso.so.1 => (0x00007fff993b6000)

libc.so.6 => /lib64/libc.so.6 (0x00007f67ab564000)

/lib64/ld-linux-x86-64.so.2 (0x00007f67abb3c000)

./libluasql_oci8D.so)

undefined symbol: lua_tolstring (./libluasql_oci8D.so)

undefined symbol: OCIDescriptorFree (./libluasql_oci8D.so)

undefined symbol: luaL_ref (./libluasql_oci8D.so)

undefined symbol: lua_createtable (./libluasql_oci8D.so)

undefined symbol: lua_rawset (./libluasql_oci8D.so)

undefined symbol: OCIStmtPrepare (./libluasql_oci8D.so)

undefined symbol: luaL_optlstring (./libluasql_oci8D.so)

undefined symbol: OCILobGetLength (./libluasql_oci8D.so)

undefined symbol: OCITransCommit (./libluasql_oci8D.so)

一看到undefined symbol,你懂的.

so文件找不到,路径不正确:

luajit: error loading module 'luasql.oci8' from file './luasql/oci8.so':

libclntshcore.so.12.1: cannot open shared object file: No such file or directory

stack traceback:

[C]: at 0x0044b8b0

[C]: in function 'require'

test.lua:1: in main chunk

[C]: at 0x00404ac0

说明对应的so文件,没有在默认的系统环境变量里面

使用上面的ln -s做软链接即可,

把缺的文件全部加上

=====================继续解决上面lua连接报错

ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

1) 安装oracle-instantclient12.1-odbc-12.1.0.2.0-1.x86_64.rpm

2) 请检查是否存在目录/home/oracle

文件内容如下:

cat /home/oracle/network/admin/tnsnames.ora

UAT_DB =  #注意这个地方的UAT_DB,一会要在ini中用到

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.1.1)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = uat)

)

)

然后配置unixODBC,用来测试

注意文件:

cat /etc/odbcinst.ini

# Example driver definitions

# Driver from the postgresql-odbc package

# Setup from the unixODBC package

[PostgreSQL]

Description     = ODBC for PostgreSQL

Driver          = /usr/lib/psqlodbcw.so

Setup           = /usr/lib/libodbcpsqlS.so

Driver64        = /usr/lib64/psqlodbcw.so

Setup64         = /usr/lib64/libodbcpsqlS.so

FileUsage       = 1

# Driver from the mysql-connector-odbc package

# Setup from the unixODBC package

[MySQL]

Description     = ODBC for MySQL

Driver          = /usr/lib/libmyodbc5.so

Setup           = /usr/lib/libodbcmyS.so

Driver64        = /usr/lib64/libmyodbc5.so

Setup64         = /usr/lib64/libodbcmyS.so

FileUsage       = 1

[Oracle]

Description     = Oracle ODBC driver for Oracle 11g

Driver      = /usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1 #文件根据安装odbc的rpm版本对应

Setup           =

FileUsage       =

CPTimeout       =

CPReuse         =

配置odbc.ini文件

cat /etc/odbc.ini

[OracleODBC]  #这个名称是给isql用的

Application Attributes = T

Attributes = W

BatchAutocommitMode = IfAllSuccessful

BindAsFLOAT = F

CloseCursor = F

DisableDPM = F

DisableMTS = T

Driver = Oracle

DSN = OracleODBC

EXECSchemaOpt =

EXECSyntax = T

Failover = T

FailoverDelay = 10

FailoverRetryCount = 10

FetchBufferSize = 64000

ForceWCHAR = F

Lobs = T

Longs = T

MetadataIdDefault = F

QueryTimeout = T

ResultSets = T

ServerName = UAT_DB ##对应oca文件中的名称

SQLGetData extensions = F

Translation DLL =

Translation ption = 0

DisableRULEHint = T

UserID =

该ini文件,需要找到UAT_DB,也就是需要找到ora文件,需要设置2个环境变量

vim /etc/profile

添加

export ORACLE_HOME=/home/oracle/

export TNS_ADMIN=/home/oracle/network/admin

使用source /etc/profile命令生效

使用isql来测试一下

用法isql OracleODBC user password -v

[root@host admin]# isql OracleODBC user passwrod -v

+---------------------------------------+

| Connected!                            |

|                                       |

| sql-statement                         |

| help [tablename]                      |

| quit                                  |

|                                       |

+---------------------------------------+

SQL> select count(*) from account;

+-----------------------------------------+

| COUNT(*)                                |

+-----------------------------------------+

| 21                                   |

+-----------------------------------------+

SQLRowCount returns -1

1 rows fetched

说明游戏,

开始使用脚本来测试

test.lua文件内容:

[root@host luaoracle]# cat test.lua

local driver = require "luasql.oci8"

print(driver)

local env = driver.oci8()

print(env)

---- 注意第一个参数为oca中的名称,后面是用户名,密码

local dbcon = assert (env:connect("UAT_DB", "username", "password"))

print( dbcon )

local sql = "select count(1) as c from account"

local cursor = dbcon:execute(sql)  ---- 执行一个sql语句,简单的,值统计表里有多少行

print(cursor)  ----返回的是一个cursor,

local ret = cursor:fetch({},"a")  ---- 取出cursor中的数据,

for k , v in pairs(ret) do

print(string.format("%s %s",k , v ))

end

执行一下试试

[root@host luaoracle]# luajit test.lua

table: 0x41f7c8a8

Oracle environment (0x41f7cc88)

Oracle connection (0x41f7ccf8)

Oracle cursor (0x41f7c090)

c 21

key:c

value: 21

表示数据库中返回的字段与值