I get this on screen
org.ofbiz.widget.screen.ScreenRenderException: Error rendering screen
[component://marketing/widget/MarketingReportScreens.xml#MarketingCampaignReport]: org.ofbiz.base.util.GeneralException: Error
running Groovy script at location [component://marketing/webapp/marketing/WEB-INF/actions/reports/MarketingCampaignReport.groovy]
(SQL Exception while executing the following:SELECT TC.MARKETING_CAMPAIGN_ID, COUNT(TCV.VISIT_ID) FROM public.TRACKING_CODE TC LEFT
OUTER JOIN public.TRACKING_CODE_VISIT TCV ON TC.TRACKING_CODE_ID = TCV.TRACKING_CODE_ID WHERE (TCV.FROM_DATE >= ? AND TCV.FROM_DATE
<= ?) GROUP BY TC.MARKETING_CAMPAIGN_ID ORDER BY TC.MARKETING_CAMPAIGN_ID ASC (ERREUR: l'op�rateur n'existe pas : timestamp with
time zone >= character varying)) (Error running Groovy script at location
[component://marketing/webapp/marketing/WEB-INF/actions/reports/MarketingCampaignReport.groovy] (SQL Exception while executing the
following:SELECT TC.MARKETING_CAMPAIGN_ID, COUNT(TCV.VISIT_ID) FROM public.TRACKING_CODE TC LEFT OUTER JOIN
public.TRACKING_CODE_VISIT TCV ON TC.TRACKING_CODE_ID = TCV.TRACKING_CODE_ID WHERE (TCV.FROM_DATE >= ? AND TCV.FROM_DATE <= ?) GROUP
BY TC.MARKETING_CAMPAIGN_ID ORDER BY TC.MARKETING_CAMPAIGN_ID ASC (ERREUR: l'op�rateur n'existe pas : timestamp with time zone >=
character varying)))
The weird part is that ViewFacilityInventoryItemsDetails works now also on
Postgres 8.3.1 (always on Win XP) :/
If I remember well it's not the 1st time I see it re-working. I guess there is
certainly something in OFBIz under all that.
Jacques
From: "Scott Gray" <[EMAIL PROTECTED]>
Are you actually getting any results displayed? I doesn't work on
mine but the error only shows up in the logs...
Thanks
Scott
2008/10/14 Jacques Le Roux <[EMAIL PROTECTED]>:
Sorry forget it, I was wrong, I did the <<type="Timestamp">> removing in the
wrong screen !
So yes I was able to reproduce the problem with Timestamp.
Not sure why ViewFacilityInventoryItemsDetails works on this new
installation though :/
Jacques
From: "Jacques Le Roux" <[EMAIL PROTECTED]>
Hi Scott,
I just commited a bug fix (typo) for MarketingCampaignReport.groovy in
r704013. It's an easy way to try with Timestamp.
If you remove <<type="Timestamp">> from lines
<set field="fromDate" from-field="requestParameters.fromDate"
type="Timestamp"/>
<set field="thruDate" from-field="requestParameters.thruDate"
type="Timestamp"/>
in MarketingCampaignReport (in MarketingReportScreens.xml)
You will see that (at least) with Postgres 8.3.4 it's still working.
FYI it's not working with my other installation which uses Postgres 8.3.1.
There I get "timestamp with time zone >= character varying". I use the
same postgresql-8.3-603.jdbc3.jar driver (447 301 bytes each).
So it could be a Postgres bug or something related to my installation (I
remember having tried some not default settings on this installation)
Please, let me know what you get
HTH
Jacques
From: "Scott Gray" <[EMAIL PROTECTED]>
I was able to reproduce the problem here (with timestamps I haven't
tried doubles) on a fresh install, could you please double check that
it's working?
Thanks
Scott
2008/10/13 Jacques Le Roux <[EMAIL PROTECTED]>:
I suppose it was a problem with my Postgres installation since with a
new
Postgres 8.3 installation ViewFacilityInventoryItemsDetails works.
I also wondered why nobody was complaining about this issue but me.
I think we can forget it, as the changes I done (suggested by Adrian)
are
harmless anyway. Hopefully I did not introduce a bug.
Sorry for the bothering
Jacques
From: "Jacques Le Roux" <[EMAIL PROTECTED]>
Yes, you are right.
https://localhost:8443/facility/control/ViewFacilityInventoryItemsDetails
works with postgres 8.2.9 Maybe it even comes from my own Postgres 8.3
installation. I will try with a new one.
Thanks
Jacques
From: "Scott Gray" <[EMAIL PROTECTED]>
Hi Jacques
I really don't think it has anything to do with groovy, it is just
passing a simple string into the entity condition which is no more or
less than beanshell would have done. I think it just seems that way
because we noticed the problem after the beanshell->groovy switch, the
change probably came from postgresql in some recent version.
I'll have a look at the double problem as soon as I get a chance.
Thanks
Scott
2008/10/13 Jacques Le Roux <[EMAIL PROTECTED]>:
Hi Scott,
No time to look into details tonight. Just to let you know that I
considered
this a Groovy issue because it seems that the same code used in
Beanshell
did not have this problem. Maybe it's no related to Groovy itself,
but
the
way we interface it ?
BTW we have the same kind of issue (not timestamp related though) at
https://localhost:8443/facility/control/ViewFacilityInventoryItemsDetails
There it's between "double precision" and "character varying". I
guess,
the
same kinf of fix may be applied as well
Jacques
From: "Scott Gray" <[EMAIL PROTECTED]>
I've had a look into this and I can't see anything related to Groovy
that is making this necessary, it appears to be entirely a
postgresql
issue.
When executing a prepared statement postgresql seems to require that
the parameter list sql types match the column types. So the problem
isn't that we are passing in a string but that we are setting the
sql
type to character varying by using PreparedStatement.setString().
Here's a patch that fixes the issue but I'm not really confident
enough to commit it, it would be great to get some comments from
people who know more about this kind of thing:
Index: framework/entity/src/org/ofbiz/entity/jdbc/SQLProcessor.java
===================================================================
--- framework/entity/src/org/ofbiz/entity/jdbc/SQLProcessor.java
(revision
703572)
+++ framework/entity/src/org/ofbiz/entity/jdbc/SQLProcessor.java
(working
copy)
@@ -592,6 +592,22 @@
*
* @throws SQLException
*/
+ public void setValueTimestampString(String field) throws
SQLException
{
+ if (field != null) {
+ _ps.setObject(_ind, field, Types.TIMESTAMP);
+ } else {
+ _ps.setNull(_ind, Types.TIMESTAMP);
+ }
+ _ind++;
+ }
+
+ /**
+ * Set the next binding variable of the currently active
prepared
statement.
+ *
+ * @param field
+ *
+ * @throws SQLException
+ */
public void setValue(java.sql.Time field) throws SQLException {
if (field != null) {
_ps.setTime(_ind, field);
Index: framework/entity/src/org/ofbiz/entity/jdbc/SqlJdbcUtil.java
===================================================================
--- framework/entity/src/org/ofbiz/entity/jdbc/SqlJdbcUtil.java
(revision
703572)
+++ framework/entity/src/org/ofbiz/entity/jdbc/SqlJdbcUtil.java
(working
copy)
@@ -731,6 +731,9 @@
fieldClassName = "byte[]";
}
+ if ("java.sql.Timestamp".equals(fieldType)) {
+ fieldClassName = fieldType;
+ }
if (Debug.verboseOn()) Debug.logVerbose("type of
field " + entityName + "." + modelField.getName() +
" is " + fieldClassName + ", was expecting "
+ mft.getJavaType() + "; this may " +
"indicate an error in the configuration or in
the class, and may result " +
@@ -749,7 +752,11 @@
break;
case 2:
- sqlP.setValue((java.sql.Timestamp) fieldValue);
+ if (fieldValue instanceof String) {
+ sqlP.setValueTimestampString((String) fieldValue);
+ } else {
+ sqlP.setValue((java.sql.Timestamp) fieldValue);
+ }
break;
case 3:
Regards
Scott
2008/10/13 Jacques Le Roux <[EMAIL PROTECTED]>:
Done in revision: 703816
It was not possible for PackingSlip.groovy and
FindInventoryEventPlan.groovy. Because there the date string is
build
dynamically in the Groovy file
Jacques
From: "Jacques Le Roux" <[EMAIL PROTECTED]>
Adrian,
Yes good idea indeed, I will do that
Jacques
From: "Adrian Crum" <[EMAIL PROTECTED]>
Jacques,
Instead of modifying the groovy files, try specifying the data
type
in
the screen widget.
Example in ReportFinancialSummaryScreens.xml:
<set field="fromDate" from-field="parameters.fromDate"
type="Timestamp"/>
<set field="thruDate" from-field="parameters.thruDate"
type="Timestamp"/>
<script
location="component://accounting/webapp/accounting/WEB-INF/actions/reports/TransactionTotals.groovy"/>
-Adrian