You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@trafodion.apache.org by Pierre Smits <pi...@gmail.com> on 2016/03/29 14:10:53 UTC
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 Eric Owhadi <er...@esgyn.com>.
Hi Pierre,
This is off topic, but looking at the ddl you provided, I see that you are
creating indexes that may not be needed.
Given that your primary key is :
PRIMARY KEY (WORK_EFFORT_ID ASC, PRODUCT_ID ASC,
WORK_EFFORT_GOOD_STD_TYPE_ID ASC, FROM_DATE ASC)
)
The data on the main table will be sorted by WORK_EFFORT_ID ASC then
PRODUCT_ID ASC then
WORK_EFFORT_GOOD_STD_TYPE_ID ASC then FROM_DATE ASC
Trafodion has a feature called MDAM (Multi Dimentional Access Method), that
will be able to leverage this sort order on main table to efficiently
retrieve the data you need, even when you are missing predicates on the left
most key columns.
This is a very cool feature that greatly limit your needs for indexes and
associated overhead.
In order to maximize benefit from MDAM, you want to order the key columns so
that the lower cardinality column that could be missing in predicates shows
up at the left most part of the key.
So given the current schema, you should only need index on status_ID.
(disclaimer, this depends on cardinality of the key columns...).
Hope this helps,
Eric
-----Original Message-----
From: Pierre Smits [mailto:pierre.smits@gmail.com]
Sent: Tuesday, March 29, 2016 11:29 AM
To: dev@trafodion.incubator.apache.org
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 <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/
> >
>
答复: NUMERIC? NUMERIC!
Posted by "Liu, Ming (Ming)" <mi...@esgyn.cn>.
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>.
Done at https://issues.apache.org/jira/browse/TRAFODION-1909
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 6:51 PM, Narain Arvind <na...@gmail.com>
wrote:
> 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:pierre.smits@gmail.com]
> Sent: Tuesday, March 29, 2016 9:29 AM
> To: dev@trafodion.incubator.apache.org
> 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 <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 Selva Govindarajan <se...@esgyn.com>.
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:narain.arvind@gmail.com]
Sent: Tuesday, March 29, 2016 9:51 AM
To: dev@trafodion.incubator.apache.org
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:pierre.smits@gmail.com]
Sent: Tuesday, March 29, 2016 9:29 AM
To: dev@trafodion.incubator.apache.org
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 <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 Narain Arvind <na...@gmail.com>.
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:pierre.smits@gmail.com]
Sent: Tuesday, March 29, 2016 9:29 AM
To: dev@trafodion.incubator.apache.org
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 <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>.
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 Dave Birdsall <da...@esgyn.com>.
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/