Hi all,

Using MyEclipse-8.8.4 and PostgreSQL-8.0.2 on Win XP SP2 machine, I tried to workout a simple sample, but no luck.

I get exeption:

log4j:WARN No appenders could be found for logger (net.sf.hibernate.cfg.Environment).
log4j:WARN Please initialize the log4j system properly.
Hibernate: select user0_.ID as ID, user0_.NAME as NAME from USER user0_
net.sf.hibernate.exception.SQLGrammarException: Could not execute query
at net.sf.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
at net.sf.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:30)
at net.sf.hibernate.impl.SessionImpl.convert(SessionImpl.java:4110)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1556)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at com.cardview.cvl.datafeeder.test.UserRunner.runMain(UserRunner.java:39)
at com.cardview.cvl.datafeeder.test.UserRunner.main(UserRunner.java:20)
Caused by: java.sql.SQLException: ERROR: column notation .id applied to type name, which is not a composite type
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1471)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1256)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:175)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:388)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:329)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:239)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:88)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:875)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:269)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
at net.sf.hibernate.loader.Loader.list(Loader.java:1024)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1553)
... 3 more

When I run my java sample test class


SQL SCRIPT used
--------------------
CREATE TABLE "public"."USER" (
"ID" BIGINT NOT NULL,
"NAME" VARCHAR NOT NULL,
CONSTRAINT "USER_pkey" PRIMARY KEY("ID")
) WITH OIDS;

ALTER TABLE "public"."USER"
ALTER COLUMN "NAME" SET STATISTICS 0;


Java class used to run the test:
-----------------------------------
public class UserRunner {

/**
* @param args
*/
public static void main(String[] args) {
UserRunner ur = new UserRunner();
try {
ur.runMain();
} catch (Exception e) {
e.printStackTrace();
}

}

private void runMain()throws Exception{

//Session session = PersistenceFactoryManager.getSession();
Session session = HibernateSessionFactory.currentSession();

Query query = session.createQuery("FROM User");
List list = query.list();

if(list.size() > 0){
Iterator it = list.iterator();
while(it.hasNext()){
User u = (User)it.next();
//- printout User detail
System.out.println("USER ID = " + u.getId());
System.out.println("USER NAME = " + u.getName());
System.out.println("----------");
}
} else {
System.out.println("QUERY result = zero!");
}

session.close();

System.out.println();
System.out.println("-------------- done! ------");
System.out.println();
}
}


Hibernate Config (generated by MyEclipse):
-------------------------------------------------
<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-2.0.dtd">

<!-- DO NOT EDIT: This is a generated file that is synchronized -->
<!-- by MyEclipse Hibernate tool integration. -->
<hibernate-configuration>

<session-factory>
<!-- properties -->
<property name="connection.username">postgres</property>
<property name="connection.url">jdbc:postgresql:TESTDB</property>
<property name="dialect">net.sf.hibernate.dialect.PostgreSQLDialect</property>
<property name="connection.password">pg-admin</property>
<property name="connection.driver_class">org.postgresql.Driver</property>
<property name="hibernate.show_sql">true</property>

<!-- mapping files -->
<mapping resource="mytest/domain/User.hbm.xml"/>

</session-factory>

</hibernate-configuration>


And the Hibernate Mapping file:
-----------------------------------
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >

<!-- DO NOT EDIT: This is a generated file that is synchronized -->
<!-- by MyEclipse Hibernate tool integration. -->
<!-- Created Fri Apr 22 14:01:47 GMT+08:00 2005 -->
<hibernate-mapping package="mytest.domain">

<class name="User" table="USER">
<id name="id" column="ID" type="java.lang.Long">
<generator class="assigned"/>
</id>

<property name="name" column="NAME" type="java.lang.String" not-null="true" />
</class>

</hibernate-mapping>


Any idea how to fix this?

Running the same sample on MS-SQLServer yielded an expected result (OK).

Also when I tried the "Generate Select in SQL Editor" of the DB Browser, it gave me this:

select ID, NAME from "public"."USER"

Executing that statement gave me an
ERROR : column "id" does not exist

However if I changed the select statement to :

select "ID", "NAME" from "public"."USER"

with "" on field names ("ID" and "NAME"), the execution is sucessfull.

Does this has something to do with the problem I encountered above, namely the net.sf.hibernate.exception.SQLGrammarException ....? If, so how do fix it??

Code:

I'm willing to bet that ID is a reserved word in PostgreSQL, so you can't use it for a column name unless you quote it. For the sake of argument, try renaming the field to "user_id", and then regenerate the mapping, did it work?

Also are you sure you didn't edit the mapping file? It looks different then other generated files I've seen.

support-rkalla wrote:

I'm willing to bet that ID is a reserved word in PostgreSQL, so you can't use it for a column name unless you quote it. For the sake of argument, try renaming the field to "user_id", and then regenerate the mapping, did it work?

I tried with even a new table arbitrarily named:

Quote:

create table YOU( ME int8(8) PRIMARY KEY not null, TELLING varchar(50) not null )

And still the problem stayed :-(

support-rkalla wrote:

Also are you sure you didn't edit the mapping file? It looks different then other generated files I've seen.

For the hibernate.cfg.xml generated file I added

Quote:

<property name="hibernate.show_sql">true</property>

since I wanted to see what sql statement was generated. Other than that, nothing was changed.

I tried a number of other tables, including a full import from a working db, when I tried using the DB Browser to execute a select statement, when not quoted [""], nothing worked. But when I used quote, then I worked just fine, returning the corect resultset.

When statement like is entered into the DB Browser and executed:

Quote:

SELECT * FROM you

The result is : ERROR: relation "you" does not exist

When the statement is changed to

Quote:

SELECT * FROM "YOU" or SELECT * FROM "public"."YOU"

Then the execution is OK. If I want to show any particular fields/columns I needed it to be quoted on the select statement to get it work properly, otherwise the execution of them would simply throw exceptions.

It seem that I needed to get the statement quoted, but do not know how it can be done using hibernate (since the generated sql shown on the log does not have any quote on them). :-(

Any idea how to get around this? Probably some tweaking on the PostgreSQL needed??

TIA.

Since I often use postgresql, I have run into this before. The problem is that the word 'user' is a reserved word in postgresql, however since your create script quotes the word it does not generate an error. In fact, the quotes in your create scripts around the column names are also contributing to the problems. Because you quote them when you create them, they have to be quoted everywhere you use them.

support-jeff wrote:

Since I often use postgresql, I have run into this before. The problem is that the word 'user' is a reserved word in postgresql, however since your create script quotes the word it does not generate an error. In fact, the quotes in your create scripts around the column names are also contributing to the problems. Because you quote them when you create them, they have to be quoted everywhere you use them.

Thank you! Thank you! Thank you Jeff!!! You just saved my weekend :-)

Hey, I'm new to both Hibernate & PostgreSQL. Guess it's obvious from my posts .....

Yeah, the USER being reserved word is abit bummer here, since I was thinking along no db tbl changes when moving to PostgreSQL from our working db, which has table, unfortunately, named "USER" ... :-(

Supposing I want to use this "" feature along with hibernate here, is there away to pass-in the "", so the query can be run sucessfully? That's I can run against sql script created with quotes for table names and columns?

Anyhow, many thanks for a quick response! At least I have away around it now. I am a happy customer :-))

No problem, glad to help.

Here is what you want:
http://www.hibernate.org/hib_docs/reference/en/html/mapping.html#mapping-quotedidentifiers