Ruby DBI Read operation

DBI provides several different ways to get records from a database. Hypothetical dbh is a database handle sth is a statement handle:

Serial number	Method and description
1	`db.select_one( stmt, *bindvars ) => aRow \| nil` executes a stmt statementwith a bindvars binding before the parameter marker. Returns the first row,or nil if the result set is empty.
2	`db.select_all( stmt, bindvars ) => [aRow, ...] \| nil` `db.select_all( stmt, bindvars ){ \|aRow\| aBlock }` executes a stmt statement with a bindvars binding before the parameter marker. Call the method without a block and return an array containing all rows. If a block is given, the method is called for each row.
3	`sth.fetch => aRow \| nil` Return to the next row. If there is no next linein the result, nil is returned.
4	`sth.fetch { \|aRow\| aBlock }` invokes the given block for the remaining rows in the result set.
5	`sth.fetch_all => [aRow, ...]` Returns all remaining rows of the result set saved in the array.
6	`sth.fetch_many( count ) => [aRow, ...]` Return to save in `[aRow, ...]` , the next count line in the array.
7	`sth.fetch_scroll( direction, offset=1 ) => aRow \| nil` Returns the direction parameter and the row specified by offset. The parameter offset isdiscarded by all methods except SQL_FETCH_ABSOLUTE and SQL_FETCH_RELATIVE. For possible values of the direction parameter, see the table below.
8	`sth.column_names => anArray` Returns the name of the column.
9	`column_info => [ aColumnInfo, ... ]` Return `DBI::ColumnInfo` an arrayof objects. Each object stores information about a column and contains moreinformation about the column’s name, type, precision, and so on.
10	`sth.rows => rpc` Returns the number of rows that executed the statement processing Count, or nil if it does not exist.
11	`sth.fetchable? => true \| false` Returns true if it is possible to get rows, false otherwise.
12	Sth.cancel frees the resources occupied by the result set. After calling this method, you can no longer get the row unless you call execute again.
13	`sth.finish` Release the resources occupied by the preparation statement. After the method is called, you cannot call other methods for further operations on the object.

Direction parameter

The following values can be used for fetch_scroll method of direction parameters:

Constant	Description
DBI::SQL_FETCH_FIRST	Get the first line.
DBI::SQL_FETCH_LAST	Gets the last line.
DBI::SQL_FETCH_NEXT	Gets the next line.
DBI::SQL_FETCH_PRIOR	Gets the previous line.
DBI::SQL_FETCH_ABSOLUTE	Gets the row at the offset at this location.
DBI::SQL_FETCH_RELATIVE	Gets the row from the offset of the current row.

Example

The following example shows how to get the metadata of a statement. Suppose we have EMPLOYEE watch.

#!/usr/bin/ruby -w

require "dbi"

begin
     # Connect to MySQL server
     dbh = DBI.connect("DBI:Mysql:TESTDB:localhost",
                        "testuser", "test123")
     sth = dbh.prepare("SELECT * FROM EMPLOYEE
                        WHERE INCOME > ?")
     sth.execute(1000)
     if sth.column_names.size == 0 then
        puts "Statement has no result set"
        printf "Number of rows affected: %d\n", sth.rows
     else
        puts "Statement has a result set"
        rows = sth.fetch_all
        printf "Number of rows: %d\n", rows.size
        printf "Number of columns: %d\n", sth.column_names.size
        sth.column_info.each_with_index do |info, i|
          printf "--- Column %d (%s) ---\n", i, info["name"]
          printf "sql_type:         %s\n", info["sql_type"]
          printf "type_name:        %s\n", info["type_name"]
          printf "precision:        %s\n", info["precision"]
          printf "scale:            %s\n", info["scale"]
          printf "nullable:         %s\n", info["nullable"]
          printf "indexed:          %s\n", info["indexed"]
          printf "primary:          %s\n", info["primary"]
          printf "unique:           %s\n", info["unique"]
          printf "mysql_type:       %s\n", info["mysql_type"]
          printf "mysql_type_name:  %s\n", info["mysql_type_name"]
          printf "mysql_length:     %s\n", info["mysql_length"]
          printf "mysql_max_length: %s\n", info["mysql_max_length"]
          printf "mysql_flags:      %s\n", info["mysql_flags"]
      end
   end
   sth.finish
rescue DBI::DatabaseError => e
     puts "An error occurred"
     puts "Error code:    #{e.err}"
     puts "Error message: #{e.errstr}"
ensure
     # server disconnect
     dbh.disconnect if dbh
end

This will produce the following results:

Statement has a result set
Number of rows: 5
Number of columns: 5
--- Column 0 (FIRST_NAME) ---
sql_type:         12
type_name:        VARCHAR
precision:        20
scale:            0
nullable:         true
indexed:          false
primary:          false
unique:           false
mysql_type:       254
mysql_type_name:  VARCHAR
mysql_length:     20
mysql_max_length: 4
mysql_flags:      0
--- Column 1 (LAST_NAME) ---
sql_type:         12
type_name:        VARCHAR
precision:        20
scale:            0
nullable:         true
indexed:          false
primary:          false
unique:           false
mysql_type:       254
mysql_type_name:  VARCHAR
mysql_length:     20
mysql_max_length: 5
mysql_flags:      0
--- Column 2 (AGE) ---
sql_type:         4
type_name:        INTEGER
precision:        11
scale:            0
nullable:         true
indexed:          false
primary:          false
unique:           false
mysql_type:       3
mysql_type_name:  INT
mysql_length:     11
mysql_max_length: 2
mysql_flags:      32768
--- Column 3 (SEX) ---
sql_type:         12
type_name:        VARCHAR
precision:        1
scale:            0
nullable:         true
indexed:          false
primary:          false
unique:           false
mysql_type:       254
mysql_type_name:  VARCHAR
mysql_length:     1
mysql_max_length: 1
mysql_flags:      0
--- Column 4 (INCOME) ---
sql_type:         6
type_name:        FLOAT
precision:        12
scale:            31
nullable:         true
indexed:          false
primary:          false
unique:           false
mysql_type:       4
mysql_type_name:  FLOAT
mysql_length:     12
mysql_max_length: 4
mysql_flags:      32768

Ruby DBI Read operation

Ruby DBI Read operation

Direction parameter

Example

Programming Language

More CSharp

More Go

More Kotlin

More Lua

More Perl

More Ruby

More Rust

More Scala

More Swift