【AntDB数据库】AntDB数据库服务启停（二）

数据库服务启停

连接AntDB

如果有使用python连接postgres的经验，那在AntDB中操作是完全一样的。 首先需要import psycopg2，然后使用psycopg2提供的conn方法创建连接即可。代码示例如下：

import psycopg2
conn = psycopg2.connect(database="postgres", user="dang31", password="123", host="10.1.226.256", port="1234")
复制

此时就创建好了一个连接，供后续使用。 在进行数据库操作之前，需要先建立一个cursor，在cursor中进行相应的操作：

cur = conn.cursor()
复制

创建表：

cur.execute("create t_test (id int,name text)")
复制

插入数据：

cur.insert("insert into t_test values (1,'name')")
复制

删除数据：

cur.execute("delete from t_test where id=%s",(3,)) 
复制

更新数据：

cur.execute("update  t_test set name=%s where id=%s",('c',1)) 
复制

查询数据：

cur.execute("select * from t_test;")
rows = cur.fetchall()
pprint.pprint(rows)
复制

完整的脚本如下：

#!/usr/bin/env python
# -*- coding: UTF-8 -*-

import psycopg2
import sys
import pprint

adb_conn="dbname=postgres user=dang31 password=123 host=10.1.226.256 port=1234"

try:
   conn = psycopg2.connect(adb_conn)
except psycopg2.Error as e:
    print"Unable to connect!"
    print e.pgerror
    print e.diag.message_detail
    sys.exit(1)
else:
    print"Connected!"    
    cur = conn.cursor()
#该程序创建一个光标将用于整个数据库使用Python编程。
print ("version:")
cur.execute("select version();")  
rows = cur.fetchall()
pprint.pprint(rows)
print ("create  table")
cur.execute("create table t_test (id int,name text);")   
print ("insert into table")
cur.execute("insert into t_test (id,name) values (%s,%s)",(1,'a'))
cur.statusmessage
cur.execute("insert into t_test (id,name) values (%s,%s)",(3,'b')) 
cur.mogrify("insert into t_test (id,name) values (%s,%s)",(3,'b')) 
cur.execute("select * from t_test;")
print ("fetchone")
row = cur.fetchone()
pprint.pprint(row)
cur.execute("select * from t_test;")
rows = cur.fetchall()
print ("fetchall")
pprint.pprint(rows)
print ("delete from table")
cur.execute("delete from t_test where id=%s",(3,)) 
cur.execute("select * from t_test;")
rows = cur.fetchall()
pprint.pprint(rows)
print ("update  table")
cur.execute("update  t_test set name=%s where id=%s",('c',1)) 
cur.execute("select * from t_test;")
rows = cur.fetchall()
pprint.pprint(rows)
print ("drop  table")
cur.execute("drop table if EXISTS  t_test ");

conn.commit()
#connection.commit() 此方法提交当前事务。如果不调用这个方法，无论做了什么修改，自从上次调用#commit()是不可见的，从其他的数据库连接。
conn.close() 
#connection.close() 此方法关闭数据库连接。请注意，这并不自动调用commit（）。如果你只是关闭数据库连接而不调用commit（）方法首先，那么所有更改将会丢失
复制

输出结果为：

Connected!
version:
[('PostgreSQL 11.6 ADB 5.0.0 37c61ca18f on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit',)]
create  table
insert into table
fetchone
(3, 'b')
fetchall
[(1, 'a'), (3, 'b')]
delete from table
[(1, 'a')]
update  table
[(1, 'c')]
drop  table
复制

更多的操作，可以参考psycopg 的文档：https://www.psycopg.org/docs/index.html

C++连接

C++ 中可以通过OTL模板库来访问不同的数据库，我们推荐使用OTL+ODBC的方式访问AntDB。

OTL的文档：http://otl.sourceforge.net/otl3.htm

配置OBDC

1. 安装ODBC开发包：

yum install unixODBC-devel
复制

1. 安装pg odbc： https://odbc.postgresql.org/ https://www.postgresql.org/ftp/odbc/versions/

