It looks like showddl preserves the data type as given by the user
>create table t2_big (c1 numeric(25,6));
--- SQL operation complete.
>>showddl t2_big ;
..
CREATE TABLE TRAFODION.SCH.T2_BIG
(
C1 NUMERIC(25, 6) DEFAULT NULL NOT
SERIALIZED
)
;
Numeric columns are internally mapped to a short, integer or largeint
depending upon the precision of the numeric column. Numeric(25,6) can't fit
into a largeint, it would be mapped into a different data type.
Numeric(18,6) would fit into largeint. The mapping exposed at the
application level is dictated by ODBC and JDBC specification.
Selva
-----Original Message-----
From: Narain Arvind [mailto:[email protected]]
Sent: Tuesday, March 29, 2016 9:51 AM
To: [email protected]
Subject: RE: NUMERIC? NUMERIC!
Hi Pierre,
I assume the application is trying to the get the column type information
via SqlGetTypeInfo or equivalent call and matching against the expected type
?
Looking at the code we currently are not returning the type for a specific
column. The first type in the list is BIGINT and that gets returned. I
thought this was fixed in an earlier version of the software. Could you
please raise a JIRA for this issue ?
Thanks
Arvind
-----Original Message-----
From: Pierre Smits [mailto:[email protected]]
Sent: Tuesday, March 29, 2016 9:29 AM
To: [email protected]
Subject: Re: NUMERIC? NUMERIC!
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/
>
