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

What Will I Learn?

<p dir="auto">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 - <ul> <li>Processing queries that do not return a result <li>Neutralization, fictitious parameters and links <li>Metadata results <h4>Requirements <p dir="auto">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 <code>sudo apt-get install libdbi-ruby. Many of these drivers require the installation of other software. <p dir="auto">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. <h4>Difficulty <ul> <li>Intermediate <h4>Tutorial Contents <hr /> <h4>Processing queries that do not return a result:- <hr /> <p dir="auto">Queries such as <code>SELECT or <code>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. <p dir="auto">One way to do this is to call <code>prepare to generate a reference to the query. Use this reference to run the query and retrieve its results and call <code>finish to release the result set: <pre><code>sth = dbh.prepare (statement) sth.execute ... recovery of lines ... sth.finish <p dir="auto">It is also possible to pass the request directly to <code>execute and to dispense with the call to <code>prepare: <pre><code>sth = dbh.execute(statement) ... recovery of lines ... sth.finish <p dir="auto">There are many ways to retrieve the result after running a query. You can call <code>fetch alone in a loop until it returns nil: <pre><code>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 <p dir="auto"><code>fetch can also be used as an iterator, in which case it behaves as each. The two recovery loops of the lines are identical: <pre><code>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 <pre><code>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 <p dir="auto"><code>fetch and <code>each produce objects <code>DBI::Row that have some methods to access their content:<br /> Values can be accessed by index or name using table notation: <pre><code>val = row [2] val = row ["size"] <p dir="auto">You can use an object that represents a line with methods <code>by_index or <code>by_field to access values by their index or name: <pre><code>val = row.by_index (2) val = row.by_field ("size") <p dir="auto">An iteration method,, <code>each_with_name returns the index of each of the columns accompanied by the name of the column: <pre><code>ow.each_with_name do | val, name | printf "% s:% s,", name, val.to_s end print "\ n <p dir="auto">Objects <code>DBI :: Row have a <code>column_names that returns an array containing the name of each column. <code>field_names is an alias of <code>column_names. <p dir="auto">Other methods of retrieving lines include <code>fetch_array and <code>fetch_hash do not return objects <code>DBI::Row. Instead, they return the next line as a table or an associative array, or <code>nil if there are no more rows. The associative arrays returned by the method <code>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 <code>fetch_hash: <pre><code>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 <pre><code>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 <p dir="auto">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: <pre><code>row = dbh.select_one(statement) rows = dbh.select_all(statement) <p dir="auto"><code>select_one executes a query and returns the first line or nil if the query returns nothing. <code>select_all returns an array of <code>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. <p dir="auto">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 <code>id, nom and <code>taille the table persons are returned as objects of type <code>Fixnum, String and <code>Float. However, you should know that if the value of a column is <code>NULL, it will be represented by <code>nil in the result set and will have as type <code>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. <hr /> <h4>Neutralization, fictitious parameters and links:- <hr /> <p dir="auto">Ruby DBI includes a placeholder mechanism that lets you avoid including literal values ​​in a query. Instead, you can use the special placeholder <code>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 <code>NULL; pass the value <code>nil as data and is placed in the query as a value <code>NULL without quotation marks. <p dir="auto">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 <code>INSERT, use the placeholder <code>?, without quotation marks, and pass the value as an additional parameter to the method <code>do, after the query: <pre><code>bh.do ("INSERT INTO people (id, name, size) VALUES (?,?,?)", nil, "Na'il", 193) <p dir="auto">The resulting query produced by <code>do and sent to the server looks like this:<br /> <code>INSERT INTO people (id, name, size) VALUES (NULL, 'Na \' il ', 193) <p dir="auto">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 <code>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 <code>INSERT prepared query for each row: <pre><code># 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 <p dir="auto">Preparing the query beforehand and executing it many times in a loop is more efficient than invoking <code>do every step of the loop (which calls <code>prepare and <code>execute at each iteration). The difference is more important for database engines that prepare a query plan and reuse it on every call to <code>execute. MySQL does not do that; Oracle though. <p dir="auto">To use placeholders in queries <code>SELECT, the correct strategy depends on whether you prepare the query beforehand: <p dir="auto">If you invoke <code>prepare for a reference to the query, use this reference to call <code>execute and pass it the values ​​of the data to link to the placeholders. <pre><code>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 <p dir="auto">If you do not use prepare, the first parameter to <code>execute is the query and the following are the values ​​of the data: <pre><code>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 <p dir="auto">Other drivers may allow or require you to represent dummy parameters otherwise. For example, you can write the placeholders as <code>:nom or <code>:n to specify them as named or subscripted. Consult the driver documentation that you want to use. <p dir="auto">The method <code>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 <code>people.txt and convert it into a set of queries <code>INSERT that can be executed by a program such as command line mysql , do the following: <pre><code># 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 <hr /> <h4>Metadata results:- <hr /> <p dir="auto">For queries that do not return any data, such as <code>INSERT or <code>DELETE, the method <code>do returns the number of rows processed. <p dir="auto">For queries that return rows, such as <code>SELECT, you can use the reference to the query after invoking the method <code>execute to retrieve the number of rows and columns or information about each column in the result: <p dir="auto">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 <code>sth.column_names.size.<br /> The method <code>column_info returns information about each column.<br /> This script shows how to get the metadata of a query: <pre><code>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 <h4>Curriculum <p dir="auto">Here is a related tutorial which help to understand this whole programming procedure better - <ul> <li><a href="https://utopian.io/utopian-io/@meblogger/how-to-write-scripts-based-on-ruby-dbi-issue-1" target="_blank" rel="nofollow noreferrer noopener" title="This link will take you away from hive.blog" class="external_link">How to write scripts based on Ruby DBI -Issue#1 <p dir="auto"><br /><hr /><em>Posted on <a href="https://utopian.io/utopian-io/@meblogger/how-to-write-scripts-based-on-ruby-dbi-issue-2" target="_blank" rel="nofollow noreferrer noopener" title="This link will take you away from hive.blog" class="external_link">Utopian.io - Rewarding Open Source Contributors<hr /><p>