=SQL Anywhere DBD Driver for Ruby-DBI
This is a SQL Anywhere driver for Ruby DBI (http://ruby-dbi.rubyforge.org/). This driver requires the
native SQL Anywhere Ruby driver. To get the native driver, use:
gem install sqlanywhere
This driver is designed for use with DBI 0.4 and greater.
This driver is licensed under the Apache License, Version 2.
The official code repository is located on GitHub. The repository can be cloned with:
git clone git://github.com/sqlanywhere/dbd-sqlanywhere.git
==Making a Connection
The following code is a sample database configuration object that connects
to a database called 'Test'.
require 'dbi'
DBI.connect('DBI:SQLAnywhere:Test') do | dbh |
if dbh.ping
print "Successfully Connected"
end
end
The connection function takes the general form:
dbh = DBI.connect(DBNAME, [USER_NAME], [PASSWORD])
The DBNAME string can be specified in the following forms:
"DBI:SQLAnywhere:"
"DBI:SQLAnywhere:{ENG}"
"DBI:SQLAnywhere:{ENG}:{DBN}"
"DBI:SQLAnywhere:{CONNECTION_STRING}" # where CONNECTION_STRING ~= "key1=val1;key2=val2;..."
For the first form, nothing will be added to the connection string. With the second and third forms
the driver will add ENG and DBN to the connection string accordingly. The fourth form will pass
the supplied connection string through unmolested.
The USER_NAME and PASSWORD can be passed into the function and they will be automatically appended to the
connection string. Since Ruby DBI will automatically fill in the username and password with defaults if they are omitted, you should NEVER include a "UID=" or "PWD=" in your connection string or an exception will be thrown.
Examples: Function Call ==> Generated Connection String
==============================================================================================
DBI.connect("DBI:SQLAnywhere:") ==> "uid=dba;pwd=sql"
DBI.connect("DBI:SQLAnywhere:Demo") ==> "eng=Demo;uid=dba;pwd=sql"
DBI.connect("DBI:SQLAnywhere:Demo:Test") ==> "eng=Demo;dbn=Test;uid=dba;pwd=sql"
DBI.connect("DBI:SQLAnywhere:Demo:Test", 'john', 'doe') ==> "eng=Demo;dbn=Test;uid=john;pwd=doe"
DBI.connect("DBI:SQLAnywhere:eng=Demo;dbn=Test") ==> "eng=Demo;dbn=Test;uid=dba;pwd=sql"
DBI.connect("DBI:SQLAnywhere:eng=Demo;dbn=Test;uid=john") ==> EXCEPTION! UID cannot be specified in the connection string
DBI.connect("DBI:SQLAnywhere:CommLinks=tcpip(port=2638)") ==> "CommLinks=tcpip(port=2638);uid=dba;pwd=sql"
==Running Test Suite
For information on running the Ruby/DBI DBD Tests, please see
test/DBD_TESTS
==Driver-Specific Features
At the time of this writing, there was no standard way to handle INOUT and OUT parameters
from stored procedures in DBI. When binding to an OUT parameter, you must bind a hash that contains a key called
:name. This :name will be used to retrieve the OUT value after execution. If the
OUT column is of string or binary type, you must also pass a key called :length with the expected maximum length of the OUT parameter.
In the case of an INOUT parameter, the :name and :length keys are the same as for
OUT parameters, but an additional :value parameter holds the value to be passed
into the stored procedure.
After execution, you can use the statement-specific function :bound_param
to retrieve the output value using the :name supplied at binding time.
===Example of using OUT and INOUT parameters
# The result that should be printed to console is:
# Complete string is PREFIX-some_string-SUFFIX and is 25 chars long
# Complete string is PREFIXPREFIX-some_string-SUFFIXSUFFIX and is 37 chars long
require 'dbi'
begin
DBI.connect("DBI:SQLAnywhere:test") do |dbh|
sql = <<SQL
IF EXISTS(SELECT * FROM SYS.SYSPROCEDURE where proc_name = 'foo') THEN
DROP PROCEDURE foo;
END IF
SQL
dbh.do(sql);
sql = <<SQL
create procedure foo ( IN prefix char(10),
INOUT buffer varchar(256),
OUT str_len int,
IN suffix char(10) )
begin set buffer = prefix || buffer || suffix;
select length( buffer ) into str_len; end SQL
dbh.do(sql);
buffer = "-some_string-"
prefix = "PREFIX"
# preparing the statement
sth = dbh.prepare("call foo( ?, ?, ?, ? )")
# Binding buffer column as :buf, and str_len column as :len
# Note that we must supply a :value for :buf since it is an INOUT
# And we must supply a :length for :buf since it is a string column
sth.execute( prefix, {:name => :buf, :value => buffer, :length => 255}, {:name => :len}, "SUFFIX")
# Retrieve the OUT value from buffer
new_buffer = sth.func(:bound_param, :buf)
# Retrieve the OUT value from str_len
length = sth.func(:bound_param, :len)
puts "Complete string is #{new_buffer} and is #{length} chars long"
# Add the results back into the string, and re-execute
sth.execute("PREFIX", {:name => :buf, :value => new_buffer, :length => 255}, {:name => :len}, "SUFFIX")
new_buffer = sth.func(:bound_param, :buf)
length = sth.func(:bound_param, :len)
puts "Complete string is #{new_buffer} and is #{length} chars long"
end
rescue DBI::DatabaseError => e
puts "An error occurred"
puts "Error code: #{e.err}"
puts "Error message: #{e.errstr}"
puts "Error SQLSTATE: #{e.state}"
ensure
DBI.disconnect_all
end