Procedure to Execute SQL in C/C++ External Stored Procedures | Teradata Vantage - 17.10 - Overall Procedure - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1147-171K
Language
English (United States)

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.");

}