Hi All:

I am using Hibernate 3.2,MyEclipse 6, SQL 2005, and SQL DRIVER:jdtd-1.2
When I used hibernate to create a table called "user", I get the error:
16:30:24,484 DEBUG SchemaExport:289 - Incorrect syntax near the keyword 'user'.

This is my Hibernate.cfg.xml:
<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<!-- Generated by MyEclipse Hibernate Tools. -->
<hibernate-configuration>

<session-factory>
<property name="connection.username">sa</property>
<property name="connection.url">jdbc:jtds:sqlserver://xxx:1433/examscam</property>
<property name="dialect">org.hibernate.dialect.SQLServerDialect</property>
<property name="myeclipse.connection.profile">net.sourceforge.jtds.jdbc.Driver</property>
<property name="connection.password">mychau1</property>
<property name="connection.driver_class">net.sourceforge.jtds.jdbc.Driver</property>
<property name="transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
<property name="current_session_context_class">thread</property>
<property name="hibernate.show_sql">true</property>
</session-factory>

</hibernate-configuration>

This is my java code:

package com.examscam.model;
import javax.persistence.Basic;import javax.persistence.Column;
import javax.persistence.Entity;import javax.persistence.GeneratedValue;
import javax.persistence.Id;import javax.persistence.Table;
import javax.persistence.Temporal;import javax.persistence.TemporalType;
import javax.persistence.Transient;import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.tool.hbm2ddl.SchemaExport;

@Entity
@Table(name = "user", schema = "examscam")
public class User {
private Long id;
private String loginName;
private String password;
private String encryptedPassword;
private String emailAddress;
private Boolean verified;
private java.util.Date lastAccessTime;
private java.util.Calendar registrationDate;

public User(){
//registrationDate = new java.util.GregorianCalendar();
//lastAccessTime = new java.util.Date();
//verified = false;
}

@Transient
public String getEncryptedPassword() {
return encryptedPassword;
}
public void setEncryptedPassword(String ep) {
this.encryptedPassword = ep;
}
@Id
@GeneratedValue
@Column(name = "id")
public Long getId() { return id; }

public void setId(Long id) { this.id = id; }

@Column(name = "login_name")
public String getLoginName() {
return loginName;
}
public void setLoginName(String loginName) {
this.loginName = loginName;
}

@Column(name = "password", nullable=false)
public String getPassword() {
return password;
}

public void setPassword(String password) {
this.password = password;
}

public String getEmailAddress() {
return emailAddress;
}

@Temporal(TemporalType.TIMESTAMP)
public java.util.Date getLastAccessTime() {
return lastAccessTime;
}

@Temporal(TemporalType.DATE)
public java.util.Calendar getRegistrationDate() {
return registrationDate;
}

@Basic
public Boolean isVerified() {
return verified;
}

public void setEmailAddress(String emailAddress) {
this.emailAddress = emailAddress;
}

public void setLastAccessTime(java.util.Date lastAccessTime) {
this.lastAccessTime = lastAccessTime;
}

public void setRegistrationDate(java.util.Calendar registrationDate){
this.registrationDate = registrationDate;
}

public void setVerified(Boolean verified) {
this.verified = verified;
}

public String toString() {
return id + " : " + loginName + " : " + password + " : " + emailAddress;
}

public static void main(String args[])
{
AnnotationConfiguration config = new AnnotationConfiguration();
config.addAnnotatedClass(User.class);
config.configure();
new SchemaExport(config).create(true,true);
}
}

