DBI操作指南

DBI简介

Ruby DBI 模块为 Ruby 脚本提供了类似于 Perl DBI 模块的独立于数据库的接口。

DBI 即 Database independent interface，代表了 Ruby 独立于数据库的接口。DBI 在 Ruby 代码与底层数据库之间提供了一个抽象层，允许您简单地实现数据库切换。它定义了一系列方法、变量和规范，提供了一个独立于数据库的一致的数据库接口。

DBI 可与下列进行交互：

  • ADO (ActiveX Data Objects)
  • DB2
  • Frontbase
  • mSQL
  • MySQL
  • ODBC
  • Oracle
  • OCI8 (Oracle)
  • PostgreSQL
  • Proxy/Server
  • SQLite
  • SQLRelay
  • STACI (神通数据库)

DBI 应用架构

DBI 独立于任何在后台中可用的数据库。无论您使用的是 神通数据库、Oracle、MySQL、Informix，您都可以使用 DBI。下面的架构图清晰地说明了这点。

神通的Ruby-aci接口会注册一个DBI的数据接口，为DBD::STACI,因此通过DBI接口可实现对神通数据库的操作。

../../../../../_images/struct-dbi.png

Ruby DBI 一般的架构使用两个层：

  • 数据库接口（DBI）层。该层是独立于数据库，并提供了一系列公共访问方法，方法的使用不分数据库服务器类型。
  • 数据库驱动（DBD）层。该层是依赖于数据库，不同的驱动提供了对不同的数据库引擎的访问。神通数据库、MySQL、PostgreSQL、InterBase、Oracle 等分别使用不同的驱动。每个驱动都负责解释来自 DBI 层的请求，并把这些请求映射为适用于给定类型的数据库服务器的请求。

数据库连接

我们使用神通数据库，在连接数据库之前，请确保：

  • 您已经创建了一个数据库 OSRDB。
  • 您已经在 TESTDB 中创建了表 EMPLOYEE：create table employee(first_name varchar(30),last_name varchar(30), age int,sex char,income int);
  • 设置用户 ID "SYSDBA" 和密码 "szoscar55" 来访问 OSRDB
  • 已经在您的机器上正确地安装了 Ruby 模块 DBI。

连接数据库示例：

#!/usr/bin/ruby -w
require "dbi"
require 'staci'
begin
        # 连接到神通数据库
        dbh = DBI.connect("DBI:STACI:localhost:2003/OSRDB", "SYSDBA", "szoscar55")
        # 获取服务器版本字符串，并显示
        row = dbh.select_one("SELECT VERSION()")
        puts "Server version: " + row[0]
rescue DBI::DatabaseError => e
        puts "An error occurred"
        puts "Error code: #{e.err}"
        puts "Error message: #{e.errstr}"
ensure
        # 断开与服务器的连接
        dbh.disconnect if dbh
end

执行结果：

Server version: 神通数据库7.0.8.211101 for Linux(x86 64bit) (200 connections) (license invalid after 146 days)

如果建立连接时带有数据源，则返回数据库句柄（Database Handle），并保存到 dbh 中以便后续使用，否则 dbh 将被设置为 nil 值，e.err 和 e::errstr 分别返回错误代码和错误字符串。

最后，在退出这段程序之前，请确保关闭数据库连接，释放资源。

INSERT 操作

当您想要在数据库表中创建记录时，需要用到 INSERT 操作。一旦建立了数据库连接，我们就可以准备使用do方法或prepare和execute方法创建表或创建插入数据表中的记录。

使用 do 语句

不返回行的语句可通过调用do数据库处理方法。该方法带有一个语句字符串参数，并返回该语句所影响的行数。

dbh.do("DROP TABLE IF EXISTS EMPLOYEE") dbh.do("CREATE TABLE EMPLOYEE ( FIRST_NAME CHAR(20) NOT NULL, LAST_NAME CHAR(20), AGE INT, SEX CHAR(1), INCOME FLOAT )" );

同样地，您可以执行 SQL?INSERT?语句来创建记录插入 EMPLOYEE 表中。

