varchar(max) question

[Russ]

Hi there, first of all, my apologies if this has been covered
elsewhere.

I'm new to the adapter and am experimenting with some simple test
applications.

By default, when I create a model with a text field, the table is
created as varchar(max). When I edit the field using the default
scaffold view the data is stored correctly in the field, but is
displayed as random binary data when queried.

I've turned off character translations in the DSN as discussed with no
change in behaviour. Setting the default type for text fields to
varchar(8000) fixes it, however.

Does anyone have any advice?

My environment:

Windows XP
SQL Server 2008 R2 Express
DSN using SQL Server Native Client 10.0
Ruby 1.8.7 (2010-01-10 patchlevel 249) [i386-mingw32]
Rails 2.3.8

Russ

[Ken Collins]

Hey Russ,

There are various github issue tickets and or threads on this group that have discussed this in various contexts. First some background, the adapter automatically convert the simplified type :text to varchar(max) and nvarchar(max) if you have had an initializer that specified all string types were unicode safe. This is described in the readme to some detail so I wont cover that. Please do read that tho.

About your issue, please do read thru the github issues. Perhaps this one [1] along with the wiki entry [2]. I get the feeling that most people (including myself) use the adapter on POSIX systems with backend of unixODBC/FreeTDS. We have had a few reports on varchar(max) in those environments, but I myself have never seen them or understand them yet (still have to read Klaus last email). Moot for you tho since your on windows. Let me know if those links below help or what you find out. I'm certain this would be at the ODBC layer tho.

[1] http://github.com/rails-sqlserver/2000-2005-adapter/issues/closed#issue/17
[2] http://wiki.github.com/rails-sqlserver/2000-2005-adapter/platform-installation-windows

[KD.Gun...@zwick-edelstahl.de]

Hi Russ,

yes, there is a problem in the ruby-odbc driver.
I am in contact with Christian Werner about this issue
and currently testing his ruby-odbc-0.99992pre2.gem

Please stay tuned

Klaus

[Russ]

Thanks for the info. On the topic of text fields, given the size
limitations inherent with using SQL server 2008 with the adapter, does
anyone have any recommendations for elegantly handling text blocks
larger than whatever (max) is?

Russ

On Jun 22, 12:27 am, "KD.Gunderm...@Zwick-Edelstahl.de"

[Ken Collins]

Consult your manual! I believe varchar(max) is very close if not greater storage than text. Again, the adapter README has directions on how you can disable the automatic conversion of the :text simplified type to varchar(max). The reason that data type is prefered is that it allows greater equality operators that are more rails style and behave like other databases. I believe even MS has said that :text and :ntext are deprecated and you should be using the varchar(max) and nvarchar(max) storage types.

- Ken

> --
> You received this message because you are subscribed to the Google Groups "Rails SQLServer Adapter" group.
> To post to this group, send email to rails-sqlse...@googlegroups.com.
> To unsubscribe from this group, send email to rails-sqlserver-a...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/rails-sqlserver-adapter?hl=en.
>

[Russ]

Hi Ken,

Right now the max I can use with the adapter is varchar(8000), as I
stated in my OP. I worded that poorly, but by (max), I meant 8000,
which is currently the most I can set it to using the settings as
described in the README. The problem currently is that when :text is
converted to varchar(max), rails is returned random binary junk.
Hopefully, as Klaus mentions, this will be fixed in ruby-odbc, however
in the meantime I have to think about how to deal with the issue. At
least, it gives me hope that someone has seen and understands this
issue.

In other applications, I've seen it where the long narrative fields
are broken out into another table containing the pages of the
document, but I'm really hoping not to have to do that.

Thanks for the replies,

Russ

[Scott Jacobsen]

As a work around have you tried manually setting the column type to
text directly though sql server? I am currently doing a project
against sqlserver 2008, and some of the fields are ntext, not
nvarchar(max), and I have not had any problems. I haven't tested them
extensively yet, but so far everything seems to work. While deprecated
ntext and text still seem to work.

[Ken Collins]

Some feedback please.

My freetds.conf has this in it.

# If you get out-of-memory errors, it may mean that your client

# is trying to allocate a huge buffer for a TEXT field. # Try setting 'text size' to a more reasonable limit text size = 64512

 

When I put a test like this in SpecificSchemaTestSqlserver test.

  should 'allow real big strings' do

    big_string = "START #{'x'*200_000} END"

    expected_length = big_string.length

    new_obj = SqlServerString.create! :varchar_max => big_string

    found_obj = SqlServerString.find(new_obj.id)

    assert found_obj.varchar_max.starts_with?('START')

    assert found_obj.varchar_max.ends_with?('END'), 

      "Expected string to be #{expected_length} chars long, but was #{found_obj.varchar_max.length} instead."

  end

My initial error was this. "Expected string to be 200010 chars long, but was 64512 instead." So if I change my freetds.conf to be 300000, the test passes. I can actually keep kicking this up higher and higher to. So what exactly is wrong with ODBC/FreeTDS in this setup? Is it just a bug in ODBC on Ubuntu or something else besides my Mac?

 - Ken

[Ken Collins]

FYI, around 2_000_000 I start to get some bugs. I absolutly can not climb as high as varchar(max) supports tho. Anyone that needs to work around the 300_000 limit I personally saw could easily use text or ntext using the initializer methods described in the readme. What's the word on the ODBC patch?

 - Ken

[Scott Jacobsen]

I see this behavior (never get back more data than I specify with the
'text size' configuration) on Linux using FreeTDS 0.64, UnixODBC
2.3.0, ruby-odbc-9999, and the Rails adapter v2.3.4, and sqlserver
2008. I see this with both varchar(max) types and ntext types. Not
sure if that's the feedback you're looking for.

[Doug James]

That looks like everything working as designed to me. Freetds is
applying an additional constraint that artificially limits the size of
a text field to "protect" you. In order to make the test pass, you
have to remove this artificial limit. I don't recall if you can just
remove that param from freetds.conf, or if it *must* have a value.

[Joe Rafaniello]

I echo James thoughts.

I looked at this a while ago, hoping there was a way to get freetds to
raise an exception instead of silently truncating data when sending
data over this "text size" value but ended up setting it to over 20 MB
as a workaround. If I remember correctly, this setting affects any
column type and possibly any insert/select with data over this limit.

Joe

[Ken Collins]

Good to know. My last post and reason for the test was to verify that large string columns would not get truncated or contain trailing bad binary data. I know some have reported that but I could not remember if that was only on Windows or not. Obviously past the FreeTDS config, this appears to be working correctly as expected with UnixODBC/FreeTDS on various posix systems – which makes me happy :)

I'll add that test into the code to hopefully remind others of the fact. Thanks ya'll!

- Ken

[Scott Jacobsen]

With no text size constraint it does truncate the data - 64k chars or so if I remember correctly.

On Jun 29, 2010 2:35 PM, "Doug James" <simpl...@gmail.com> wrote:

That looks like everything working as designed to me.  Freetds is
applying an additional constraint that artificially limits the size of
a text field to "protect" you.  In order to make the test pass, you
have to remove this artificial limit. I don't recall if you can just
remove that param from freetds.conf, or if it *must* have a value.

On Jun 28, 6:54 pm, Ken Collins <k...@metaskills.net> wrote:
> Some feedback please.
>

> My freetds...