MogDB - 搭建unixodbc环境，并使用简单测试用例验证环境有效性

原创伊织鸟 2024-10-21

547

概述

本文通过搭建unixODBC编译环境，及通过编写C语言测试脚本用例。描述如何通过unixODBC连接并操作MogDB数据库方法，提供类似测试案例。

软件名称	版本	说明
PTK	v1.5.3	MogDB安装配置工具
MogDB	5.0.8	MogDB数据库
unixODBC	2.3.9	官网提供的unixODBC源码版本
mogdb plsqlodbc	5.0.0.2	官网提供的mogdb plsqlodbc驱动
gcc	4.8.5-39	GCC编译版本

测试步骤

1、安装MogDB数据库

## 配置ptk
# ptk --version
PTK Version:    v1.5.3 release
Go Version:     go1.19.10
Build Date:     2024-06-21T10:26:49
Git Hash:       d90a48ea
OS/Arch:        linux/amd64

## 编写ptk config.yaml文件
# cat config.yaml 
global:
  cluster_name: "mogdb508"
  user: "omm"
  group: "dbgrp"
  db_password: "pTk6YjE1MTZkMDg8PTxBPTxAP2ZwNGNuU29nbHdackpiWVdrejVUTU5VWGg0b0t6XzEwRUE0QVBBc0w0dzA="
  db_port: 26000
  base_dir: "/data/mogdb508"
  app_dir: "/data/mogdb508/app"
  data_dir: "/data/mogdb508/data"
  log_dir: "/data/mogdb508/log"
  tmp_dir: "/data/mogdb508/tmp"
  tool_dir: "/data/mogdb508/tool"
  core_file_dir: "/data/mogdb508/core"
db_servers:
  - host: "192.168.118.140"
    db_port: 26000
    ha_port: 26001
    role: "primary"

## 建立用户及用户组
# groupadd dbgrp -g 2000
# useradd omm -g 2000 -u 2000 
# echo "MogDB@2024" | passwd --stdin omm
更改用户 omm 的密码 。
passwd：所有的身份验证令牌已经成功更新。

## 创建数据库安装目录并赋权
# mkdir /data
# chown omm: /data

## PTK预检查
# ptk checkos -f /opt/software/config.yaml
# Check Results
                Item                |  Level   
------------------------------------+----------
  A1.Check_OS_Version               | OK       
  A2.Check_Kernel_Version           | OK       
  A3.Check_Unicode                  | OK       
  A4.Check_TimeZone                 | OK       
  A5.Check_Swap_Memory_Configure    | Warning  
  A6.Check_SysCtl_Parameter         | Warning  
  A7.Check_FileSystem_Configure     | OK       
  A8.Check_Disk_Configure           | OK       
  A9.Check_BlockDev_Configure       | Warning  
  A9.Check_Logical_Block            | OK       
  A10.Check_Asynchronous_IO_Request | OK       
  A10.Check_IO_Configure            | OK       
  A10.Check_NR_Request              | Warning  
  A11.Check_Network_Configure       | OK       
  A12.Check_Time_Consistency        | OK       
  A13.Check_Firewall_Status         | OK       
  A14.Check_THP_Status              | OK       
  A15.Check_Dependent_Package       | OK       
  A16.Check_CPU_Instruction_Set     | OK       
  A17.Check_Port                    | OK       
  A18.Check_Selinux                 | OK       
  A19.Check_User_Ulimit             | OK       
  A20.Check_Directory               | OK       
Total count 23, abnormal count 0, warning count 4

## PTK安装MogDB
# ptk install -y -f /opt/software/config.yaml -p /opt/software/mogdb508/MogDB-5.0.8-CentOS-x86_64-all.tar.gz --skip-create-user --skip-check-os

# ptk cluster -n mogdb508 status --detail
[   Cluster State   ]
cluster_name                   : mogdb508
cluster_state                  : Normal
database_version               : MogDB 5.0.8 (build 41aa0432)

[  Datanode State   ]
  cluster_name |  id  |       ip        | port  | user | nodename | db_role | state  |  uptime  | upstream  
---------------+------+-----------------+-------+------+----------+---------+--------+----------+-----------
  mogdb508     | 6001 | 192.168.118.140 | 26000 | omm  | dn_6001  | primary | Normal | 00:00:35 | -         

[  DataNode Detail  ]
--------------- 192.168.118.140:26000(dn_6001) ---------------
role                           : primary
data_dir                       : /data/mogdb508/data
az_name                        : AZ1

## 创建测试数据库及用户
# su - omm
$ gsql -r
gsql ((MogDB 5.0.8 build 41aa0432) compiled at 2024-07-26 12:43:48 commit 0 last mr 1804 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

MogDB=# create database testdb;
CREATE DATABASE
MogDB=# create user moguser with sysadmin password 'Mogdb@1234';
NOTICE:  The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
MogDB=#

2、检查gcc编译环境

# gcc --version
gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-39)
Copyright © 2015 Free Software Foundation, Inc.
本程序是自由软件；请参看源代码的版权声明。本软件没有任何担保；
包括没有适销性和某一专用目的下的适用性担保。

3、安装unixODBC软件

## 源码安装unixodbc
# tar -xvf unixODBC-2.3.9.tar.gz
# cd unixODBC-2.3.9/
# ./configure --prefix=/opt/unixodbc --enable-gui=no  --enable-drivers=no
# make && make install

## 检查unixodbc安装状态
# /opt/unixodbc/bin/odbcinst -j
unixODBC 2.3.9
DRIVERS............: /opt/unixodbc/etc/odbcinst.ini
SYSTEM DATA SOURCES: /opt/unixodbc/etc/odbc.ini
FILE DATA SOURCES..: /opt/unixodbc/etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

##安装odbc相关c库文件
# yum install unixODBC-devel -y
# yum install sqlite-devel -y

## 配置环境变量
# export PATH=/opt/unixodbc/bin/:$PATH
# export LD_LIBRARY_PATH=/opt/unixodbc/lib/:$LD_LIBRARY_PATH

# odbcinst -j
unixODBC 2.3.9
DRIVERS............: /opt/unixodbc/etc/odbcinst.ini
SYSTEM DATA SOURCES: /opt/unixodbc/etc/odbc.ini
FILE DATA SOURCES..: /opt/unixodbc/etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

4、安装mogdb plsqlodbc驱动

## 解压mogdbodbc驱动
# tar -xvf MogDB-ODBC-5.0.0.2-CentOS-x86_64.tar.gz -C /opt/mogdbodbc

## 配置环境变量
# export PATH=/opt/unixodbc/bin/:$PATH
# export LD_LIBRARY_PATH=/opt/unixodbc/lib/:/opt/mogdbodbc/lib/:$LD_LIBRARY_PATH

## 配置unixodbc驱动，并执行mogdbodbc
# cat /opt/unixodbc/etc/odbcinst.ini
[ODBC]
Trace=Yes
TraceFile=/tmp/unixodbc.log

[MogDB]
Description     = ODBC for PostgreSQL
Driver          = /opt/mogdbodbc/odbc/lib/psqlodbcw.so
Setup           = /opt/mogdbodbc/odbc/lib/psqlodbcw.so
Driver64        = /opt/mogdbodbc/odbc/lib/psqlodbcw.so
Setup64         = /opt/mogdbodbc/odbc/lib/psqlodbcw.so
FileUsage       = 1

## 配置数据库连接
# cat /opt/unixodbc/etc/odbc.ini
[MogDB_LINK]
Description = PostgresSQLODBC
Driver = MogDB
Database = testdb
Servername = 192.168.118.140
UserName = moguser
Password = Mogdb@1234
Port = 26000
ForExtensionConnector = 1     ##关闭自动savepoint
UseBatchProtocol = 1          ##开启批量绑定
ConnSettings=set client_encoding=UTF8 ##客户端字符集，于server_encoding一致即可
ReadOnly = 0

5、使用unixODBC的isql工具链接并操作数据库

# isql -v MogDB_LINK
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select 1;
+------------+
| ?column?   |
+------------+
| 1          |
+------------+
SQLRowCount returns 1
1 rows fetched
SQL>

6、编写简单的C语言用例

# vi /tmp/test.c
//C语言头文件
#include <sql.h>
#include <sqlext.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

//异常中断错误信息
void checkRC(SQLRETURN rc, SQLHANDLE handle, SQLSMALLINT type, const char *msg) {
    if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO) {
        SQLCHAR sqlState[6], message[SQL_MAX_MESSAGE_LENGTH];
        SQLINTEGER nativeError;
        SQLSMALLINT textLength;
        SQLGetDiagRec(type, handle, 1, sqlState, &nativeError, message, sizeof(message), &textLength);
        printf("%s (RC: %d, SQLSTATE: %s, ERROR: %s)\n", msg, rc, sqlState, message);
    }
}


