string getting truncated when saved into database

Question

I have a database datatype defined as Text:

text columns are variable-length columns that can hold up to 2,147,483,647 (2³¹ - 1) bytes of printable characters.

What does that means exactly? How many string characters will I be able to save into the Text column?

Basically, I try to save a c# string object into that column where

myString.ToString().Length == 39418

but when I pull it back from the database

myString.ToString().Length == 32768

-----------EDITED---------------

guys this is very confusing.

The Text column is defined as 2,147,483,647 bytes which is 2GB

The string i'm trying to save is ?System.Text.ASCIIEncoding.Unicode.GetByteCount(param.Value.ToString()) 78836 bytes i.e. 0.0000734217 gigabytes

So that confirms that what I am trying to save IS NOT too big for the Text datatype column? i.e. I'm saving 0.0000734217 GB into a column capable of handling 2GB

I'm using Sybase. Saving like this:

        OdbcParameter param = new OdbcParameter();
        param.DbType = DbType.String;
        param.Size = int.MaxValue;
        param.Value = myBigString
        parameters.Add(param);
        OdbcHelper.ExecuteNonQuery(connectionString, sql, parameters);

And retreiving like this

    DataSet ds = new DataSet();
    OdbcConnection conn = new OdbcConnection(connectionString);
    OdbcDataAdapter adp = new OdbcDataAdapter(command, conn);
    conn.Open();
    adp.Fill(ds);....

Also when I try this I can still see the data is truncated so it doesn't look like a problem when retreiving the data

var obj = OdbcHelper.ExecuteScalar(connectionString, "select myBigString FROM ...");

If this is SQL Server, don't use `text` as the data type, it is deprecated. — Darren, Jun 11 '13 at 12:55
How exactly are you saving this to the database? If you have C# code involved I'd assume you're using either ADO or an ORM... — Dan Puzey, Jun 11 '13 at 13:17
Possible duplicate: http://stackoverflow.com/questions/3008267/sql-server-text-datatype-maxlength-65-535 — djf, Jun 11 '13 at 13:20
You're either truncating on insert (probably unlikely) or when reading back (most likely) into a buffer. Post the code that gets myString. — Mark Brackett, Jun 11 '13 at 13:21
I still don't see the part where you explicitly read the string out of your dataset. — jszigeti, Jun 11 '13 at 14:05
Hi jszigeti. When I call ExecuteScalar (editied in the question) and retreive the data I can still see it's truncated — Bob, Jun 11 '13 at 14:14
In SQL do a select len(col) do see if it is truncated on the way in or out. — paparazzo, Jun 11 '13 at 14:38

Darren · Answer 1 · 2013-06-11T13:13:23.910

2

As I mentioned in my comment, do not use TEXT as it is deprecated. Use VARCHAR or NVARCHAR for Unicode data. This will allow you to store up to 2GB of data.

http://msdn.microsoft.com/en-us/library/ms186939.aspx

Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size, in bytes, is two times the actual length of data entered + 2 bytes. The ISO synonyms for nvarchar are national char varying and national character varying.

2,147,483,647 bytes is approximately 1.862GB and the maximum capacity for that column. I assume the data you're attempting to store is too large for the column, hence the data being truncated.

edited Jun 11 '13 at 13:13

answered Jun 11 '13 at 12:58

Darren

68,902
24
138
144

thanks. Let's assume for a sec that I cannot change the datatype of the database. So am i bascially saving too large a string into the column? What does 2,147,483,647 bytes mean? Can the Text column not handle a string of lenght 39418? I need to know this specific question please – Bob Jun 11 '13 at 13:07
@Bob - 2,147,483,647 bytes is approximately 1.862GB. I assume the data you're attempting to store is too large for the column, hence the data being truncated. – Darren Jun 11 '13 at 13:12
Unicode in "worst case" uses four bytes per character (iirc). `39418 * 4 << 2147483647` so I think you should be safe there. Doesn't explain why you would lose characters though. – Corak Jun 11 '13 at 13:15
Thanks. Do you know how I can find out how big a string of 39418 charachers is so I can confirm this? – Bob Jun 11 '13 at 13:16
Btw. [Text](http://msdn.microsoft.com/library/ms187993.aspx) (which you shouldn't use. Use [nvarchar(max)](http://msdn.microsoft.com/library/ms186939.aspx) instead) is non-Unicode, so you should have the full 2147483647 characters. Even if the server code page uses double-byte characters. – Corak Jun 11 '13 at 13:22
GetByteCount(myBigString) returns 78836 bytes which is 0.0000734217 gigabytes... so what i'm saving isn't too big for the Text datatype – Bob Jun 11 '13 at 13:33

score 0 · Answer 2 · answered Jun 11 '13 at 13:27

0

use the nvarchar(MAX) type for that column

real example from a DB in action

answered Jun 11 '13 at 13:27

mihai

2,746
3
35
56

a usefull related link: http://stackoverflow.com/questions/11131958/what-is-the-maximum-characters-for-the-nvarcharmax – mihai Jun 11 '13 at 13:40
Thanks meorfi, just trying to confirm if it's because of the datatype of the column first. Question edited to reflect this – Bob Jun 11 '13 at 14:15
@Bob, can you check the string which you pass by param.Value with a profiler. Has it full value before gets written in DB, or, may the trunc is when it pass from System to DB. – mihai Jun 12 '13 at 06:31

score 0 · Answer 3 · answered Jun 12 '13 at 14:46

0

In my connection string I needed to add textsize=2147483647

answered Jun 12 '13 at 14:46

Bob

4,236
12
45
65

string getting truncated when saved into database

3 Answers3