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'
This topic has been locked.
4 replies Latest Post - 2010-04-27T18:09:33Z by SystemAdmin
Pinned topic Portlet Factory gives Error on AND DATE(timestamp_column) BETWEEN ? AND ?
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2010-04-27T18:09:33Z at 2010-04-27T18:09:33Z by SystemAdmin
DGawron 10000054HC251 PostsACCEPTED ANSWER
Re: Portlet Factory gives Error on AND DATE(timestamp_column) BETWEEN ? AND ?2008-06-06T13:35:22Z in response to SystemAdminWhen 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.logUpdated on 2008-06-06T13:35:22Z at 2008-06-06T13:35:22Z by DGawron
_simonluo_ 2700011W1G1 PostACCEPTED ANSWER
Re: Portlet Factory gives Error on AND DATE(timestamp_column) BETWEEN ? AND ?2008-10-23T09:16:27Z in response to DGawronI 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 --
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"
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
Re: Portlet Factory gives Error on AND DATE(timestamp_column) BETWEEN ? AND ?2010-04-27T18:09:33Z in response to SystemAdminFor 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.