-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users (Deutsch)

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 1 post ] 
  Previous topic | Next topic 
Author Message
Zwitsch
 Post subject: Native SQLQuery fragt einfach alle Spalten ab???
PostPosted: Tue Oct 25, 2005 5:35 am 
Newbie

Joined: Fri Aug 05, 2005 9:55 am
Posts: 9
Hallo,
ich bastel gerade an einem Native SQL Statement und hab da ein komisches Phänomen. Ich habe eine Abfrage mit sums und avgs und frage auch nicht alle Spalten der Tabelle (und somit des Mappings) ab.

Wenn ich jetzt mein Statement absetze kriege ich immer den Fehler, dass er eine bestimmte Spalte SCHL0_ nicht findet - im Statement kommt diese Spalte allerdings nie vor (siehe unten). Hierzu muss man wissen, dass die Index-Spalte 'schl' heißt. Wenn ich schl mit in die Abfrage einbeziehe wird aus der SCHL0_-Fehlermeldung eine key2_1_0_.

Warum fragt er im Hintergrund einfach alles ab??? Ich bekomme dieses Statement nie zu sehen. Das, welches ich sehe, funktioniert einwandfrei.

Ich hoffe mir kann jemand helfen.

Danke!
Zwitsch

Java-Code:
Code:
String stmt = "select label, count(skill.schl) as {skill.schl}, "
                  + "sum(skill.acd_calls) as {skill.acd_calls}, "
                + "avg(skill.avg_speed_ans_min) as {skill.avg_speed_ans_min}, "
                + "avg(skill.avg_speed_ans_sec) as {skill.avg_speed_ans_sec}, "
                + "sum(skill.aband_calls) as {skill.aband_calls}, "
                + "avg(skill.avg_aband_time_min) as {skill.avg_aband_time_min}, "
                + "avg(skill.avg_aband_time_sec) as {skill.avg_aband_time_sec}, "
                + "avg(skill.avg_talk_time_min) as {skill.avg_talk_time_min}, "
                + "avg(skill.avg_talk_time_sec) as {skill.avg_talk_time_sec}, "
                + "sum(skill.total_after_call_min) as {skill.total_after_call_min}, "
                + "sum(skill.total_after_call_sec) as {skill.total_after_call_sec}, "
                + "sum(skill.flow_in) as {skill.flow_in}, "
                + "sum(skill.flow_out) as {skill.flow_out}, "
                + "sum(skill.total_aux_other_min) as {skill.total_aux_other_min}, "
                + "sum(skill.total_aux_other_sec) as {skill.total_aux_other_sec}, "
                + "avg(skill.avg_staff) as {skill.avg_staff}, "
                + "sum(skill.serv_levl) as {skill.serv_levl} "
                + "from SKILLS {skill} " + "where skill.srt = 'D' "
                + "and month(skill.hours) = :month ";

     [...]

        SQLQuery q = getSession().createSQLQuery(stmt);

     [...]

        q.setString("month", Integer.toString(month));
        List result = q.addEntity("skill",TelSkill.class).list();


Hibernate version: 3

Mapping:
Code:
<hibernate-mapping>
   <class name="com.wlgore.test.TelSkill" table="SKILLS">
      <id name="schl" column="SCHL">
         <generator class="increment" />
      </id>
      <property name="key1" />
      <property name="label" />
      <property name="hours" type="timestamp" />
      <property name="srt" />
      <property name="acd_calls" />
      <property name="avg_speed_ans_min" />
      <property name="avg_speed_ans_sec" />
      <property name="aband_calls" />
      <property name="avg_aband_time_min" />
      <property name="avg_aband_time_sec" />
      <property name="avg_talk_time_min" />
      <property name="avg_talk_time_sec" />
      <property name="total_after_call_min" />
      <property name="total_after_call_sec" />
      <property name="flow_in" />
      <property name="flow_out" />
      <property name="total_aux_other_min" />
      <property name="total_aux_other_sec" />
      <property name="avg_staff" />
      <property name="serv_levl" />

   </class>
</hibernate-mapping>


Full stack trace of any exception that occurs:
Code:
[25.10.05 11:11:40:163 CEST]   68b3f4 SystemOut     O Hibernate: select label, sum(skill.acd_calls) as acd6_1_0_, avg(skill.avg_speed_ans_min) as avg7_1_0_, avg(skill.avg_speed_ans_sec) as avg8_1_0_, sum(skill.aband_calls) as aband9_1_0_, avg(skill.avg_aband_time_min) as avg10_1_0_, avg(skill.avg_aband_time_sec) as avg11_1_0_, avg(skill.avg_talk_time_min) as avg12_1_0_, avg(skill.avg_talk_time_sec) as avg13_1_0_, sum(skill.total_after_call_min) as total14_1_0_, sum(skill.total_after_call_sec) as total15_1_0_, sum(skill.flow_in) as flow16_1_0_, sum(skill.flow_out) as flow17_1_0_, sum(skill.total_aux_other_min) as total18_1_0_, sum(skill.total_aux_other_sec) as total19_1_0_, avg(skill.avg_staff) as avg20_1_0_, sum(skill.serv_levl) as serv21_1_0_ from SKILLS skill where skill.srt = 'D' and month(skill.hours) = ?  and ( skill.key1=?) group by skill.key1, skill.label order by skill.label asc
[25.10.05 11:11:40:263 CEST]   68b3f4 JDBCException W org.hibernate.util.JDBCExceptionReporter  SQL Error: 0, SQLState: 07009
[25.10.05 11:11:40:263 CEST]   68b3f4 JDBCException E org.hibernate.util.JDBCExceptionReporter  Invalid column name SCHL0_.
[25.10.05 11:11:40:273 CEST]   68b3f4 WebGroup      E SRVE0026E: [Servlet-Fehler]-[ReportSkills]: org.hibernate.exception.SQLGrammarException: could not execute query
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.loader.Loader.doList(Loader.java:1596)
   at org.hibernate.loader.Loader.list(Loader.java:1577)
   at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:112)
   at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1414)
   at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:153)
   at com.wlgore.test.SkillsAction.getSumReportPerMonth(SkillsAction.java:138)
   at com.wlgore.test.ReportSkills.doPost(ReportSkills.java:86)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
   at com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(StrictServletInstance.java:110)
   at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(StrictLifecycleServlet.java:174)
   at com.ibm.ws.webcontainer.servlet.IdleServletState.service(StrictLifecycleServlet.java:313)
   at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecycleServlet.java:116)
   at com.ibm.ws.webcontainer.servlet.ServletInstance.service(ServletInstance.java:283)
   at com.ibm.ws.webcontainer.servlet.ValidServletReferenceState.dispatch(ValidServletReferenceState.java:42)
   at com.ibm.ws.webcontainer.servlet.ServletInstanceReference.dispatch(ServletInstanceReference.java:40)
   at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:1019)
   at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:592)
   at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:204)
   at com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:125)
   at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:286)
   at com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:71)
   at com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:182)
   at com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:334)
   at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:56)
   at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:615)
   at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:439)
   at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:912)
Caused by: java.sql.SQLException: Invalid column name SCHL0_.
   at net.sourceforge.jtds.jdbc.JtdsResultSet.findColumn(JtdsResultSet.java:938)
   at net.sourceforge.jtds.jdbc.JtdsResultSet.getInt(JtdsResultSet.java:942)
   at org.hibernate.type.IntegerType.get(IntegerType.java:26)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:77)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:68)
   at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:759)
   at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:292)
   at org.hibernate.loader.Loader.doQuery(Loader.java:412)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
   at org.hibernate.loader.Loader.doList(Loader.java:1593)
   ... 27 more



Name and version of the database you are using: MSSQL 2000

The generated SQL (show_sql=true):
Code:
select label,
sum(skill.acd_calls) as acd6_1_0_,
avg(skill.avg_speed_ans_min) as avg7_1_0_,
avg(skill.avg_speed_ans_sec) as avg8_1_0_,
sum(skill.aband_calls) as aband9_1_0_,
avg(skill.avg_aband_time_min) as avg10_1_0_,
avg(skill.avg_aband_time_sec) as avg11_1_0_,
avg(skill.avg_talk_time_min) as avg12_1_0_,
avg(skill.avg_talk_time_sec) as avg13_1_0_,
sum(skill.total_after_call_min) as total14_1_0_,
sum(skill.total_after_call_sec) as total15_1_0_,
sum(skill.flow_in) as flow16_1_0_,
sum(skill.flow_out) as flow17_1_0_,
sum(skill.total_aux_other_min) as total18_1_0_,
sum(skill.total_aux_other_sec) as total19_1_0_,
avg(skill.avg_staff) as avg20_1_0_,
sum(skill.serv_levl) as serv21_1_0_ from SKILLS skill where skill.srt = 'D' and month(skill.hours) = ?  and ( skill.key1=?) group by skill.key1,
skill.label order by skill.label asc

Debug level Hibernate log excerpt:
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 1 post ] 

Board index » Hibernate & Java Persistence » Hibernate Users (Deutsch)

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.