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

I think you will have to subclass the SQLServerDialect, and from the constructor call

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

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.

Here is how it worked for SQL Server 2005

Class RegisterFunction


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.


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.

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

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?

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

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..

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..

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

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!

Try to wrap your function in a cast(function(parameters,...) as <hibernate type>) IF your database supports it...