[NPRST]

I am working on the following:

Eclipse 3.1.0
MyEclipse 4.0.3
Apache Tomcat 5.5
Hibernate 3.0

I am getting an error, which is very generic and when I posted this error in forums.hibernate.com they advised me this
“You could turn on SQL output (hibernate.show_sql=true in your properties file or <property name=”hibernate.show_sql”>true</property> in your config file), then go through the last few SQL statements it issues looking for bad SQL grammar. Once you track it down, you can figure out where it came from.”

Well I am not sure how to turn those settings ON. Since I am using Hibernate that is embedded with my eclipse. Can anyone please help me out with this.

I dont know how to view the Error log report file.

And also, for every error I am getting, I get a note saying:

note The full stack trace of the root cause is available in the Apache Tomcat/5.5.11 logs.

Can any one please tell me how to view the full stack of errors since when I try to look at logs I dont see anything.

[Haris Peco]

NPRST,

You can turn on hibernate.show_sql=true in MyEclipse config editor – open your hibernate.cf.xml, find tab ‘Properties’ click add, choose
show_sql and set value to true
You can add properties direct to hibernate.cfg.xml like this :
…
<property name=”show_sql”>true</property>
…
in configuration editor MyEclipse will do it for you

for stack trace you can see error log view in eclipse/myeclipse (Windows-Show View-Other-PDE Runtime-Error log view) or check files in TOMCAT_HOME/logs (you will find different *.log files and catalina.out)

Best regards

PS
use myEclipse 4.1.1 – this version have a lot enhancements and fixes

[NPRST]

Snpe,

Thanks a lot for your quick reply! I have installed myeclipse 4.1.1 and using it.

I am still getting the same errors, may be you can help me. I am actually running stored procedures of SQL Server 2000 from my program. The stored procedure looks like this:

CREATE PROCEDURE dbo.NewResultProc  AS
 
DECLARE @GUID uniqueidentifier
SET @GUID = NEWID()

Insert into ResultsReview (ID, ReJobId, ReEmc, RePaygrade, ReDutyType, ReJobType, ReLocationRegion, ReSailorId, ReRow, Message)
select @GUID, * from DNNResults

select * from ResultsReview
return @@ROWCOUNT
GO

I also added <property name=”show_sql”>true</property>in the hibernate.cfg.xml.
Here is my mapping:

       <sql-query name="NewResultProc" callable= "true">
       <return alias="ResultsReview" class="com.command.hibernate.ResultsReview">
           <return-property name="uniId" column="UniID" />
           <return-property name="reJobId" column="ReJobId" />
           <return-property name="reEmc" column="ReEmc" />
           <return-property name="rePaygrade" column="RePaygrade" />
           <return-property name="reDutyType" column="ReDutyType" />
           <return-property name="reLocationRegion" column="ReLocationRegion" />
           <return-property name="reSailorId" column="ReSailorId" />
           <return-property name="reRow" column="ReRow" />
           <return-property name="message" column="Message" />
       </return>
        { ? = call NewResultProc() }       
    </sql-query>

          Query query = session.getNamedQuery("NewResultProc");
            session.flush();
            tx.commit();
            return query.list();

The error I am getting is as follows when I run the last statement that is query.list()

java.lang.RuntimeException: org.hibernate.exception.SQLGrammarException: could not execute query
com.command.hibernate.FinalResultReviewService.getResultsReviewdataList(FinalResultReviewService.java:150)
org.apache.jsp.jsp.ResultAdded_jsp._jspService(org.apache.jsp.jsp.ResultAdded_jsp:102)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.struts.action.RequestProcessor.doForward(RequestProcessor.java:1069)
org.apache.struts.action.RequestProcessor.processForwardConfig(RequestProcessor.java:455)
org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:279)
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

note The full stack trace of the root cause is available in the Apache Tomcat/5.5.11 logs.
Apache Tomcat/5.5.11