require "dbi"
require 'staci'

begin
     # 连接到 神通数据库
        dbh = DBI.connect("DBI:STACI:localhost:2003/OSRDB", "SYSDBA", "szoscar55")
     dbh.do( "INSERT INTO EMPLOYEE(FIRST_NAME,
                   LAST_NAME,
                   AGE,
         SEX,
         INCOME)
          VALUES ('Mac', 'Mohan', 20, 'M', 2000)" )
     puts "Record has been created"
     dbh.commit
rescue DBI::DatabaseError => e
     puts "An error occurred"
     puts "Error code:    #{e.err}"
     puts "Error message: #{e.errstr}"
     dbh.rollback
ensure
     # 断开与服务器的连接
     dbh.disconnect if dbh
end

执行完成后，查询数据库中的数据如下：

SQL> select * from EMPLOYEE;

FIRST_NAME | LAST_NAME | AGE | SEX | INCOME

------------+-----------+-----+-----+--------

Mac | Mohan | 20 | M | 2000

使用prepare和execute

您可以使用 DBI 的prepare和execute方法来执行 Ruby 代码中的 SQL 语句。

创建记录的步骤如下：

准备带有 INSERT 语句的 SQL 语句。这将通过使用prepare方法来完成。

执行 SQL 查询，从数据库中选择所有的结果。这将通过使用execute方法来完成。

释放语句句柄。这将通过使用finish API 来完成。

如果一切进展顺利，则commit该操作，否则您可以rollback完成交易。

下面是使用这两种方法的语法：

sth = dbh.prepare(statement)
sth.execute
        ... zero or more SQL operations ...
sth.finish

这两种方法可用于传bind值给 SQL 语句。有时候被输入的值可能未事先给出，在这种情况下，则会用到绑定值。使用问号（?）替代实际值，实际值通过 execute() API 来传递。

下面的实例在 EMPLOYEE 表中创建了两个记录：

#!/usr/bin/ruby -w
require "dbi"
begin
     # 连接到 MySQL 服务器
    dbh = DBI.connect("DBI:STACI:localhost:2003/OSRDB", "SYSDBA", "szoscar55")
     sth = dbh.prepare( "INSERT INTO EMPLOYEE(FIRST_NAME,
                   LAST_NAME,
                   AGE,
         SEX,
         INCOME)
                   VALUES (?, ?, ?, ?, ?)" )
     sth.execute('John', 'Poul', 25, 'M', 2300)
     sth.execute('Zara', 'Ali', 17, 'F', 1000)
     sth.finish
     dbh.commit
     puts "Record has been created"
rescue DBI::DatabaseError => e
     puts "An error occurred"
     puts "Error code:    #{e.err}"
     puts "Error message: #{e.errstr}"
     dbh.rollback
ensure
     # 断开与服务器的连接
     dbh.disconnect if dbh
end

执行完成后，数据库新增了2条数据：

SQL> select * from EMPLOYEE;

FIRST_NAME | LAST_NAME | AGE | SEX | INCOME

------------+-----------+-----+-----+--------

Mac | Mohan | 20 | M | 2000

John | Poul | 25 | M | 2300

Zara | Ali | 17 | F | 1000

如果同时使用多个 INSERT，那么先准备一个语句，然后在一个循环中多次执行它要比通过循环每次调用 do 有效率得多。

READ 操作

对任何数据库的 READ 操作是指从数据库中获取有用的信息。

一旦建立了数据库连接，我们就可以准备查询数据库。我们可以使用do方法或prepare和execute方法从数据库表中获取值。

获取记录的步骤如下：

基于所需的条件准备 SQL 查询。这将通过使用prepare方法来完成。

执行 SQL 查询，从数据库中选择所有的结果。这将通过使用execute方法来完成。

逐一获取结果，并输出这些结果。这将通过使用fetch方法来完成。

释放语句句柄。这将通过使用finish方法来完成。

下面的实例从 EMPLOYEE 表中查询所有工资（salary）超过 1000 的记录。

