Shadowbase Support Tips

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.