How to write scripts based on Ruby DBI -Issue#2

What Will I Learn?

The Ruby DBI module provides Ruby scripts with a database-independent interface, similar to the DBI Perl module. This tutorial describes how to write scripts based on Ruby DBI. In this issue we will provide tutorial about -

• Processing queries that do not return a result
• Neutralization, fictitious parameters and links
• Metadata results

Requirements

The Ruby DBI module includes code that implements the DBI general layer and a set of database-pecific drivers. You can probably install the Ruby DBI module using your distribution's package manager . For example, under Ubuntu, we can install this module simply by typing sudo apt-get install libdbi-ruby. Many of these drivers require the installation of other software.

For example, the MySQL database driver is written in Ruby and depends on the MySQL Ruby module, which is itself written in C and provides a bridge to the MySQL C client API. This means that if you want to write DBI scripts to access MySQL databases, you will need to install the Ruby MySQL module as well as the C API.

Difficulty

• Intermediate

Tutorial Contents

---

Processing queries that do not return a result:-

---

Queries such as SELECT or SHOW return lines. To handle such requests, send them to the server for execution, retrieve the rows of the generated result, and release the result set.

One way to do this is to call prepare to generate a reference to the query. Use this reference to run the query and retrieve its results and call finish to release the result set:

sth = dbh.prepare (statement)
sth.execute
... recovery of lines ...
sth.finish

It is also possible to pass the request directly to execute and to dispense with the call to prepare:

sth = dbh.execute(statement)
... recovery of lines ...
sth.finish

There are many ways to retrieve the result after running a query. You can call fetch alone in a loop until it returns nil:

sth = dbh.execute ("SELECT * FROM people")
while row = sth.fetch do
  printf "ID:% d, Name:% s, Size:% .1f \ n", row [0], row [1], row [2]
end
sth.finish

fetch can also be used as an iterator, in which case it behaves as each. The two recovery loops of the lines are identical:

sth = dbh.execute ("SELECT * FROM people")
sth.fetch do | row |
  printf "ID:% d, Name:% s, Size:% .1f \ n", row [0], row [1], row [2]
end
sth.finish

sth = dbh.execute ("SELECT * FROM people")
sth.each do | row |
  printf "ID:% d, Name:% s, Size:% .1f \ n", row [0], row [1], row [2]
end
sth.finish

fetch and each produce objects DBI::Row that have some methods to access their content:
Values can be accessed by index or name using table notation:

val = row [2]
val = row ["size"]

You can use an object that represents a line with methods by_index or by_field to access values by their index or name:

val = row.by_index (2)
val = row.by_field ("size")

An iteration method,, each_with_name returns the index of each of the columns accompanied by the name of the column:

ow.each_with_name do | val, name |
  printf "% s:% s,", name, val.to_s
end
print "\ n

Objects DBI :: Row have a column_names that returns an array containing the name of each column. field_names is an alias of column_names.

Other methods of retrieving lines include fetch_array and fetch_hash do not return objects DBI::Row. Instead, they return the next line as a table or an associative array, or nil if there are no more rows. The associative arrays returned by the method fetch_hash have the names of the columns and the values of the columns as values. Each of these methods can be invoked alone or as an iterator. The following examples show it for the method fetch_hash:

sth = dbh.execute ("SELECT * FROM people")
while row = sth.fetch_hash do
  printf "ID:% d, Name:% s, Size:% .1f \ n",
         row ["id"], row ["name"], row ["size"]
end
sth.finish

sth = dbh.execute ("SELECT * FROM people")
sth.fetch_hash do | row |
  printf "ID:% d, Name:% s, Size:% .1f \ n",
         row ["id"], row ["name"], row ["size"]
end
sth.finish

You can avoid the run-retrieve-end sequence by using the database-acting methods that do all the work for you and return the results:

row = dbh.select_one(statement)
rows = dbh.select_all(statement)

select_one executes a query and returns the first line or nil if the query returns nothing. select_all returns an array of DBI :: Row objects. You can access the contents of these objects as we saw earlier. The array is empty if the query does not return rows.

The MySQL driver examines the metadata in the result set and uses it to cast the row values to the corresponding Ruby type. This means, for example, that the values for id, nom and taille the table persons are returned as objects of type Fixnum, String and Float. However, you should know that if the value of a column is NULL, it will be represented by nil in the result set and will have as type NilClass. Also note that this cast behavior does not appear to be mandatory for the DBI specification and may not be done by all drivers.

---

Neutralization, fictitious parameters and links:-

---

