«

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-6ubuntu) #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

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



Ben wrote:

> 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-6ubuntu) #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','passwo rd')
>=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=3 D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=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=3 D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3 D=3D=3D=3D=3D
> Char Code: 150
> Character: (blank) <---- because the shell window won't display
> the em dash

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

I'm not familiar with FreeTDS, but Google says that some versions of FreeTDS
have problems with extended ascii:

http://lists.ibiblio.org/pipermail/f...q2/012800.html
http://lists.ibiblio.org/pipermail/f...q2/012791.html
http://www.phpbuilder.com/board/hist...0268497-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 Gribaudo wrote:

> 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