Truncate Table effect on Change Views
Go Up to Truncate Table
When a client database connection activates subscriptions containing one or more truncated tables, the client receives two indications of the underlying truncate activity.
First, when a cursor opens (the SELECT
operation is executed), a warning status vector indicating isc_table_truncated
returns with the name of the truncated table. A warning status vector can chain together five separate isc_table_truncated
status codes of truncated tables in a SELECT statement
. Clients can use this form of table notification to truncate one or more corresponding tables on the client.For example, after executing the query:
if (isc_dsql_execute(status_vector, ...) == 0) /* after successful execution check for warnings */
{
if (status_vector[2] == isc_arg_warning)
// A warning status vector for one or more truncated tables shall have the following format.
status_vector[0] = isc_arg_gds
status_vector[1] = 0
status_vector[2] = isc_arg_warning
// The following sequence can be repeated up to 5 times
status_vector[3] = isc_table_truncated
status_vector[4] = isc_arg_string
status_vector[5] = name of table truncated
// status_vector terminator
status_vector[last element] = isc_arg_end
Second, on every fetch from the cursor, a SQLIND_TRUNCATE
flag is set in the SQL indicator member of a SQLVAR
element for a column of a truncated table. Clients can use this form of column notification to delete one or more rows in corresponding tables before using the other SQLIND
flags to decide on the appropriate row modification operation.
/* Bit flag definitions for SQLVAR.sqlind output variable */
#define SQLIND_NULL (short) (1 << 15)
#define SQLIND_INSERT (1 << 0)
#define SQLIND_UPDATE (1 << 1)
#define SQLIND_DELETE (1 << 2)
#define SQLIND_CHANGE (1 << 3)
#define SQLIND_TRUNCATE (1 << 4)
#define SQLIND_CHANGE_VIEW (1 << 5)
If the query returns no rows because there were no changes to the subscribed tables subsequent to table truncation, then only the first method can be used. The second method will not work since the SQLDA/SQLVAR element will not be populated because no rows have been returned.
Higher level database frameworks such as FireDAC may surface these truncate notifications with supporting APIs (e.g., isTruncated()
.)