Topic
4 replies Latest Post - ‏2010-04-27T18:09:33Z by SystemAdmin
SystemAdmin
SystemAdmin
532 Posts
ACCEPTED ANSWER

Pinned topic Portlet Factory gives Error on AND DATE(timestamp_column) BETWEEN ? AND ?

‏2008-06-05T21:41:01Z |
I have a SQL query that fails when I add the following to the end of the query:

AND DATE(timestamp_column) BETWEEN ? AND ?

It only works if I put some hardcoded date here:
AND DATE(timestamp_column) BETWEEN '2001-10-10' AND '2001-10-20'

Any Ideas??
Updated on 2010-04-27T18:09:33Z at 2010-04-27T18:09:33Z by SystemAdmin
  • DGawron
    DGawron
    251 Posts
    ACCEPTED ANSWER

    Re: Portlet Factory gives Error on AND DATE(timestamp_column) BETWEEN ? AND ?

    ‏2008-06-06T13:35:22Z  in response to SystemAdmin
    When you say it fails do you mean you get incorrect results or do you mean there is an exception? If an exception, then please post it and I'll take a look. If you are getting incorrect results, then the most likely cause is that the values you are providing for the 2 positional parameters are incorrectly formatted. In the SQL Call builder enable the SQL Statement Logging input and rerun your test. The builder will log the SQL statement and the value of all positional parameters to web-inf/logs/event.log
    Updated on 2008-06-06T13:35:22Z at 2008-06-06T13:35:22Z by DGawron
    • _simonluo_
      _simonluo_
      1 Post
      ACCEPTED ANSWER

      Re: Portlet Factory gives Error on AND DATE(timestamp_column) BETWEEN ? AND ?

      ‏2008-10-23T09:16:27Z  in response to DGawron
      I encount the same problem,and my log event tell me the following message.Could anybody solve the problem?

      -- TIME: 2008-10-23 17:08:32,688 --
      Category: bowstreet.system.server.logging.event.criterion.abnormal
      Priority: ERROR
      Thread: Event Thread
      Msg: Component: MODEL
      SQL to XML Schema builder "getSchema_XmlSchema" encountered an exception while generating a schema; the exception message was "String index out of range: 1"
      • SystemAdmin
        SystemAdmin
        532 Posts
        ACCEPTED ANSWER

        Re: Portlet Factory gives Error on AND DATE(timestamp_column) BETWEEN ? AND ?

        ‏2009-01-15T20:02:43Z  in response to _simonluo_
        Hi,

        I seem to be getting the same error as yourself although I'm trying to setup and test a database connection to a DB2 database via WASCE.

        Any chance you've experienced a similar issue to myself regarding WASCE and database connectivity and can suggest how a database, called jdbc/WPFTEST, should be setup and tested from Portlet factory so that one can actually test portlets via WASCE ?

        Any help would be greatly appreciated as I'm really stuck and I'm sure I'm just doing something simple wrong.

        Please see the thread below:

        Error accessing data source in SQL Builder Call
  • SystemAdmin
    SystemAdmin
    532 Posts
    ACCEPTED ANSWER

    Re: Portlet Factory gives Error on AND DATE(timestamp_column) BETWEEN ? AND ?

    ‏2010-04-27T18:09:33Z  in response to SystemAdmin
    For the "SQL Statement" input, use your desired query (... AND DATE(timestamp_column) BETWEEN ? AND ? ) For the "Schema Generation" input, select "From alternate SQL Statement" , which will allow the builder to generate a result set schema from an alternate sql statement. In the "Alternate SQL" input below, enter in the SQL query that contains the hard coded "... AND DATE(timestamp_column) BETWEEN '2001-10-10' AND '2001-10-20' " .

    Hopefully, this will let the builder generate the exact same xml result schema as the real sql statement with the positional parameters.
    So the SQL statements should be the same, except that the alternate sql statement will hard code the dates.