Menu
▾
▴
#2467 DB.TO_CHAR doesn't format dates and numbers
Dates and numbers included in lookup display values are not formatted properly when using postgresql.
The existing implementation in DB_Postgresql just casts the data/number to string rather than formatting it with TO_CHAR. This results in Dates being displayed with timestamps and numbers having an arbitrary number of decimal places shown.
Revision: 13223
http://adempiere.svn.sourceforge.net/adempiere/?rev=13223&view=rev
Author: phib
Date: 2010-07-16 06:55:37 +0000 (Fri, 16 Jul 2010)
What was the reason for changing the methods behaviour?
When I try to add an invoice line Adempiere freaks out with a lot of these:
-----------> MIssue.set_ValueNoCheck: ReleaseNo - Value too long - truncated to length=4 [11]
===========> DB.getSQLValueString: SELECT 'C'||(SELECT TRIM(TO_CHAR(COUNT(*),'999.999.999.990,9999999999')) FROM AD_Client)||'U'|| (SELECT TRIM(TO_CHAR(COUNT(*),'999.999.999.990,9999999999')) FROM AD_User)||'B'|| (SELECT TRIM(TO_CHAR(COUNT(*),'999.999.999.990,9999999999')) FROM C_BPartner)||'P'|| (SELECT TRIM(TO_CHAR(COUNT(*),'999.999.999.990,9999999999')) FROM M_Product)||'I'|| (SELECT TRIM(TO_CHAR(COUNT(*),'999.999.999.990,9999999999')) FROM C_Invoice)||'L'|| (SELECT TRIM(TO_CHAR(COUNT(*),'999.999.999.990,9999999999')) FROM C_InvoiceLine)||'M'|| (SELECT TRIM(TO_CHAR(COUNT(*),'999.999.999.990,9999999999')) FROM M_Transaction) FROM AD_System [11]
org.postgresql.util.PSQLException: ERROR: multiple decimal points; State=42601; ErrorCode=0
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.compiere.db.StatementProxy.invoke(StatementProxy.java:100)
at $Proxy1.executeQuery(Unknown Source)
at org.compiere.util.DB.getSQLValueStringEx(DB.java:1339)
at org.compiere.util.DB.getSQLValueString(DB.java:1382)
at org.compiere.model.MSystem.getStatisticsInfo(MSystem.java:177)
at org.compiere.model.MIssue.init(MIssue.java:238)
at org.compiere.model.MIssue.<init>(MIssue.java:131)
at org.compiere.model.MIssue.<init>(MIssue.java:157)
at org.compiere.model.MIssue.create(MIssue.java:74)
at org.compiere.util.CLogErrorBuffer.publish(CLogErrorBuffer.java:222)
===========> MLookup.getDirect: C_Invoice.C_Invoice_ID: SQL=SELECT C_Invoice.C_Invoice_ID,NULL,NVL(C_Invoice.DocumentNo,'-1') ||'_'|| NVL(TRIM(TO_CHAR(C_Invoice.DateInvoiced,'YYYY-MM-DD')),'-1') ||'_'|| NVL(TRIM(TO_CHAR(C_Invoice.GrandTotal,'999.999.999.990,00')),'-1'),C_Invoice.IsActive FROM C_Invoice WHERE C_Invoice.C_Invoice_ID=?; Key=1001234 [11]
org.postgresql.util.PSQLException: ERROR: multiple decimal points; State=42601; ErrorCode=0
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
at sun.reflect.GeneratedMethodAccessor5.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.compiere.db.StatementProxy.invoke(StatementProxy.java:100)
at $Proxy1.executeQuery(Unknown Source)
at org.compiere.model.MLookup.getDirect(MLookup.java:470)
at org.compiere.model.MLookup.get(MLookup.java:229)
at org.compiere.model.MLookup.getDisplay(MLookup.java:243)
at org.compiere.grid.ed.VLookup.setValue(VLookup.java:558)
at org.compiere.grid.ed.VLookup.propertyChange(VLookup.java:628)
at java.beans.PropertyChangeSupport.firePropertyChange(PropertyChangeSupport.java:339)
at java.beans.PropertyChangeSupport.firePropertyChange(PropertyChangeSupport.java:276)
at org.compiere.model.GridField.setValue(GridField.java:1294)
at org.compiere.model.GridTable.dataNew(GridTable.java:2523)
at org.compiere.model.GridTab.dataNew(GridTab.java:1135)
at org.compiere.apps.APanel.stateChanged(APanel.java:1479)
Changed back to previous version so you don't have to stress about that :-)
http://adempiere.svn.sourceforge.net/adempiere/?rev=13804&view=rev
Best regards
/Daniel