Purpose
Tests the completion status of the asynchronous SQL statement identified by async_statement_identifier.
When used with the WAIT statement, returns the completion status of the asynchronous SQL statement identified by async_statement_identifier or by host_variable_name, but does not wait if the request has not completed.
Invocation
Executable.
Embedded SQL only.
Syntax
TEST {
async_statement_identifier |
:async_statement_identifier_variable_name
} COMPLETION
- async_statement_identifier
- A case-sensitive, application-supplied identifier for an asynchronously executed SQL statement assigned by the ASYNC modifier.
- :async_statement_identifier_variable_name
- the name of a host variable that contains an asynchronous statement identifier.
ANSI Compliance
TEST is a Teradata extension to the ANSI/ISO SQL:2011 standard.
Authorization
None.
Rules
Each async_statement_identifier assignment is case-sensitive and must be unique across all active connections.
The maximum length of each async_statement_identifier is 30 bytes.
The value for :async_statement_identifier_variable_name must be a fixed or varying length character variable no longer than 30 bytes.
- SQLCODE is set to -650.
- SQLSTATE is set to ‘04000’.
- SQLCODE is set to -651.
- SQLSTATE is set to ‘03000’.
- The runtime finishes processing the request and returns the completion status via the SQLCODE or the SQLSTATE.
- The SQL statement named by async_statement_identifier can no longer be referenced.
- Cursor requests specified by the DECLARE CURSOR statement.
- Dynamic requests specified by the PREPARE or EXECUTE IMMEDIATE statements.
Examples 1 - 3
The following RDTIN fields are important for these examples:
| This RDTIN field … | Must … |
|---|---|
| RdtCType | be set to 460. |
| RdtVersn | be set to 10. |
| RdtExt | be set to ‘Y’ to indicate the existence of an extension area. |
| RdtXTotL | include the size of the RDTXASYN extension area. |
Additionally, the RdtX008 (RDTXASYN) structure must be included as one of the extension areas because it communicates the connection name.
Example: Explicitly Specifying an Async Statement Identifier
This example uses an explicitly specified async statement identifier.
EXEC SQL TEST ASYNSTMT1 COMPLETION;
Lines Generated by C Preprocessor2 for Example 1
{
static struct {
SQLInt32 RdtCType;
SQLInt16 RdtVersn;
SQLInt16 RdtRfu1;
char RdtUserid[8];
SQLInt32 RdtEntty;
char *RdtCA;
char *RdtDAIn;
char *RdtDAOut;
char *RdtSql;
char *RdtRtCon;
SQLInt32 RdtAux1;
SQLInt32 RdtAux2;
char RdtLCS;
char RdtComit;
char RdtRelse;
char RdtExt;
char RdtSepBT;
char RdtUCStm;
char RdtCmpat;
char RdtComp;
SQLInt16 RdtXTotL;
char RdtXFill[2];
struct {
SQLInt16 RdtXLen;
SQLInt16 RdtXType;
SQLInt32 RdtXCode;
} RdtX005;
struct {
SQLInt16 RdtXLen;
SQLInt16 RdtXType;
SQLInt32 RdtXAsyC;
struct {
SQLInt16 RdtXAsyL;
char RdtXAsyT[30];
} RdtXAsyS;
} RdtX008;
} RDTIN011 =
{460,9,0,{' '},0,0,0,0,0,0,0,0,'N','C','N','Y','N','N',' ','C'
,52,{' '},{8,5,255},{40,8,1,{9,'A','S','Y','N','S','T','M','T'
,'1',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',
' ',' ',' ',' ',' ',' ',' '}}};
...
RDTIN011.RdtX008.RdtXAsyL = strlen(STMTNAMF);
memcpy(RDTIN011.RdtX008.RdtXAsyT,STMTNAMF,strlen(STMTNAMF));
...
}
Example 2
The following example uses a host variable to obtain the async statement identifier:
EXEC SQL TEST :STMTNAMV COMPLETION;
Lines Generated by C Preprocessor2 for Example 2
{
static struct {
SQLInt32 RdtCType;
SQLInt16 RdtVersn;
SQLInt16 RdtDec;
char RdtUserid[8];
SQLInt32 RdtEntty;
char *RdtCA;
char *RdtDAIn;
char *RdtDAOut;
char *RdtSql;
char *RdtRtCon;
SQLInt32 RdtAux1;
SQLInt32 RdtAux2;
char RdtLCS;
char RdtComit;
char RdtRelse;
char RdtExt;
char RdtSepBT;
char RdtUCStm;
char RdtCmpat;
char RdtComp;
SQLInt16 RdtXTotL;
char RdtXFill[2];
struct {
SQLInt16 RdtXLen;
SQLInt16 RdtXType;
SQLInt32 RdtXCode;
} RdtX005;
struct {
SQLInt16 RdtXLen;
SQLInt16 RdtXType;
SQLInt32 RdtXAsyC;
struct {
SQLInt16 RdtXAsyL;
char RdtXAsyT[30];
} RdtXAsyS;
} RdtX008;
} RDTIN017 =
{460,10,0,{' '},0,0,0,0,0,0,0,0,'N','B','N','Y','N','N',' ','C',
52,{' '},{8,5,255},{40,8,1,}};
RDTIN017.RdtX008.RdtXAsyS.RdtXAsyL = STMTNAMV.len;
memcpy(RDTIN017.RdtX008.RdtXAsyS.RdtXAsyT,STMTNAMV.arr,STMTNAMV.len);
RDTIN017.RdtCA = (char *)(&sqlca);
RDTIN017.RdtRtCon = SQL_RDTRTCON;
TDARDI(&RDTIN017);
SQL_RDTRTCON = RDTIN017.RdtRtCon;
}
Example 3
The following example uses a host variable to pass the async statement identifier as a fixed length character string
EXEC SQL TEST :STMTNAMF COMPLETION;
Lines Generated by C Preprocessor2 for Example 3
{
static struct {
SQLInt32 RdtCType;
SQLInt16 RdtVersn;
SQLInt16 RdtDec;
char RdtUserid[8];
SQLInt32 RdtEntty;
char *RdtCA;
char *RdtDAIn;
char *RdtDAOut;
char *RdtSql;
char *RdtRtCon;
SQLInt32 RdtAux1;
SQLInt32 RdtAux2;
char RdtLCS;
char RdtComit;
char RdtRelse;
char RdtExt;
char RdtSepBT;
char RdtUCStm;
char RdtCmpat;
char RdtComp;
SQLInt16 RdtXTotL;
char RdtXFill[2];
struct {
SQLInt16 RdtXLen;
SQLInt16 RdtXType;
SQLInt32 RdtXCode;
} RdtX005;
struct {
SQLInt16 RdtXLen;
SQLInt16 RdtXType;
SQLInt32 RdtXAsyC;
struct {
SQLInt16 RdtXAsyL;
char RdtXAsyT[30];
} RdtXAsyS;
} RdtX008;
} RDTIN018 =
{460,10,0,{' '},0,0,0,0,0,0,0,0,'N','B','N','Y','N','N',' ','C',
52,{' '},{8,5,255},{40,8,1,}};
RDTIN018.RdtX008.RdtXAsyS.RdtXAsyL = strlen(STMTNAMF);
memcpy(RDTIN018.RdtX008.RdtXAsyS.RdtXAsyT,STMTNAMF,strlen(STMTNAMF));
RDTIN018.RdtCA = (char *)(&sqlca);
RDTIN018.RdtRtCon = SQL_RDTRTCON;
TDARDI(&RDTIN018);
SQL_RDTRTCON = RDTIN018.RdtRtCon;
}
Examples 1-3
The following examples present TEST statement SQL text without any client programming code context.
Example: Testing the Statement Identified by the Name req_1
This example tests the statement identified by the name req_1 for completion and returns the appropriate exception or completion code to SQLCODE or SQLSTATE.
The name req_1 is defined by the ASYNC clause using the async_statement_modifier variable.
TEST req_1 COMPLETION
Example: Testing the Statement Identified by the Host Variable :reqid_var
This example tests the statement identified by the host variable :reqid_var for completion and returns the appropriate exception or completion code to SQLCODE or SQLSTATE.
The name contained within :reqid_var is defined by the ASYNC clause using the async_statement_modifier variable.
TEST :reqid_var COMPLETION
Example: Using TEST with WAIT
This example uses TEST with WAIT. The program asynchronously spawns two update requests, req_1 and req_2, respectively, then waits until both req_1 and req_2 have completed before proceeding.
TEST statements monitor SQLCODE to determine when both req_1 and req_2 have returned successful completion codes (SQLCODE = 0) before continuing with the rest of the main program.
If either request has not completed (SQLCODE = -651), then the wait continues.
When both statements have completed, then the main program continues processing.
The non-SQL statements are pseudocode to indicate crudely a general idea of how the WAIT and TEST SQL statements might fit into a host main program.
... EXEC-SQL ASYNC req_1 UPDATE table_a SET a = :a; EXEC-SQL ASYNC req_2 UPDATE table_b SET b = :b; ... 100 EXEC-SQL WAIT req_1, req_2 COMPLETION; ... EXEC-SQL TEST req_1 COMPLETION; IF SQLCODE = -651 THEN GOTO 100 IF SQLCODE = 0 THEN CONTINUE EXEC-SQL TEST req_2 COMPLETION; IF SQLCODE = -651 THEN GOTO 100 IF SQLCODE = 0 THEN CONTINUE ...
Related Topics
- See ASYNC Statement Modifier for information about how to submit an asynchronous request.
- See WAIT for information about how to wait on and test the status of an asynchronous request.