lua链接oracle解决方法与步骤

2017-01-03 10:11:05来源:oschina作者:testwork人点击

测试环境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: 需要下载:


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


http://www.oracle.com/technetwork/cn/topics/linuxx86-64soft-092277.html


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文件修改


本人测试使用的是oracle-instantclient12.1-sqlplus-12.1.0.1.0-1.x86_64.rpm


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@hostadmin]# 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) doprint(string.format("%s %s",k , v )) end


执行一下试试


[root@hostluaoracle]# luajit test.lua table: 0x41f7c8a8 Oracle environment (0x41f7cc88) Oracle connection (0x41f7ccf8) Oracle cursor (0x41f7c090) c 21


key:c


value: 21


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


最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台