This section provides an overview of how to write Ruby applications that use the DBI driver.
To use the DBI:SQLAnywhere interface from a Ruby application, you must first tell Ruby that you plan to use the Ruby DBI module. To do so, include the following line near the top of the Ruby source file.
require 'dbi'
The DBI module automatically loads the SQLAnywhere database driver (DBD) interface as required.
Generally, you open a single connection to a database and then perform all the required operations through it by executing a sequence of SQL statements. To open a connection, you use the connect function. The return value is a handle to the database connection that you use to perform subsequent operations on that connection.
The call to the connect function takes the general form:
dbh = DBI.connect('DBI:SQLAnywhere:server-name', user-id, password, options)
To demonstrate the connect function, make the iqdemo database, start the database server and iqdemo database before running the sample Ruby scripts.
$IQDIR16/demo mkiqdemo.sh start_iq @iqdemo.cfg iqdemo.db
The following code sample opens and closes a connection to the iqdemo database. The string "myserver" in the example below is the server name.
require 'dbi'
DBI.connect('DBI:SQLAnywhere:myserver', '<user_id>', '<password>') do |dbh|
if dbh.ping
print "Successfully Connected\n"
dbh.disconnect()
end
end
Optionally, you can specify a connection string in place of the server name. For example, in the above script may be altered by replacing the first parameter to the connect function as follows:
require 'dbi'
DBI.connect('DBI:SQLAnywhere:SERVER=myserver;DBN=iqdemo', '<user_id>', '<password>') do |dbh|
if dbh.ping
print "Successfully Connected\n"
dbh.disconnect()
end
end
The user ID and password cannot be specified in the connection string. Ruby DBI will automatically fill in the username and password with defaults if these arguments are omitted, so you should never include a UID or PWD connection parameter in your connection string. If you do, an exception will be thrown.
The following example shows how additional connection parameters can be passed to the connect function as a hash of key/value pairs.
require 'dbi'
DBI.connect('DBI:SQLAnywhere:myserver', '<user_id>', '<password>',
{ :ConnectionName => "RubyDemo",
:DatabaseFile => "iqdemo.db",
:DatabaseName => "iqdemo" }
) do |dbh|
if dbh.ping
print "Successfully Connected\n"
dbh.disconnect()
end
end
Once you have obtained a handle to an open connection, you can access and modify data stored in the database. Perhaps the simplest operation is to retrieve some rows and print them out.
A SQL statement must be executed first. If the statement returns a result set, you use the resulting statement handle to retrieve meta information about the result set and the rows of the result set. The following example obtains the column names from the metadata and displays the column names and values for each row fetched.
require 'dbi'
def db_query( dbh, sql )
sth = dbh.execute(sql)
print "# of Fields: #{sth.column_names.size}\n"
sth.fetch do |row|
print "\n"
sth.column_info.each_with_index do |info, i|
unless info["type_name"] == "LONG VARBINARY"
print "#{info["name"]}=#{row[i]}\n"
end
end
end
sth.finish
end
begin
dbh = DBI.connect('DBI:SQLAnywhere:demo', '<user_id>', '<password>')
db_query(dbh, "SELECT * FROM Products")
rescue DBI::DatabaseError => e
puts "An error occurred"
puts "Error code: #{e.err}"
puts "Error message: #{e.errstr}"
puts "Error SQLSTATE: #{e.state}"
ensure
dbh.disconnect if dbh
end
The first few lines of output that appear are reproduced below.
# of Fields: 8 ID=300 Name=Tee Shirt Description=Tank Top Size=Small Color=White Quantity=28 UnitPrice=9.00 ID=301 Name=Tee Shirt Description=V-neck Size=Medium Color=Orange Quantity=54 UnitPrice=14.00
It is important to call finish to release the statement handle when you are done. If you do not, then you may get an error like the following:
Resource governor for 'prepared statements' exceeded
To detect handle leaks, the SAP Sybase IQ database server limits the number of cursors and prepared statements permitted to a maximum of 50 per connection by default. The resource governor automatically generates an error if these limits are exceeded. If you get this error, check for undestroyed statement handles. Use prepare_cached sparingly, as the statement handles are not destroyed.
If necessary, you can alter these limits by setting the max_cursor_count and max_statement_count options.
Inserting rows requires a handle to an open connection. The simplest way to insert rows is to use a parameterized INSERT statement, meaning that question marks are used as placeholders for values. The statement is first prepared, and then executed once per new row. The new row values are supplied as parameters to the execute method.
require 'dbi'
def db_query( dbh, sql )
sth = dbh.execute(sql)
print "# of Fields: #{sth.column_names.size}\n"
sth.fetch do |row|
print "\n"
sth.column_info.each_with_index do |info, i|
unless info["type_name"] == "LONG VARBINARY"
print "#{info["name"]}=#{row[i]}\n"
end
end
end
sth.finish
end
def db_insert( dbh, rows )
sql = "INSERT INTO Customers (ID, GivenName, Surname,
Street, City, State, Country, PostalCode,
Phone, CompanyName)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
sth = dbh.prepare(sql);
rows.each do |row|
sth.execute(row[0],row[1],row[2],row[3],row[4],
row[5],row[6],row[7],row[8],row[9])
end
end
begin
dbh = DBI.connect('DBI:SQLAnywhere:demo', '<user_id>', '<password>')
rows = [
[801,'Alex','Alt','5 Blue Ave','New York','NY','USA',
'10012','5185553434','BXM'],
[802,'Zach','Zed','82 Fair St','New York','NY','USA',
'10033','5185552234','Zap']
]
db_insert(dbh, rows)
dbh.commit
db_query(dbh, "SELECT * FROM Customers WHERE ID > 800")
rescue DBI::DatabaseError => e
puts "An error occurred"
puts "Error code: #{e.err}"
puts "Error message: #{e.errstr}"
puts "Error SQLSTATE: #{e.state}"
ensure
dbh.disconnect if dbh
end