org.eclipse.ui.PartInitException: Warning: Detected recursive attempt by part com.genuitec.eclipse.webdesigner.jsp to create itself (this is probably, but not necessarily, a bug)
at org.eclipse.ui.internal.WorkbenchPartReference.getPart(WorkbenchPartReference.java:543)
at org.eclipse.ui.internal.EditorReference.getEditor(EditorReference.java:214)
at org.eclipse.ui.internal.EditorManager.findEditor(EditorManager.java:477)
at org.eclipse.ui.internal.EditorManager.findEditor(EditorManager.java:402)
at org.eclipse.ui.internal.EditorManager.findEditor(EditorManager.java:372)
at org.eclipse.ui.internal.WorkbenchPage.findEditor(WorkbenchPage.java:1772)
at com.genuitec.eclipse.core.util.EditorUtil.getEditorForInput(EditorUtil.java:158)
at com.genuitec.eclipse.core.util.EditorUtil.checkConflictingEditor(EditorUtil.java:219)
at com.genuitec.eclipse.webdesigner2.HTMLMultiPageEditorPart.init(HTMLMultiPageEditorPart.java:878)
at org.eclipse.ui.internal.EditorManager.createSite(EditorManager.java:773)
at org.eclipse.ui.internal.EditorReference.createPartHelper(EditorReference.java:572)
at org.eclipse.ui.internal.EditorReference.createPart(EditorReference.java:365)
at org.eclipse.ui.internal.WorkbenchPartReference.getPart(WorkbenchPartReference.java:552)
at org.eclipse.ui.internal.EditorReference.getEditor(EditorReference.java:214)
at org.eclipse.ui.internal.EditorManager.findEditor(EditorManager.java:477)
at org.eclipse.ui.internal.EditorManager.findEditor(EditorManager.java:402)
at org.eclipse.ui.internal.WorkbenchPage.busyOpenEditorBatched(WorkbenchPage.java:2274)
at org.eclipse.ui.internal.WorkbenchPage.busyOpenEditor(WorkbenchPage.java:2258)
at org.eclipse.ui.internal.WorkbenchPage.access$9(WorkbenchPage.java:2250)
at org.eclipse.ui.internal.WorkbenchPage$9.run(WorkbenchPage.java:2236)
at org.eclipse.swt.custom.BusyIndicator.showWhile(BusyIndicator.java:69)
at org.eclipse.ui.internal.WorkbenchPage.openEditor(WorkbenchPage.java:2231)
at org.eclipse.ui.internal.WorkbenchPage.openEditor(WorkbenchPage.java:2212)
at org.eclipse.debug.internal.ui.sourcelookup.SourceLookupFacility$1.run(SourceLookupFacility.java:353)
at org.eclipse.swt.custom.BusyIndicator.showWhile(BusyIndicator.java:69)
at org.eclipse.debug.internal.ui.sourcelookup.SourceLookupFacility.openEditor(SourceLookupFacility.java:362)
at org.eclipse.debug.internal.ui.sourcelookup.SourceLookupFacility.openEditor(SourceLookupFacility.java:289)
at org.eclipse.debug.internal.ui.sourcelookup.SourceLookupFacility.display(SourceLookupFacility.java:209)
at org.eclipse.debug.ui.DebugUITools.displaySource(DebugUITools.java:673)
at org.eclipse.debug.internal.ui.views.launch.LaunchView$SourceDisplayJob.runInUIThread(LaunchView.java:209)
at org.eclipse.ui.progress.UIJob$1.run(UIJob.java:93)
at org.eclipse.swt.widgets.RunnableLock.run(RunnableLock.java:35)
at org.eclipse.swt.widgets.Synchronizer.runAsyncMessages(Synchronizer.java:123)
at org.eclipse.swt.widgets.Display.runAsyncMessages(Display.java:3057)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:2716)
at org.eclipse.ui.internal.Workbench.runEventLoop(Workbench.java:1699)
at org.eclipse.ui.internal.Workbench.runUI(Workbench.java:1663)
at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:367)
at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:143)
at org.eclipse.ui.internal.ide.IDEApplication.run(IDEApplication.java:103)
at org.eclipse.core.internal.runtime.PlatformActivator$1.run(PlatformActivator.java:226)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:376)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:163)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.eclipse.core.launcher.Main.invokeFramework(Main.java:334)
at org.eclipse.core.launcher.Main.basicRun(Main.java:278)
at org.eclipse.core.launcher.Main.run(Main.java:973)
at org.eclipse.core.launcher.Main.main(Main.java:948)

Can you please help me out with this!!! Thanks.

[Haris Peco]

NPRST,

Hibernate claim that you have bad sql syntax – it can be that your property isn’t good defined or that you have to add some parameters
I can’t tell you except you send me create commnad for your table which procedure use

Best regards

Advice : hibernate haven’t good support for stored procedure – you can achieve this function wtih standard hibernate mechanism (procedure/methods in java), but if you need procedures I advice you that use jdbc mechanism (got connection with session.getConnection() and execute your procedure with jdbc CallableStatement).Hibernate have too much constraints for stored procedures and it is natural – stored procedures are against hibernate concepts

[NPRST]

Well, I dont think the SQL syntax is wrong since, the stored procedure when executed from the SQL server 2000 works fine, the only problem comes when I try to integrate it. I am not creating the table from the program, but rather the table is already created and exists in the SQL Server 2000. Can you please tell me in more detail how to use session.getConnection()…. My total application is on Hibernate, now I dont want to change everything to conventional JDBC.

[Haris Peco]

My total application is on Hibernate, now I dont want to change everything to conventional JDBC.

I don’t advice that you change everything to JDBC.Hibernate is great product and you need use hibernate , of course, but hibernate support for stored procedures ins’t too good (this is natural, because hibernate do thing like stored procedures and stored procedures are very database vendor specific ).
However,It is possible that you use this procedure (procedure which return result set) in hibernate at all

I am not creating the table from the program, but rather the table is already created and exists in the SQL Server 2000

maybe, but i don’t know how this tables looks and if your named query defined correct
for example , your ResultsReview table must have columns uniID,ReJobId … If some column isn’t defined
in your <sql-query> (or have wrong name) then it will work in SQL server, but don’t work in hibernate

Q: When you exceute procedure from SQL server do you add some parameters and what is type of parameters (if exists) ?

Can you please tell me in more detail how to use session.getConnection()….

session.getConnection() return standard JDBC java.sql.Connection and you can execute procedure with
standard CallableStatement from jdbc

[Haris Peco]

NPRST,

You have this insert command (i supposet that it is table definition, because it work in sql server)

Insert into ResultsReview (ID, ReJobId, ReEmc, RePaygrade, ReDutyType, ReJobType, ReLocationRegion, ReSailorId, ReRow, Message) 

but your first definition in sql-query is :

<return-property name="uniId" column="UniID" /> 

you need (probably)

<return-property name="uniId" column="ID" /> 

best

[Haris Peco]

NPRST,

I made test with mssql stored procedure (hibernate and jdbc) for you.You can find idea for using stored procedures

1. make simple eclipse project and add hibernate 3 possibilities and make configuration for your sql server

2. make simple table in sql server (in your database)

create table "dbo"."TEST"(
"ID" bigint PRIMARY KEY,
"NAME" varchar(30),
"FK" bigint)

3. Make hibernate mapping in MyEclipse – you will get Test.java and Test.hbm.xml

/**

 * Test generated by MyEclipse - Hibernate Tools

 */



public class Test  implements java.io.Serializable {





    // Fields    



     private long id;

     private String name;

     private long fk;





    // Constructors



    /** default constructor */

    public Test() {

    }



    /** minimal constructor */

    public Test(long id) {

        this.id = id;

    }

    

    /** full constructor */

    public Test(long id, String name, long fk) {

        this.id = id;

        this.name = name;

        this.fk = fk;

    }



   

    // Property accessors



    public long getId() {

        return this.id;

    }

    

    public void setId(long id) {

        this.id = id;

    }



    public String getName() {

        return this.name;

    }

    

    public void setName(String name) {

        this.name = name;

    }



    public long getFk() {

        return this.fk;

    }

    

    public void setFk(long fk) {

        this.fk = fk;

    }



}

<?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="com.genuitec.mssql.Test" table="TEST" schema="dbo" catalog="dbtest">
        <id name="id" type="long">
            <column name="ID" />
            <generator class="assigned" />
        </id>
        <property name="name" type="string">
            <column name="NAME" length="30" />
        </property>
        <property name="fk" type="long">
            <column name="FK" />
        </property>
    </class>
    
</hibernate-mapping>

4. make stored procedure (must return result set for hibernate; jdbc havent this constraint)

create procedure dbo.sptTest AS
select * from test

5. add sql-query in *hbm.xml (it can be in Test.hbm.xml) like this

<sql-query name="sptTest" callable= "true">
       <return alias="Test" class="com.genuitec.mssql.Test">
           <return-property name="id" column="id" />
           <return-property name="name" column="name" />
           <return-property name="fk" column="fk" />
       </return>
        { call dbo.sptTest() }       
    </sql-query>

6. Test for using stored procedures with hibernate

public void testStoredProcedure() {
        Session session = getSession();
        Transaction tx = session.beginTransaction();
        Query query = session.getNamedQuery("sptTest");
        
        List list = query.list();
        System.out.println("list.size()" + list.size());
        session.flush();
        tx.commit();
        session.close();
    }
    

7. test with jdbc (from hibernate)

public void testStoredProcedureJDBC() throws SQLException {
        Connection connection = getSession().connection();
        CallableStatement stmt = connection.prepareCall("{call dbo.sptTest}");
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            System.out.println("id " + rs.getInt(1));
            System.out.println("name " + rs.getString(2));
            System.out.println("fk " + rs.getString(3));
        }
    }

notes : for jdbc you needn’t define sql-query and you haven’t constraint that sql must return result set

Best

[NPRST]

Thanks SNPE.
The test with JDBC(from Hibernate) worked out for me with few changes. Thanks for your help.

Guys please dont forget to close the resultset and callablestatement.

[Author]

Posts