1 Reply Latest reply on Sep 30, 2004 1:58 PM by kasturiranganr

    Problem using EJBQL (in JBOSS) to access a SQL Server Databa

    kasturiranganr Newbie

      Hi,

      My problem is incompatibility between java.sql.Date and SQL Server 'datetime' variable. Details follow:

      I am using JBoss 3.2.3. I have an entity bean that has to search through a SQL Server table and find all those orders whose 'dateOrderPlaced' = some-supplied-date. I have written the query in EJB-QL like so:


      <query-method>
      <method-name>findNumberOfOrders</method-name>
      <method-params>
      <method-param>java.sql.Date</method-param>
      </method-params>
      </query-method>
      <jboss-ql>[CDATA[ SELECT OBJECT(o) From POrder AS o
      WHERE o.dateOrderPlaced = ?1
      ]]
      </jboss-ql>


      The argument that I supply above is in the java.sql.Date format. The code for that is given below

      -----------------------------------------------------------------------------------------------------------
      java.sql.Date sqlFromDate = new java.sql.Date((new Date()).getTime());
      // where sqlFromDate is the argument to the EJB-QL above
      -----------------------------------------------------------------------------------------------------------

      SQL Server stores dates in a 'datetime' variable. When I run the application as above, I get a return value of 0 orders.

      So I ran a query from a SQL Server client as follows

      select dateorderplaced from orders
      where datepart(yyyy,dateorderplaced) = '2004'
      and datepart(mm,dateorderplaced) = '09'
      and datepart(dd,dateorderplaced) = '30';

      and got some results as follows

      -----------------------------------------------------------------------------------------------------
      Sep 30 2004 12:00AM
      Sep 30 2004 12:00AM
      Sep 30 2004 12:00AM
      Sep 30 2004 12:00AM
      Sep 30 2004 09:44AM
      Sep 30 2004 12:00AM
      Sep 30 2004 10:24AM
      Sep 30 2004 10:26AM
      Sep 30 2004 10:29AM
      -----------------------------------------------------------------------------------------------------

      So obviously I am either messing up sending the java.sql.Date or there is an inherent problem writing EJB-QLs to access SQL-Server 'datetime' types.

      Any help would be appreciated.

      Unsuccessfully tried using java.sql.TimeStamp instead of java.sql.Date

      Thanks,
      Sharad