When I runned it I get the following error. I donot seem to be able to create the table called "user" in my schema examscam.
16:30:23,640 INFO Version:15 - Hibernate Annotations 3.3.0.GA
16:30:23,656 INFO Environment:514 - Hibernate 3.2.5
16:30:23,656 INFO Environment:547 - hibernate.properties not found
16:30:23,656 INFO Environment:681 - Bytecode provider name : cglib
16:30:23,671 INFO Environment:598 - using JDK 1.4 java.sql.Timestamp handling
16:30:23,734 INFO Configuration:1426 - configuring from resource: /hibernate.cfg.xml
16:30:23,781 INFO Configuration:1403 - Configuration resource: /hibernate.cfg.xml
16:30:23,984 INFO Configuration:1541 - Configured SessionFactory: null
16:30:24,000 INFO Dialect:152 - Using dialect: org.hibernate.dialect.SQLServerDialect
16:30:24,093 INFO AnnotationBinder:398 - Binding entity from annotated class: com.examscam.model.User
16:30:24,125 INFO EntityBinder:420 - Bind entity com.examscam.model.User on table user
16:30:24,187 INFO Version:17 - Hibernate Validator 3.0.0.GA
16:30:24,359 INFO SchemaExport:154 - Running hbm2ddl schema export
16:30:24,359 DEBUG SchemaExport:170 - import file not found: /import.sql
16:30:24,359 INFO SchemaExport:179 - exporting generated schema to database
16:30:24,359 INFO DriverManagerConnectionProvider:41 - Using Hibernate built-in connection pool (not for production use!)
16:30:24,359 INFO DriverManagerConnectionProvider:42 - Hibernate connection pool size: 20
16:30:24,359 INFO DriverManagerConnectionProvider:45 - autocommit mode: false
16:30:24,375 INFO DriverManagerConnectionProvider:80 - using driver: net.sourceforge.jtds.jdbc.Driver at URL: jdbc:jtds:sqlserver://xxx:1433/examscam
16:30:24,375 INFO DriverManagerConnectionProvider:86 - connection properties: {user=sa, password=****}
drop table examscam.user
16:30:24,484 DEBUG SchemaExport:303 - drop table examscam.user
16:30:24,484 DEBUG SchemaExport:288 - Unsuccessful: drop table examscam.user
16:30:24,484 DEBUG SchemaExport:289 - Incorrect syntax near the keyword 'user'.
create table examscam.user (id numeric(19,0) identity not null, emailAddress varchar(255) null, lastAccessTime datetime null, login_name varchar(255) null, password varchar(255) not null, registrationDate datetime null, verified tinyint null, primary key (id))
16:30:24,484 DEBUG SchemaExport:303 - create table examscam.user (id numeric(19,0) identity not null, emailAddress varchar(255) null, lastAccessTime datetime null, login_name varchar(255) null, password varchar(255) not null, registrationDate datetime null, verified tinyint null, primary key (id))
16:30:24,484 ERROR SchemaExport:274 - Unsuccessful: create table examscam.user (id numeric(19,0) identity not null, emailAddress varchar(255) null, lastAccessTime datetime null, login_name varchar(255) null, password varchar(255) not null, registrationDate datetime null, verified tinyint null, primary key (id))
16:30:24,484 ERROR SchemaExport:275 - Incorrect syntax near the keyword 'user'.
16:30:24,484 INFO SchemaExport:196 - schema export complete
16:30:24,484 INFO DriverManagerConnectionProvider:147 - cleaning up connection pool: jdbc:jtds:sqlserver://xxx:1433/examscam


Any hint or help would be greatly appreciated.

Yours,

Frustrated

Hi,

I would guess that user is probably a restricted keyword, you will have to quote the table name in order for it to work.

From section 5.4 of the docs:

You may force Hibernate to quote an identifier in the generated SQL by enclosing the table or column name in backticks in the mapping document. Hibernate will use the correct quotation style for the SQL Dialect (usually double quotes, but brackets for SQL Server and backticks for MySQL).

<class name="LineItem" table="`Line Item`">
<id name="id" column="`Item Id`"/><generator class="assigned"/></id>
<property name="itemNumber" column="`Item #`"/>
...
</class>

Hi:

Thank for replying to my problem!!!
I am using Annotations.
What do I needed to change for the annotation:

@Entity
@Table(name = "user", schema = "examscam")


I used the above annotations for Mysql it is fine but I get the error as stated in the previous message body for Microsoft SQL 2005 AND Oracle 10g.

Yours,

Frustrated.

Hi:

Thank for replying to my problem!!!
I am using Annotations.
What do I needed to change for the annotation:

@Entity
@Table(name = "user", schema = "examscam")


I used the above annotations for Mysql it is fine but I get the error as stated in the previous message body for Microsoft SQL 2005 AND Oracle 10g.

Yours,

Frustrated.

I think if you changed it to:

@Entity
@Table(name = "`user`", schema = "examscam")

it should work.

Hi:

I appreciated your help but unfortunately the code still does not work:
Here is the java code:
package com.examscam.model;
import javax.persistence.Basic;import javax.persistence.Column;
import javax.persistence.Entity;import javax.persistence.GeneratedValue;
import javax.persistence.Id;import javax.persistence.Table;
import javax.persistence.Temporal;import javax.persistence.TemporalType;
import javax.persistence.Transient;import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.tool.hbm2ddl.SchemaExport;

@Entity
@Table(name = "`user`", schema = "examscam")
public class User
{
private Long id;
private String password;

@Id
@GeneratedValue
public Long getId()
{
return id;
}

public void setId(Long id)
{
this.id = id;
}

public String getPassword()
{
return password;
}

public void setPassword(String password)
{
this.password = password;
}
public static void main(String args[])
{
AnnotationConfiguration config = new AnnotationConfiguration();
config.addAnnotatedClass(User.class);
config.configure();
new SchemaExport(config).create(true,true);
}
}


