gistfile1.rb

# encoding: utf-8

require 'odbc'
require 'odbc_utf8'

GC.disable

module ODBC
  class Statement
    def finished?
      begin
        connected?
        false
      rescue ODBC::Error
        true
      end
    end
  end
end

module ODBC_UTF8
  class Statement
    def finished?
      begin
        connected?
        false
      rescue ODBC_UTF8::Error
        true
      end
    end
  end
end

def select_value(con,sql)
  h = con.run(sql)
  v = h.fetch.first
  h.drop
  v
end

con1 = ODBC.connect 'mc2008', 'rails', ''
con2 = ODBC_UTF8.connect 'mc2008', 'rails', ''

select_value(con1, "SELECT N'v' AS N'n'").encoding  # => #<Encoding:ASCII-8BIT>
select_value(con2, "SELECT N'v' AS N'n'").encoding  # => #<Encoding:UTF-8>

con1.odbc_version # => 2
con2.odbc_version # => 3

sth_runprc1 = con1.run "EXEC sp_helpconstraint 'people', 'nomsg'"
sth_runprc1.columns(true).map{|c|c.name}  # => ["constraint_type", "constraint_name", "delete_action", "update_action", "status_enabled", "status_for_replication", "constraint_keys"]
sth_runprc1.fetch_all                     # => [["DEFAULT on column lock_version", "DF__people__lock_ver__22D86D16", "(n/a)", "(n/a)", "(n/a)", "(n/a)", "((0))"], ["PRIMARY KEY (clustered)", "PK__people__3213E83F20F024A4", "(n/a)", "(n/a)", "(n/a)", "(n/a)", "id"]]
sth_runprc1.more_results                  # => false
sth_runprc1.drop

sth_runprc2 = con2.run "EXEC sp_helpconstraint 'people', 'nomsg'"
sth_runprc2.columns(true).map{|c|c.name}  # => ["constraint_type", "constraint_name", "delete_action", "update_action", "status_enabled", "status_for_replication", "constraint_keys"]
sth_runprc2.fetch_all                     # => [["DEFAULT on column lock_version", "DF__people__lock_ver__22D86D16", "(n/a)", "(n/a)", "(n/a)", "(n/a)", "((0))"], ["PRIMARY KEY (clustered)", "PK__people__3213E83F20F024A4", "(n/a)", "(n/a)", "(n/a)", "(n/a)", "id"]]
sth_runprc2.more_results                  # => true
sth_runprc2.drop

sth_runsel1 = con1.run "SELECT N'v' AS N'n'"
sth_runsel1.columns(true).map{|c|c.name}              # => ["n"]
sth_runsel1.columns(true).map{|c|c.name}[0].encoding  # => #<Encoding:ASCII-8BIT>
sth_runsel1.fetch_all                                 # => [["v"]]
sth_runsel1.more_results                              # => false
sth_runsel1.drop

sth_runsel2 = con2.run "SELECT N'v' AS N'n'"
sth_runsel2.columns(true).map{|c|c.name}              # => ["n"]
sth_runsel2.columns(true).map{|c|c.name}[0].encoding  # => #<Encoding:UTF-8>
sth_runsel2.fetch_all                                 # => [["v"]]
sth_runsel2.more_results                              # => false
sth_runsel2.drop

sth = con2.run "SELECT N'v' AS N'n'"
sth.columns(true)[0].name           # => "n"
sth.columns(true)[0].name.encoding  # => # => #<Encoding:UTF-8>
sth.drop

con1.do "DELETE FROM [sql_server_unicodes]"
con1.do "INSERT INTO [sql_server_unicodes] ([nvarchar]) VALUES (N'一二34五六')"         # => 1
select_value(con1, "SELECT TOP 1 [nvarchar] FROM [sql_server_unicodes]")            # => 一二34五六
select_value(con1, "SELECT TOP 1 [nvarchar] FROM [sql_server_unicodes]").encoding   # => #<Encoding:ASCII-8BIT>

con2.do "DELETE FROM [sql_server_unicodes]"
con2.do "INSERT INTO [sql_server_unicodes] ([nvarchar]) VALUES (N'一二34五六')"         # => [FreeTDS][SQL Server]Incorrect syntax near ''. (ODBC_UTF8::Error)
select_value(con2, "SELECT TOP 1 [nvarchar] FROM [sql_server_unicodes]")            # => 
select_value(con2, "SELECT TOP 1 [nvarchar] FROM [sql_server_unicodes]").encoding   # => 

=begin

Example Adapter Fails with UTF8 variant.

  1) Error:
test: Testing unicode data should insert into nvarchar field. (UnicodeTestSqlserver):
ActiveRecord::StatementInvalid: ODBC::Error: 42000 (102) [FreeTDS][SQL Server]Incorrect syntax near ''.: INSERT INTO [sql_server_unicodes] ([nchar], [nchar_10], [ntext], [ntext_10], [nvarchar], [nvarchar_100], [nvarchar_max], [nvarchar_max_10]) VALUES (NULL, NULL, NULL, NULL, N'一二34五六', NULL, NULL, NULL)

  2) Error:
test: Testing unicode data should re-encode data on DB reads. (UnicodeTestSqlserver):
ActiveRecord::StatementInvalid: ODBC::Error: 42000 (102) [FreeTDS][SQL Server]Incorrect syntax near ''.: INSERT INTO [sql_server_unicodes] ([nchar], [nchar_10], [ntext], [ntext_10], [nvarchar], [nvarchar_100], [nvarchar_max], [nvarchar_max_10]) VALUES (NULL, NULL, NULL, NULL, N'一二34五六', NULL, NULL, NULL)

  3) Error:
test_coerced_validate_uniqueness_with_limit_and_utf8(UniquenessValidationTest):
ActiveRecord::StatementInvalid: ODBC::Error: 42000 (102) [FreeTDS][SQL Server]Incorrect syntax near ''.: SELECT TOP (1) [events].[id] FROM [events] WHERE ([events].[title] COLLATE Latin1_General_CS_AS_WS = N'一二三四五')

=end



# [written_on]  # => datetime
# [bonus_time]  # => time
# [last_read]   # => date

con1.use_time # => false
sth = con1.run "SELECT [topics].* FROM [topics]" #<ODBC::Statement:0x00000100a48868>
row = sth.fetch_hash # => {"id"=>1, "title"=>"The First Topic", "author_name"=>"David", "author_email_address"=>"david@loudthinking.com", "written_on"=>#<ODBC::TimeStamp: "2003-07-16 10:28:11 223000000">, "bonus_time"=>"09:28:00.0000000", "last_read"=>"2004-04-15", "content"=>"Have a nice day", "approved"=>0, "replies_count"=>1, "parent_id"=>nil, "parent_title"=>nil, "type"=>nil, "group"=>nil}
row['written_on'].class # => ODBC::TimeStamp
row['bonus_time'].class # => String
row['last_read'].class  # => String

con1.use_time = true
sth = con1.run "SELECT [topics].* FROM [topics]"
row = sth.fetch_hash  # => {"id"=>1, "title"=>"The First Topic", "author_name"=>"David", "author_email_address"=>"david@loudthinking.com", "written_on"=>2003-07-16 10:28:11 -0400, "bonus_time"=>"09:28:00.0000000", "last_read"=>"2004-04-15", "content"=>"Have a nice day", "approved"=>0, "replies_count"=>1, "parent_id"=>nil, "parent_title"=>nil, "type"=>nil, "group"=>nil}
row['written_on'].class # => Time
row['bonus_time'].class # => String
row['last_read'].class  # => String


con1.use_time # => false
sth = con1.run "SELECT [topics].* FROM [topics]"
sth.fetch_all # => [[1, "The First Topic", "David", "david@loudthinking.com", #<ODBC::TimeStamp: "2003-07-16 10:28:11 223000000">, "09:28:00.0000000", "2004-04-15", "Have a nice day", 0, 1, nil, nil, nil, nil], [2, "The Second Topic of the day", "Mary", nil, #<ODBC::TimeStamp: "2004-07-15 10:28:00 10000000">, nil, nil, "Have a nice day", 1, 0, 1, nil, "Reply", nil], [3, "The Third Topic of the day", "Carl", nil, #<ODBC::TimeStamp: "2005-07-15 10:28:00 10000000">, nil, nil, "I'm a troll", 1, 1, nil, nil, nil, nil], [4, "The Fourth Topic of the day", "Carl", nil, #<ODBC::TimeStamp: "2006-07-15 10:28:00 10000000">, nil, nil, "Why not?", 1, 0, 3, nil, "Reply", nil]]

con1.use_time = true
sth = con1.run "SELECT [topics].* FROM [topics]"
sth.fetch_all # ArgumentError: wrong number of arguments (7 for 0)