wget  https://ftp.postgresql.org/pub/odbc/versions/src/psqlodbc-11.01.0000.tar.gz
tar xzvf psqlodbc-11.01.0000.tar.gz 
cd psqlodbc-11.01.0000 
./configure 
make 
make install #root 执⾏
复制

安装的路径如下：

/bin/mkdir -p '/usr/local/lib' 
/bin/sh ./libtool --mode=install /bin/install -c psqlodbcw.la psqlodbca.la '/usr/local/lib' 
libtool: install: /bin/install -c .libs/psqlodbcw.so /usr/local/lib/psqlodbcw.so 
libtool: install: /bin/install -c .libs/psqlodbcw.lai /usr/local/lib/psqlodbcw.la 
libtool: install: /bin/install -c .libs/psqlodbca.so /usr/local/lib/psqlodbca.so 
libtool: install: /bin/install -c .libs/psqlodbca.lai /usr/local/lib/psqlodbca.la 
libtool: finish: PATH="/data/sy/hive/hive-2.3.3/bin:/data/sy/hadoop/hadoop-2.8.5/bin:/data/sy/hadoop/hadoop-
2.8.5/sbin:/data/sy/jdk/jdk1.8.0_191/bin:/data/sy/jdk/jdk1.8.0_191/jre/bin:/home/adb40sy/mysql/install/bin:/data/sy/hive/h ive-2.3.3/bin:/data/sy/hadoop/hadoop-2.8.5/bin:/data/sy/hadoop/hadoop-
2.8.5/sbin:/data/sy/jdk/jdk1.8.0_191/bin:/data/sy/jdk/jdk1.8.0_191/jre/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/ sbin:/usr/bin:/root/bin:/usr/local/madlib/Versions/1.15/bin:/sbin" ldconfig -n /usr/local/lib

