[jasoncwarner]

Hey Guys –

I am using Eclipse 3.2 with MyEclipse 5.0M1….on mac osx…with java 1.5….hibernate 3.0…

This is driving me nuts. I have gone through and tried some basic stuff with Hibernate and I was able to do what I wanted against a small test db. Once I move over and do exactly the same stuff against work DB, I get nothing to work. I will try and post all the relevent details, but here is what I am getting.

When I try to do a simple ‘from Zip’ in the HQL editor, I get an ‘org.hibernate.exception.SQLGrammarException: could not execute query’

Just so you don’t think I am lying ( 😉 ), here is the info on the object form the hibernate dynamic query translator window:

SQL #0 types: Zip
—————–
select
zip0_.Zip as Zip89_,
zip0_.Longitude as Longitude89_,
zip0_.Latitude as Latitude89_,
zip0_.City as City89_,
zip0_.State as State89_,
zip0_.TimeZone as TimeZone89_,
zip0_.TimeZoneName as TimeZone7_89_,
zip0_.AreaCode as AreaCode89_,
zip0_.DST as DST89_
from
xxxxxxx(changed this so work doesn’t know 😉 ).dbo.Zips zip0_

here is Zip.java

// default package
// Generated by MyEclipse - Hibernate Tools



/**
 * Zip generated by MyEclipse - Hibernate Tools
 */
public class Zip extends AbstractZip implements java.io.Serializable {

    // Constructors

    /** default constructor */
    public Zip() {
    }

    
   
}

and AbstractZip

// default package



/**
 * AbstractZip generated by MyEclipse - Hibernate Tools
 */

public abstract class AbstractZip  implements java.io.Serializable {


    // Fields    

     private ZipId id;


    // Constructors

    /** default constructor */
    public AbstractZip() {
    }

    

   
    // Property accessors

    public ZipId getId() {
        return this.id;
    }
    
    public void setId(ZipId id) {
        this.id = id;
    }}

and the mapping for those….

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- 
    Mapping file autogenerated by MyEclipse - Hibernate Tools
-->
<hibernate-mapping>
    <class name="Zip" table="Zips" schema="dbo" catalog="PhishingNet-Build50">
        <composite-id name="id" class="ZipId">
            <key-property name="zip" type="java.lang.String">
                <column name="Zip" />
            </key-property>
            <key-property name="longitude" type="java.lang.Double">
                <column name="Longitude" precision="53" scale="0" />
            </key-property>
            <key-property name="latitude" type="java.lang.Double">
                <column name="Latitude" precision="53" scale="0" />
            </key-property>
            <key-property name="city" type="java.lang.String">
                <column name="City" />
            </key-property>
            <key-property name="state" type="java.lang.String">
                <column name="State" length="50" />
            </key-property>
            <key-property name="timeZone" type="java.lang.Short">
                <column name="TimeZone" />
            </key-property>
            <key-property name="timeZoneName" type="java.lang.String">
                <column name="TimeZoneName" length="2" />
            </key-property>
            <key-property name="areaCode" type="java.lang.String">
                <column name="AreaCode" length="50" />
            </key-property>
            <key-property name="dst" type="java.lang.String">
                <column name="DST" length="1" />
            </key-property>
        </composite-id>
    </class>
</hibernate-mapping>

and finally, my hibernate.cfg.xml file

<?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">xxxxxx</property>
        <property name="connection.url">
            jdbc:jtds:sqlserver://xxxxx:1433;DatabaseName=xxxxx
        </property>
        <property name="dialect">
            org.hibernate.dialect.SQLServerDialect
        </property>
        <property name="myeclipse.connection.profile">
            yyyyyy
        </property>
        <property name="connection.password">xxxxxx</property>
        <property name="connection.driver_class">
            net.sourceforge.jtds.jdbc.Driver
        </property>
                 <mapping resource="Zip.hbm.xml" />
        </session-factory>

</hibernate-configuration>

Any help would be appreciated. I just don’t know what to do. I googled the exception and of course there was nothing on it. Stupid google 😉

