• Establishing a Sessions
• Executing Queries
• Commits and Rollbacks
• Catching Errors
• Bind Variables
• Re-Binding and Re-Executing
• Accessing Fields in the Result Set
• Concurrent Statements
• Getting Column Information

To use SQL Relay, you have to identify the connection that you intend to use.

require 'dbi'

db=DBI.connect("DBI:SQLRelay:host=localhost;port=9000;socket=/tmp/mysock.socket","myuser","mypassword")

... execute some queries ...

After calling connect() and prepare(), a session is established when the first execute() is run.

For the duration of the session, the client stays connected to a database connection daemon. While one client is connected, no other client can connect. Care should be taken to minimize the length of a session.

If you're using a transactional database, ending a session has a catch. Database connection daemons can be configured to send either a commit or rollback at the end of a session if DML queries were executed during the session with no commit or rollback. Program accordingly.

Call prepare() and execute() to run a query.

require 'dbi'

db=DBI.connect("DBI:SQLRelay:host=localhost;port=9000;socket=/tmp/mysock.socket","myuser","mypassword")

stmt=db.prepare("select * from mytable")

stmt.execute()

... process the result set ...

If you need to execute a commit or rollback, you should use the commit() and rollback() methods rather than sending a "commit" or "rollback" query. There are two reasons for this. First, it's much more efficient to call the methods. Second, if you're writing code that can run on transactional or non-transactional databases, some non-transactional databases will throw errors if they receive a "commit" or "rollback" query, but by calling the commit() and rollback() methods you instruct the database connection daemon to call the commit and rollback API methods for that database rather than issuing them as queries. If the API's have no commit or rollback methods, the calls do nothing and the database throws no error. This is especially important when using SQL Relay with ODBC.

You can also turn Autocommit on or off by setting the AutoCommit attribute of the database handle.

The following command turns Autocommit on.

db["AutoCommit"]=true

The following command turns Autocommit off.

db["AutoCommit"]=false

When Autocommit is on, the database performs a commit after each successful DML or DDL query. When Autocommit is off, the database commits when the client instructs it to, or (by default) when a client disconnects. For databases that don't support Autocommit, setting the AutoCommit attribute has no effect.

If your call to execute() raises an exception, the query failed. You can find out why by catching the exception.

require 'dbi'

begin
        db=DBI.connect("DBI:SQLRelay:host=localhost;port=9000;socket=/tmp/mysock.socket","myuser","mypassword")

        stmt=db.prepare("select * from mytable")

        stmt.execute()

rescue DBI::ProgrammingError => error
        print error
        print "\n"
end

Programs rarely execute fixed queries. More often than not, some part of the query is dynamically generated. The Ruby DBI API provides the bind_param method for using bind variables in those queries.

For a detailed discussion of binds, see this document.

require 'dbi'

db=DBI.connect("DBI:SQLRelay:host=localhost;port=9000;socket=/tmp/mysock.socket","myuser","mypassword")

stmt=db.prepare("select * from mytable where column1>:val1 and column2=:val2 and column3<val3")

stmt.bind_param("val1",1,false)
stmt.bind_param("val2","hello",false)
stmt.bind_param("val3",50.546,false)

stmt.execute()

... process the result set ...

A feature of the prepare/bind/execute paradigm is the ability to prepare, bind and execute a query once, then re-bind and re-execute the query over and over without re-preparing it. If your backend database natively supports this paradigm, you can reap a substantial performance improvement.

require 'dbi'

db=DBI.connect("DBI:SQLRelay:host=localhost;port=9000;socket=/tmp/mysock.socket","myuser","mypassword")

stmt=db.prepare("select * from mytable where column1>:val1 and column2=:val2 and column3<val3")

stmt.bind_param("val1",1,false)
stmt.bind_param("val2","hello",false)
stmt.bind_param("val3",1.1,false)

stmt.execute()

... process the result set ...

stmt.bind_param("val1",2,false)
stmt.bind_param("val2","hi",false)
stmt.bind_param("val3",2.22,false)

stmt.execute()

... process the result set ...

stmt.bind_param("val1",3,false)
stmt.bind_param("val2","bye",false)
stmt.bind_param("val3",3.333,false)

stmt.execute()

... process the result set ...

The fetch(), fetch_many() and fetch_all() methods are useful for processing result sets. fetch() returns a list of values. fetch_many() and fetch_all() each return an Array of rows where each row is an Array of values.

The rows() method gives the number of rows in the result set of a select query.

require 'dbi'

db=DBI.connect("DBI:SQLRelay:host=localhost;port=9000;socket=/tmp/mysock.socket","myuser","mypassword")

stmt=db.prepare("select * from mytable")

stmt.execute()

print "rowcount: "+stmt.rows().to_s+"\n"

print "the first row:\n"
for i in cur.fetch()
        print i+","
end
print "\n\n"

print "the next three rows:\n"
for i in cur.fetch_many()
        for j in i
                print j+","
        end
        print "\n"
end
print "\n"

print "the rest of the rows:\n"
for i in cur.fetch_all()
        for j in i
                print j+","
        end
        print "\n"
end

The fetch_scroll() method provides arbitrary access to the result set. You can use it to skip forward or backward.

require 'dbi'

db=DBI.connect("DBI:SQLRelay:host=localhost;port=9000;socket=/tmp/mysock.socket","myuser","mypassword")

stmt=db.prepare("select * from mytable")

stmt.execute()

print "the first row:\n"
for i in cur.fetch_scroll(DBD:SQL_FETCH_FIRST)
        print i+","
end
print "\n\n"

print "the last row:\n"
for i in cur.fetch_scroll(DBD:SQL_FETCH_LAST)
        print i+","
end
print "\n\n"

print "the second to last row:\n"
for i in cur.fetch_scroll(DBD:SQL_FETCH_PRIOR)
        print i+","
end
print "\n\n"

print "the last row again:\n"
for i in cur.fetch_scroll(DBD:SQL_FETCH_NEXT)
        print i+","
end
print "\n\n"

print "the first row again:\n"
for i in cur.fetch_scroll(DBD:SQL_FETCH_ABSOLUTE,0)
        print i+","
end
print "\n\n"

print "the 4th row:\n"
for i in cur.fetch_scroll(DBD:SQL_FETCH_RELATIVE,3)
        print i+","
end
print "\n\n"

It is possible to execute queries while processing the result set of another query. You can select rows from a table in one query, then iterate through it's result set, inserting rows into another table, using only 1 database connection for both operations.

For example:

require 'dbi'

db=DBI.connect("DBI:SQLRelay:host=localhost;port=9000;socket=/tmp/mysock.socket","myuser","mypassword")

stmt1=db.prepare("select * from mytable")
stmt2=db.prepare("insert into my_other_table values (:var1,:var2:,var3)")

stmt1.execute()

for i in stmt1.fetch_all():
        stmt2.bind_param("var1",i[0],false)
        stmt2.bind_param("var2",i[1],false)
        stmt2.bind_param("var3",i[2],false)
        stmt2.execute()
end

After executing a query, column information can be retrieved using the column_info() method. column_info() returns an Array of hashes. Each hash contains 'name', 'type_name' and 'precision' keys.

require 'dbi'

db=DBI.connect("DBI:SQLRelay:host=localhost;port=9000;socket=/tmp/mysock.socket","myuser","mypassword")

stmt=db.prepare("select * from mytable")

stmt.execute()

for i in stmt.column_info()
        print "Name:       "+i['name']+"\n"
        print "Type:       "+i['type_name']+"\n"
        print "Length:     "+i['precision']+"\n"
end