YouTip

DBI provides several different methods for fetching records from a database. Assuming **dbh** is a database handle and **sth** is a statement handle: | No. | Method & Description | | --- | --- | | 1 | **db.select_one( stmt, *bindvars ) => aRow | nil** Executes the _stmt_ statement with _bindvars_ bound to the parameter markers. 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 the _stmt_ statement with _bindvars_ bound to the parameter markers. When called without a block, returns an array containing all rows. If a block is given, it is called for each row. | | 3 | **sth.fetch => aRow | nil** Returns the _next row_. Returns _nil_ if there is no next row in the result. | | 4 | **sth.fetch { |aRow| aBlock }** Calls the given block for each of the remaining rows in the result set. | | 5 | **sth.fetch_all => [aRow, ...]** Returns all remaining rows of the result set stored in an array. | | 6 | **sth.fetch_many( count ) => [aRow, ...]** Returns the next _count_ rows stored in an [aRow, ...] array. | | 7 | **sth.fetch_scroll( direction, offset=1 ) => aRow | nil** Returns the row specified by the _direction_ parameter and _offset_. All methods except SQL_FETCH_ABSOLUTE and SQL_FETCH_RELATIVE ignore the _offset_ parameter. For possible values of the _direction_ parameter, see the table below. | | 8 | **sth.column_names => anArray** Returns the column names. | | 9 | **column_info => [ aColumnInfo, ... ]** Returns an array of DBI::ColumnInfo objects. Each object stores information about a column and contains the column's name, type, precision, and other details. | | 10 | **sth.rows => rpc** Returns the row count _Count_ processed by the executed statement, or _nil_ if not available. | | 11 | **sth.fetchable? => true | false** Returns _true_ if rows can be fetched, otherwise returns _false_. | | 12 | **sth.cancel** Releases the resources held by the result set. After calling this method, you cannot fetch any more rows unless you call _execute_ again. | | 13 | **sth.finish** Releases the resources held by the prepared statement. After calling this method, you cannot call any further methods on this object. | ## The direction Parameter The following values can be used for the direction parameter of the _fetch_scroll_ method: | Constant | Description | | --- | --- | | DBI::SQL_FETCH_FIRST | Fetch the first row. | | DBI::SQL_FETCH_LAST | Fetch the last row. | | DBI::SQL_FETCH_NEXT | Fetch the next row. | | DBI::SQL_FETCH_PRIOR | Fetch the previous row. | | DBI::SQL_FETCH_ABSOLUTE | Fetch the row at the specified offset. | | DBI::SQL_FETCH_RELATIVE | Fetch the row at the specified offset from the current row. | ## Example The following example demonstrates how to fetch metadata for a statement. Assume we have an EMPLOYEE table. #!/usr/bin/ruby -wrequire "dbi"begin # Connect to the 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: %dn", sth.rows else puts "Statement has a result set" rows = sth.fetch_all printf "Number of rows: %dn", rows.size printf "Number of columns: %dn", sth.column_names.size sth.column_info.each_with_index do |info, i| printf "--- Column %d (%s) ---n", i, info printf "sql_type: %sn", info printf "type_name: %sn", info printf "precision: %sn", info printf "scale: %sn", info printf "nullable: %sn", info printf "indexed: %sn", info printf "primary: %sn", info printf "unique: %sn", info printf "mysql_type: %sn", info printf "mysql_type_name: %sn", info printf "mysql_length: %sn", info printf "mysql_max_length: %sn", info printf "mysql_flags: %sn", info end end sth.finish rescue DBI::DatabaseError => e puts "An error occurred" puts "Error code: #{e.err}" puts "Error message: #{e.errstr}"ensure # Disconnect from the server dbh.disconnect if dbh end This will produce the following result: Statement has a result setNumber of rows: 5Number 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