Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 12 posts ] 
Author Message
 Post subject: Error while using the DATEADD function in Hibernate+SQL
PostPosted: Tue Mar 10, 2009 4:11 am 
Newbie

Joined: Tue Mar 10, 2009 3:53 am
Posts: 5
I am getting the errors while executing DATEADD function in hibernate. My database is Microsoft SQL server.

EX:

Query q1 = this.session
.createQuery("select DATEADD(Month, -3, 3-1-2008)");


Error:

Caused by: java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.tree.MethodNode
\-[METHOD_CALL] MethodNode: '('
+-[METHOD_NAME] IdentNode: 'DATEADD'{originalText=DATEADD}
\-[EXPR_LIST] SqlNode: 'exprList'
+-[IDENT] IdentNode: 'Month' {originalText=Month}
+-[UNARY_MINUS] UnaryArithmeticNode: '-'
| \-[NUM_INT] LiteralNode: '3'
\-[MINUS] BinaryArithmeticOperatorNode: '-' {dataType=org.hibernate.type.IntegerType@b49448}
+-[MINUS] BinaryArithmeticOperatorNode: '-' {dataType=org.hibernate.type.IntegerType@b49448}
| +-[NUM_INT] LiteralNode: '3'
| \-[NUM_INT] LiteralNode: '1'
\-[NUM_INT] LiteralNode: '2008'

at org.hibernate.hql.ast.tree.SelectClause.initializeExplicitSelectClause(SelectClause.java:145)
at org.hibernate.hql.ast.HqlSqlWalker.useSelectClause(HqlSqlWalker.java:705)
at org.


Please help on this :)

Thanks in advance

_________________
thanks,
kiran.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Mar 10, 2009 8:43 am 
Expert
Expert

Joined: Fri Jan 30, 2009 1:47 am
Posts: 292
Location: Bangalore, India
I think you will have to subclass the SQLServerDialect, and from the constructor call
Code:
registerFunction("dateadd", new StandardSQLFunction("dateadd", Hibernate.TIMESTAMP));

_________________
Regards,
Litty Preeth


Top
 Profile  
 
 Post subject:
PostPosted: Tue Mar 10, 2009 10:10 am 
Newbie

Joined: Tue Mar 10, 2009 3:53 am
Posts: 5
littypreethkr wrote:
I think you will have to subclass the SQLServerDialect, and from the constructor call
Code:
registerFunction("dateadd", new StandardSQLFunction("dateadd", Hibernate.TIMESTAMP));

---------------------------------------------------------------------------

Thank you very much for your reply.

I have tried with ur solution.. but still error is coming..

steps i did:

1) creared the new class:

import java.sql.Types;

import org.hibernate.Hibernate;
import org.hibernate.dialect.SQLServerDialect;
import org.hibernate.dialect.function.NoArgSQLFunction;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.dialect.function.StandardSQLFunction;

public class RegisterFunction extends SQLServerDialect {
/**
* Constructs
*/
public RegisterFunction() {
super();
registerColumnType(Types.DATE, "DATE");
registerColumnType(Types.TIME, "TIME");
registerColumnType(Types.TIMESTAMP, "TIMESTAMP");

registerFunction("DATEADD", new StandardSQLFunction("DATEADD",Hibernate.TIMESTAMP));


....

}



2) The query is :

Query q1 = this.session
.createQuery("select DATEADD(Month, -3, current_date())");



the error is :

Caused by: java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.tree.MethodNode
\-[METHOD_CALL] MethodNode: '('
+-[METHOD_NAME] IdentNode: 'DATEADD' {originalText=DATEADD}
\-[EXPR_LIST] SqlNode: 'exprList'
+-[IDENT] IdentNode: 'Month' {originalText=Month}
+-[UNARY_MINUS] UnaryArithmeticNode: '-'
| \-[NUM_INT] LiteralNode: '3'
\-[METHOD_CALL] MethodNode: '('
+-[METHOD_NAME] IdentNode: 'current_date' {originalText=current_date}
\-[EXPR_LIST] SqlNode: 'exprList'

