How to view the generated SQL

vas · **Joined:** Thu Aug 18, 2005 2:50 pm **Posts:** 4

how to view the generated SQl ? I can see the HSQL but wanted to see what it translates to when the HSQL exactly hits the database.

I presume there is a config file where I can turn on the sql trace.

a5sk4s · **Joined:** Fri Jan 07, 2005 5:46 pm **Posts:** 19

Use hibernate.show_sql true in either hibernate.properties or the corresponding XML syntax in hibernate.cfg.xml

d · **Joined:** Mon Jul 04, 2005 5:19 pm **Posts:** 720

Or put these in your hibernate.cfg.xml

<property name="show_sql">true</property>
<property name="use_sql_comments">true</property>

vas · **Joined:** Thu Aug 18, 2005 2:50 pm **Posts:** 4

a5sk4s wrote:

Use hibernate.show_sql true in either hibernate.properties or the corresponding XML syntax in hibernate.cfg.xml

I already have that and this is what I see in the console

The reason why I want to see this is I am not able to retrieve an Object and I thought looking at the sql would a starting point and then I can move on further.

Code:

public Panel loadByName(final String name) {
        return (Panel) getHibernateTemplate().execute(new HibernateCallback() {
                    public Object doInHibernate(Session session)
                        throws HibernateException {

                        String query = " from Panel p where p.name = :name ";
                        Query queryObject = session.createQuery(query);

                        queryObject.setString("name", name);
                        
                        List panels = queryObject.list();
                        // the list that i got back is null.


                        return null;
                    }
                });
    }

this is what i see in console.

Code:

10:35:46,758 INFO  [STDOUT] Hibernate: select panel0_.OBJECTID as OBJECTID, panel0_.LOCKVERSION as LOCKVERS2_, panel0_.CREATION_DATE as CREATION3_, panel0_.NAME as NAME, panel0_.TITLE as TITLE from PANEL2 panel0_ where (panel0_.NAME=? )
10:36:48,637 INFO  [STDOUT] Hibernate: select panel0_.OBJECTID as OBJECTID, panel0_.LOCKVERSION as LOCKVERS2_, panel0_.CREATION_DATE as CREATION3_, panel0_.NAME as NAME, panel0_.TITLE as TITLE from PANEL2 panel0_ where (panel0_.NAME=? )

see that the place holder is not replaced with what ever String that I had set it to?

nathanmoon · **Posted:** Thu Aug 18, 2005 4:50 pm

If you want to see what values are bound to the parameters, enable DEBUG logging in the org.hibernate logger

e.g.:

Code:

<logger name="org.hibernate" additivity="false">
      <level value="DEBUG"/>
      <appender-ref ref="SomeFile"/>
   </logger>

in log4j.xml.

You don't see the values replaced in the SQL output because that is done through JDBC.

vas · **Joined:** Thu Aug 18, 2005 2:50 pm **Posts:** 4

nathanmoon wrote:

If you want to see what values are bound to the parameters, enable DEBUG logging in the org.hibernate logger

e.g.:

Code:

<logger name="org.hibernate" additivity="false">
      <level value="DEBUG"/>
      <appender-ref ref="SomeFile"/>
   </logger>

in log4j.xml.

You don't see the values replaced in the SQL output because that is done through JDBC.

thank you I will do that.

By the way I could retrieve an Object and here is what I did

the name parameter was a String with white spaces and I replaced it with a name that did not have any white spaces and it worked. what is it that I would have to do inorder to pass a String with empty spaces like
"String with spaces" ?

a5sk4s · **Joined:** Fri Jan 07, 2005 5:46 pm **Posts:** 19

Your database may provide an SQL profiler, or you could use something like P6Spy (http://www.p6spy.com/), which works transparently as a JDBC data source.

vas · **Joined:** Thu Aug 18, 2005 2:50 pm **Posts:** 4

a5sk4s wrote:

Your database may provide an SQL profiler, or you could use something like P6Spy (http://www.p6spy.com/), which works transparently as a JDBC data source.

thanks for all the replies guys. I found the problem and didn't have to fix it ;-). I was logging in as a different schema user who does not have all the permissions to view some objects. bummer .....

d · **Joined:** Mon Jul 04, 2005 5:19 pm **Posts:** 720

So give one of us the points.