Nginx下php连接到GBase 8s数据库 - ODBC方式

PHP可以通过odbc,pdo_odbc和pdo_gbasedbt三种方式连接到GBase 8s数据库,这三种方式均需要通过CSDK客户端工具。
操作系统:CentOS 7.9 x86_64

必要组件:

  • unixODBC及unixODBC-devel
  • php及扩展,如php-fpm/php-odbc/php-pdo等
  • nginx服务

数据库连接工具(CSDK)

  • GBase 8s CSDK

配置前提

  • GBase 8s数据库服务器正常运行
  • GBase 8s客户端连接工具

系统环境

Linux关闭selinux(至少需要放行httpd_can_network_connect),通过yum方式安装必要的组件,包含unixODBC/unixODBC-devel,nginx和php-fpm等
unixODBC

yum -y install unixODBC unixODBC-devel

CentOS7的nginx服务需要使用epel源

# 安装nginx需要使用epel源
yum -y install epel-release

yum -y install nginx

安装php及php-fpm支持

yum -y install php php-cli php-devel php-fpm php-odbc php-soap php-pdo php-mbstring php-gd php-intl

GBase 8s客户端连接工具安装及ODBC配置

安装过程省略,指定安装目录为/opt/gbase8s-odbc-driver
需要的LIB库加入到ld.so.conf配置文件中

cat > /etc/ld.so.conf.d/gbasedbt-x86_64.conf <<!
/opt/gbase8s-odbc-driver/lib
/opt/gbase8s-odbc-dirver/lib/cli
/opt/gbase8s-odbc-driver/lib/esql
!

重新加载

ldconfig

设置临时环境变量

export ODBCINI=/etc/odbc.ini
export GBASEBDTDIR=/opt/gbase8s-odbc-driver

编辑ODBCINI配置文件,内容如下:

[ODBC Data Sources]
testdb=GBase ODBC DRIVER
;
; Define ODBC Database Driver's Below - Driver Configuration Section
;
[testdb]
Driver=/opt/gbase8s-odbc-driver/lib/cli/iclis09b.so
Description=GBase ODBC DRIVER
Database=testdb
LogonID=gbasedbt
pwd=GBase123$%
Servername=gbase01
CursorBehavior=0
CLIENT_LOCALE=zh_CN.utf8
DB_LOCALE=zh_CN.utf8
TRANSLATIONDLL=/opt/gbase8s-odbc-driver/lib/esql/igo4a304.so
;
; UNICODE connection Section
;
[ODBC]
;uncomment the below line for UNICODE connection
UNICODE=UCS-2
;
; Trace file Section
;
Trace=0
TraceFile=/tmp/odbctrace.out
InstallDir=/opt/gbase8s-odbc-driver
TRACEDLL=idmrs09a.so

配置GBASEBDTSQLHOSTS(/opt/gbase8s-odbc-driver/etc/sqlhosts)配置文件

# SQLHOSTS配置文件,按实际配置  
gbase01 onsoctcp 192.168.80.70 9088

测试ODBC连接

isql -v testdb
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select dbinfo('dbname') from dual;
+---------------------------------------+
|                                       |
+---------------------------------------+
| testdb                                |
+---------------------------------------+
SQLRowCount returns -1
1 rows fetched

php-fpm配置

编辑/etc/php-fpm.d/www.conf配置文件

; 修改user和group值为ngingx,因为nginx使用nginx用户及组
user = nginx
group = nginx


; 打开环境变量配置参数
clear_env = no
; 使用ODBC连接时需要配置以下两个环境变量
env[ODBCINI] = /etc/odbc.ini
env[GBASEDBTDIR] = /opt/gbase8s-odbc-driver

启动php-fpm,并加入到systemctl自启动

systemctl start php-fpm

systemctl enable php-fpm

nginx配置

编辑/etc/nginx/nginx.conf配置文件
在server中增加

        location ~* \.php$ {
            # fastcgi_pass unix:/run/php-fpm/www.sock;  
            fastcgi_pass 127.0.0.1:9000;
            include fastcgi_params;
            fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
            fastcgi_param SCRIPT_NAME $fastcgi_script_name;
        }

启动nginx,并加入到systemctl自启动

systemctl start nginx

systemctl enable nginx

测试数据库操作

在/usr/share/nginx/html目录下编辑测试代码odbc.php

<?php
    header('Content-type:text/html;charset=utf-8');
    echo "连接到数据库<br>";
    $conn=odbc_connect("testdb",'','');            # 连接到 dsn = testdb
    if (!$conn)
        {exit("Connection Failed: " . $conn);}

    echo "初始化表 tabodbc<br>";
    echo "drop table tabodbc<br>";
    $sql="drop table if exists tabodbc";
    $rs=odbc_exec($conn,$sql);

    echo "create table tabodbc<br>";
    $sql="create table tabodbc(col1 int, col2 varchar(255), primary key(col1))";
    $rs=odbc_exec($conn,$sql);

    echo "insert into tabodbc<br>";
    $sql="insert into tabodbc values(?,?)";
    $stmt=odbc_prepare($conn,$sql);
    $params=array(1,"南大通用");
    $rs=odbc_execute($stmt, $params);
    $params=array(2,"南大通用北京分公司");
    $rs=odbc_execute($stmt, $params);

    echo "select from tabodbc<br>";
    $sql="select * from tabodbc";

    $rs=odbc_exec($conn,$sql);
    if (!$rs)
        {exit("Error in SQL");}

    echo "<table><tr>";
    echo "<th>col1</th>";
    echo "<th>col2</th></tr>";

    while (odbc_fetch_row($rs))
    {
        $col1=odbc_result($rs,"col1");
        $col2=odbc_result($rs,"col2");
        echo "<tr><td>$col1</td>";
        echo "<td>$col2</td></tr>";
    }

    odbc_close($conn);

    echo "</table>";

浏览器中访问odbc.php

连接到数据库
初始化表 tabodbc
drop table tabodbc
create table tabodbc
insert into tabodbc
select from tabodbc
col1    col2
1    南大通用
2    南大通用北京分公司

标签: gbase 8s, odbc, php, GBase, nginx

添加新评论