odbc error

rsunny · Post by **rsunny** » Sun Sep 26, 2010 6:16 pm

hi ,

i have a date field with datatype as 'timestamp' , need to upload it to oracle using odbc stage. i got the following error

'insert..Transformer_5.DSLink5: DSD.BCIPut call to SQLExecute failed.
SQL statement:INSERT INTO"sanexis"("sid", STOREID, NAME, CITY, STATE, CID, STARTDATE, ENDDATE) VALUES (?,?,?,?,?,?,?,?)
SQLSTATE=22008, DBMS.CODE=0
[DataStage][SQL Client][ODBC][DataDirect][ODBC Oracle Wire Protocol driver]Datetime field overflow. Error in parameter 8.'

please can anyone tell me what should i do to overcome this error.

thanks in advance

chulett · Post by **chulett** » Sun Sep 26, 2010 7:45 pm

What is the data type of your target table column? DATE? Unless you precisely match the NLS_DATE_FORMAT of your target, you need to use TO_DATE() in your SQL with a mask that matches your incoming timestamp's format.

psreepa123 · Post by **psreepa123** » Sun Sep 26, 2010 7:50 pm

I think its the problem with your DNS connection error.
Psreepa

rsunny · Post by **rsunny** » Sun Sep 26, 2010 7:55 pm

chulett wrote:What is the data type of your target table column? DATE? Unless you precisely match the NLS_DATE_FORMAT of your target, you need to use TO_DATE() in your SQL with a mask that matches your incom ...

Hi ,

my target datatype is 'timestamp' and can you please tell me how to match the NLS_DATE_FORMAT.i am using ODBC target(oracle).

thanks in advance.

chulett · Post by **chulett** » Sun Sep 26, 2010 8:12 pm

Not in the job, but in the Oracle table itself - it is a TIMESTAMP? With what precision? If so, you should be using the TO_TIMESTAMP() function then.

rsunny · Post by **rsunny** » Mon Sep 27, 2010 7:06 am

Hi everyone,

Even though if i use to_timestamp() , still i am getting the same error:

insert..Transformer_8.DSLink9: DSD.BCIPut call to SQLExecute failed.
SQL statement:INSERT INTO "sanexis"("sid", STOREID, NAME, CITY, STATE, CID, STARTDATE, ENDDATE) VALUES (?,?,?,?,?,?,TO_TIMESTAMP('10-SEP-0214:10:10.123000','DD-MON-RRHH24:MI:SS.FF')
,?)
SQLSTATE=22008, DBMS.CODE=0
[DataStage][SQL Client][ODBC][DataDirect][ODBC Oracle Wire Protocol driver]Datetime field overflow. Error in parameter 7.

sid = "323"
STOREID = "100"
NAME = "san"
CITY = "bradly"
STATE = "nj"
CID = "Y"
STARTDATE = "15611"
ENDDATE = "12/12/2099"

can anyone please tell what is the function to be used in my target which is ODBC(oracle) to be recovering from this error.I have no idea what to do with this error .itried using ICONV(DSJobStartDate,"D-YMD") in my transformer too but its not working.My datatype in target is 'Timestamp'.please give me suggestions or code to overcome this error.

chulett · Post by **chulett** » Mon Sep 27, 2010 7:12 am

Well, look carefully at the message and you can see it's not quite the same error - you "solved" the problem for parameter 8 but now see the same issue for parameter 7.

rsunny · Post by **rsunny** » Mon Sep 27, 2010 7:36 am

Hi ,

I haven't done anything with parameter8, but i am trying to fix it for parameter7 only.so please can you tell me now what is the error in parameter7.Does my function is wrong or anything else.So what is the solution to fix in parameter7.

thanks in advance

chulett · Post by **chulett** » Mon Sep 27, 2010 7:39 am

And note that I put 'solved' in quotes because for some odd reason you decided to hard-code your timestamp value in the DML and while it matches the format mask you used (and thus 'worked') it isn't the same value nor format as your actual data.

Plus what kind of date/timestamp value is a STARTDATE of 15611?

chulett · Post by **chulett** » Mon Sep 27, 2010 7:46 am

rsunny wrote:I haven't done anything with parameter8, but i am trying to fix it for parameter7 only.

OK, fine... by goofing up the sql and hard-coding one value, you now have only seven parameter markers. It is complaining about trying to put 15611 into a timestamp.

rsunny · Post by **rsunny** » Mon Sep 27, 2010 7:58 am

Hi ,

I tried to solve the problem but i have a small problem as in my input i had given as 2099-12-12 08:45:54 but in my output i am getting as 1973-09-05 08:45:54.Can anyone tell me why am i not getting the same date as given in the input.

thanks in advance

chulett · Post by **chulett** » Mon Sep 27, 2010 9:21 am

Not without details. For example, what does you input look like directly from the source? What transformations are you doing? What does it look like when it hits your target stage? You've given an example, but we don't know what 'state' it represents.

Bottom line, though, is all you need to do is get your data into a consistent state and then use a matching format mask in your TO_DATE() or TO_TIMESTAMP() function in the DML.