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

`OPENROWSET`? : http://msdn.microsoft.com/en-us/library/aa276850%28v=sql.80%29.aspx — Anand Shah, Feb 21 '12 at 09:59
Not only is saving binary data to SQLServer a nightmare, but so is the query to read it. I have done this in PHP and ColdFusion, if you'd like to see a snippet in one of those languages. — Teddy, Feb 21 '12 at 16:29

score 1 · Answer 1 · answered Jun 25 '13 at 12:24

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.

score 0 · Answer 2 · answered Feb 22 '12 at 13:36

Here is PHP-MSSQL code to save binary data:

mssql_query("SET TEXTSIZE 2147483647",$link);
$sql = "UPDATE UploadTable SET UploadTable_Data = ".varbinary_encode($data)." WHERE Person_ID = '".intval($p_id)."'";
mssql_query($sql,$link)  or 
  die('cannot upload_resume() in '.__FILE__.' on line '.__LINE__.'.<br/>'.mssql_get_last_message());

function varbinary_encode($data=null) {
  $encoded = null;
  if (!is_null($data)) {
    $a = unpack("H*hex", $data);
    $encoded = "0x";
    $encoded .= $a['hex'];
  }
  return $encoded;
}

Here is PHP-MSSQL code to get binary data:

mssql_query("SET TEXTSIZE 2147483647",$link);
$sql = "SELECT * FROM UploadTable WHERE ID = 123";
$db_result = mssql_query($sql,$link);
// work with result like normal

I also have Cold Fusion code and PHP-SQLSRV based code snippets if you need them. — Teddy, Feb 22 '12 at 13:37

score 0 · Accepted Answer · answered Feb 27 '12 at 02:51

I ended up using activerecord:

require 'rubygems'
require 'tiny_tds'
require 'activerecord-sqlserver-adapter'

..

my_table.create(:file_name => "abc.pdf", :file_data => File.open("abc.pdf", "rb").read)

For SQLServer 2000 support, go for 2.3.x version activerecord-sqlserver-adapter gem.

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

3 Answers3