Problem with double SQL Server (ODBC)

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
ponzio
Participant
Posts: 165
Joined: Mon Dec 05, 2005 9:13 am
Location: Italy

Problem with double SQL Server (ODBC)

Post by ponzio »

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
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

Since this is a Server Job, have you tried defining MVPREZZO as VarChar(24) in the Output stage?
ponzio
Participant
Posts: 165
Joined: Mon Dec 05, 2005 9:13 am
Location: Italy

Post by ponzio »

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

Thanks,
Andrea
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
ponzio
Participant
Posts: 165
Joined: Mon Dec 05, 2005 9:13 am
Location: Italy

Post by ponzio »

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
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
ponzio
Participant
Posts: 165
Joined: Mon Dec 05, 2005 9:13 am
Location: Italy

Post by ponzio »

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
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
ponzio
Participant
Posts: 165
Joined: Mon Dec 05, 2005 9:13 am
Location: Italy

Post by ponzio »

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. :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
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
ponzio
Participant
Posts: 165
Joined: Mon Dec 05, 2005 9:13 am
Location: Italy

Post by ponzio »

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
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

You can trace what the ODBC driver is doing. This includes the SQL queries it is sending to the database.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply