2017 Spring Newsletter — Shadowbase Support Tips

Bill G Holenstein

William Holenstein
Senior Manager of Product Delivery

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.