In general, a C or C++ external stored procedure that executes SQL follows standard CLI application programming practices. For more information on using CLIv2 to communicate with the database, see Teradata® Call-Level Interface Version 2 Reference for Workstation-Attached Systems, B035-2418.
Here are the basic steps for defining C or C++ external stored procedures that use CLIv2 to directly execute SQL. For complete code examples, see External Stored Procedure Code Examples.
| Step | Action | Code Example |
|---|---|---|
| 1 | Include the CLIv2 header files after the sqltypes_td.h header file. | #define SQL_TEXT Latin_Text #include <sqltypes_td.h> #include <string.h> #include <stdio.h> #include <wchar.h> #include <coptypes.h> #include <coperr.h> #include <parcel.h> #include <dbcarea.h> ... |
| 2 | Declare the function, following the parameter passing convention you specify in the CREATE PROCEDURE or REPLACE PROCEDURE statement. | void ET001_xsp1( VARCHAR_LATIN *A_Name,
VARCHAR_LATIN *B_Result,
char sqlstate[6])
{
...
|
| 3 | Initialize the DBCAREA. | ...
DBCAREA dbcarea;
Int32 result;
char cntxt[4];
char str1[200];
dbcarea.total_len = sizeof(struct DBCAREA);
DBCHINI(&result, cntxt, &dbcarea);
...
|
| 4 | Establish a default connection by changing the Create Default Connection option to 'Y'. This step is different from standard CLI application programming. The default connection sends SQL to the session that the external stored procedure is running in. |
... dbcarea.change_opts = 'Y'; dbcarea.use_presence_bits = 'N'; dbcarea.keep_resp = 'N'; dbcarea.loc_mode = 'Y'; dbcarea.var_len_req = 'N'; dbcarea.save_resp_buf = 'N'; dbcarea.two_resp_bufs = 'N'; dbcarea.ret_time = 'N'; dbcarea.wait_for_resp = 'Y'; dbcarea.req_proc_opt = 'E'; dbcarea.req_buf_len = 1024; dbcarea.resp_buf_len = 1024; dbcarea.data_encryption = 'N'; dbcarea.create_default_connection = 'Y'; dbcarea.func = DBFCON; DBCHCL(&result, cntxt, &dbcarea); ... |
| 5 | Initiate the SQL request, fetch the response, and end the request. (Repeat this step as needed.) |
... memset(str1, ' ', 100); sprintf(str1, "DELETE USER %s;", A_Name); dbcarea.func = DBFIRQ; dbcarea.req_ptr = str1; dbcarea.req_len = strlen(str1); DBCHCL(&result, cntxt, &dbcarea); if (result != EM_OK) { strcpy(sqlstate, "U0007"); return; } ... dbcarea.func = DBFFET; DBCHCL(&result, cntxt, &dbcarea); if (result != EM_OK) { strcpy(sqlstate, "U0008"); return; } ... dbcarea.func = DBFERQ; DBCHCL(&result, cntxt, &dbcarea); if (result != EM_OK) { strcpy(sqlstate, "U0009"); return; } ... |
| 6 | Set any INOUT or OUT arguments. Perform cleanup, including releasing allocated memory and closing any connections to other systems. Return. |
... strcpy((char *)B_Result, "User deleted."); } |