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

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

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

    ‏2008-06-06T13:35:22Z  
    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

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

    ‏2008-10-23T09:16:27Z  
    • DGawron
    • ‏2008-06-06T13:33:41Z
    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
    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

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

    ‏2009-01-15T20:02:43Z  
    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"
    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

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

    ‏2010-04-27T18:09:33Z  
    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.