6.34. 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.

6.34.1. 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.

6.34.2. 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 