Blob in Java/Hibernate/sql-server 2005

Question

Hi, I'm trying to insert an HTML blob into our sql-server2005 database. i've been using the data-type [text] for the field the blob will eventually live in. i've also put a '@Lob' annotation on the field in the domain model. The problem comes in when the HTML blob I'm attempting to store is larger than 65536 characters. Its seems that is the caracter-limit for a text data type when using the @Lob annotation. Ideally I'd like to keep the whole blob in tact rather than chunk it up into multiple rows in the database. I appreciate any help or insight that might be provided. Thanks! _Ramy

Allow me to clarify annotation:
@Lob
@Column(length = Integer. MAX_VALUE) //per an answer on stackoverflow
private String htmlBlob;

database side (sql-server-2005):
CREATE TABLE dbo.IndustrySectorTearSheetBlob(
...
htmlBlob text NULL
... )

Still seeing truncation after 65536 characters...

EDIT: i've printed out the contents of all possible strings (only 10 right now) that would be inserted into the Database. Each string seems to contain all cahracters, judging by the fact that the close html tag is present at the end of the string....

Answer 1

Hmm, I haven't used Hibernate with Sql-Server 2005, but I have used Oracle TopLink with MySQL. I in my object I used used a straight byte[] with no annotations at all and it worked fine.

So, if I were you I would try storing your HTML in an encoded byte[] (use UTF-8 or whatever). Whenever you need to access the HTML text as a string, you can just decode it.

Theoretically, your text shouldn't be getting cut off, but sometimes these things fall short.

Answer 2

You could look at annotating with this also

@Column(length = Integer.MAX_VALUE)

Not sure why a blob is necessary though, NVARCHAR(MAX) will store all the html you want.

Answer 3

Actually, I think that what you're looking for is a CLOB field. Quoting Using Advanced Data Types:

BLOB and CLOB and NCLOB Data Types

The JDBC driver implements all the methods of the java.sql.Blob, java.sql.Clob, and java.sql.NClob interfaces.

Note: CLOB values can be used with SQL Server 2005 large-value data types. Specifically, CLOB types can be used with the varchar(max) and nvarchar(max) data types, BLOB types can be used with varbinary(max) and image data types, and NCLOB types can be used with ntext and nvarchar(max).

In other words, use a VARCHAR(MAX) or a NVARCHAR(MAX) if you need unicode support. About their maximum length:

The maximum storage size for VARCHAR(MAX) is 2^31-1 bytes (2,147,483,647 bytes or 2GB - 1 bytes). The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. Since each character in a VARCHAR data type uses one byte, the maximum length for a VARCHAR(MAX) data type is 2,147,483,645.

The maximum storage size for NVARCHAR(MAX) is also 2^31-1 bytes (2,147,483,647 bytes or 2GB - 1 bytes). The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. Since each Unicode character in an NVARCHAR data type uses two bytes, the maximum length for an NVARCHAR(MAX) data type is 1,073,741,822.

That should be enough for your HTML.

EDIT: On the Hibernate side, your annotated entity looks fine. On the database side, it should be ok. However, could you try to use VARCHAR(MAX) instead of TEXT (and remove this doubt about TEXT).

CREATE TABLE dbo.IndustrySectorTearSheetBlob (
...
htmlBlob varchar(max) NULL
... 
)

By the way, what Hibernate dialect are you using? And what JDBC driver are you using?