[2.0.1] SQL Server 2008 - Invalid object name.

[David Byron]

Hi!,

I got the following application error while executing   http://localhost:9000/ :

[PersistenceException: org.hibernate.exception.SQLGrammarException: Invalid object name 'TablaUsuarios'.] 

My application is a play2 + jpa-hibernate + sql server , the table has a clustered primary key. 

application.conf:

db.default.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver

db.default.url="jdbc:sqlserver://localhost\\SQLEXPRESS:1433;"

db.default.databaseName=DBase1

db.default.user=sa

db.default.password=test

db.default.jndiName=DefaultDS

# You can expose this datasource via JNDI if needed (Useful for JPA)

jpa.default=defaultPersistenceUnit

peristence.xml

<persistence xmlns="http://java.sun.com/xml/ns/persistence"

             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

             xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"

             version="2.0">

             

    <persistence-unit name="defaultPersistenceUnit" transaction-type="RESOURCE_LOCAL">

        <provider>org.hibernate.ejb.HibernatePersistence</provider>

        <non-jta-data-source>DefaultDS</non-jta-data-source>

        <properties>

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

            </properties>

    </persistence-unit>

    

</persistence>

build.scala

    val appDependencies = Seq(

        "mysql" % "mysql-connector-java" % "5.1.13",

        "org.hibernate" % "hibernate-entitymanager" % "4.1.1.Final",

        "org.hibernate" % "hibernate-core" % "4.1.1.Final",

        "org.hibernate" % "hibernate-validator" % "4.2.0.Final"

)

         

models.TablaUsuarios

package models;

import java.util.List;

import javax.persistence.Entity;

import javax.persistence.Id;

import javax.persistence.Table;

import play.data.validation.Constraints.Required;

import play.db.ebean.Model.Finder;

import play.db.jpa.JPA;

@Entity

public class TablaUsuarios{

@Id

public String IdUsuario;

@Required

public String Nombre;

public String Password;

@Required

public Integer Nivel;

@Required

public Integer Nivel2;

public static TablaUsuarios findById(String id) {

return JPA.em().find(TablaUsuarios.class, id);

}

public static List<TablaUsuarios> all() {

 return find.all();

}

public static Finder<Long,TablaUsuarios> find = new Finder(

String.class, TablaUsuarios.class

);

}

I tried also with "extends Model " , but with the same error.

controllers.Application

  @Transactional  

  public static Result maindef() throws IOException {

TablaUsuarios tu = new TablaUsuarios();

 

tu.findById("usuario1");  

return ok("/index.html");

  }

The table creation script: (Notice that has a clustered primary key)

REATE TABLE [dbo].[TablaUsuarios](

[IdUsuario] [varchar](32) NOT NULL,

[Nombre] [varchar](100) NOT NULL,

[Password] [varchar](32) NULL,

[Nivel] [tinyint] NOT NULL,

[Nivel2] [tinyint] NOT NUL

 CONSTRAINT [PK_TablaUsuarios] PRIMARY KEY CLUSTERED 

(

[IdUsuario] ASC,

[Nombre] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

Console error:

----------------------------

[info] play - datasource [jdbc:sqlserver://localhost\SQLEXPRESS:1433;] bound to

JNDI as DefaultDS

[info] play - database [default] connected at jdbc:sqlserver://localhost\sqlexpr

ess:1433;xopenStates=false;sendTimeAsDatetime=true;trustServerCertificate=false;

sendStringParametersAsUnicode=true;selectMethod=direct;responseBuffering=adaptiv

e;packetSize=8000;loginTimeout=15;lockTimeout=-1;lastUpdateCount=true;encrypt=fa

lse;disableStatementPooling=true;applicationName=Microsoft SQL Server JDBC Drive

r;

[info] play - Application started (Dev)

[error] o.h.e.j.s.SqlExceptionHelper - Invalid object name 'TablaUsuarios'.

[error] application -

! @6apcn6b99 - Internal server error, for request [GET /] ->

play.core.ActionInvoker$$anonfun$receive$1$$anon$1: Execution exception [[Persis

tenceException: org.hibernate.exception.SQLGrammarException: Invalid object name

 'TablaUsuarios'.]]

        at play.core.ActionInvoker$$anonfun$receive$1.apply(Invoker.scala:134) [

play_2.9.1.jar:2.0.1]

        at play.core.ActionInvoker$$anonfun$receive$1.apply(Invoker.scala:115) [

play_2.9.1.jar:2.0.1]

        at akka.actor.Actor$class.apply(Actor.scala:311) [akka-actor.jar:2.0.1]

        at play.core.ActionInvoker.apply(Invoker.scala:113) [play_2.9.1.jar:2.0.

1]

        at akka.actor.ActorCell.invoke(ActorCell.scala:619) [akka-actor.jar:2.0.

1]

        at akka.dispatch.Mailbox.processMailbox(Mailbox.scala:196) [akka-actor.j

ar:2.0.1]

Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGr

ammarException: Invalid object name 'TablaUsuarios'.

        at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityMan

agerImpl.java:1360) ~[hibernate-entitymanager-4.1.1.Final.jar:4.1.1.Final]

        at org.hibernate.ejb.AbstractEntityManagerImpl.find(AbstractEntityManage

rImpl.java:817) ~[hibernate-entitymanager-4.1.1.Final.jar:4.1.1.Final]

        at org.hibernate.ejb.AbstractEntityManagerImpl.find(AbstractEntityManage

rImpl.java:771) ~[hibernate-entitymanager-4.1.1.Final.jar:4.1.1.Final]

        at models.TablaUsuarios.findById(TablaUsuarios.java:34) ~[classes/:na]

        at controllers.Application.maindef(Application.java:56) ~[classes/:na]

        at Routes$$anonfun$routes$1$$anonfun$apply$1$$anonfun$apply$2.apply(rout

es_routing.scala:72) ~[classes/:na]

Caused by: org.hibernate.exception.SQLGrammarException: Invalid object name 'Tab

laUsuarios'.

        at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(S

QLStateConversionDelegate.java:122) ~[hibernate-core-4.1.1.Final.jar:4.1.1.Final

]

        at org.hibernate.exception.internal.StandardSQLExceptionConverter.conver

t(StandardSQLExceptionConverter.java:49) ~[hibernate-core-4.1.1.Final.jar:4.1.1.

Final]

        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlException

Helper.java:125) ~[hibernate-core-4.1.1.Final.jar:4.1.1.Final]

        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlException

Helper.java:110) ~[hibernate-core-4.1.1.Final.jar:4.1.1.Final]

        at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandle

r.continueInvocation(AbstractStatementProxyHandler.java:129) ~[hibernate-core-4.

1.1.Final.jar:4.1.1.Final]

        at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(

AbstractProxyHandler.java:81) ~[hibernate-core-4.1.1.Final.jar:4.1.1.Final]

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name

'TablaUsuarios'.

        at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError

(SQLServerException.java:197) ~[sqljdbc4.jar:na]

        at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServ

erStatement.java:1493) ~[sqljdbc4.jar:na]

        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePrep

aredStatement(SQLServerPreparedStatement.java:390) ~[sqljdbc4.jar:na]

        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecC

md.doExecute(SQLServerPreparedStatement.java:340) ~[sqljdbc4.jar:na]

        at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575) ~

[sqljdbc4.jar:na]

        at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLSe

rverConnection.java:1400) ~[sqljdbc4.jar:na]

-----------------------------

Thanks!.

[ezin]

Hello,

I had the same problem. I updated the configuration in persistence.xml:

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

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

Br,

Nils