ruby-odbc -- Strange Chaning of Special Character in Result Set

B

Ben

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
 
L

Lou Vanek

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

=3D> 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=3Dcp437

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

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

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


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

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

=FB=F9



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

require 'odbc'

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

connection=3DODBC.connect(DNS,USR,PWD)

a =3D [97,150]

File.open('out', 'wb') do |wbf|
a.each { |c|
wbf.puts
sql1 =3D "select char(#{c})"
wbf.puts "Selecting the ASCII character for char code #{c} [#{sql1}]"
wbf.puts "=3D" * 70
results1 =3D connection.run(sql1)
results_array1 =3D 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 =3D "select ascii(char(#{c}))"
wbf.puts "Selecting the numeric code for char(#{c}) [#{sql2}]"
wbf.puts "=3D" * 70
results2 =3D connection.run(sql2)
results_array2 =3D 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 helpfu=
l to
know which database [and version] you used.

-lv


Hi,
=20
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. :-(
=20
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]
=20
=20
What do y'all think?
=20
Thanks,
Ben
=20
=3D=3D=3D script =3D=3D=3D
require 'odbc'
=20
connection=3DODBC.connect('dsn','username','password')
=20
sql1 =3D 'select char(150)'
puts "Selecting the ASCII character for char code 150 [#{sql1}]"
puts "=3D" * 70
results1 =3D connection.run(sql1)
results_array1 =3D results1.fetch_all
puts "Character: \t" + results_array1[0][0]
print "Char Code: \t"
print results_array1[0][0][0].to_i
puts
=20
puts
=20
sql2 =3D 'select ascii(char(150))'
puts "Selecting the numeric code for char(150) [#{sql2}]"
puts "=3D" * 70
results2 =3D connection.run(sql2)
results_array2 =3D results2.fetch_all
print "Char Code: \t"
print results_array2[0][0]
puts
puts "Character: \t" + results_array2[0][0].chr
=20
=20
=3D=3D=3D Output =3D=3D=3D
Selecting the ASCII character for char code 150 [select char(150)]
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Character: ? <---- should be blank like the below
Char Code: 63 <---- should be 150
=20
Selecting the numeric code for char(150) [select ascii(char(150))]
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Char Code: 150
Character: (blank) <---- because the shell window won't display
the em dash
 
B

Ben Gribaudo

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
 
L

Lou Vanek

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/012800.html
http://lists.ibiblio.org/pipermail/freetds/2003q2/012791.html
http://www.phpbuilder.com/board/history/topic.php/10268497-1.html

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. '?').






Ben said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,899
Latest member
RodneyMcAu

Latest Threads

Top