These are the steps to validate and send a long-long (64-bit) HPE NonStop Julian timestamp to MS SQL server to be used as a datetime or datetime2 datatype.
Calculate the datetime start and stop ranges in USRXINIT. These are used in validating the source long-long based on the target SQL server datatype (DATETIME or DATETIME2).
/* Global variables */
long long ValidStartTimestamp;
long long ValidStopTimestamp;
…
void USRXINIT (void)
{
unsigned short DateTime[8];
short DateError;
memset(DateTime, 0, sizeof(DateTime));
/*DateTime[0] = 1753;*/ /* first valid year for MSSQL datetime */
DateTime[0] = 1; /* first valid year for MSSQL datetime2 */
DateTime[1] = 1; /* month */
DateTime[2] = 1; /* day */
ValidStartTimestamp = COMPUTETIMESTAMP(DateTime, &DateError);
DateTime[0] = 9999; /* year */
DateTime[1] = 12; /* month */
DateTime[2] = 31; /* day */
ValidStopTimestamp = COMPUTETIMESTAMP(DateTime, &DateError);
} /* end USRXINIT */
Use SBGET… to get the long-long column.
short IsKey;
short ColumnValueLength;
long long ColumnValue;
short ReturnCode;
/* Get the datetime column from the audit event */
if (EventType == DELETE_OP)
ReturnCode = SBGETBEFORECOLUMN(“DT”, &IsKey, &ColumnValueLength, &ColumnValue);
else if ((EventType == INSERT_OP) || (EventType == UPDATE_OP))
ReturnCode = SBGETAFTERCOLUMN(“DT”, &IsKey, &ColumnValueLength, &ColumnValue);
if (ReturnCode != SB_RECORD_RETURNED)
SB_log_fatal(“SBGETCOLUMN return error %d”, ReturnCode);
Validate the timestamp.
short ValidDateRange(const long long Julian) /* Valid MS SqlServer date range */
{
if ((ValidStartTimestamp <= Julian) && (Julian <= ValidStopTimestamp))
return TRUE;
return FALSE;
}
First convert the long-long timestamp from Greenwich mean time to local civil time if necessary. Then convert the long-long timestamp into the internal format and do a SBPUTCOLUMN(). The target column in the NonStop dummy table must be a datetime year to fraction(6).
long long TransactionTS;
short stime[8];
unsigned char datetime[11];
long fraction;
TransactionTS = CONVERTTIMESTAMP(TransactionTS,0); /* Convert GMT timestamp to Local civil time (LCT). */
INTERPRETTIMESTAMP(TransactionTS,&stime[0]); /* Convert timestamp into array with year...fraction. */
memcpy(datetime, &stime[0], 2); /* year is two bytes */
datetime[2] = (unsigned char) stime[1]; /* month is one byte */
datetime[3] = (unsigned char) stime[2]; /* day is one byte */
datetime[4] = (unsigned char) stime[3]; /* hour is one byte */
datetime[5] = (unsigned char) stime[4]; /* minute is one byte */
datetime[6] = (unsigned char) stime[5]; /* second is one byte */
fraction = (stime[6] * 1000) + stime[7]; /* fraction is four bytes */
memcpy(&datetime[7], &fraction, 4);
ReturnCode = SBPUTCOLUMN("SHBA_TRXTIMESTAMP", sizeof(datetime), datetime);
if (ReturnCode != 1) /* Success */
SB_log_fatal("SBPUTCOLUMN return error %d", ReturnCode);
[END OF DOCUMENT]
Feel free to contact us to discuss these steps.
Please reference our Newsletter Disclaimer.