#!/usr/bin/ruby -w
require "dbi"
begin
     # 连接到 MySQL 服务器
     dbh = DBI.connect("DBI:STACI:localhost:2003/OSRDB", "SYSDBA", "szoscar55")
     sth = dbh.prepare("SELECT * FROM EMPLOYEE
                        WHERE INCOME > ?")
     sth.execute(1000)
      sth.fetch do |row|
        printf "First Name: %s, Last Name : %s\n", row[0], row[1]
        printf "Age: %d, Sex : %s\n", row[2], row[3]
        printf "Salary :%d \n\n", row[4]
     end
     sth.finish
rescue DBI::DatabaseError => e
     puts "An error occurred"
     puts "Error code:    #{e.err}"
     puts "Error message: #{e.errstr}"
ensure
     # 断开与服务器的连接
     dbh.disconnect if dbh
end

执行完成后结果：

First Name: Mac, Last Name : Mohan

Age: 20, Sex : M

Salary :2000

First Name: John, Last Name : Poul

Age: 25, Sex : M

Salary :2300

Update 操作

对任何数据库的 UPDATE 操作是指更新数据库中一个或多个已有的记录。下面的实例更新 SEX 为 'M' 的所有记录。在这里，我们将把所有男性的 AGE 增加一岁。这将分为三步：

基于所需的条件准备 SQL 查询。这将通过使用prepare方法来完成。

执行 SQL 查询，从数据库中选择所有的结果。这将通过使用execute方法来完成。

释放语句句柄。这将通过使用finish方法来完成。

如果一切进展顺利，则commit该操作，否则您可以rollback完成交易。

#!/usr/bin/ruby -w
 require "dbi"
 begin
     # 连接到 MySQL 服务器
     dbh = DBI.connect("DBI:STACI:localhost:2003/OSRDB", "SYSDBA", "szoscar55")
     sth = dbh.prepare("UPDATE EMPLOYEE SET AGE = AGE + 1
                        WHERE SEX = ?")
     sth.execute('M')
     sth.finish
     dbh.commit
rescue DBI::DatabaseError => e
     puts "An error occurred"
     puts "Error code:    #{e.err}"
     puts "Error message: #{e.errstr}"
     dbh.rollback
ensure
     # 断开与服务器的连接
     dbh.disconnect if dbh
end

DELETE 操作

当您想要从数据库中删除记录时，需要用到 DELETE 操作。下面的实例从 EMPLOYEE 中删除 AGE 超过 20 的所有记录。该操作的步骤如下：

基于所需的条件准备 SQL 查询。这将通过使用prepare法来完成。

执行 SQL 查询，从数据库中删除所需的记录。这将通过使用execute方法来完成。

释放语句句柄。这将通过使用finish方法来完成。

如果一切进展顺利，则commit该操作，否则您可以rollback完成交易。

#!/usr/bin/ruby -w
require "dbi"
begin
     # 连接到 MySQL 服务器
     dbh = DBI.connect("DBI:STACI:localhost:2003/OSRDB", "SYSDBA", "szoscar55")
     sth = dbh.prepare("DELETE FROM EMPLOYEE
                        WHERE AGE > ?")
     sth.execute(20)
     sth.finish
     dbh.commit
rescue DBI::DatabaseError => e
     puts "An error occurred"
     puts "Error code:    #{e.err}"
     puts "Error message: #{e.errstr}"
     dbh.rollback
ensure
     # 断开与服务器的连接
     dbh.disconnect if dbh
end

执行事务

事务是一种确保交易一致性的机制。事务应具有下列四种属性：

  • 原子性（Atomicity）：事务的原子性指的是，事务中包含的程序作为数据库的逻辑工作单位，它所做的对数据修改操作要么全部执行，要么完全不执行。
  • 一致性（Consistency）：事务的一致性指的是在一个事务执行之前和执行之后数据库都必须处于一致性状态。假如数据库的状态满足所有的完整性约束，就说该数据库是一致的。
  • 隔离性（Isolation）：事务的隔离性指并发的事务是相互隔离的，即一个事务内部的操作及正在操作的数据必须封锁起来，不被其它企图进行修改的事务看到。
  • 持久性（Durability）：事务的持久性意味着当系统或介质发生故障时，确保已提交事务的更新不能丢失。即一旦一个事务提交，它对数据库中数据的改变应该是永久性的，耐得住任何数据库系统故障。持久性通过数据库备份和恢复来保证。

DBI 提供了两种执行事务的方法。一种是commit或rollback方法，用于提交或回滚事务。还有一种是transaction方法，可用于实现事务。接下来我们来介绍这两种简单的实现事务的方法：

方法 I

第一种方法使用 DBI 的 commit 和 rollback 方法来显式地提交或取消事务：

示例：

     dbh['AutoCommit'] = false # 设置自动提交为 false.
begin
  dbh.do("UPDATE EMPLOYEE SET AGE = AGE+1
          WHERE FIRST_NAME = 'John'")
  dbh.do("UPDATE EMPLOYEE SET AGE = AGE+1
          WHERE FIRST_NAME = 'Zara'")
  dbh.commit
rescue
  puts "transaction failed"
  dbh.rollback
end
dbh['AutoCommit'] = true

方法 II

第二种方法使用 transaction 方法。这个方法相对简单些，因为它需要一个包含构成事务语句的代码块。transaction 方法执行块，然后根据块是否执行成功，自动调用 commit 或 rollback：

示例：

     dbh['AutoCommit'] = false # 设置自动提交为 false
dbh.transaction do |dbh|
  dbh.do("UPDATE EMPLOYEE SET AGE = AGE+1
          WHERE FIRST_NAME = 'John'")
  dbh.do("UPDATE EMPLOYEE SET AGE = AGE+1
          WHERE FIRST_NAME = 'Zara'")
end
dbh['AutoCommit'] = true

COMMIT 操作

Commit 是一种标识数据库已完成更改的操作，在这个操作后，所有的更改都不可恢复。

下面是一个调用commit方法的简单实例。

dbh.commit

ROLLBACK 操作

如果您不满意某个或某几个更改，您想要完全恢复这些更改，则使用rollback方法。

下面是一个调用rollback方法的简单实例。

dbh.rollback

设置事务模式

我们修改事务的默认工作模式，有两种方式，在连接时设置和在连接后设置：

连接时设置：

DBI.connect("dbi:STACI:#{$dbname}", $dbuser, $dbpass, 'AutoCommit' => false)

连接后设置：

dbh['AutoCommit'] = true/false

断开数据库

如需断开数据库连接，请使用 disconnect API。

dbh.disconnect

如果用户通过 disconnect 方法关闭了数据库连接，DBI 会回滚所有未完成的事务。但是，不需要依赖于任何 DBI 的实现细节，您的应用程序就能很好地显式调用 commit 或 rollback。

处理错误

有许多不同的错误来源。比如在执行 SQL 语句时的语法错误，或者是连接失败，又或者是对一个已经取消的或完成的语句句柄调用 fetch 方法。

如果某个 DBI 方法失败，DBI 会抛出异常。DBI 方法会抛出任何类型的异常，但是最重要的两种异常类是DBI::InterfaceError和?DBI::DatabaseError。

这些类的 Exception 对象有err、errstr和state三种属性，分表代表了错误号、一个描述性的错误字符串和一个标准的错误代码。属性具体说明如下：

err：返回所发生的错误的整数表示法，如果 DBD 不支持则返回?nil。例如，Oracle DBD 返回ORA-XXXX错误消息的数字部分。

errstr：返回所发生的错误的字符串表示法。

state：返回所发生的错误的 SQLSTATE 代码。SQLSTATE 是五字符长度的字符串。大多数的 DBD 并不支持它，所以会返回 nil。

在上面的实例中您已经看过下面的代码：

rescue DBI::DatabaseError => e
     puts "An error occurred"
     puts "Error code:    #{e.err}"
     puts "Error message: #{e.errstr}"
     dbh.rollback
ensure
     # 断开与服务器的连接
     dbh.disconnect if dbh
end

为了获取脚本执行时有关脚本执行内容的调试信息，您可以启用跟踪。为此，您必须首先下载 dbi/trace 模块，然后调用控制跟踪模式和输出目的地的?trace?方法：

require "dbi/trace"
..............

trace(mode, destination)

mode 的值可以是 0（off）、1、2 或 3，destination 的值应该是一个 IO 对象。默认值分别是 2 和 STDERR。

方法的代码块

有一些创建句柄的方法。这些方法通过代码块调用。使用带有方法的代码块的优点是，它们为代码块提供了句柄作为参数，当块终止时会自动清除句柄。下面是一些实例，有助于理解这个概念。

DBI.connect ：该方法生成一个数据库句柄，建议在块的末尾调用 disconnect 来断开数据库。

dbh.prepare ：该方法生成一个语句句柄，建议在块的末尾调用 finish。在块内，您必须调用 execute 方法来执行语句。

dbh.execute ：该方法与 dbh.prepare 类似，但是 dbh.execute 不需要在块内调用 execute 方法。语句句柄会自动执行。

实例 1:DBI.connect 可带有一个代码块，向它传递数据库句柄，且会在块的末尾自动断开句柄。

dbh = DBI.connect("DBI:STACI:localhost:2003/OSRDB",
                  "SYSDBA", "szoscar55") do |dbh|

实例 2:dbh.prepare 可带有一个代码块，向它传递语句句柄，且会在块的末尾自动调用 finish。

dbh.prepare("list database") do |sth|
       sth.execute
       puts "Databases: " + sth.fetch_all.join(", ")
end

实例 3:dbh.execute 可带有一个代码块，向它传递语句句柄，且会在块的末尾自动调用 finish。

dbh.execute("list database") do |sth|
   puts "Databases: " + sth.fetch_all.join(", ")
end

DBI transaction 方法也可带有一个代码块，这在上面的章节中已经讲解过了。

DBI Read 操作

DBI 提供了一些从数据库获取记录的不同方法。假设dbh是一个数据库句柄，sth是一个语句句柄：

db.select_one

db.select_one( stmt, *bindvars ) => aRow | nil

执行带有bindvars绑定在参数标记前的stmt语句。返回第一行，如果结果集为空则返回nil。

db.select_all

db.select_all( stmt, *bindvars ) => [aRow, ...] | nil

db.select_all( stmt, *bindvars ){ |aRow| aBlock }

执行带有bindvars绑定在参数标记前的stmt语句。调用不带有块的该方法，返回一个包含所有行的数组。如果给出了一个块，则会为每行调用该方法。

sth.fetch

sth.fetch => aRow | nil

返回下一行。如果在结果中没有下一行，则返回nil。

sth.fetch { |aRow| aBlock }

为结果集中剩余的行调用给定的块。

sth.fetch_all

sth.fetch_all => [aRow, ...]

返回保存在数组中的结果集的所有剩余的行。

sth.fetch_many

sth.fetch_many( count ) => [aRow, ...]

返回保存在 [aRow, ...] 数组中的往下第count行。

sth.fetch_scroll

sth.fetch_scroll( direction, offset=1 ) => aRow | nil

返回direction参数和offset指定的行。除了 SQL_FETCH_ABSOLUTE 和 SQL_FETCH_RELATIVE，其他方法都会丢弃参数offset。direction参数可能的值，请查看下面的表格。

sth.column_names

sth.column_names => anArray

返回列的名称。

column_info

column_info => [ aColumnInfo, ... ]

返回 DBI::ColumnInfo 对象的数组。每个对象存储有关某个列的信息，并包含该列的名称、类型、精度等其他更多的信息。

sth.rows

sth.rows => rpc

返回执行语句处理的行数Count，如果不存在则返回nil。

sth.fetchable?

sth.fetchable? => true | false

如果可能获取行，则返回true，否则返回?false。

sth.cancel

释放结果集所占有的资源。在调用该方法后，您就不能在获取行了，除非再次调用execute。

sth.finish

释放准备语句所占有的资源。在调用该方法后，您就不能在该对象上调用其他进一步操作的方法了。