Thanks,
jason

[Haris Peco]

Jason,

SQLGrammarException is hibernate implementation JDBCException.This is from API for this class :

Implementation of JDBCException indicating that the SQL sent to the database server was invalid (syntax error, invalid object
references, etc).

It mean that something bad in your database – can you send us ‘create table script for your database – you have to use ‘generate ddl ‘ on Zips table in MyEclipse Db Browser)

you can try execute generated sql (from dynamic query translatorview ) in sqleditor too

Best regards

[jasoncwarner]

Thanks for the quick reply! Here is what I have to give you:

 create table "xxxxx"."dbo"."Zips"(
        "Zip" nvarchar(255) not null,
       "Longitude" float(53) null,
       "Latitude" float(53) null,
       "City" nvarchar(255) null,
       "State" nvarchar(50) null,
       "TimeZone" smallint null,
       "TimeZoneName" nvarchar(2) null,
       "AreaCode" nvarchar(50) null,
       "DST" char(1) null
    );

    create index "IX_Zips" on "xxxxx"."dbo"."Zips"("Zip");

I was able to execute SQL against the db in the form of this generated SQL:

    select
        "Zip",
        "Longitude",
        "Latitude",
        "City",
        "State",
        "TimeZone",
        "TimeZoneName",
        "AreaCode",
        "DST" 
    from
        "xxxx"."dbo"."Zips"

Let me give some background: This application already exists in the world. I am a new employee at a company and they tasked me with moving to hibernate to move off of MSSQL server. I have run into nothing but problems when trying to do that and thep people I am working for/with are less than sympathetic about those problems …. so … I hope I can figure this out some other way.

thanks,
jason

[jasoncwarner]

Ok..I have a rather interesting and weird follow up. The server I am connecting to has several test dbs on it. The one I am trying to use is called this:

productname-Build50

If I open up the SQL editor and am connected to this DB, I cannot generate DDL for ZIPS table on this DB. I get this error:

Error generate ddl -> Server user ‘xxxxxx’ is not a valid user in database ‘productname’.

note how the database named is ‘productname’ and not ‘productname-Build50′?

Well, curiously enough, if I connect to a different DB in the SQL editor (something like productname_clean_master’), I CAN generate the DDL against productname-Build50 db. (but my previous errors of the SQLGrammarExceptions are all still there, as I would have expected them to be).

I am wondering if the ‘-‘ is causing some weird problems somewhere deep down?

Will investigate further, but if anyone else has ideas….send ’em my way!

Thanks,
jason

[Haris Peco]

Jason,

Yes, there is problem with ddl generation if catalog name have “-” in name – we will fix this in next release

I have tried reproduce your hibernate problem and you will have same problem with database which contain ‘-‘ in name
your sql query from dynamic query view will not execute in database productname-Build50
this query will not work, too

select * from productname-Build50 .dbo.Zips

you have to use quote for this database/catalog name

select * from “productname-Build50”.dbo.Zips

for quick fix in your mapping you can change catalog mapping to

...
<hibernate-mapping>
    <class name="Zip" table="Zips" schema="dbo" catalog="[PhishingNet-Build50]">
...
 

