Page 1 of 2
How to convert sybase timestamp to Oracl timestamp
Posted: Tue Feb 06, 2007 5:16 pm
by jherr22
I have input Seq File stage, pointing to a csv file which came from Sybase, with a Timestamp field whose data looks like:
Feb 10 2007 10:30:30:450AM
1) In the columns view (of the Seq File Stage) should the length of the timestamp be specified?
2) What type of conversion (function, macro, or whatever) should be used within the Transform Stage to generate an Oracle-compatible value as input to the ODBC Oracle Stage?
Thanks afrorehand,
-- john
Posted: Tue Feb 06, 2007 5:33 pm
by DSguru2B
1)Yes.
2)Look into the timestamp functions inside the transformer or in the px developers guide. THere is a complete list. I believe you need to change it to string and then to timestamp again, but not to sure.
Posted: Wed Feb 07, 2007 11:23 am
by jherr22
There appears to be 3 different timestamp formats, viz:
a) Sybase,
b) Ascential, and
c) Oracle.
I guess my real problem is that I don't understand how we determine if the data from the input file is accepted as a valid timestamp format (in Sybase or Ascential format???), prior to exercising any derivation function.
Somehow I have to use AT LEAST 2 functions to go thru all 3 formats.
And then you suggest that I use a String as an intermediate step, which
now create 5 steps:
a) Sybase
b) string
c) Ascential
d) string, and
e) Oracle
Now I am really confused.
Are there any examples available is see how this is done?
-- john
Posted: Wed Feb 07, 2007 12:30 pm
by us1aslam1us
Have you checked or tried the SDK function DategenericToODBCTimestamp and other functions. Also what exactly you mean by Ascential format?
Re: How to convert sybase timestamp to Oracl timestamp
Posted: Wed Feb 07, 2007 1:51 pm
by DSguru2B
jherr22 wrote:
Feb 10 2007 10:30:30:450AM
Do you have a space before AM?
Posted: Wed Feb 07, 2007 3:50 pm
by jherr22
There is no space before AM; should there be?
I'll look into DategenericToODBCTimestep()
Thanks
-- john
Posted: Wed Feb 07, 2007 3:55 pm
by DSguru2B
DategenericToODBCTimestep() is a server function and not present in PX. You need to write a parsing C program to bring your input into a oracle format and then apply StringToTimestamp() function on it.
Posted: Wed Feb 07, 2007 4:16 pm
by DSguru2B
Use the following C function to convert your input into YYYY-MM-DD 24HH:mm:ss.nnn format. THen use StringToTimestamp() to convert the string to timestamp.
Code: Select all
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
char* SybaseToOracleTp(char* InTp)
{
//Initialize variables
const int SIZE = 30;
char* month = (char *)malloc(SIZE);
char* day = (char *)malloc(SIZE);
char* year = (char *)malloc(SIZE);
char* hour = (char *)malloc(SIZE);
char* newHr = (char *)malloc(SIZE);
char* min = (char *)malloc(SIZE);
char* sec = (char *)malloc(SIZE);
char* msec = (char *)malloc(SIZE);
char* time = (char *)malloc(SIZE);
char* intMon = (char *)malloc(SIZE);
char* finOut = (char *)malloc(SIZE);
const char* calender[] = {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"};
int hr = 0;
//AM or PM
char* p = strstr(InTp, "PM");
//Disect the Date
strcpy(month, strtok(InTp, " "));
strcpy(day, strtok(NULL, " "));
strcpy(year, strtok(NULL, " "));
strcpy(time, strtok(NULL," "));
//Disect Time
strcpy(hour, strtok(time, ":"));
strcpy(min, strtok(NULL, ":"));
strcpy(sec, strtok(NULL, ":"));
strcpy(msec, strtok(NULL, ":"));
//get numeric representation of Month
for(int i = 0; i < 12; i++)
{
if (strcmp(month, calender[i]) == 0)
sprintf(intMon, "%02d", i + 1);
}
if ((p) && strcmp(hour, "12") != 0)
{
hr = atoi(hour);
hr+=12;
sprintf(hour, "%02d", hr);
}
if ((!p) && strcmp(hour, "12") == 0)
{
strcpy(hour, "00");
}
//format string to YYYY-MM-DD HH:MM:SS.sss
sprintf(finOut, "%s-%2s-%s %s:%s:%s.%s", year, intMon, day, hour, min, sec, msec);
//free memory
free(month);
free(day);
free(year);
free(hour);
free(min);
free(sec);
free(msec);
free(time);
free(intMon);
return finOut;
}
Posted: Wed Feb 07, 2007 4:22 pm
by us1aslam1us
Woow!! But does it accept any timestamp Format as Input?
Posted: Wed Feb 07, 2007 4:24 pm
by DSguru2B
No Sam, this is just for the input shown by the OP. Because i am disecting the date and I know that the first part is going to be Month in three character string, the second part is Day and so on. It wont work for any input format.
Posted: Wed Feb 07, 2007 7:34 pm
by ray.wurlod
... but you could adapt it to your own requirements.
Posted: Wed Feb 07, 2007 9:31 pm
by DSguru2B
Most certainly. Maybe a generic routine could be created to handle all date and timestamp formats.
Posted: Wed Feb 07, 2007 11:52 pm
by ray.wurlod
Including all foreign language representations? If you claim to write anything "generic" in DataStage you will need to make use of NLS locales, for example 23 AVR 2007 is a valid date in Canada, France and lots of other places.
Posted: Thu Feb 08, 2007 12:26 pm
by DSguru2B
ray.wurlod wrote:Including all foreign language representations?
Possibly. Nothing is impossible. Just needs time. Maybe some kind hearted soul will do that for us
Posted: Thu Feb 08, 2007 4:21 pm
by jherr22
I am not getting anywhere with this Timestamp problem.
This post will simplify the problem, in hopes of advice.
Within the Seq. File Stage, I now point to a CSV file, each row with 6 fields, with each field in double quotes, and each separated by a coma
(and no trailing coma).
The 4th field in the row is a Timestamp.
I loaded the table definition with the 6 fields, the 4th of which is a
Timestamp, with length 26, and no scale value, and not nullable.
The data in the 4th field is: "Jan 10 2007 10:30:31:246AM" which is
26 chars long. (The data came from a Timestamp in Sybase.)
I then bring up Seq. File Properties, selecting the columns tab, and then I click on the "View Data", and I get:
"Bad trailing quote character at field "my_date"; looking for '34' but found '0', at offset: 89"
This makes no sense to me.
I have tried everything:
-- changing the length of the column definition,
-- dropping the double quotes between the comas,
and many other attempts.
Nothing will bring up the timestamp (using View Data)
If I switch the field type from Timestamp to Varchar, "my_date" shows up just fine (under View Data).
Within DataStage Manager | selected Project | Properties button | Parallel tab , and then under "Timestamp String" I see the default Timestamp string which is:
%yyyy-%mm-%dd %hh:%nn:%ss
I have no idea when this default format is used, but I decided to try it.
So, I tried changing my data in the input file to look like that, and I still
could not get View Data to bring up the data.
In a previous post by Brian, C-code is provided, which is great, which I am now trying to compile into a library.
But, if View Data cannot see the data, (which means to me that the data will be rejected upon Job Execution), then how could a C-function operate on it?
I'm baffled.
Any possibility of help with this?
Thanks
-- john