Here are the log4j log:
23:11:50,953 INFO Version:15 - Hibernate Annotations 3.3.0.GA
23:11:51,171 INFO Environment:514 - Hibernate 3.2.5
23:11:51,187 INFO Environment:547 - hibernate.properties not found
23:11:51,234 INFO Environment:681 - Bytecode provider name : cglib
23:11:51,421 INFO Environment:598 - using JDK 1.4 java.sql.Timestamp handling
23:11:51,546 INFO Configuration:1426 - configuring from resource: /hibernate.cfg.xml
23:11:51,546 INFO Configuration:1403 - Configuration resource: /hibernate.cfg.xml
23:11:52,359 INFO Configuration:1541 - Configured SessionFactory: null
23:11:52,421 INFO Dialect:152 - Using dialect: org.hibernate.dialect.SQLServerDialect
23:11:52,781 INFO AnnotationBinder:398 - Binding entity from annotated class: com.examscam.model.User
23:11:52,906 INFO EntityBinder:420 - Bind entity com.examscam.model.User on table user
23:11:52,968 INFO Version:17 - Hibernate Validator 3.0.0.GA
23:11:53,234 INFO SchemaExport:154 - Running hbm2ddl schema export
23:11:53,234 DEBUG SchemaExport:170 - import file not found: /import.sql
23:11:53,234 INFO SchemaExport:179 - exporting generated schema to database
23:11:53,250 INFO DriverManagerConnectionProvider:41 - Using Hibernate built-in connection pool (not for production use!)
23:11:53,250 INFO DriverManagerConnectionProvider:42 - Hibernate connection pool size: 20
23:11:53,250 INFO DriverManagerConnectionProvider:45 - autocommit mode: false
23:11:53,265 INFO DriverManagerConnectionProvider:80 - using driver: net.sourceforge.jtds.jdbc.Driver at URL: jdbc:jtds:sqlserver://localhost:1433/examscam
23:11:53,265 INFO DriverManagerConnectionProvider:86 - connection properties: {user=sa, password=****}
drop table examscam.[user]
23:11:53,734 DEBUG SchemaExport:303 - drop table examscam.[user]
23:11:53,812 DEBUG SchemaExport:288 - Unsuccessful: drop table examscam.[user]
23:11:53,812 DEBUG SchemaExport:289 - Cannot drop the table 'examscam.user', because it does not exist or you do not have permission.
create table examscam.[user] (id numeric(19,0) identity not null, password varchar(255) null, primary key (id))
23:11:53,812 DEBUG SchemaExport:303 - create table examscam.[user] (id numeric(19,0) identity not null, password varchar(255) null, primary key (id))
23:11:54,093 ERROR SchemaExport:274 - Unsuccessful: create table examscam.[user] (id numeric(19,0) identity not null, password varchar(255) null, primary key (id))
23:11:54,093 ERROR SchemaExport:275 - The specified schema name "examscam" either does not exist or you do not have permission to use it.
23:11:54,093 INFO SchemaExport:196 - schema export complete
23:11:54,093 INFO DriverManagerConnectionProvider:147 - cleaning up connection pool: jdbc:jtds:sqlserver://localhost:1433/examscam


Yours,

Thank You.

Check your local database contains the schema "examscam"

ERROR SchemaExport:275 - The specified schema name "examscam" either does not exist or you do not have permission to use it.

jdbc:jtds:sqlserver://localhost:1433/examscam
the connection string should be provided with database name at the end
jdbc:jtds:sqlserver://localhost:1433/[databasename]
instead of a schema name.

Hi:

I am using SQL 2005.
I did an odbc connection and I can connect to examscam using id:sa password:mychau1

This is the same id and password used for the hibernate call in User.java.

I think it might have something to do with the fact that in sql 2005 they have schema.

The database examscam is correct.
I just don't know if I have to put in the hibernate.cfg.xml what the schema name is.

For example, in Northwind database, the tables are dbo.Customers, dbo.Employees.


Maybe when Hibernate try to create the User table it doesn't know what schema to put in front of the table.

Yours,

Frustrated

I didn't use annotations, but I just see it from hibernate homepage that the schema doesn't seem to be a mandatory field, why don't you simply use
@Table(name = "'user'")

http://www.hibernate.org/hib_docs/annot ... ml_single/

Hi:

I took out the schema annotation but I still get the same problem or errors.
Hibernate gets the schema from the hibernate.cfg.xml file.


Yours,

Albert Lam

Would you post your hibernate.cfg.xml here also ?
I don't seem to contain schema in it but database connection detail only.

And may I know if the exception still shows failed to drop table in
examscam.user ?

Hi All:

I want to thank everyone for your time and help.
The problem was that the word User is a reserver word in sql 2005 and I was trying to create a table in sql 2005.

Here is what I done to make it work:

import javax.persistence.Basic;import javax.persistence.Column;
import javax.persistence.Entity;import javax.persistence.GeneratedValue;
import javax.persistence.Id;import javax.persistence.Table;
import javax.persistence.Temporal;import javax.persistence.TemporalType;
import javax.persistence.Transient;import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.AnnotationConfiguration;
import org.hibernate.tool.hbm2ddl.SchemaExport;

@Entity
@Table(name = "[user]", schema = "dbo", catalog = "examscam")
public class User
{
private Long id;
private String password;

@Id
@GeneratedValue
public Long getId()
{
return id;
}

public void setId(Long id)
{
this.id = id;
}

public String getPassword()
{
return password;
}

public void setPassword(String password)
{
this.password = password;
}
public static void main(String args[])
{
AnnotationConfiguration config = new AnnotationConfiguration();
config.addAnnotatedClass(User.class);
config.configure();
new SchemaExport(config).create(true,true);
}
}


I hoped this will help other people who encounter the same problem.

Yours,

Happy