SQL/MX Queuing and Publish/Subscribe Services

Embedded SQL Examples
HP NonStop SQL/MX Queuing and Publish/Subscribe Services—523734-002
3-12
Managing Transactions With the Stream Timeout
Attribute
:hv_sqlcode = SQLCODE;
hv_sqlstate[5] = 0;
printf("condition number: %d\n", hv_cond_num);
printf("sqlstate: %s\n", hv_sqlstate);
printf("table name: %s\n", hv_table_name);
printf("column name: %s\n", hv_column_name);
printf("message text: %s\n", hv_message_text);
printf("sqlcode: %ld\n", hv_sqlcode);
printf("\n");
}
/* Special handling for stream_timeout exception. */
if (hv_sqlcode == -8006 && num == 1)
{
printf("Stream timeout.\n");
timed_out = TRUE;
return;
}
exit(1);
err_exit:
printf("Error in the error handler. SQLCODE =%d\n", SQLCODE);
exit(2);
}
---------------------------------------------------------------
After the stream timeout error (SQLCODE -8006) is returned, the application shown in
the preceding example commits the transaction, starts a new transaction, and
reexecutes the statement. (If it were using a cursor, it could simply fetch again from the
cursor after committing and restarting the transaction.) In this way, no transaction lasts
longer than 30 seconds. However, this solution allows some empty transactions (those
with no operations). Also, efforts to lower the stream timeout setting to further limit the
duration of each transaction cause a higher rate of empty transactions.
The next application takes a different approach. It defers starting a transaction until
one or more rows are available in the stream. It does so by using two SQL statements.
The first statement's function is to monitor the stream. The first statement does not
perform an embedded DELETE, so it can use READ UNCOMMITTED access.
Statements using READ UNCOMMITTED access do not start transactions or hold
locks, so this first statement is coded with a STREAM_TIMEOUT configuration set to
wait forever.
When the first statement has returned, indicating that one or more row is available, the
application enters a loop in which a transaction is started and a second statement
deletes the available rows from the stream. The rows are deleted by using an
embedded DELETE. After each row, the transaction is committed and a new
transaction is started. The second statement uses a STREAM_TIMEOUT configuration