Search code examples
sql-serverhibernatejpavarcharmax

Hibernate schema validation | MS SQL varchar(max) | MS SQL 2008

Having problems with JPA/Hibernate and an existing MS-SQL 2008 database, which has a table with a column defined as "varchar(max)".

MS Docs about varchar(n|max) and additional Stack thread - see the answer

Column definition copied from MS SQL 2008 Server: screenshot

The Entity generated by hbm2java:

@Entity
@Table(name = "Log")
public class Log implements java.io.Serializable {

    private String fehlerLevel;

    @Column(name = "FehlerLevel", nullable = false)
    public String getFehlerLevel() {

Used libraries:

  • tomcat 8.5

  • hibernate 5.2.17.Final

  • hibernate.validator 6.0.9.Final

  • hibernate.validator.cdi 6.0.9.Final

  • hibernate.jpa.2.1.api 1.0.2.Final

Case 1:

<property name="hibernate.dialect" value="org.hibernate.dialect.SQLServer2008Dialect" />

Local developer machine = OK

Testserver (catalina loghost...log) =

Schema-validation: wrong column type encountered in column [FehlerLevel] in table [Log]; found [text (Types#LONGVARCHAR)], but expecting [varchar(255) (Types#VARCHAR)]

Case 2:

<property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect" />

Local developer machine = OK

Testserver (catalina loghost...log) =

Schema-validation: wrong column type encountered in column [FehlerLevel] in table [Log]; found [text (Types#LONGVARCHAR)], but expecting [varchar(255) (Types#VARCHAR)]

Case 3:

Changed Entity, added @Lob

    @Lob
    @Column(name = "FehlerLevel", nullable = false)
    public String getFehlerLevel() {

Start with (like case 2):

<property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect" />

Local developer machine (console output) =

Schema-validation: wrong column type encountered in column [FehlerLevel] in table [Log]; found [varchar (Types#VARCHAR)], but expecting [text (Types#CLOB)]

Testserver = OK

__________Now it runs on testserver, but not on my local machine.

Case 4:

Entity with @Lob and with 2008-dialect (like case 1)::

<property name="hibernate.dialect" value="org.hibernate.dialect.SQLServer2008Dialect" />

Local developer machine = OK

Testserver (catalina loghost...log) =

Schema-validation: wrong column type encountered in column [FehlerLevel] in table [Log]; found [text (Types#LONGVARCHAR)], but expecting [varchar(max) (Types#CLOB)]

__________ Now it runs again on my local machine and again not on testserver

Whats going on here?

What is the solution for my problem?

All libs will be packed in the war-file and are available on the server in the "app\WEB-INF\lib" folder. Our tomcat admins told me they have no restrictions, so the app can use their own libs. This means there should be no difference between my local machine and the server. We have checked, that both uses the same database.

And ... can someone pls explain what is done/included by a schema-validation? Which libraries (hibernate, db-driver, etc.) are touched?

The difference in the messages if validation fails:
found [varchar (Types#VARCHAR)] --- local machine - (case 3)
found [text (Types#LONGVARCHAR)] --- testserver - (case 1, 2, 4)

So why there are different "founds"? From whom get´s hibernate this info "found"?

Solution

  • Does adding columnDefinition = "TEXT" to the @Column descriptor correct the issue? It sounds to me like the database type is TEXT and Hibernate is defaulting to a 255 length string. It also sounds like the database columns are not identical between your local machine and your test server.

    https://docs.jboss.org/hibernate/jpa/2.1/api/javax/persistence/Column.html