README

=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