Ruby DBI includes a placeholder mechanism that lets you avoid including literal values ​​in a query. Instead, you can use the special placeholder f in the query to indicate where the data values ​​are going. When running the query, you supply values ​​to bind to the placeholders. DBI replaces fictitious values ​​in the query with values, enclosing quoting strings, and overriding special characters as needed. This makes it easy to build queries without having to worry about whether the values ​​contain special characters and without having to surround the strings with quotation marks yourself. This mechanism of the fictitious parameters also manages the values ​​correctly NULL; pass the value nil as data and is placed in the query as a value NULL without quotation marks.

The following example illustrates how it works. Suppose you want to add a new line to the table peoples for someone called Na'il (a name that has a quotation mark) and is 1.93 m tall. To indicate where the value in the query is going INSERT, use the placeholder ?, without quotation marks, and pass the value as an additional parameter to the method do, after the query:

bh.do ("INSERT INTO people (id, name, size) VALUES (?,?,?)",
       nil, "Na'il", 193)

The resulting query produced by do and sent to the server looks like this:
INSERT INTO people (id, name, size) VALUES (NULL, 'Na \' il ', 193)

If you want to execute a query more than once, you can first prepare it to get a reference to the query, and then execute it by passing the values ​​as parameters. Suppose a file named people.txt contains tab-delimited name / size pairs to insert into the table people. The following example reads the file to obtain row data and executes a INSERT prepared query for each row:

# preparation of the request for use in the loop
sth = dbh.prepare ("INSERT INTO people (id, name, size) VALUES (?,?,?)")
# reads each line of the file, separates the values ​​and injects into the
# database
File.open ("people.txt", "r") do | f |
  f.each_line do | line |
    name, height = line.chomp.split ("\ t")
    sth.execute (nil, name, size)
  end
end

Preparing the query beforehand and executing it many times in a loop is more efficient than invoking do every step of the loop (which calls prepare and execute at each iteration). The difference is more important for database engines that prepare a query plan and reuse it on every call to execute. MySQL does not do that; Oracle though.

To use placeholders in queries SELECT, the correct strategy depends on whether you prepare the query beforehand:

If you invoke prepare for a reference to the query, use this reference to call execute and pass it the values ​​of the data to link to the placeholders.

sth = dbh.prepare ("SELECT * FROM people WHERE name =?")
sth.execute ( "Na'il")
sth.fetch do | row |
  printf "ID:% d, Name:% s, Size:% .1f \ n", row [0], row [1], row [2]
end
sth.finish

If you do not use prepare, the first parameter to execute is the query and the following are the values ​​of the data:

sth = dbh.execute ("SELECT * FROM people WHERE name =?", "Na'il")
sth.fetch do | row |
  printf "ID:% d, Name:% s, Size:% .1f \ n", row [0], row [1], row [2]
end
sth.finish

Other drivers may allow or require you to represent dummy parameters otherwise. For example, you can write the placeholders as :nom or :n to specify them as named or subscripted. Consult the driver documentation that you want to use.

The method quote cites and retrieves a value from the data and returns the result. This can be useful for generating queries to run in other programs. For example, if you want to read the file people.txt and convert it into a set of queries INSERT that can be executed by a program such as command line mysql , do the following:

# reads each line of file, separates the values ​​and executes a
# INSERT query
File.open ("people.txt", "r") do | f |
  f.each_line do | line |
    name, height = line.chomp.split ("\ t")
    printf "INSERT INTO people (id, name, size) VALUES (% s,% s,% s); \ n",
           dbh.quote (nil), dbh.quote (name), dbh.quote (height)
  end
end

---

Metadata results:-

---

For queries that do not return any data, such as INSERT or DELETE, the method do returns the number of rows processed.

For queries that return rows, such as SELECT, you can use the reference to the query after invoking the method execute to retrieve the number of rows and columns or information about each column in the result:

The number of rows and columns is not directly available. To find the number of rows, you can either count them as you retrieve them or extract them into a data structure and then count the number of items it contains. To determine the number of columns, you can count the number of column names, available with sth.column_names.size.
The method column_info returns information about each column.
This script shows how to get the metadata of a query:

sth = dbh.execute (stmt)
puts "Query: # {stmt}"
if sth.column_names.size == 0 then
  puts "The query does not return a result"
  printf "Number of rows affected:% d \ n", sth.rows
else
  puts "The query returns a result"
  rows = sth.fetch_all
  printf "Number of lines:% 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

Curriculum

Here is a related tutorial which help to understand this whole programming procedure better -

• How to write scripts based on Ruby DBI -Issue#1

---

Posted on Utopian.io - Rewarding Open Source Contributors

---