MSSQL Defaults to ~64KB #525

Closed
Wardrop opened this Issue Jul 24, 2012 · 3 comments

Projects

None yet

2 participants

@Wardrop
Wardrop commented Jul 24, 2012

MSSQL defaults to a maximum binary size of roughly 64KB (64512 bytes). This can be overridden on a per-connection basis using: SET TEXTSIZE 2147483647; where 2147483647 is the maximum size (2GB). As requested by Jeremy, a spec has been provided below. It checks to ensure a 50MB blob can be set and retrieved from the database.

diff --git spec/adapters/mssql_spec.rb spec/adapters/mssql_spec.rb
index 0d1700d..3f94e66 100644
--- spec/adapters/mssql_spec.rb
+++ spec/adapters/mssql_spec.rb
@@ -32,7 +32,7 @@ MSSQL_DB.create_table! :test3 do
 end
 MSSQL_DB.create_table! :test4 do
   varchar :name, :size => 20
-  varbinary :value
+  varbinary :value, :size => :max
 end

 describe "A MSSQL database" do
@@ -400,6 +400,14 @@ describe "MSSSQL::Dataset#insert" do
     h[:value].should == 10
     @ds.first(:xid=>h[:xid])[:value].should == 10
   end
+  
+  specify "should allow large text and binary values" do
+    string = (0..(2**22)).reduce(''){ |memo, n| memo << n % 0xD800 } # Large string, roughly 50mb.
+    @db[:test4].insert(:name => 'max varbinary test', :value => Sequel::SQL::Blob.new(string))
+    h = @db[:test4].filter(:name => 'max varbinary test').first
+    h[:value].length.should == string.length
+    h[:value].should == string
+  end
 end

 describe "MSSSQL::Dataset#disable_insert_output" do
@jeremyevans
Owner

This appears to be a problem on tinytds and odbc, but not on jdbc/sqlserver or jdbc/jtds (which probably set the textsize internally). odbc has a problem with blobs anyway, so I'm not sure it's worth setting a large textsize there. Are you using tinytds?

@jeremyevans
Owner

Let me rephrase. The textsize limit doesn't appear to be a problem on odbc, but the spec above would fail there because blobs odbc doesn't support blobs (at least in my testing).

@Wardrop
Wardrop commented Jul 25, 2012

Yes, this is using TinyTDS. Apparently the SQL Native Client sets the TEXTSIZE option to it's maximum on each connection, so it's likely some other clients do this also. I've just done some further reading and found there's a line in freetds.conf which controls the default text size: text size = 64512. So I guess I wouldn't advise Sequel override the textsize option, but providing a friendly API to change the textsize from with Sequel may not be a bad idea.

@jeremyevans jeremyevans added a commit that closed this issue Jul 25, 2012
@jeremyevans Add :textsize option to tinytds adapter to override the default TEXTS…
…IZE (Fixes #525)

The default TEXTSIZE setting on tinytds is about 64k, which is
pretty small.  While you can override the setting in freetds.conf,
for some users (Windows users using a binary gem), that might not
be the easiest change to make.  Allow a :textsize option to manual
set the setting for each connection.

While here, initialize the mssql unicode strings setting on
initialization instead of on connection.

Add a spec that tests >64k of data, and switch some test tables
to use generic types instead of specific types.
246fc41
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment