You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@trafodion.apache.org by "Liu, Ming (Ming)" <mi...@esgyn.cn> on 2016/03/30 17:12:32 UTC
答复: NUMERIC? NUMERIC!
Hi Pierre,
Does OFbiz using JDBC or ODBC to connect Trafodion?
I think Arvind may already notice some issue, I just want to double check here, because I was using a JDBC tool to get a table's metadata and NUMERIC return good to the tool : (Squirrel)
Thanks,
Ming
-----邮件原件-----
发件人: Pierre Smits [mailto:pierre.smits@gmail.com]
发送时间: 2016年3月30日 0:29
收件人: dev@trafodion.incubator.apache.org
主题: 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 <da...@esgyn.com>
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:pierre.smits@gmail.com]
> Sent: Tuesday, March 29, 2016 5:11 AM
> To: dev@trafodion.incubator.apache.org
> 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/
>
Re: 答复: NUMERIC? NUMERIC!
Posted by Pierre Smits <pi...@gmail.com>.
I used the jdbc T4 client.
Best regards,
Pierre Smits
ORRTIZ.COM <http://www.orrtiz.com>
OFBiz based solutions & services
OFBiz Extensions Marketplace
http://oem.ofbizci.net/oci-2/
On Wed, Mar 30, 2016 at 5:12 PM, Liu, Ming (Ming) <mi...@esgyn.cn> wrote:
> Hi Pierre,
>
> Does OFbiz using JDBC or ODBC to connect Trafodion?
> I think Arvind may already notice some issue, I just want to double check
> here, because I was using a JDBC tool to get a table's metadata and NUMERIC
> return good to the tool : (Squirrel)
>
> Thanks,
> Ming
>
> -----邮件原件-----
> 发件人: Pierre Smits [mailto:pierre.smits@gmail.com]
> 发送时间: 2016年3月30日 0:29
> 收件人: dev@trafodion.incubator.apache.org
> 主题: 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 <da...@esgyn.com>
> 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:pierre.smits@gmail.com]
> > Sent: Tuesday, March 29, 2016 5:11 AM
> > To: dev@trafodion.incubator.apache.org
> > 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/
> >
>