Writing MySQL-Based Programs - MySQL - page 73

row.size tells you the number of columns in the result set.
fetch can also be used as an iterator that returns each row in turn:
sth . fetch do | row |
printf "id: %s, name: %s, cats: %s \n " , row [ 0 ] , row [ 1 ] , row [ 2 ]
end
sth . finish
In iterator context (such as just shown), the each method is a synonym for fetch .
The fetch method returns DBI::Row objects. Column values within the row are acces‐
sible by position, beginning with 0, as just shown, or by name:
sth . fetch do | row |
printf "id: %s, name: %s, cats: %s \n " ,
row [ "id" ] , row [ "name" ] , row [ "cats" ]
end
sth . finish
To fetch all rows at once, use fetch_all , which returns an array of DBI::Row objects:
sth = dbh . execute ( "SELECT id, name, cats FROM profile" )
rows = sth . fetch_all
sth . finish
rows . each do | row |
printf "id: %s, name: %s, cats: %s \n " ,
row [ "id" ] , row [ "name" ] , row [ "cats" ]
end
To fetch each row as a hash keyed on column names, use the fetch_hash method. It
can be called in a loop or used as an iterator. The following example shows the iterator
approach:
sth . fetch_hash do | row |
printf "id: %s, name: %s, cats: %s \n " ,
row [ "id" ] , row [ "name" ] , row [ "cats" ]
end
sth . finish
The preceding examples invoke execute to get a statement handle, then invoke fin
ish when that handle is no longer needed. If instead you invoke execute with a code
block, it passes the statement handle to the block and invokes finish on that handle
implicitly:
dbh . execute ( "SELECT id, name, cats FROM profile" ) do | sth |
sth . fetch do | row |
printf "id: %s, name: %s, cats: %s \n " , row [ 0 ] , row [ 1 ] , row [ 2 ]
end
end
Ruby DBI has some high-level database-handle methods for executing statements that
produce result sets: