5 Replies Latest reply on Sep 23, 2007 1:11 AM by 796254

    JavaDB or Toplink not recognizing named parameters?

    843859
      Creating a query within a session bean but the named parameter is causing an exception. Using Derby with Toplink in the Sun App Server 9.0_01. Any ideas what the problem or solution is?
          public String getNumberOfInvoices(String supplierAccount) {
              String suppAcc = "";
              Query qry = em.createNativeQuery(
                      "select count(d) from App.Documents as d " +
                      "where d.PERSONALACCOUNT = :suppAcc;");
              qry.setParameter("suppAcc", supplierAccount);
              Long count = (Long) qry.getSingleResult();
              return Long.toString(count);
          }


      [#|2007-09-22T23:41:09.271+0100|WARNING|sun-appserver-pe9.0|oracle.toplink.essentials.file:/C:/Sun/SDK/domains/domain1/applications/j2ee-apps/Accounting/Accounting-ejb_jar/-Accounting-ejbPU|_ThreadID=16;_ThreadName=httpWorkerThread-8080-1;_RequestID=4ffb3c50-34f3-42cf-a00b-26e93a0f8ed6;|
      Local Exception Stack:
      Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2006.8 (Build 060830)): oracle.toplink.essentials.exceptions.DatabaseException
      Internal Exception: org.apache.derby.client.am.SqlException: Syntax error: Encountered ":" at line 1, column 67.Error Code: -1
      Call:select count(d) from App.Documents as d where d.PERSONALACCOUNT = :suppAcc;
      Query:DataReadQuery()
           at oracle.toplink.essentials.exceptions.DatabaseException.sqlException(DatabaseException.java:303)
           at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:551)
           at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:437)
           at oracle.toplink.essentials.threetier.ServerSession.executeCall(ServerSession.java:465)
           at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:213)
           at oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:199)
        • 1. Re: JavaDB or Toplink not recognizing named parameters?
          796254
          Try taking the semicolon off the end of the SQL statement. It's not necessary. The SQL interpreter might think it's part of the identifier name.

          %
          • 2. Re: JavaDB or Toplink not recognizing named parameters?
            843859
            Thanks duffymo but that didn't help.

            I have amended my query to:
                public String getNumberOfInvoices(String supplierAccount) {
                    String suppAcc = "";
                    Query qry = em.createNativeQuery(
                            "select count(*) from App.Documents as d " +
                            "where d.PERSONALACCOUNT = ?1");
                    qry.setParameter(1, supplierAccount);
                    Long count = (Long) qry.getSingleResult();
                    return Long.toString(count);
                }
            This seems to have resolved the problem to a degree. I am not sure why
            numeric parameters are accepted but named one are not. I have also
            changed from count(d) to count(*). Is this because I am using EJB Query
            Language but createNativeQuery is plain JDBC?

            Any way the error is now saying:-
            Caused by: java.lang.ClassCastException: java.util.Vector cannot be cast
            to java.lang.Long

            How would you get the value of a count?
            • 3. Re: JavaDB or Toplink not recognizing named parameters?
              796254
              I haven't used TopLink; Hibernate is my ORM tool of choice. The ideas are identical, though.

              I'll have to dig a bit.

              %
              • 4. Re: JavaDB or Toplink not recognizing named parameters?
                796254
                This seems to have resolved the problem to a degree. I am not sure why
                numeric parameters are accepted but named one are not.
                I don't know. I can do named parameters with Hibernate. I don't know TopLink.

                You understand that "named query" is different from "query with named parameters", right? You seem to want the latter, not the former. Maybe you're reading the wrong docs.
                I have also
                changed from count(d) to count(*). Is this because I am using EJB Query
                Language but createNativeQuery is plain JDBC?
                If you're counting all the rows in a table, it would be count(*). If you want to only count certain rows, it'd be a GROUP BY and HAVING.
                Any way the error is now saying:-
                Caused by: java.lang.ClassCastException: java.util.Vector cannot be cast
                to java.lang.Long
                This is pretty clear. You're assuming that the method returns a Long and it's telling you that it returns a Vector. It should only have size 1, and you should get the zeroth index value out of it.

                %
                • 5. Re: JavaDB or Toplink not recognizing named parameters?
                  796254
                  Maybe this can help:

                  http://download.oracle.com/docs/cd/B31017_01/web.1013/b28221/ent30qry003.htm

                  %