- Ruby Basics
- Ruby - Home
- Ruby - Overview
- Ruby - Environment Setup
- Ruby - Syntax
- Ruby - Classes and Objects
- Ruby - Variables
- Ruby - Operators
- Ruby - Comments
- Ruby - IF...ELSE
- Ruby - Loops
- Ruby - Methods
- Ruby - Blocks
- Ruby - Modules
- Ruby - Strings
- Ruby - Arrays
- Ruby - Hashes
- Ruby - Date & Time
- Ruby - Ranges
- Ruby - Iterators
- Ruby - File I/O
- Ruby - Exceptions
- Ruby Advanced
- Ruby - Object Oriented
- Ruby - Regular Expressions
- Ruby - Database Access
- Ruby - Web Applications
- Ruby - Sending Email
- Ruby - Socket Programming
- Ruby - Ruby/XML, XSLT
- Ruby - Web Services
- Ruby - Tk Guide
- Ruby - Ruby/LDAP Tutorial
- Ruby - Multithreading
- Ruby - Built-in Functions
- Ruby - Predefined Variables
- Ruby - Predefined Constants
- Ruby - Associated Tools
- Ruby Useful Resources
- Ruby - Quick Guide
- Ruby - Useful Resources
- Ruby - Discussion
- Ruby - Ruby on Rails Tutorial
Ruby DBI Methods for Read Operation
DBI provides several different methods to fetch records from the database. Assuming dbh is a database handle and sth is a statement handle −
Sr.No. | Methods with Description |
---|---|
1 | db.select_one( stmt, *bindvars ) = > aRow | nil Executes the stmt statement with the bindvars binding beforehand to 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 the bindvars binding beforehand to parameter markers. Calling this method without block returns an array containing all rows. If a block is given, this will be called for each row. |
3 | sth.fetch => aRow | nil Returns the next row. Returns nil if no further rows are in the result-set. |
4 | sth.fetch { |aRow| aBlock } Invokes the given block for the remaining rows of the result-set. |
5 | sth.fetch_all => [aRow, ...] Returns all remaining rows of the result-set collected in an array. |
6 | sth.fetch_many( count ) => [aRow, ...] Returns the next count rows collected in an [aRow, ...] array. |
7 | sth.fetch_scroll( direction, offset=1 ) => aRow | nil Returns the row specified by the direction parameter and offset. Parameter offset is discarded for all but SQL_FETCH_ABSOLUTE and SQL_FETCH_RELATIVE. See a table below for possible values of direction parameter. |
8 | sth.column_names => anArray Returns the names of the columns. |
9 | column_info => [ aColumnInfo, ... ] Returns an array of DBI::ColumnInfo objects. Each object stores information about one column and contains its name, type, precision and more. |
10 | sth.rows => rpc Returns the Row Processed Count of the executed statement or nil if no such exist. |
11 | sth.fetchable? => true | false Returns true if it's possible to fetch rows, otherwise false. |
12 | sth.cancel Frees the resources held by the result-set. After calling this method, it is no longer possible to fetch rows until you again call execute. |
13 | sth.finish Frees the resources held by the prepared statement. After calling this method no further methods can be called onto this object. |
The direction Parameter
Following values could be used for the direction Parameter of the fetch_scroll Method −
Sr.No. | Constant & Description |
---|---|
1 | DBI::SQL_FETCH_FIRST Fetches first row. |
2 | DBI::SQL_FETCH_LAST Fetches last row. |
3 | DBI::SQL_FETCH_NEXT Fetches next row. |
4 | DBI::SQL_FETCH_PRIOR Fetches previous row. |
5 | DBI::SQL_FETCH_ABSOLUTE Fetches row at position offset. |
6 | DBI::SQL_FETCH_RELATIVE Fetches the row that is offset rows away from the current. |
Example
The following example shows how to get the metadata for a statement. Consider the EMPLOYEE table, which we created in the last chapter.
#!/usr/bin/ruby -w require "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: %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 # disconnect from server dbh.disconnect if dbh end
This will produce the following result −
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