DSXchange

Posted: **Thu Apr 19, 2018 2:58 am**

Hi.
I'm a problem inserting floatig values, in a SQL Server db, in the field "MVPREZZO" defined as below:

[MVPREZZO] decimal(18, 5) NULL

The job is very simple:

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:

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:

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

Posted: **Thu Apr 19, 2018 5:38 am**

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

Posted: **Thu Apr 19, 2018 6:05 am**

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

Thanks,
Andrea

Posted: **Thu Apr 19, 2018 6:41 am**

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.

Posted: **Thu Apr 19, 2018 7:09 am**

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

Posted: **Thu Apr 19, 2018 7:31 am**

Well, to be honest, if it was clear I wouldn't have asked for clarification.

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.

Posted: **Thu Apr 19, 2018 9:43 pm**

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 metadata in your job design? That's the recommended way to avoid metadata mismatches.

Posted: **Fri Apr 20, 2018 6:50 am**

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)

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)

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 "'"

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

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

Posted: **Fri Apr 20, 2018 8:12 am**

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.

Posted: **Fri Apr 20, 2018 9:17 am**

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: Select all

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.

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

Posted: **Fri Apr 20, 2018 9:42 am**

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...

Posted: **Mon Apr 23, 2018 6:20 am**

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

Posted: **Wed Apr 25, 2018 11:33 pm**

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

DSXchange

Problem with double SQL Server (ODBC)

Problem with double SQL Server (ODBC)