After you have your database connection, then you want to run queries. So you create a Query object using Database::query(). A query object is bound to that database and can exec SQL statements over and over. Each time you call Query::exec(), it populates itself with the result set. You can then either iterate over that set or pick out specific records by index. The follows example shows general usage:

require 'jw/dbi'

db = JW::DBI::Database.new()

db.open(db: 'foods.db', driver: 'QSQLITE')

query = db.query()

# 1. Iterate over results

# The Ruby way:
query.exec('select * from foods').each do | row |
  row.each do |key, value|
    puts "#{key}: #{value}"
  end
end

# The C programmer's way:
do
  puts query.record['name']
while query.next()

# 2. Set Navigation

query.exec('select * from foods')

# Move cursor to end of result set
query.moveLast()

# Get the row
row = query.row()

# Get the record index
puts query.at()

# Move back:
query.moveFirst()

# One step forward, two steps back
query.moveNext()
query.movePrevious()
assert query.movePrevious() == false

# Move to the 77th record:
if query.seek(77)
  puts 'It exists'
  assert query.record.isNull('name') == false
end

# Could just fetch 77th as follows:
record = query[77]
assert record.isEmpty?() == false

Notice that there is not a Database::exec() method. This would be convenient, but based on the design of QSql along with the nature of Ruby, this could be problematic. The reason being that Query objects are what assocate database resources with them (e.g. result sets, locks, etc.). A Database::exec() implementation would essentially create a Query object, execute the SQL through it, and pass it back. The problem is that this would quickly create a usage pattern of Database.exec() where the created query object would be created and used in the background over and over. In this case, resources (e.g. locks) would not get freed until Ruby's GC sweep. This is dangerous in that you don't know when it would take place and therefore you could end up with deadlocks without knowing why. And UPDATE to SQLite this way is a perfect example.

Therefore, the primary way to work with the database is by directly using a Query instance. In this case, when you execute statements over and over, you are using the same query instance and resources are automatically freed on each call to Query::exec(). Furthermore, when in doubt, you are also encouraged to explicitly free resources using Query::clear().

Thus, you really only use the database object for anything other than connecting to the database. Everything else is via a query object derived from it.