3 Replies Latest reply on Aug 4, 2008 12:21 PM by Peter Johnson

    JBoss + MS Sql server 2005: 'nvl' is not a recognized built-

    illi g Newbie

      Hi All,

      We have been trying to configure JBoss to use MS Sql server 2005 from our application. Technology we used in our application is hibernate. We are now able to access our application, but while loading some objects in one of screens, it is showing a error "Caused by: java.sql.SQLException: 'nvl' is not a recognized built-in function name." what more need to be configured to make it use such functions.

      --> following is the ds config we used.
      <local-tx-datasource>
      <jndi-name>applDS</jndi-name>
      <connection-url>jdbc:jtds:sqlserver://common:3403;instanceName=RESEARCH;databaseName=prdName</connection-url>
      <driver-class>net.sourceforge.jtds.jdbc.Driver</driver-class>
      <use-java-context>false</use-java-context>
      <user-name>myName</user-name>
      myPwd

      <type-mapping>MS SQLSERVER2005</type-mapping>

      </local-tx-datasource>
      --------------------------------------------------------------------------------------

        • 1. Log: JBoss + MS Sql server 2005: 'nvl' is not a recognized b
          illi g Newbie

          Adding full log info:

          2008-08-04 12:05:30,613 INFO [STDOUT] Caused by: org.hibernate.exception.GenericJDBCException: could not execute query
          2008-08-04 12:05:30,613 INFO [STDOUT] at org.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:92)
          2008-08-04 12:05:30,613 INFO [STDOUT] at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:80)
          2008-08-04 12:05:30,613 INFO [STDOUT] at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
          2008-08-04 12:05:30,613 INFO [STDOUT] at org.hibernate.loader.Loader.doList(Loader.java:1565)
          2008-08-04 12:05:30,613 INFO [STDOUT] at org.hibernate.loader.Loader.list(Loader.java:1545)
          2008-08-04 12:05:30,613 INFO [STDOUT] at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:375)
          2008-08-04 12:05:30,613 INFO [STDOUT] at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:271)
          2008-08-04 12:05:30,613 INFO [STDOUT] at org.hibernate.impl.SessionImpl.list(SessionImpl.java:840)
          2008-08-04 12:05:30,613 INFO [STDOUT] at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
          2008-08-04 12:05:30,613 INFO [STDOUT] at com.dcs.applications.service.LogicModuleServiceBean.getDisplayValueForValueObjectWithLocaleData(LogicModuleServiceBean.java:2228)
          2008-08-04 12:05:30,613 INFO [STDOUT] at com.dcs.applications.service.LogicModuleServiceBean.getDisplayValueForValueObject(LogicModuleServiceBean.java:2157)
          2008-08-04 12:05:30,613 INFO [STDOUT] at sun.reflect.GeneratedMethodAccessor298.invoke(Unknown Source)
          2008-08-04 12:05:30,613 INFO [STDOUT] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
          2008-08-04 12:05:30,613 INFO [STDOUT] at java.lang.reflect.Method.invoke(Method.java:585)
          2008-08-04 12:05:30,613 INFO [STDOUT] at org.jboss.invocation.Invocation.performCall(Invocation.java:345)
          2008-08-04 12:05:30,613 INFO [STDOUT] at org.jboss.ejb.StatelessSessionContainer$ContainerInterceptor.invoke(StatelessSessionContainer.java:214)
          2008-08-04 12:05:30,613 INFO [STDOUT] at org.jboss.resource.connectionmanager.CachedConnectionInterceptor.invoke(CachedConnectionInterceptor.java:185)
          2008-08-04 12:05:30,613 INFO [STDOUT] at org.jboss.ejb.plugins.StatelessSessionInstanceInterceptor.invoke(StatelessSessionInstanceInterceptor.java:130)
          2008-08-04 12:05:30,613 INFO [STDOUT] at org.jboss.webservice.server.ServiceEndpointInterceptor.invoke(ServiceEndpointInterceptor.java:51)
          2008-08-04 12:05:30,613 INFO [STDOUT] at org.jboss.ejb.plugins.CallValidationInterceptor.invoke(CallValidationInterceptor.java:48)
          2008-08-04 12:05:30,613 INFO [STDOUT] at org.jboss.ejb.plugins.AbstractTxInterceptor.invokeNext(AbstractTxInterceptor.java:105)
          2008-08-04 12:05:30,613 INFO [STDOUT] at org.jboss.ejb.plugins.TxInterceptorCMT.runWithTransactions(TxInterceptorCMT.java:335)
          2008-08-04 12:05:30,613 INFO [STDOUT] at org.jboss.ejb.plugins.TxInterceptorCMT.invoke(TxInterceptorCMT.java:166)
          2008-08-04 12:05:30,613 INFO [STDOUT] at org.jboss.ejb.plugins.SecurityInterceptor.invoke(SecurityInterceptor.java:139)
          2008-08-04 12:05:30,613 INFO [STDOUT] at org.jboss.ejb.plugins.LogInterceptor.invoke(LogInterceptor.java:192)
          2008-08-04 12:05:30,613 INFO [STDOUT] ... 59 more
          2008-08-04 12:05:30,613 INFO [STDOUT] Caused by: java.sql.SQLException: 'nvl' is not a recognized built-in function name.
          2008-08-04 12:05:30,613 INFO [STDOUT] at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:365)
          2008-08-04 12:05:30,613 INFO [STDOUT] at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2781)
          2008-08-04 12:05:30,613 INFO [STDOUT] at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2224)
          2008-08-04 12:05:30,613 INFO [STDOUT] at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:628)
          2008-08-04 12:05:30,613 INFO [STDOUT] at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:418)
          2008-08-04 12:05:30,613 INFO [STDOUT] at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:693)
          2008-08-04 12:05:30,613 INFO [STDOUT] at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:296)
          2008-08-04 12:05:30,613 INFO [STDOUT] at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:118)
          2008-08-04 12:05:30,613 INFO [STDOUT] at org.hibernate.loader.Loader.getResultSet(Loader.java:1239)
          2008-08-04 12:05:30,613 INFO [STDOUT] at org.hibernate.loader.Loader.doQuery(Loader.java:374)
          2008-08-04 12:05:30,613 INFO [STDOUT] at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:210)
          2008-08-04 12:05:30,613 INFO [STDOUT] at org.hibernate.loader.Loader.doList(Loader.java:1562)

          • 2. Re: JBoss + MS Sql server 2005: 'nvl' is not a recognized bu
            jaikiran pai Master

            Can you post the query that is being fired, which runs into this error? You might have to enable show_sql on hibernate to see those queries in the logs.

            • 3. Re: JBoss + MS Sql server 2005: 'nvl' is not a recognized bu
              Peter Johnson Master

              Also, in your hibernate configuration file, what dialect are you using?