---------------------------------------------------------------------Libraries have been installed in:
/usr/local/lib
If you ever happen to want to link against installed libraries in a given directory, LIBDIR, you must either use libtool, and specify the full pathname of the library, or use the `-LLIBDIR' flag during linking and do at least one of the following:
- add LIBDIR to the `LD_LIBRARY_PATH' environment variable during execution
- add LIBDIR to the `LD_RUN_PATH' environment variable during linking
- use the `-Wl,-rpath -Wl,LIBDIR' linker flag
- have your system administrator add LIBDIR to `/etc/ld.so.conf'
See any operating system documentation about shared libraries for more information, such as the ld(1) and ld.so(8) manual pages.
复制

1. isql测试：

**********************************************
* unixODBC - isql                            *
**********************************************
* Syntax                                     *
*                                            *
*      isql DSN [UID [PWD]] [options]        *
*                                            *
* Options                                    *
*                                            *
* -b         batch.(no prompting etc)        *
* -dx        delimit columns with x          *
* -x0xXX     delimit columns with XX, where  *
*            x is in hex, ie 0x09 is tab     *
* -w         wrap results in an HTML table   *
* -c         column names on first row.      *
*            (only used when -d)             *
* -mn        limit column display width to n *
* -v         verbose.                        *
* -lx        set locale to x                 *
* -q         wrap char fields in dquotes     *
* -3         Use ODBC 3 calls                *
* -n         Use new line processing         *
* -e         Use SQLExecDirect not Prepare   *
* -k         Use SQLDriverConnect            *
* --version  version                         *
*                                            *
* Commands                                   *
*                                            *
* help - list tables                         *
* help table - list columns in table         *
* help help - list all help options          *
*                                            *
* Examples                                   *
*                                            *
*      isql WebDB MyID MyPWD -w < My.sql     *
*                                            *
*      Each line in My.sql must contain      *
*      exactly 1 SQL command except for the  *
*      last line which must be blank (unless *
*      -n option specified).                 *
*                                            *
* Please visit;                              *
*                                            *
*      http://www.unixodbc.org               *
*      nick@lurcher.org                      *
*      pharvey@codebydesign.com              *
**********************************************
复制

1. 修改odbc默认配置(vi /etc/odbcinst.ini)：

默认PG的配置：

[PostgreSQL]
Description     = ODBC for PostgreSQL
Driver          = /usr/lib64/psqlodbc.so
#Driver         = /usr/lib/psqlodbcw.so
#Setup          = /usr/lib/libodbcpsqlS.so
#Driver64       = /usr/lib64/psqlodbcw.so
#Setup64        = /usr/lib64/libodbcpsqlS.so
FileUsage       = 1

复制

可以不动这个配置参数，在⾃⼰的环境中找出上述⽂件，建⽴软链接：

root@intel175 ~]# find / -name 'psqlodbcw.so'
/ssd/home/billing/soft/psqlodbc-11.01.0000/.libs/psqlodbcw.so
/usr/local/lib/psqlodbcw.so
[root@intel175 ~]# find / -name 'libodbcpsqlS.so'
/usr/lib64/libodbcpsqlS.so

ln -s /usr/local/lib/psqlodbcw.so /usr/lib/otlobbc.so 
ln -s /usr/local/lib/psqlodbcw.so /usr/lib64/otlobbc.so
ln -s /usr/lib64/libodbcpsqlS.so /usr/lib/libodbcpsqlS.so
复制

1. 配置DSN

vi /etc/odbc.ini

[antdb]
Description = Test to antdb 
Driver = PostgreSQL 
Database = postgres 
Servername = localhost 
UserName = billing
Password = billing 
Port = 4333
复制

1. 连接测试：

[billing@intel175 soft]$ isql antdb -v
+---------------------------------------+
| Connected!  |
| |
| sql-statement |
| help [tablename]  |
| quit  |
| |
+---------------------------------------+
SQL> select count(*) from pg_class;
+---------------------+
| count |
+---------------------+
| 380 |
+---------------------+
SQLRowCount returns 1
1 rows fetched SQL>
复制

如果执行isql测试出现如下的错误

[unixODBC][Driver Manager]Data source name not found, and no default driver specified
复制

首先确认odbc的环境变量，是否正确指向了自己的配置文件，确认方法

[antdb@adb01 mgr1]$dbcinst -j
unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini  # 确认路径是上面配置odbcinst.ini文件路径
SYSTEM DATA SOURCES: /etc/odbc.ini      # 确认路径是上面配置odbc.ini文件路径
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/antdb/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
复制

再确认/etc/odbc.ini文件的访问权限，确认执行isql的用户有读权限

通过OTL操作数据库示例

1. 创建文件：test_antdb.cpp

#include <iostream>
using namespace std;

#include <stdio.h>
#include <string.h>
#include <stdlib.h>

#define OTL_ODBC_UNIX // uncomment this line if UnixODBC is used
#define OTL_ODBC_ALTERNATE_RPC
#if !defined(_WIN32) && !defined(_WIN64)
#define OTL_ODBC
#else 
#define OTL_ODBC_POSTGRESQL // required with PG ODBC on Windows
#endif
#include "otlv4.h" // include the OTL 4.0 header file

otl_connect db; // connect object

void insert()
// insert rows into table
{ 
 otl_stream o(10000, // PostgreSQL 8.1 and higher, the buffer can be > 1
              "insert into test_tab values(:f1<int>,:f2<int>)", 
                 // SQL statement
              db // connect object
             );
 char tmp[8];
 for(int i=1;i<=10;++i){
  sprintf(tmp,"Name%d\0",i);
  // o<<i<<i*10;
  o<<i<<i;
  o.flush();
  cout<<"Number:"<<i<<endl;
 }
}
void update(const int af1)
// insert rows into table
{ 
 otl_stream o(1, // PostgreSQL 8.1 and higher, buffer size can be >1
              "UPDATE test_tab "
              "   SET f2=:f2<int> "
              " WHERE f1=:f1<int>", 
                 // UPDATE statement
              db // connect object
             );
 o<<otl_null()<<af1+1; // set f2 to NULL

}
void select(const int af1)
{ 
 otl_stream i(50, // On SELECT, the buffer size can be > 1
              "select * from test_tab where f1>=:f11<int> and f1<=:f12<int> and rownum < 10", 
                 // SELECT statement
              db // connect object
             ); 
   // create select stream

 int f1;
//  char f2[31];
 int f2;

#if (defined(_MSC_VER) && _MSC_VER>=1600) || defined(OTL_CPP_11_ON)
// C++11 (or higher) compiler
#if defined(OTL_ANSI_CPP_11_VARIADIC_TEMPLATES)
 otl_write_row(i,af1,af1); // Writing input values into the stream
#else
  // when variadic template functions are not supported by the C++
  // compiler, OTL provides nonvariadic versions of the same template
  // functions in the range of [1..15] parameters
 otl_write_row(i,af1,af1); // Writing input values into the stream
  // the old way (operators >>() / <<()) is available as always:
  // i<<af1<<af1; // Writing input values into the stream
#endif
 for(auto& it : i){
  it>>f1;
  cout<<"f1="<<f1<<", f2=";
  it>>f2;
  if(it.is_null())
   cout<<"NULL";
  else
   cout<<f2;
  cout<<endl;
 }
#else
// C++98/03 compiler
 i<<af1<<af1; // Writing input values into the stream
 while(!i.eof()){ // while not end-of-data
   i>>f1;
   cout<<"f1="<<f1<<", f2=";
   i>>f2;
   if(i.is_null())
     cout<<"NULL";
   else
     cout<<f2;
   cout<<endl;
 }
#endif

}

int main()
{
 otl_connect::otl_initialize(); // initialize ODBC environment
 try{

  //db.rlogon("scott/tiger@postgresql");
  //db.rlogon("billappxc/123@sunyu");
  db.rlogon("danghb/123@antdb");

  otl_cursor::direct_exec
   (
    db,
    "drop table IF EXISTS test_tab",
    otl_exception::disabled // disable OTL exceptions
   ); // drop table

  //  db.commit();

  otl_cursor::direct_exec
   (
    db,
    "create table test_tab(f1 bigint, f2 bigint)"
    );  // create table

  otl_cursor::direct_exec
   (
    db,
    "set grammar to oracle"
    );  // set grammar

  // db.commit();
  insert(); // insert records into the table
  //db.commit();
  //return 0;
  update(10); // update records in the table
  select(8); // select records from the table
  select(3); // select records from the table
  select(5); // select records from the table
  db.commit();
 }

 catch(otl_exception& p){ // intercept OTL exceptions
  cerr<<p.msg<<endl; // print out error message
  cerr<<p.stm_text<<endl; // print out SQL that caused the error
  cerr<<p.sqlstate<<endl; // print out SQLSTATE message
  cerr<<p.var_info<<endl; // print out the variable that caused the error
 }

 db.logoff(); // disconnect from ODBC

 return 0;
}

复制

1. 编译文件

g++ test_antdb.cpp -L/usr/lib -lotlobbc -o test_antdb -g
复制

1. 执行程序

./test_antdb
Number:1
Number:2
Number:3
Number:4
Number:5
Number:6
Number:7
Number:8
Number:9
Number:10
f1=8, f2=8
f1=3, f2=3
f1=5, f2=5
复制

注意事项：程序中对绑定变量的使用，需要保证绑定变量类型和传值类型一致，否则会报错：

Incompatible data types in stream operation
UPDATE test_tab    SET f2=?         WHERE f1=?       

Variable: :f2<INT>, datatype in operator <</>>: CHAR
复制

AntDB数据库始于2008年，在运营商的核心系统上，为全国24个省份的10亿多用户提供在线服务，具备高性能、弹性扩展、高可靠等产品特性，峰值每秒可处理百万笔电信核心交易，保障系统持续稳定运行近十年，并在通信、金融、交通、能源、物联网等行业成功商用落地。