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

Discussion in 'Ruby' started by Ben, Jan 9, 2006.

  1. Ben

    Ben Guest

    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
     
    Ben, Jan 9, 2006
    #1
    1. Advertising

  2. Ben

    Lou Vanek Guest

    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



    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-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
     
    Lou Vanek, Jan 9, 2006
    #2
    1. Advertising

  3. Ben

    Ben Gribaudo Guest

    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
     
    Ben Gribaudo, Jan 11, 2006
    #3
  4. Ben

    Lou Vanek Guest

    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 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
     
    Lou Vanek, Jan 11, 2006
    #4
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Maximus

    Chaning session state programatically

    Maximus, Feb 5, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    390
    Maximus
    Feb 5, 2004
  2. Darren Clark

    asp.net and chaning betten http and https

    Darren Clark, Aug 20, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    611
    Patrice
    Aug 20, 2004
  3. Michael Tan
    Replies:
    32
    Views:
    996
    Ara.T.Howard
    Jul 21, 2005
  4. Jens Auer
    Replies:
    1
    Views:
    94
    James Edward Gray II
    Mar 28, 2006
  5. Rob Morrison

    Hey, who keeps chaning my escaped character!

    Rob Morrison, Aug 22, 2004, in forum: Javascript
    Replies:
    1
    Views:
    94
    Thomas 'PointedEars' Lahn
    Aug 22, 2004
Loading...

Share This Page