Varbinary(16) display?

[denisks]

Hello,
I am stucked with a problem => I have a Varbinary(16) field in the db
where hex values are stored (0x1bcf5....) When I try query those
fields in the rails app or in the console i got just some weird
symbols. I have never used MSSQL adapter before and would be thankful
for the help and any hints.

Thanks

[KLausG]

Could you show us some sample code where you are using the varbinary
field?

Cheers
Klaus

[Ken Collins]

Assuming you are using TinyTDS, we pass all the test for any type of binary field. You should run them yourself to double check. But here is one of a few sanity checks.

https://github.com/rails-sqlserver/tiny_tds/blob/master/test/schema_test.rb#L11

https://github.com/rails-sqlserver/tiny_tds/blob/master/test/schema_test.rb#L262-266
https://github.com/rails-sqlserver/tiny_tds/blob/master/test/schema/sqlserver_2000.sql#L130

Perhaps you are just misunderstanding what TinyTDS does. We covert any SQL Server data type to a matching Ruby primitive. So in that first schema link above you can see how the binary data has to be packed and encoded for SQL Server to hold it. But in Ruby, the value looks much different because this is the format Ruby uses. Case in point, here is what a file read for that same 1px from the console looks like.

>> File.read('test/schema/1px.gif',:mode=>"rb:BINARY")

=> "GIF89a\x01\x00\x01\x00\x91\x00\x00\xFF\xFF\xFF\xFF\xFF\xFF\xFE\x01\x02\x00\x00\x00!\xF9\x04\x04\x14\x00\xFF\x00,\x00\x00\x00\x00\x01\x00\x01\x00\x00\x02\x02D\x01\x00;"

[denisks]

Hi,

I just do find on the model which is associated with the table that
has varbinary(16) field and get a binary output in the console.
However when I do the same via mssql server management studio i get
hex values by default. I used to convert in rails binary to hex and
back but i want to find a normal solution for that. I miss some
setting maybe?

thanks

[ja...@buildit.io]

Did you ever overcome this?? I too have the same problem where the product I'm connecting too has GUIDs stored in BINARY(16) columns and the results of these fields look something like this;

"&\xBA_\xAF\xAC\x11\x01{\x01\x97\xE6\xDC\xAA\x04\xC8\xF9"

as opposed to something like 98A212F0204011E290E9040CCEDF8123

I had this same issue some time ago working with Oracle (RAW) and ended up converting the query in the adapter to cast the values to hex accordingly.

Let me know how you made out or if this is still a problem.

[ja...@buildit.io]

Ok - so i forked the adapter and made a couple of simple changes to allow the conversion of binary to hex and the ability to find where field equals hex/guid.

In sqlserver_adapter.rb I modified the following two methods:

def string_to_binary(value)

         "0x#{value}"

        end

        

        def binary_to_string(value)

          if value.length == 16 

            value = value.unpack('C*').map{ |b| "%02X" % b }.join('')

          else

            value =~ /[^[:xdigit:]]/ ? value : [value].pack('H*')

          end

        end

Everything seems fine except for Model.find(id). I attempts to do some strange substitution in a manner that is different to Model.where(id_field: id). This may not be a showstopper for me, but I'll try and find a way to support this as well.

[Ken Collins]

Can you explain a few things?


1) Why should a binary filed not come back as binary data?

2) Do you expect this behavior for all binary columns or just a certain type? Why? Remember, Ruby has to work with the binary data, like images, etc. Is this type supposed to be human readable?

3) If for a certain type, you own needs, etc. Why have you not just set an attribute accessor in your model? Why is this an adapter concern?

4) Depending on some of these answer, why is this not a TinyTDS concern? Which at first glance, it should not be. Binary data should come back as binary, not a hex string for a human to read. Note:

https://github.com/rails-sqlserver/tiny_tds/blob/master/test/schema/sqlserver_2008.sql#L130
https://github.com/rails-sqlserver/tiny_tds/blob/master/test/schema_test.rb#L262-L266
https://github.com/rails-sqlserver/tiny_tds/blob/master/test/test_helper.rb#L120-L122


- Ken

[Jason Ihaia]

Hi Ken,

I agree with your comments - and recognize this is not a shortcoming of
the adapter itself, but rather the reality of my circumstance. I deal with
a number of legacy systems that support multiple rdbms and as such have
settled on using binary(16) with hex encoded GUIDs as their key structure.
I'M not in a position to change these (or I surely would) and have my own
generic layer on top which would be too resource intensive to account for
this in the case of MSSQL.

That said, I do need the binary capabilities, but for now will determine
that if the value is a length of 16, it must be a key, otherwise
everything will be status quo. Furthermore, I could set a global flag
somewhere to leverage this transformation if set, otherwise leave
everything may remain the same.

Hope that explains my current situation. I suspect the earlier poster had
a similar requirement.

Cheers,

Jason Ihaia | CEO | Buildit, Inc. | +1.720.224.2010 | www.buildit.io

>--
>You received this message because you are subscribed to a topic in the
>Google Groups "Rails SQLServer Adapter" group.
>To unsubscribe from this topic, visit
>https://groups.google.com/d/topic/rails-sqlserver-adapter/s9KkCsmXBCk/unsu
>bscribe?hl=en.
>To unsubscribe from this group and all its topics, send an email to
>rails-sqlserver-a...@googlegroups.com.
>To post to this group, send email to
>rails-sqlse...@googlegroups.com.
>Visit this group at
>http://groups.google.com/group/rails-sqlserver-adapter?hl=en.
>For more options, visit https://groups.google.com/groups/opt_out.
>
>

[Ken Collins]

On Apr 3, 2013, at 10:50 AM, Jason Ihaia <ja...@buildit.io> wrote:

> That said, I do need the binary capabilities, but for now will determine
> that if the value is a length of 16, it must be a key, otherwise
> everything will be status quo. Furthermore, I could set a global flag
> somewhere to leverage this transformation if set, otherwise leave
> everything may remain the same.

So yea… if you need to, just do some quick additions in your models.

class Something < ActiveRecord::Base

def my_varbinary_field
read_attribute[:my_varbinary_field].unpack('H*').first
end

end

The sky is the limit here, you could write your own setter. You can make a little library/concern that allows you to do all this manually in a model declaration with a little bit of meta-programming. Cool?


- Ken