How to insert a file into sql-server via tiny_tds?

Question

In a data importing script:

 client = TinyTds.Client.new(...)
 insert_str = "INSERT INTO [...] (...) VALUE (...)"
 client.execute(insert_str).do

So far so good.

However, how can I attach a .pdf file into the varbinary field (SQL Server 2000)?

Answer 1

I've recently had the same issue and using activerecord was not really adapted for what I wanted to do...

So, without using activerecord:

client = TinyTds.Client.new(...)
data = "0x" + File.open(file, 'rb').read.unpack('H*').first
insert_str = "INSERT INTO [...] (...) VALUE (... #{data})"
client.execute(insert_str).do

To send proper varbinary data, you need to read the file, convert it to hexadecimal string with unpack('H*').first and prepend '0x' to the result.