at org.hibernate.hql.ast.tree.SelectClause.initializeExplicitSelectClause(SelectClause.java:145)



----------------------------------------

Please let me know if i did any mistake .....

thanks.

_________________
thanks,
kiran.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Mar 10, 2009 5:13 pm 
Regular
Regular

Joined: Mon Jan 05, 2009 6:42 pm
Posts: 99
Location: IL
Here is how it worked for SQL Server 2005

Class RegisterFunction
Code:
public class RegisterFunction extends SQLServerDialect{
   public RegisterFunction() {
      super();
      registerColumnType(Types.DATE, "DATE");
      registerColumnType(Types.TIME, "TIME");
      registerColumnType(Types.TIMESTAMP, "TIMESTAMP");
      //registerFunction("DATEADD", new StandardSQLFunction("DATEADD",Hibernate.TIMESTAMP));
      registerFunction( "dateadd", new VarArgsSQLFunction( Hibernate.TIMESTAMP, "dateadd(", ",", ")" ) );

   }


Modify the hibernate.properties to use this Class.

In the Unit test
Two things to notice:-
1) I could not use current_date() function instead its getdate() function.
2) Hibernate would only query it on a valid Hibernate mapped entity.

Code:
public void tesSQLFun()throws Exception{
      Session session=somehow get session;
      //Query q1=session.createQuery("select DATEADD(Month, -3, '3-1-2008')from User");
      Query q1=session.createQuery("select DATEADD(Month, -3, GETDATE()) from User");
      List records =q1.list();
      if(records !=null){
         System.out.println("records "+records.get(0));
      }
      

   }

Unit test output
Hibernate: select dateadd(Month,-3,'3-1-2008') as col_0_0_ from USR usr0_
records 2007-12-01 00:00:00.0

Hibernate: select dateadd(Month,-3,getdate()) as col_0_0_ from USR usr0_
records 2008-12-10 15:08:08.15


Top
 Profile  
 
 Post subject:
PostPosted: Thu Mar 12, 2009 9:45 am 
Newbie

Joined: Tue Mar 10, 2009 3:53 am
Posts: 5
latha1119 wrote:
Here is how it worked for SQL Server 2005

Class RegisterFunction
Code:
public class RegisterFunction extends SQLServerDialect{
   public RegisterFunction() {
      super();
      registerColumnType(Types.DATE, "DATE");
      registerColumnType(Types.TIME, "TIME");
      registerColumnType(Types.TIMESTAMP, "TIMESTAMP");
      //registerFunction("DATEADD", new StandardSQLFunction("DATEADD",Hibernate.TIMESTAMP));
      registerFunction( "dateadd", new VarArgsSQLFunction( Hibernate.TIMESTAMP, "dateadd(", ",", ")" ) );

   }


Modify the hibernate.properties to use this Class.

In the Unit test
Two things to notice:-
1) I could not use current_date() function instead its getdate() function.
2) Hibernate would only query it on a valid Hibernate mapped entity.

Code:
public void tesSQLFun()throws Exception{
      Session session=somehow get session;
      //Query q1=session.createQuery("select DATEADD(Month, -3, '3-1-2008')from User");
      Query q1=session.createQuery("select DATEADD(Month, -3, GETDATE()) from User");
      List records =q1.list();
      if(records !=null){
         System.out.println("records "+records.get(0));
      }
      

   }

Unit test output
Hibernate: select dateadd(Month,-3,'3-1-2008') as col_0_0_ from USR usr0_
records 2007-12-01 00:00:00.0

Hibernate: select dateadd(Month,-3,getdate()) as col_0_0_ from USR usr0_
records 2008-12-10 15:08:08.15



---------------------------------------------------------

Hello,

Thank you for you support and all.

I have one query regarding your solution. My question is how to map the RegisterFunction class to my CRUD class.

I have extend the RegisterFunction class and implemented my functionality. But i got again error.

I created the class, extend the RegisterFunction Class and implemented the below stuff ..

public class SubContractHome extends RegisterFunction {

Query q1=session.createQuery("select DATEADD(Month, -3, GETDATE()) from SubContract");
List records =q1.list();
if(records !=null){
System.out.println("records "+records.get(0));
}

------ }
----------------------------------

Error:

EBUG org.hibernate.hql.ast.QueryTranslatorImpl [http-8080-1] [BUS] 12 Mar 2009 19:06:56,530 :parse() - HQL: select DATEADD(Month, -3, GETDATE()) from com.data.SubContract
DEBUG org.hibernate.hql.ast.AST [http-8080-1] [BUS] 12 Mar 2009 19:06:56,561 :--- HQL AST ---
\-[QUERY] 'query'
\-[SELECT_FROM] 'SELECT_FROM'
+-[FROM] 'from'
| \-[RANGE] 'RANGE'
| \-[DOT] '.'
| +-[DOT] '.'
| | +-[DOT] '.'
| | | +-[DOT] '.'
| | | | +-[IDENT] 'com'
| | \-[IDENT] 'data'
| \-[IDENT] 'SubContract'
\-[SELECT] 'select'
\-[METHOD_CALL] '('
+-[IDENT] 'DATEADD'
\-[EXPR_LIST] 'exprList'
+-[IDENT] 'Month'
+-[UNARY_MINUS] '-'
| \-[NUM_INT] '3'
\-[METHOD_CALL] '('
+-[IDENT] 'GETDATE'
\-[EXPR_LIST] 'exprList'

DEBUG org.hibernate.hql.ast.ErrorCounter [http-8080-1] [BUS] 12 Mar 2009 19:06:56,561 :throwQueryException() : no errors
DEBUG org.hibernate.hql.antlr.HqlSqlBaseWalker [http-8080-1] [BUS] 12 Mar 2009 19:06:56,561 :select << begin [level=1, statement=select]
DEBUG org.hibernate.hql.ast.tree.FromElement [http-8080-1] [BUS] 12 Mar 2009 19:06:56,561 :FromClause{level=1} : com.data.SubContract (no alias) -> subcontrac0_
DEBUG org.hibernate.hql.antlr.HqlSqlBaseWalker [http-8080-1] [BUS] 12 Mar 2009 19:06:56,593 :select : finishing up [level=1, statement=select]
DEBUG org.hibernate.hql.ast.HqlSqlWalker [http-8080-1] [BUS] 12 Mar 2009 19:06:56,593 :processQuery() : ( SELECT ( {select clause} ( ( DATEADD ( exprList Month ( - 3 ) ( ( GETDATE exprList ) ) ) ) ( FromClause{level=1} dbo.SubContract subcontrac0_ ) )
ERROR com.web.util.ExceptionFilter [http-8080-1] [BUS] 12 Mar 2009 19:07:01,968 :Unexpected error
org.apache.jasper.JasperException: javax.el.ELException: Error reading 'allToBeRenewed' on type com.web.common.SubcontractCRUD
at org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:522)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:416)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:337)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:266)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.web.util.ExceptionFilter.doFilter(ExceptionFilter.java:38)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:433)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
at java.lang.Thread.run(Unknown Source)
Caused by: javax.el.ELException: Error reading 'allToBeRenewed' on type com.web.common.SubcontractCRUD
at javax.el.BeanELResolver.getValue(BeanELResolver.java:66)
at javax.el.CompositeELResolver.getValue(CompositeELResolver.java:53)
at org.apache.el.parser.AstValue.getValue(AstValue.java:114)
at org.apache.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:186)
at org.apache.jasper.runtime.PageContextImpl.proprietaryEvaluate(PageContextImpl.java:923)
at org.apache.jsp.SubcontractsToBeRenewed_jsp._jspx_meth_c_005fset_005f0(SubcontractsToBeRenewed_jsp.java:829)
at org.apache.jsp.SubcontractsToBeRenewed_jsp._jspService(SubcontractsToBeRenewed_jsp.java:670)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)
... 19 more
Caused by: java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.tree.MethodNode
\-[METHOD_CALL] MethodNode: '('
+-[METHOD_NAME] IdentNode: 'DATEADD' {originalText=DATEADD}
\-[EXPR_LIST] SqlNode: 'exprList'
+-[IDENT] IdentNode: 'Month' {originalText=Month}
+-[UNARY_MINUS] UnaryArithmeticNode: '-'
| \-[NUM_INT] LiteralNode: '3'
\-[METHOD_CALL] MethodNode: '('
+-[METHOD_NAME] IdentNode: 'GETDATE' {originalText=GETDATE}
\-[EXPR_LIST] SqlNode: 'exprList'

at org.hibernate.hql.ast.tree.SelectClause.initializeExplicitSelectClause(SelectClause.java:145)
at org.hibernate.hql.ast.HqlSqlWalker.useSelectClause(HqlSqlWalker.java:705)
---------------------------------------------

Could u Please respond on this.

_________________
thanks,
kiran.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Mar 12, 2009 11:31 am 
Regular
Regular

Joined: Mon Jan 05, 2009 6:42 pm
Posts: 99
Location: IL
Hi kiran,
1)What version of SQL Server database you are using? did you execute the query in the database and make sure it works fine.
select DATEADD(Month, -3, GETDATE())

2) Have you tried to modify the hibernate.properties to use the Register function dialect at all?and did your query worked?
something like this in hibernate.properties:-
hibernate.dialect <<your package>>.RegisterFunction
you need not extend the RegisterFunction
Just
Code:
public class SubContractHome  {
..//your query should work.
}


3)I'm not sure if you can apply the custom Dialect only to a given CRUD operation or to a given DAO. I guess the only way Hibernate knows about the custom dialect is while creating the Session factory. You can post another question to the forum on this one.

4)Also try using a simple Hibernate entity in your domain which has few associations to other entities.

I don't know what this error means:
org.apache.jasper.JasperException: javax.el.ELException: Error reading 'allToBeRenewed' on type com.web.common.SubcontractCRUD

if you write a query like below does it work at all?

Code:
public class SubContractHome  {

Query q1=session.createQuery("from SubContract");
List records =q1.list();
if(records !=null){
System.out.println("records "+records.get(0));
}


Top
 Profile  
 
 Post subject:
PostPosted: Mon Mar 16, 2009 8:02 am 
Newbie

Joined: Tue Mar 10, 2009 3:53 am
Posts: 5
latha1119 wrote:
Hi kiran,
1)What version of SQL Server database you are using? did you execute the query in the database and make sure it works fine.
select DATEADD(Month, -3, GETDATE())

2) Have you tried to modify the hibernate.properties to use the Register function dialect at all?and did your query worked?
something like this in hibernate.properties:-
hibernate.dialect <<your package>>.RegisterFunction
you need not extend the RegisterFunction
Just
Code:
public class SubContractHome  {
..//your query should work.
}


3)I'm not sure if you can apply the custom Dialect only to a given CRUD operation or to a given DAO. I guess the only way Hibernate knows about the custom dialect is while creating the Session factory. You can post another question to the forum on this one.

4)Also try using a simple Hibernate entity in your domain which has few associations to other entities.

I don't know what this error means:
org.apache.jasper.JasperException: javax.el.ELException: Error reading 'allToBeRenewed' on type com.web.common.SubcontractCRUD

if you write a query like below does it work at all?

Code:
public class SubContractHome  {

Query q1=session.createQuery("from SubContract");
List records =q1.list();
if(records !=null){
System.out.println("records "+records.get(0));
}

--------------------------------------------------------------

Hi Latha,

Thank you for ur inputs.

1) I am using the SQL Server 2005 and select DATEADD(Month, -3, GETDATE()) query also working fine.

2) I am not using the hibernate.properties file and using the hibernate.cfg.xml and set the <property name="dialect">
org.hibernate.dialect.SQLServerDialect
</property>....

Also i tried by extending the RegisterFunction class.


3) I did not understand the 4th point completly.

Regarding org.apache.jasper.JasperException is related to application..sorry to confuse :)

Could you please respond..

_________________
thanks,
kiran.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Mar 16, 2009 10:57 am 
Regular
Regular

Joined: Mon Jan 05, 2009 6:42 pm
Posts: 99
Location: IL
Kiran,
RegisterFunction that you have created is your new Dialect that hibernate needs to point to.In the hibernate.cfg.xml and set the <property name="dialect">
yourpackage.RegisterFunction
</property>....

and you should be good to go.
Hope this helps,
Latha.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Mar 18, 2009 9:17 am 
Newbie

Joined: Tue Mar 10, 2009 3:53 am
Posts: 5
latha1119 wrote:
Kiran,
RegisterFunction that you have created is your new Dialect that hibernate needs to point to.In the hibernate.cfg.xml and set the <property name="dialect">
yourpackage.RegisterFunction
</property>....

and you should be good to go.
Hope this helps,
Latha.

-------------------------------

Hi Latha,

I did the same as u mentioned in the last post and not working. Still i am trying ...Thank u for all and let u know the updates..

_________________
thanks,
kiran.


Top
 Profile  
 
 Post subject: Re: Error while using the DATEADD function in Hibernate+SQL
PostPosted: Mon Jun 22, 2009 11:39 am 
Newbie

Joined: Mon Jun 22, 2009 11:20 am
Posts: 1
hi Kiran,
I will give you an other example which will give you exact solution to your issue.
Let take example datediff (day, inputdate1, inputdate2) function in SQL Server which does not exist in HQL.

For accessing this function through dialect I need to register this function like…..
Step1: I have created custom dialect (subclass for SQLServerDialect) for getting all properties of SQLServerDialect and registered a column & function in this class constructor

public class CustomSQLServerDialect extends SQLServerDialect {
public CustomSQLServerDialect() {
super ();
registerColumnType(Types.INTEGER, "INTEGER");
registerFunction("datediff", new StandardSQLFunction("datediff",Hibernate.INTEGER));
}

Step 2: In hibernate.cfg.xml I have changed dialect name to my custom dialect

<property name="hibernate.dialect">com.ecomglobe.dao.impl.reports.SQLServerDialect</property>

Changed to
<property name="hibernate.dialect">com.ecomglobe.dao.impl.reports.CustomSQLServerDialect</property>

Step 3: Sample Query with the custom function usage
StringBuilder strQuery = new StringBuilder();
Query query = null;
List list=null;
strQuery.append("SELECT DISTINCT datediff(day, '5/13/2009 6:28:07 PM', current_timestamp) as INVOICEAGE ");
query1 = session.createQuery(strQuery.toString());
list = query.list();

actually this datediff() is our custom function which internally intimating dialect to use sql inbuilt datediff() function.

current_timestamp is HQL built in function which is equal to getdate() of SQL Server

Output:

INVOICEAGE
----
40

That’s it. Its given expected result. Still if its not clear to you, i can send you one example
with clear description...

Feel free to ask your queries.....
Enjoy the work...

-Cheers
Gopal S


Top
 Profile  
 
 Post subject: Re: Error while using the DATEADD function in Hibernate+SQL
PostPosted: Fri Jul 30, 2010 6:45 pm 
Newbie

Joined: Thu Jul 29, 2010 12:13 pm
Posts: 1
Hi people!

I've also need to use datediff SqlServer function.

I've tried to use Gopal's sugestion but my query still don't work and throw this exception:

java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.tree.MethodNode


There's some hibernate additional configuration that I need to enable, disable or even set to make it work?

Thanks a lot!


Top
 Profile  
 
 Post subject: Re: Error while using the DATEADD function in Hibernate+SQL
PostPosted: Thu Sep 30, 2010 10:25 am 
Newbie

Joined: Thu Sep 30, 2010 10:22 am
Posts: 1
Try to wrap your function in a cast(function(parameters,...) as <hibernate type>) IF your database supports it...


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 12 posts ] 

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.