0

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.

It seems that is the character-limit for a text data type when using the @Lob annotation. Ideally I'd like to keep the whole blob intact rather than chunk it up into multiple rows in the database.

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 characters, judging by the fact that the close html tag is present at the end of the string....

Jason Aller
  • 3,541
  • 28
  • 38
  • 38
Ramy
  • 20,541
  • 41
  • 103
  • 153
  • show your mappings / annotations – Bozho Dec 07 '09 at 15:56
  • HTML BLOB? What the hell is that? Why would you use a BLOB for characters? – Pascal Thivent Dec 07 '09 at 16:00
  • it seems like CLOB, but it's called 'text' is MS SQL. So "HTML Lob" would be more correct. – Bozho Dec 07 '09 at 16:01
  • 1
    Just for the record, **TEXT**, **NTEXT** and **IMAGE** are "deprecated" since SQL Server 2005 and should be avoided in favor of **VARCHAR(MAX)**, **NVARCHAR(MAX)** and **VARBINARY(MAX)**. – Pascal Thivent Dec 07 '09 at 16:37
  • does my latest edit clarify the mappings enough or did i forget something? – Ramy Dec 07 '09 at 20:06
  • Please refer my link. If you still have any doubts let me know. http://stackoverflow.com/questions/42950938/proper-hibernate-mapping-for-lob-in-hibernate-pojo-we-are-using-hibernate-mapp – Ravi Mar 26 '17 at 19:22

3 Answers3

4

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.

Michael Behan
  • 3,433
  • 2
  • 28
  • 38
1

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?

Pascal Thivent
  • 562,542
  • 136
  • 1,062
  • 1,124
  • i've tried varchar(max), nvarchar(max) with the same truncation results. When I use varbinary(max), I get the following message: "com.microsoft.sqlserver.jdbc.SQLServerException: Implicit conversion from data type nvarchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query." not sure what Hibernate dialect I'm using. I think JDBC driver is:com.microsoft.sqlserver.jdbc.SQLServerDriver (taken from a JBoss config file) – Ramy Dec 07 '09 at 21:05
  • Actually, you don't want to use `varbinary(max)` for characters (hence the exception). Then, there may be an issue with `nvarchar(max)` (see http://opensource.atlassian.com/projects/hibernate/browse/HHH-3405). That's why I was suggesting to use `varchar(max)`. Sadly, this doesn't change anything (I would have been suprised though). Regarding the dialect, it would be nice if you could confirm that you are using `org.hibernate.dialect.SQLServerDialect`. And about the JDBC driver, I didn't find anything about such a limitation. Could you try straight JDBC? – Pascal Thivent Dec 07 '09 at 21:27
  • Pascal, I'm not sure how to confirm this. I don't find anything in the workspace when i search for the string "org.hibernate.dialect" in any java file. – Ramy Dec 07 '09 at 21:41
  • Also, i'm not sure what you mean by "try straight JDBC" – Ramy Dec 07 '09 at 21:44
  • 1. The dialect should be available in your `hibernate.cfg.xml` or `hibernate.properties`. 2. By straight JDBC, I mean without using Hibernate, just the JDBC API. – Pascal Thivent Dec 07 '09 at 21:53
  • this is the dialect: org.hibernate.dialect.SQLServerDialect Currently trying to test using JDBC. This won't be a viable solution if it does work though as it doesn't fit with our domain model. I can present it as a solution to our lead architect if it does work, but if it does work, does that imply an issue with hibernate annotations? – Ramy Dec 08 '09 at 21:24
  • Ok, then it's the right dialect. Regarding the test with JDBC, the idea is more to find where the issue is located (Hibernate? JDBC? Java?) and to solve it, not to replace Hibernate by JDBC. – Pascal Thivent Dec 08 '09 at 21:56
  • Pascal i want to sincerely thank you for all your help thus far. I have some news to report. I've tried doing an insert (programatically genearated statement) with the full html as one of the values on the insert statement. This STILL had the truncation issue. It seems that this may be a database setting on my end. – Ramy Dec 09 '09 at 17:32
  • No problem, you're welcome. Regarding the new feedback, I'd say that it's a big step forward. Next step: check the database (e.g. with raw SQL). Try to get some help from a DBA if required. Then, another interesting test would be to use another JDBC driver (http://jtds.sourceforge.net/). Good luck. – Pascal Thivent Dec 09 '09 at 18:25
0

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.

Chad Okere
  • 4,570
  • 1
  • 21
  • 19