sth1 = con1.run "SELECT [topics].* FROM [topics]"
r1 = sth1.fetch_hash # => {"id"=>1, "title"=>"The First Topic", "author_name"=>"David", "author_email_address"=>"david@loudthinking.com", "written_on"=>#<ODBC::TimeStamp: "2003-07-16 10:28:11 223000000">, "bonus_time"=>"09:28:00.0000000", "last_read"=>"2004-04-15", "content"=>"Have a nice day", "approved"=>0, "replies_count"=>1, "parent_id"=>nil, "parent_title"=>nil, "type"=>nil, "group"=>nil}
r2 = sth1.fetch_hash # => {"id"=>2, "title"=>"The Second Topic of the day", "author_name"=>"Mary", "author_email_address"=>nil, "written_on"=>#<ODBC::TimeStamp: "2004-07-15 10:28:00 10000000">, "bonus_time"=>nil, "last_read"=>nil, "content"=>"Have a nice day", "approved"=>1, "replies_count"=>0, "parent_id"=>1, "parent_title"=>nil, "type"=>"Reply", "group"=>nil}
r3 = sth1.fetch_hash # => {"id"=>3, "title"=>"The Third Topic of the day", "author_name"=>"Carl", "author_email_address"=>nil, "written_on"=>#<ODBC::TimeStamp: "2005-07-15 10:28:00 10000000">, "bonus_time"=>nil, "last_read"=>nil, "content"=>"I'm a troll", "approved"=>1, "replies_count"=>1, "parent_id"=>nil, "parent_title"=>nil, "type"=>nil, "group"=>nil}
r4 = sth1.fetch_hash # => {"id"=>4, "title"=>"The Fourth Topic of the day", "author_name"=>"Carl", "author_email_address"=>nil, "written_on"=>#<ODBC::TimeStamp: "2006-07-15 10:28:00 10000000">, "bonus_time"=>nil, "last_read"=>nil, "content"=>"Why not?", "approved"=>1, "replies_count"=>0, "parent_id"=>3, "parent_title"=>nil, "type"=>"Reply", "group"=>nil}
r1.keys.map { |k| k.object_id } # => [2156581320, 2156581300, 2156581280, 2156581260, 2156581240, 2156581220, 2156581200, 2156581180, 2156581160, 2156581140, 2156581120, 2156581100, 2156581080, 2156581060]
r2.keys.map { |k| k.object_id } # => [2156581320, 2156581300, 2156581280, 2156581260, 2156581240, 2156581220, 2156581200, 2156581180, 2156581160, 2156581140, 2156581120, 2156581100, 2156581080, 2156581060]
r3.keys.map { |k| k.object_id } # => [2156581320, 2156581300, 2156581280, 2156581260, 2156581240, 2156581220, 2156581200, 2156581180, 2156581160, 2156581140, 2156581120, 2156581100, 2156581080, 2156581060]
r4.keys.map { |k| k.object_id } # => [2156581320, 2156581300, 2156581280, 2156581260, 2156581240, 2156581220, 2156581200, 2156581180, 2156581160, 2156581140, 2156581120, 2156581100, 2156581080, 2156581060]
sth1.drop

sth1 = con1.run "SELECT [topics].* FROM [topics]"
data = sth1.each_hash
data[0].keys.map { |k| k.object_id }  # => [2168558420, 2168558400, 2168558380, 2168558360, 2168558340, 2168558320, 2168558300, 2168558280, 2168558260, 2168558240, 2168558220, 2168558200, 2168558180, 2168558160]
data[1].keys.map { |k| k.object_id }  # => [2168558420, 2168558400, 2168558380, 2168558360, 2168558340, 2168558320, 2168558300, 2168558280, 2168558260, 2168558240, 2168558220, 2168558200, 2168558180, 2168558160]
data[2].keys.map { |k| k.object_id }  # => [2168558420, 2168558400, 2168558380, 2168558360, 2168558340, 2168558320, 2168558300, 2168558280, 2168558260, 2168558240, 2168558220, 2168558200, 2168558180, 2168558160]
data[3].keys.map { |k| k.object_id }  # => [2168558420, 2168558400, 2168558380, 2168558360, 2168558340, 2168558320, 2168558300, 2168558280, 2168558260, 2168558240, 2168558220, 2168558200, 2168558180, 2168558160]
sth1.drop

sth2 = con2.run "SELECT [topics].* FROM [topics]"

# Same as above!!!!

The problem is (1) not getting utf8 encoded strings back from the utf8 library version and (2) that the #do method does execute the sql but does not correctly cope with a nil/-1 return value.

OK, revision 2 shows that all #do SQL commands that do not return things like rows affected, in this case ddl, etc, will not get dropped automatically. Also, the return value is 0 instead of the -1 we were used to seeing. Not that this is used, but just and observation.

(1) you will get utf8 ENCODED strings ( as you would enocde them in Ruby 1.8 )
but they are not TAGGED as utf8 ( as you would do in Ruby 1.9 )

(2) running on Windows all Statements gets finished

Feedback on 0.99992pre4 and with d65bae8e of the gist.

1. The #more_results always return true after #fetch_all when using run to execute a procedure. A second call to #fetch_all will make #more_results finally answer false.

2. I can not INSERT unicode data, however selecting does work correctly.

1. String keys work great!

2. I could not test ODBC::Database#timezone=. What is the format?

3. Still getting syntax error when quoting UTF8 data. Included some sample test fails from adapter.

4. No matter which way #use_time is set, time/date data types come back as strings. I'm somewhat OK with that. They were previously ODBC::TimeStamps too. I know that ActiveRecord will convert these higher up for me to correct ruby objects. But I think ideally when #use_time is false, they maintain backward compatibility as ODBC::TimeStamp objects and when set to true, become full blown Date object, the #time data type should just be like #datetime. Lemme double check what mysql2 gem does. Yea, here is a report that I think we should shoot for too. http://gist.github.com/562465

5. Bad bug in #fetch_all. Raises argument error.