//主函数
int main() {
    SQLHENV   henv;             // Handle ODBC environment 
    SQLHDBC   hdbc;             // Handle connection
    SQLHSTMT  stmt;             // Handle statement
    SQLRETURN rc;
    
    //申请环境句柄
    rc = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
    checkRC(rc, SQL_NULL_HANDLE, SQL_HANDLE_ENV, "SQLAllocHandle(ENV) failed");
    
    //设置环境属性
    rc = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
    checkRC(rc, henv, SQL_HANDLE_ENV, "SQLSetEnvAttr failed");
    
    //申请连接句柄
    rc = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
    checkRC(rc, henv, SQL_HANDLE_ENV, "SQLAllocHandle(DBC) failed");
    
    //配置连接数据源，需要修改databasename,username,password
    rc = SQLConnect(hdbc, (SQLCHAR*) "mogdb_link", SQL_NTS, (SQLCHAR*)"moguser", SQL_NTS, (SQLCHAR*)"Mogdb@1234", SQL_NTS);
    checkRC(rc, hdbc, SQL_HANDLE_DBC, "SQLConnect failed");
    
    //设置连接属性，1：自动提交，2：链接超时，单位为s
    rc = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_OFF, 0);
    checkRC(rc, hdbc, SQL_HANDLE_DBC, "SQLSetConnectAttr autocommit_off failed");
    rc = SQLSetConnectAttr(hdbc,SQL_ATTR_LOGIN_TIMEOUT,(void*)5,0);
    checkRC(rc, hdbc, SQL_HANDLE_DBC, "SQLSetConnectAttr login_timeout failed");    

    printf("DB Connected !\n"); 
    
    //申请语句句柄
    rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &stmt);
    checkRC(rc, hdbc, SQL_HANDLE_DBC, "SQLAllocHandle(STMT) failed");

    //执行ddl
    rc = SQLExecDirect(stmt, (SQLCHAR*)"drop table if exists t1", SQL_NTS);
    checkRC(rc, stmt, SQL_HANDLE_STMT, "SQLExecDirect(drop table t1) failed");

    rc = SQLExecDirect(stmt, (SQLCHAR*)"create table t1(id int primary key, c1 varchar(1000))", SQL_NTS);
    checkRC(rc, stmt, SQL_HANDLE_STMT, "SQLExecDirect(create table t1) failed");
    printf("create success \n");
     
    //直接执行SQL语句
    rc = SQLExecDirect(stmt, (SQLCHAR*)"insert into t1 select 1,'odbc_test'", SQL_NTS);
    checkRC(rc, stmt, SQL_HANDLE_STMT, "SQLExecDirect(insert into t1) failed");
    printf("insert success \n");    

    //通过SQLGetData获取SQL结果集
    rc = SQLExecDirect(stmt, (SQLCHAR*)"select * from t1", SQL_NTS);
    checkRC(rc, stmt, SQL_HANDLE_STMT, "SQLExecDirect(select t1) failed");    
    
    printf("begin table select output\n");
    
    char typename[100]; 
    SQLColAttribute(stmt,1,SQL_DESC_TYPE,typename,100,NULL,NULL);                 
    printf("SQLColAtrribute %s\n",typename);

    while(SQL_SUCCEEDED(SQLFetch(stmt)))
    { 
      //定义int数字类型变量
      SQLINTEGER id;
      //定义char字符类型变量
      SQLCHAR remark1[20];
      //GET第一列数据结果集,int类型数据
      SQLGetData(stmt, 1, SQL_C_SLONG, &id, 0, NULL);
      //GET第二列数据结果集，varchar类型数据
      SQLGetData(stmt, 2, SQL_C_CHAR, remark1, sizeof(remark1), NULL);
      //打印数据
      printf("Fetched t1 id: %d, t1 remark: %s\n",id,remark1);
    };
    
    printf("end table select output\n");
    
    //释放stmt句柄
    SQLFreeStmt(stmt, SQL_CLOSE);                           

    //关闭事务
    rc = SQLEndTran(SQL_HANDLE_DBC, hdbc, SQL_COMMIT);
    checkRC(rc, hdbc, SQL_HANDLE_DBC, "SQLEndTran(COMMIT) failed");

    //释放链接
    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
    SQLDisconnect(hdbc);
    SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
    SQLFreeHandle(SQL_HANDLE_ENV, henv);
    printf("All done.\n");
    return 0;
}

7、执行脚本，并查询返回结构

##编译脚本
# gcc /tmp/test.c -o /tmp/test -lodbc

##执行脚本
/tmp/test

图片.png

墨力计划 mogdb

最后修改时间：2024-10-22 10:22:48

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

MogDB - 搭建unixodbc环境，并使用简单测试用例验证环境有效性

概述

相关软件版本

测试步骤

1、安装MogDB数据库

2、检查gcc编译环境

3、安装unixODBC软件

4、安装mogdb plsqlodbc驱动

5、使用unixODBC的isql工具链接并操作数据库

6、编写简单的C语言用例

7、执行脚本，并查询返回结构

评论