see [ and ] around catalog name – hibernate request backtick (`) and translate to datbase quote char, but doesn’t do it for catalog – it is probably bug in hibernate

It mean, your case execute perfect in database like ‘dbtest’, but no ‘test-db’ – change catalog name in mapping to [test-db] work for me

We will discuss about adding quote chars in mapping file, too

Best regards

PS
You need PK in table and then MyEclipse will generate normal ID and no composite from all columns

[Haris Peco]

Jason,

Please try 5.0M2, if you can – there are some enhancements in db tools, hibernate mapping adn DAO generating

Thanks

[jasoncwarner]

Hey again guys –

Thanks for the info on the []. That did solve the grammarException…however…

I upgraded to 5.0M2, as suggested…so that is what I am using now.

Now, when I run this HQL query (same mappings and everything else as before but the [] are around the catalog now), do not get any results…there is just a tab with the name of the query and no data.

from Zip where id.zip = '00210'

SQL #0 types: Zip
-----------------
select
  zip0_.Zip as Zip0_,
  zip0_.Longitude as Longitude0_,
  zip0_.Latitude as Latitude0_,
  zip0_.City as City0_,
  zip0_.State as State0_,
  zip0_.TimeZone as TimeZone0_,
  zip0_.TimeZoneName as TimeZone7_0_,
  zip0_.AreaCode as AreaCode0_,
  zip0_.DST as DST0_ 
 from
  [xxxxx-Build50].dbo.Zips zip0_ 
 where
  zip0_.Zip='00210'

Nothing shows up.

But, when I run the same SQL query…it all works out just fine.

    select
        "Zip",
        "Longitude",
        "Latitude",
        "City",
        "State",
        "TimeZone",
        "TimeZoneName",
        "AreaCode",
        "DST" 
    from
        "xxxxx-Build50"."dbo"."Zips"
    where 
        zip = '00210'

I get all the data I was excpeting back here.

Any ideas?

jason

[Haris Peco]

Jason,

I suppose that you have some column null and hibernate (and relational database at all) doesn’t allow null in primary key (id)
Hibernate return all rows, but they are null values.You have null exception in log (i suppose)

You have to set primary key on table and regenerate mappings

Best regards

[jasoncwarner]

Ok…more digging and I was able to get a log file of what went wrong….

It appears that a whole lot went wrong…

Here is a link to the log file: Log File

Thanks,
jason[/url]

[Haris Peco]

Jason,

I can reproduce your case and I saw log.
Can you make primary key for your table and regenerate mappings, please ?

Thanks

[jasoncwarner]

@support-snpe wrote:

Jason,

I can reproduce your case and I saw log.
Can you make primary key for your table and regenerate mappings, please ?

Thanks

Thanks for the reply.

I am trying to regenerate the mappings using MyEclipse 5.0M2 and I am not able to generate the .java files?!?

For some reason none of them are being generated anymore. I am able to see all the .xml and .hbm.xml files.

And just so there is no confusion, I am using :

*** Date: Mon Jul 10 16:12:55 PDT 2006

*** System properties:
OS=WindowsXP
OS version=5.1
Java version=1.5.0_07

*** MyEclipse details:
MyEclipse Enterprise Workbench

Version: 5.0 Milestone 2
Build id: 20060703-5.0-M2

*** Eclipse details:
Eclipse SDK

Version: 3.2.0
Build id: M20060629-1905

Eclipse Graphical Editing Framework

Version: 3.2.0.v20060626
Build id: 20060627-0816

Eclipse Platform

Version: 3.2.0.v20060609m-AgOexn6hlEUsvBO
Build id: M20060629-1905

Eclipse RCP

Version: 3.2.0.v20060609m-SVDNgVrNoh-MeGG
Build id: M20060629-1905

Eclipse Java Development Tools

Version: 3.2.0.v20060609m-F7snq1fxia-Z4XP
Build id: M20060629-1905

Eclipse Plug-in Development Environment

Version: 3.2.0.v20060609m------6zXJJzJzJq
Build id: M20060629-1905

Eclipse Project SDK

Version: 3.2.0.v20060609m-GNq6oQq-7es-Y81
Build id: M20060629-1905

Eclipse startup command=-os
win32
-ws
win32
-arch
x86
-launcher
C:\apps\eclipse\eclipse.exe
-name
Eclipse
-showsplash
600
-exitdata
152c_58
-vm
C:\WINDOWS\system32\javaw.exe

[jasoncwarner]

Ok…I am stupid. I just figured out that you can take the .hbm.xml file and generate the POJOs, so that is ok.

I am looking at the rest of it now…thanks…sorry for being a dunce.

[Haris Peco]

Jason,

No problem – there are a lot functionality and it’s hard find little, good features
Please, generate PK for table – hibernate work bad for table without PK and we can’t do much about this – except this PK is necessary for relational table (but it’s theory and no important always)
However, you will have a lot problems with tables without PK and hibernate

Best

[Author]

Posts