lua 数据库 Finalize lua oracle

转载

IT剑客风云 2024-08-22 12:35:24

文章标签 lua 数据库 Finalize lua 数据库运维 ci 文章分类 游戏开发

测试环境centos7

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

省略

以下是具体操作步骤:

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

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

-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

说明游戏,

开始使用脚本来测试

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

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

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。