DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic is not resolved, but there is a WORKAROUND.
Author Message
ponzio
Participant



Joined: 05 Dec 2005
Posts: 165
Location: Italy
Points: 1879

Post Posted: Thu Apr 19, 2018 2:58 am Reply with quote    Back to top    

DataStage® Release: 8x
Job Type: Server
OS: Windows
Hi.
I'm a problem inserting floatig values, in a SQL Server db, in the field "MVPREZZO" defined as below:

Quote:
[MVPREZZO] decimal(18, 5) NULL



The job is very simple:
Quote:
CSV -> transformer -> ODBC Stage


The only data type I can use on the target ODBC stage to avoid the warnig "SQLColAttributes(MVPREZZO) gave MetaData mismatch" is "Decimal".


I can't find a metadata combination that works fine. I tried both decimal format separator in the input CSV file.


With "." decimal separator in the input file I get the error:

Quote:
SQLSTATE=22005, DBMS.CODE=0
[DataStage][SQL Client]Non-numeric data was found where numeric required

XORDERDETAILID = "SGR000000001"
XORDERID = "SGR000000001"
MVCODICE = "CALD_MANUTENZIONE"
MVDESART = "Manutenzione Caldaia"
MVPREZZO = 62.95081967
MVCODIVA = "22"
CPCCCHK = "XXX"


With "," decimal separator in the input file I get the error:

