Forum: Ruby ruby-odbc -- Strange Chaning of Special Character in Result

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Ben (Guest)
on 2006-01-09 17:09
(Received via mailing list)
Hi,

Some strange behind-the-scenes character conversion appears to be
occuring when fetching ASCII code 150 using ruby-odbc. This is
exempilfied by the script below. It *should* return identical data for
both selects. However, it does not. When char(150) is selected, the
ASCII character code 63 is returned. Not good. :-(

Server information:
Linux version 2.6.12-10-686-smp (buildd@terranova) (gcc version 3.4.5
20050809 (prerelease) (Ubuntu 3.4.4-6ubuntu8)) #1 SMP Fri Nov 18
12:27:41 UTC 2005
ruby 1.8.3 (2005-06-23) [i486-linux]


What do y'all think?

Thanks,
Ben

=== script ===
require 'odbc'

connection=ODBC.connect('dsn','username','password')

sql1 = 'select char(150)'
puts "Selecting the ASCII character for char code 150 [#{sql1}]"
puts "=" * 70
results1 = connection.run(sql1)
results_array1 = results1.fetch_all
puts "Character: \t" + results_array1[0][0]
print "Char Code: \t"
print results_array1[0][0][0].to_i
puts

puts

sql2 = 'select ascii(char(150))'
puts "Selecting the numeric code for char(150) [#{sql2}]"
puts "=" * 70
results2 = connection.run(sql2)
results_array2 = results2.fetch_all
print "Char Code: \t"
print results_array2[0][0]
puts
puts "Character: \t" + results_array2[0][0].chr


=== Output ===
Selecting the ASCII character for char code 150 [select char(150)]
======================================================================
Character:      ?      <---- should be blank like the below
Char Code:      63   <---- should be 150

Selecting the numeric code for char(150) [select ascii(char(150))]
======================================================================
Char Code:      150
Character:       (blank)  <---- because the shell window won't display
the em dash
Lou V. (Guest)
on 2006-01-10 00:52
(Received via mailing list)
I tested your script on my system (actually I rewrote portions of it,
as shown below), and when the output was sent to bash standard output
all
extended-ascii characters where output as a simple dash ("-"). This
apparently is either a limitation of bash or cygwin.

Then I sent the output to a file called "out", and I was able to see
the extended-ascii characters.

   od -c out

=> 0001500   h   a   r   a   c   t   e   r   :      \t 226  \n   C   h
a

shows char(150) as 226, which is octal for 150, so that's right.

Gvim shows the accented-u character properly once I set my encoding
for extended-ascii (8-bit):

   :set enc=cp437

The output of the test script as viewed in gvim (cp437 encoding):

Selecting the ASCII character for char code 97 [select char(97)]
======================================================================
Character: 	a
Char Code: 	97
result array: [["a"]]

Selecting the numeric code for char(97) [select ascii(char(97))]
======================================================================
Char Code: 	97
Character: 	a


Selecting the ASCII character for char code 150 [select char(150)]
======================================================================
Character: 	û
Char Code: 	150
result array: [["\226"]]

Selecting the numeric code for char(150) [select ascii(char(150))]
======================================================================
Char Code: 	150
Character: 	û

ûù


# test script =======================================================
#!/usr/bin/ruby

require 'odbc'

DNS='test'
USR='odbcuser'
PWD='super_secret_password'

connection=ODBC.connect(DNS,USR,PWD)

a = [97,150]

File.open('out', 'wb') do |wbf|
   a.each { |c|
	wbf.puts
	sql1 = "select char(#{c})"
	wbf.puts "Selecting the ASCII character for char code #{c} [#{sql1}]"
	wbf.puts "=" * 70
	results1 = connection.run(sql1)
	results_array1 = results1.fetch_all
	wbf.puts "Character: \t" + results_array1[0][0]
	wbf.print "Char Code: \t"
	wbf.puts results_array1[0][0][0].to_i
	wbf.puts "result array: #{results_array1.inspect}"
	wbf.puts

	results1.drop

	sql2 = "select ascii(char(#{c}))"
	wbf.puts "Selecting the numeric code for char(#{c}) [#{sql2}]"
	wbf.puts "=" * 70
	results2 = connection.run(sql2)
	results_array2 = results2.fetch_all
	wbf.print "Char Code: \t"
	wbf.print results_array2[0][0]
	wbf.puts
	wbf.puts "Character: \t" + results_array2[0][0].chr
	wbf.puts

	results2.drop
   }
   wbf.puts "\226\227" #little test
end

connection.disconnect


------------------------------------------------------------------------------
I didn't get any warnings when compiling ruby-odbc, but I was using gcc
3.4.4.
I also ran the ruby-odbc tests and they were all fine.

So, in conclusion, at least on cygwin/win xp pro sp2, ruby-odbc seems to
work swell.
I recommend sending your output to a file and doing something similar to
what I've done to see exactly what's being sent back from odbc instead
of relying on a (possibly lying) terminal. It would also have been
helpful to
know which database [and version] you used.

-lv
Ben Gribaudo (Guest)
on 2006-01-11 19:00
(Received via mailing list)
Hi Lou,

Thanks for your reply.

> I recommend sending your output to a file and doing something similar to
> what I've done to see exactly what's being sent back from odbc instead
> of relying on a (possibly lying) terminal. It would also have been helpful to
> know which database [and version] you used.

I'm not concerned about if/how the terminal outputs the character--that
output was for illustration only. The "Char Code" line is the one of
interest. In my original script, shouldn't the terminal output this
number correctly?

Running your script and looking at the output file still shows a char
code of 63 for select char(150) and 150 for select ascii(char(150)).
:-(

I'm using MS Sql Server 7.0, FreeTDS and Ruby-ODBC.

> I didn't get any warnings when compiling ruby-odbc, but I was using gcc 34.4.

Christian (author of ruby-odbc) communicated with me about this and
provided a solution to the signedness difference error. Thank you,
Christian!

Ben
Lou V. (Guest)
on 2006-01-11 20:21
(Received via mailing list)
I'm not familiar with FreeTDS, but Google says that some versions of
FreeTDS
have problems with extended ascii:

   http://lists.ibiblio.org/pipermail/freetds/2003q2/...
   http://lists.ibiblio.org/pipermail/freetds/2003q2/...
   http://www.phpbuilder.com/board/history/topic.php/...

so it appears that if you are running FreeTDS .61, extended ascii is
broken.

Your FreeTDS log should show the error. What does your log say?
[http://www.freetds.org/userguide/logging.htm]


    On retrieving data from the server, FreeTDS substitutes an ASCII '?'
    in the character's place, and emits a warning message stating that
    some characters could not be converted.
    [http://www.freetds.org/userguide/nonwestern.htm]

This explains why '150' is mysteriously being converted to 63 (i.e.
'?').
This topic is locked and can not be replied to.