Hi Dave,
Thanks for the tip regarding showddl.
So in the earlier mentioned ESTIMATED_COST example, these are the findings:
- The defintion in OFBiz in the table definition for
WorkEffortGoodStandard for the field is
<field name="estimatedCost" type="currency-amount"></field>
-
The data type definition for 'currency-amount' is
<field-type-def type="currency-amount" sql-type="NUMERIC(18,2)" java-type
="java.math.BigDecimal"/>
-
The example in my earlier posting is an excerpt of the OFBiz log
-
showddl table OFBIZ.WORK_EFFORT_GOOD_STANDARD returns
>>showddl table OFBIZ.WORK_EFFORT_GOOD_STANDARD;
CREATE TABLE TRAFODION.OFBIZ.WORK_EFFORT_GOOD_STANDARD
(
WORK_EFFORT_ID VARCHAR(20) CHARACTER SET ISO88591
COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
, PRODUCT_ID VARCHAR(20) CHARACTER SET ISO88591
COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
, WORK_EFFORT_GOOD_STD_TYPE_ID VARCHAR(20) CHARACTER SET ISO88591
COLLATE
DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED
, FROM_DATE TIMESTAMP(6) NO DEFAULT NOT NULL NOT
DROPPABLE NOT SERIALIZED
, THRU_DATE TIMESTAMP(6) DEFAULT NULL NOT
SERIALIZED
, STATUS_ID VARCHAR(20) CHARACTER SET ISO88591
COLLATE
DEFAULT DEFAULT NULL SERIALIZED
, ESTIMATED_QUANTITY DOUBLE PRECISION DEFAULT NULL NOT
SERIALIZED
, ESTIMATED_COST NUMERIC(18, 2) DEFAULT NULL
SERIALIZED
, LAST_UPDATED_STAMP TIMESTAMP(6) DEFAULT NULL NOT
SERIALIZED
, LAST_UPDATED_TX_STAMP TIMESTAMP(6) DEFAULT NULL NOT
SERIALIZED
, CREATED_STAMP TIMESTAMP(6) DEFAULT NULL NOT
SERIALIZED
, CREATED_TX_STAMP TIMESTAMP(6) DEFAULT NULL NOT
SERIALIZED
, PRIMARY KEY (WORK_EFFORT_ID ASC, PRODUCT_ID ASC,
WORK_EFFORT_GOOD_STD_TYPE_ID ASC, FROM_DATE ASC)
)
;
CREATE INDEX WKEFF_GDSTD_PROD ON
TRAFODION.OFBIZ.WORK_EFFORT_GOOD_STANDARD
(
PRODUCT_ID ASC
)
;
CREATE INDEX WKEFF_GDSTD_STTS ON
TRAFODION.OFBIZ.WORK_EFFORT_GOOD_STANDARD
(
STATUS_ID ASC
)
;
CREATE INDEX WKEFF_GDSTD_TYPE ON
TRAFODION.OFBIZ.WORK_EFFORT_GOOD_STANDARD
(
WORK_EFFORT_GOOD_STD_TYPE_ID ASC
)
;
CREATE INDEX WKEFF_GDSTD_WEFF ON
TRAFODION.OFBIZ.WORK_EFFORT_GOOD_STANDARD
(
WORK_EFFORT_ID ASC
)
;
It seems to me that everything is ok underneath, but that the return
message is misinterpreted by the OFBiz functions.
Best regards,
Pierre Smits
ORRTIZ.COM <http://www.orrtiz.com>
OFBiz based solutions & services
OFBiz Extensions Marketplace
http://oem.ofbizci.net/oci-2/
On Tue, Mar 29, 2016 at 5:59 PM, Dave Birdsall <[email protected]>
wrote:
> Hi Pierre,
>
> Can you say more?
>
> Trafodion does support NUMERIC(18,6) as a distinct data type, for example:
>
> >>create table t1 (a int not null, b largeint not null, c numeric(18,6) not
> >>null
> +> , primary key (a));
>
> --- SQL operation complete.
> >>insert into t1 values (1,2,3.8);
>
> --- 1 row(s) inserted.
> >>insert into t1 values (4,5,6.712345);
>
> --- 1 row(s) inserted.
> >>insert into t1 values (7,8,9.000000001);
>
> --- 1 row(s) inserted.
> >>select * From t1;
>
> A B C
> ----------- -------------------- ---------------------
>
> 1 2 3.800000
> 4 5 6.712345
> 7 8 9.000000
>
> --- 3 row(s) selected.
> >>
>
> It might be useful to do a "showddl" on the underlying Trafodion table to
> see what the column data types are. There might, for example, be a problem
> in DDL generation so the wrong data type gets created. Or perhaps the DDL
> is
> correct but something happens on the way back out, say for a "describe" of
> a
> statement.
>
> Dave
>
> -----Original Message-----
> From: Pierre Smits [mailto:[email protected]]
> Sent: Tuesday, March 29, 2016 5:11 AM
> To: [email protected]
> Subject: NUMERIC? NUMERIC!
>
> Hi all,
>
> While trying to marry Apache OFBiz with Trafodion, I found this oddity:
>
> In OFBiz we type some fields as NUMERIC, e.g:
>
> <field-type-def type="fixed-point" sql-type="NUMERIC(18,6)" java-type=
> "java.math.BigDecimal"/>
>
> However, when looking at the feedback we get from Trafodion after a restart
> we get:
>
> Column [ESTIMATED_COST] of table [OFBIZ.WORK_EFFORT_GOOD_STANDARD] of
> entity
> [WorkEffortGoodStandard] is of type [BIGINT] in the database, but is
> defined
> as type [NUMERIC] in the entity definition.
>
> Is something wrong inside Trafodion with respect to mapping field types?
>
> Best regards,
>
> Pierre Smits
>
> ORRTIZ.COM <http://www.orrtiz.com>
> OFBiz based solutions & services
>
> OFBiz Extensions Marketplace
> http://oem.ofbizci.net/oci-2/
>