Quote:
SQLSTATE=22005, DBMS.CODE=0
[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification

XORDERDETAILID = "SGR000000001"
XORDERID = "SGR000000001"
MVCODICE = "CALD_MANUTENZIONE"
MVDESART = "Manutenzione Caldaia"
MVPREZZO = 62,95082
MVCODIVA = "22"
CPCCCHK = "XXX"


Can someone help me ?

Thanks,
Andrea
ArndW

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup

Joined: 16 Nov 2004
Posts: 16318
Location: Germany
Points: 92566

Post Posted: Thu Apr 19, 2018 5:38 am Reply with quote    Back to top    

Since this is a Server Job, have you tried defining MVPREZZO as VarChar(24) in the Output stage?

_________________

Image
Rate this response:  
Not yet rated
ponzio
Participant



Joined: 05 Dec 2005
Posts: 165
Location: Italy
Points: 1879

Post Posted: Thu Apr 19, 2018 6:05 am Reply with quote    Back to top    

I tried now.
It doesn't work. Using, for example, both "10.5" and "10,5" as input value.

Thanks,
Andrea
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42762
Location: Denver, CO
Points: 220350

Post Posted: Thu Apr 19, 2018 6:41 am Reply with quote    Back to top    

Always best to explain, when something "doesn't work", the why of it. What doesn't work about it?

I'd also be curious what your actual target datatype is - float(n), real? With a mismatch like you have, you may need to use custom DML in the ODBC stage where you cast the internal datatype to the required target datatype.

_________________
-craig

Research shows that 6 out of 7 dwarves aren't happy
Rate this response:  
Not yet rated
ponzio
Participant



Joined: 05 Dec 2005
Posts: 165
Location: Italy
Points: 1879

Post Posted: Thu Apr 19, 2018 7:09 am Reply with quote    Back to top    

Hi.

chulett wrote:
Always best to explain, when something "doesn't work", the why of it. What doesn't work about it?

Sorry, I supposed it was clear , the data is not loaded into the table.

chulett wrote:
I'd also be curious what your actual target datatype is - float(n), real? With a mismatch like you have, you may need to use custom DML in the ODBC stage where you cast the internal datatype to the required target datatype.

The actual target data type is "Decimal", according to the SQL Server DDL :

[MVPREZZO] decimal(18, 5) NULL

This data type is the metadata imported and it's the only one that doesn't produce and error when I do the "view data".

On the input side I tried many ways for the data type (Numeric, Float, Double, Decimal) and also for the data itself (decimal separator "," or ".")



Thanks,
Andrea
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42762
Location: Denver, CO
Points: 220350

Post Posted: Thu Apr 19, 2018 7:31 am Reply with quote    Back to top    

Well, to be honest, if it was clear I wouldn't have asked for clarification. Wink We could guess the data didn't make it into the table or perhaps it was inserted but incorrectly, the value getting corrupted in the process. Was looking for any error(s) generated as well, like you reported earlier.

Asked the question about the SQL Server datatype because you said you were inserting "floating values" so was thinking it would be float rather than a simple decimal. You're also going to have truncation issues inserting "62.95081967" into an 18,5 decimal, btw, whenever you get past this troubling conversion issue.

I'd still suggest you try reading it as a string and then use a custom insert statement that does the appropriate cast, see if that works.

_________________
-craig

Research shows that 6 out of 7 dwarves aren't happy
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54395
Location: Sydney, Australia
Points: 295036

Post Posted: Thu Apr 19, 2018 9:43 pm Reply with quote    Back to top    

It seems to me that 62.95081967 has more than five decimal places. What is the defined data type in SQL Server? Did you import the table definition from SQL Server into DataStage, and use those ...

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne (especially seeking good business analysts)
Rate this response:  
Not yet rated
ponzio
Participant



Joined: 05 Dec 2005
Posts: 165
Location: Italy
Points: 1879

Post Posted: Fri Apr 20, 2018 6:50 am Reply with quote    Back to top    

Hi all.

ray.wurlod wrote:
It seems to me that 62.95081967 has more than five decimal places.

What is the defined data type in SQL Server? Did you import the table definition from SQL Server into DataStage, and use those me ...


I think the problem is not due to the number of decimal places, because I've also tried fixed values with less ones.

chulett wrote:

I'd still suggest you try reading it as a string and then use a custom insert statement that does the appropriate cast, see if that works.


I tried to use "varchar" both on input and output stage, using the default "Insert new or update existing rows", and I get the followint 2 errors in the director

(1)
Quote:
CopyOfJSGRV_FATTVASIP_00300_FATTURE_MESE_LOADDB1..ODBC_ORDER.ORDERDETAIL_insupd: DSD.BCIOpenW results of function SQLColAttributes(MVPREZZO) gave MetaData mismatch
MetaData mismatch on COLUMN.SCALE Expected = 0 Actual = 5


because varchar is diferent from decimal(18,5) that is the imported one.

(2)
Quote:
CopyOfJSGRV_FATTVASIP_00300_FATTURE_MESE_LOADDB1..Transformer_253.ORDERDETAIL_insupd: DSD.BCIPut call to function SQLExecute failed.
SQL statement:UPDATE SE_ORDERDETAIL SET XORDERID = ?, MVCODICE = ?, MVDESART = ?, MVPREZZO = ?, MVCODIVA = ?, CPCCCHK = ? WHERE (XORDERDETAILID = ?)
SQLSTATE=22005, DBMS.CODE=0
[DataStage][SQL Client]Non-numeric data was found where numeric required

XORDERDETAILID = "SGR000000001"
XORDERID = "SGR000000001"
MVCODICE = "CALD_MANUTENZIONE"
MVDESART = "Manutenzione Caldaia"
MVPREZZO = 62.95081967
MVCODIVA = "22"
CPCCCHK = "XXX"


Since the value in the csv file is "62.95081967" I suppose Datastage is not doing a conversion, and store this value in director.
I suppose ODBC is doing a conversion and it's passing a different value to the db server.
In fact, launching the followig sql in a sql pad, with the number enclosed with "'"

Quote:
insert into SE_ORDERDETAIL (
XORDERDETAILID,
XORDERID,
MVPREZZO,
CPCCCHK
) values(
'SGR000000001',
'SGR000000001',
'13.4',
'XXX'
)


the value is correctly inserted.

I tried to user "User-defined SQL" tryng t put "'" around the values

INSERT INTO SE_ORDERDETAIL(XORDERDETAILID, XORDERID, MVCODICE, MVDESART, MVPREZZO, MVCODIVA, CPCCCHK) VALUES (?,?,?,?,'?',?,?);

it doesn't work..and I get the error

Quote:
CopyOfJSGRV_FATTVASIP_00300_FATTURE_MESE_LOADDB1..Transformer_253.ORDERDETAIL_insupd: DSD.BCIPut call to function SQLExecute failed.
SQL statement:INSERT INTO SE_ORDERDETAIL(XORDERDETAILID, XORDERID, MVCODICE, MVDESART, MVPREZZO, MVCODIVA, CPCCCHK) VALUES (?,?,?,?,'?',?,?)
SQLSTATE=37000, DBMS.CODE=8114
[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to numeric.

XORDERDETAILID = "SGR000000001"
XORDERID = "SGR000000001"
MVCODICE = "CALD_MANUTENZIONE"
MVDESART = "Manutenzione Caldaia"
MVPREZZO = "62.95081967"
MVCODIVA = "22"
CPCCCHK = "XXX"


It's a very big problem I'm not able to insert this value in the db.
Please, let me know how to do this

chulett wrote:
I'd still suggest you try reading it as a string and then use a custom insert statement that does the appropriate cast, see if that works.



Thanks,
Andrea
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42762
Location: Denver, CO
Points: 220350

Post Posted: Fri Apr 20, 2018 8:12 am Reply with quote    Back to top    

Surely there's someone there who can help you with basic SQL Server syntax? It's not specific to DataStage, just thinking that leveraging CAST in custom SQL would be one way to go. It's also strange to me that you are having problems with this, wondering what piece of information we're missing that would explain the issue.

How about something else to try? Server jobs are very forgiving about data types, they are not strongly typed like PX jobs and generally handle implicit conversions properly. In your shoes I would still try reading the original value as a string, then in a transformer moving it from the varchar input port to a decimal output port that matches the metadata in your target. Lastly, link that decimal value to ODBC. The transformer will convert it so any target insert or update should be happy. Should. Wink

_________________
-craig

Research shows that 6 out of 7 dwarves aren't happy
Rate this response:  
Not yet rated
ponzio
Participant



Joined: 05 Dec 2005
Posts: 165
Location: Italy
Points: 1879

Post Posted: Fri Apr 20, 2018 9:17 am Reply with quote    Back to top    

Hi.
chulett wrote:
Surely there's someone there who can help you with basic SQL Server syntax? It's not specific to DataStage, just thinking that leveraging CAST in custom SQL would be one way to go.

I'd like just to put "'" around the value and it should work, but I don't know what DS insert in place of "?" symbol in the User-Defined query (form exmaple I can't a see the "'" character for string in the query)

Code:
INSERT INTO SE_ORDERDETAIL(XORDERDETAILID, XORDERID, MVCODICE, MVDESART, MVPREZZO, MVCODIVA, CPCCCHK) VALUES (?,?,?,?,?,?,?);

I also don't know what ODBC try to do knowing that field is a decimal.
It's different, for example, working with Oracle stage, where you can see exactly what query DS send to the oracle client (for example timestamp conversion).

chulett wrote:
It's also strange to me that you are having problems with this, wondering what piece of information we're missing that would explain the issue.

How about something else to try? Server jobs are very forgiving about data types, they are not strongly typed like PX jobs and generally handle implicit conversions properly

I'm working with DS for many years, and I've never had problem like this.
But the decimal data type with SQL Server is a "know problem". If I'm not wrong I've found a similar thread here in DSXchange.
the problem is also present in PX: I've talk about it with a DS more expert collegue that daily has problem with this ... usually solved using PX routine not present in DS Server version.

chulett wrote:
In your shoes I would still try reading the original value as a string, then in a transformer moving it from the varchar input port to a decimal output port that matches the metadata in your target. Lastly, link that decimal value to ODBC. The transformer will convert it so any target insert or update should be happy. Should. Wink

I've read the original value as a string. What function should I use to move the varchar input into a decimal ?

Many thanks,
Andrea
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42762
Location: Denver, CO
Points: 220350

Post Posted: Fri Apr 20, 2018 9:42 am Reply with quote    Back to top    

No function involved, hence the use of the term implicit conversion. Simply move it from one data type to the other inside the transformer. It will convert the value 'under the covers'.

Those "?" you see in the generated SQL are bind variables. Since the values change for every row of data, the ODBC target has to know which column on the input link to bind to each update value. Questions marks are positional, meaning the first ? gets the value from the first column, the second ? from the second column, etc. etc. And lastly, two sets of numbers are maintained - one for key columns (which bind into the where clause filter) and one for non-key data columns which bind into the set side. Apologies if you are already fully aware of this...

_________________
-craig

Research shows that 6 out of 7 dwarves aren't happy
Rate this response:  
Not yet rated
ponzio
Participant



Joined: 05 Dec 2005
Posts: 165
Location: Italy
Points: 1879

Post Posted: Mon Apr 23, 2018 6:20 am Reply with quote    Back to top    

Hi.

I'm aware about all.
The real problem is that we actually don't know what sql is sent to the db server trought odbc driver.
I'm my opinion the combination datastage+odbc+SQL Server+Decimal can't work at all (many others agree).
I solved using OLE DB spending 5 minutes.

Many thanks for the support.
Andrea
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54395
Location: Sydney, Australia
Points: 295036

Post Posted: Wed Apr 25, 2018 11:33 pm Reply with quote    Back to top    

You can trace what the ODBC driver is doing. This includes the SQL queries it is sending to the database.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne (especially seeking good business analysts)
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours