Re: Help! Looking for Data load utilities
This is a multi-part message in MIME format.
--------------6EFF5A93A68
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Kenneth L. Shuck Jr. wrote:
>
> Hello,
>
> We are in the process of moving from Oracle to Informix OnLine Server
[SNIP]
> database - dbload does not allow us to easily indicate the incoming date
> format or provide conversion "on-the-fly" to the Informix standard
> internal format. We believe that there may be a way to handle this
> problem using the DBDATE environmental variable, but that involves
> making changes to the registry which has us a little scared. Does
> anyone have any experience dealing with this situation or with any
Best I can do is donate my date handling functions. Attached find two
files stddate.c and getdate.y which together may help you formulate your
own loader. Let me know if you have no access to bison to compile
getdate.y and I will forward getdate.c to you. Good luck.
Art S. Kagel
--------------6EFF5A93A68
Content-Type: text/plain; charset=us-ascii; name="stddate.c"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="stddate.c"
/*
Written by Art S. Kagel
stddate.c -- Routine to convert any freeform date to an Informix/ANSI
standard date/time string. Converts times to GMT from Local
time if no timezone string is included in the input date string.
get_date.y -- The underlying function which converts freeform dates into
binary dates. I used bison to compile this yacc may choke.
str_to_stdstr -- The function which takes the output from get_date and
converts it into standard string format. Currently does not
check the get_date error handling.
TESTING -- #define this macro to compile the test program.
See the end of this file for an explanation of valid date/time inputs
The get_date function accepts almost anything!
*/
/* -------------------------- BEGIN FILE stddate.c ------------------------ */
#include <sys/timeb.h>
#include <stdio.h>
/* Use this definition of timeval since time.h seems to have a different
definition on different systems. */
struct timeval
{
long tv_sec;
long tv_usec;
};
extern struct timeval get_date( char *, struct timeb * );
typedef struct bdate_s {
long b_date;
long b_secs;
long b_usecs;
} bdate_t;
long days_in_mo[12] = {0, 31, 59, 90, 120, 151, 181, 212, 243, 273, 304, 334 };
long days_in_lp[12] = {0, 31, 60, 91, 121, 152, 182, 213, 244, 274, 305, 335 };
/* str_to_stdstr - Convert a date/time string in freeform format into a string
standard Informix/ANSI date-time format. Number of decimal places is
configurable.
datestr - pointer to input string
stdstr - pointer to returned formatted string
Converts to full Informix precision: YEAR TO FRAC(5)
for lesser precisions just trucate the end of the string or
copy from some point in the middle. The format is fixed (ie
4digit year, 2digit, lead zero, month, day hour, minute, seconds
and 5digits for fraction.
*/
char *str_to_stdstr( char *datestr, char *stdstr )
{
struct timeval tv;
struct timeb tb;
long id; /* Holds Informix format date. */
int year, month, day, hr, min, sec, ydays, yr, idx;
/* Convert the string to a high resolution format. */
/* The timeb is more standard but timeval has higher resolution */
/* 1/(10^6) vs 1/(10^4) */
tv = get_date( datestr, &tb );
/* Cannot use the standard UNIX date routines since they do not recognize
dates before 1970. */
id = (tv.tv_sec / 86400) + 25568;
sec = tv.tv_sec % 60;
min = (tv.tv_sec / 60) % 60;
hr = (tv.tv_sec / 3600) % 24;
/* # Days between leap years. 365 * 3 + 366 == 1461 */
yr = (id - (id / 1461)) / 365;
ydays = id - ((yr - 1) / 4 + yr * 365);
if (((id - (id / 1461)) % 365) == 0 && (ydays == 0 || ydays > 364))
{
year = 1900 + yr - 1;
month = 12;
day = 31;
} else {
year = 1900 + yr;
if ( ((year % 400) == 0 && (year % 4000) != 0) /* Skip 4th millenium */
|| ((year % 100) != 0 && (year % 4) == 0)) /* Not centuries. */
{
/* In a leap year use adjusted table. */
for (idx = 11; idx > 0; idx--)
if (ydays > days_in_lp[idx])
break;
day = ydays - days_in_lp[idx];
} else {
for (idx = 11; idx > 0; idx--)
if (ydays > days_in_mo[idx])
break;
day = ydays - days_in_mo[idx];
}
month = idx + 1;
}
sprintf( stdstr,
"%04.04d-%02.02d-%02.02d %02.02d:%02.02d:%02.02d.%05.05d",
year,
month,
day,
hr,
min,
sec,
tv.tv_usec / 10 );
return stdstr;
}
#ifdef TESTING
#include <time.h>
void main( int argc, char **argv )
{
char string[26];
time_t t;
t = time( (time_t *)NULL );
printf( "%s\\n", str_to_stdstr( argv[1], string ) );
}
#endif
/* ----------------------- END FILE stddate.c ------------------------------ */
--------------6EFF5A93A68
Content-Type: text/plain; charset=us-ascii; name="GETDATE.Y"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="GETDATE.Y"
%{
/*
** Originally written by Steven M. Bellovin <smb@research.att.com> while
** at the University of North Carolina at Chapel Hill. Later tweaked by
** a couple of people on Usenet. Completely overhauled by Rich $alz
** <rsalz@bbn.com> and Jim Berets <jberets@bbn.com> in August, 1990;
** send any email to Rich.
**
** This grammar has 10 shift/reduce conflicts.
**
** This code is in the public domain and has no copyright.
**
** Art S. Kagel, 2/28/1995 - enhanced to handle dates between 1902-01-01
** and 2037-12-31. Also enhanced to accept hh:mm:ss.fff, times with fractions
** of a second (requires 24-hour time format). Also enhanced error handling.
** since there are now valid dates <0 returned, now returns LONG_MIN on error
** and places an error code into a global variable: get_date_err, similar to
** UNIX getdate().
*/
/* SUPPRESS 287 on yaccpar_sccsid *//* Unused static variable */
/* SUPPRESS 288 on yyerrlab *//* Label unused */
#ifdef HAVE_CONFIG_H
#if defined (emacs) || defined (CONFIG_BROKETS)
#include <config.h>
#else
#include "config.h"
#endif
#endif
/* Since the code of getdate.y is not included in the Emacs executable
itself, there is no need to #define static in this file. Even if
the code were included in the Emacs executable, it probably
wouldn't do any harm to #undef it here; this will only cause
problems if we try to write to a static va