Following is a sample program demonstrating the use of an input SQLDA structure. The client application invokes a stored procedure that creates a table named Presidents and loads the table with data.
This program creates a table called Presidents in the SAMPLE database. It then inserts the values Washington, Jefferson, and Lincoln into the table.
Without using stored procedures, the sample program would have been designed to transmit data across the network in four separate requests in order to process each SQL statement, as shown in Figure 23.
Figure 23. Input-SQLDA Sample Without a Stored Procedure
|
Instead, the sample program makes use of the stored procedures technique to transmit all of the data across the network in one request, allowing the server procedure to execute the SQL statements as a group. This technique is shown in Figure 24.
Figure 24. Input-SQLDA Sample With a Stored Procedure
 |
A sample input-SQLDA client application and sample input-SQLDA stored procedure is shown on "How the Example Input-SQLDA Client Application Works".
The CHECKERR macro/function is an error checking utility which is external to the program. The location of this error checking utility depends upon the programming language used:
See Using GET ERROR MESSAGE in Example Programs for the source code for this error checking utility.
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlca.h>
#include <sqlda.h>
#include <sqlutil.h>
#include "util.h"
#define CHECKERR(CE_STR) if (check_error (CE_STR, &sqlca) != 0) return 1;
int main(int argc, char *argv[]) {
EXEC SQL BEGIN DECLARE SECTION;
char database[9];
char userid[9];
char passwd[19];
char procname[255] = "inpsrv";
char table_name[11] = "PRESIDENTS";
char data_item0[21] = "Washington";
char data_item1[21] = "Jefferson";
char data_item2[21] = "Lincoln";
short tableind, dataind0, dataind1, dataind2;
EXEC SQL END DECLARE SECTION;
/* Declare Variables for CALL USING */
struct sqlca sqlca;
struct sqlda *inout_sqlda = NULL;
char eBuffer[1024]; /* error message buffer */
if (argc != 4) {
printf ("\nUSAGE: inpcli remote_database userid passwd\n\n");
return 1;
}
strcpy (database, argv[1]);
strcpy (userid, argv[2]);
strcpy (passwd, argv[3]);
/* Connect to Remote Database */
printf("CONNECT TO Remote Database.\n");
EXEC SQL CONNECT TO :database USER :userid USING :passwd;
CHECKERR ("CONNECT TO SAMPLE");
/********************************************************\
* Call the Remote Procedure via CALL with Host Variables *
\********************************************************/
printf("Use CALL with Host Variable to invoke the Server Procedure"
" named inpsrv.\n");
tableind = dataind0 = dataind1 = dataind2 = 0;
EXEC SQL CALL :procname (:table_name:tableind, :data_item0:dataind0,
:data_item1:dataind1, :data_item2:dataind2); (2a)
/* COMMIT or ROLLBACK the transaction */
if (SQLCODE == 0)
{ EXEC SQL COMMIT;
printf("Server Procedure Complete.\n\n");
}
else
{ /* print the error message, roll back the transaction and return */
sqlaintp (eBuffer, 1024, 80, &sqlca);
printf("\n%s\n", eBuffer);
EXEC SQL ROLLBACK;
printf("Server Procedure Transaction Rolled Back.\n\n");
return 1;
}
/* Allocate and Initialize Input SQLDA */ (1)
inout_sqlda = (struct sqlda *)malloc( SQLDASIZE(4) );
inout_sqlda->sqln = 4;
inout_sqlda->sqld = 4;
inout_sqlda->sqlvar[0].sqltype = SQL_TYP_NCSTR;
inout_sqlda->sqlvar[0].sqldata = table_name;
inout_sqlda->sqlvar[0].sqllen = strlen( table_name ) + 1;
inout_sqlda->sqlvar[0].sqlind = &tableind;
inout_sqlda->sqlvar[1].sqltype = SQL_TYP_NCSTR;
inout_sqlda->sqlvar[1].sqldata = data_item0;
inout_sqlda->sqlvar[1].sqllen = strlen( data_item0 ) + 1;
inout_sqlda->sqlvar[1].sqlind = &dataind0;
inout_sqlda->sqlvar[2].sqltype = SQL_TYP_NCSTR;
inout_sqlda->sqlvar[2].sqldata = data_item1;
inout_sqlda->sqlvar[2].sqllen = strlen( data_item1 ) + 1;
inout_sqlda->sqlvar[2].sqlind = &dataind1;
inout_sqlda->sqlvar[3].sqltype = SQL_TYP_NCSTR;
inout_sqlda->sqlvar[3].sqldata = data_item2;
inout_sqlda->sqlvar[3].sqllen = strlen( data_item2 ) + 1;
inout_sqlda->sqlvar[3].sqlind = &dataind2;
/***********************************************\
* Call the Remote Procedure via CALL with SQLDA *
\***********************************************/
printf("Use CALL with SQLDA to invoke the Server Procedure named "
"inpsrv.\n");
tableind = dataind0 = dataind1 = dataind2 = 0;
inout_sqlda->sqlvar[0].sqlind = &tableind;
inout_sqlda->sqlvar[1].sqlind = &dataind0;
inout_sqlda->sqlvar[2].sqlind = &dataind1;
inout_sqlda->sqlvar[3].sqlind = &dataind2;
EXEC SQL CALL :procname USING DESCRIPTOR :*inout_sqlda; (2b)
/* COMMIT or ROLLBACK the transaction */
if (SQLCODE == 0)
{ EXEC SQL COMMIT;
printf("Server Procedure Complete.\n\n");
}
else
{ /* print the error message, roll back the transaction and return */
sqlaintp (eBuffer, 1024, 80, &sqlca);
printf("\n%s\n", eBuffer);
EXEC SQL ROLLBACK;
printf("Server Procedure Transaction Rolled Back.\n\n");
return 1;
}
/* Free allocated memory */
free( inout_sqlda );
/* Drop the PRESIDENTS table created by the stored procedure */
EXEC SQL DROP TABLE PRESIDENTS;
CHECKERR("DROP TABLE");
/* Disconnect from Remote Database */
EXEC SQL CONNECT RESET;
CHECKERR ("CONNECT RESET");
return 0;
}
/* end of program : inpcli.sqc */
| Note: | Server procedures cannot be written in REXX on AIX systems. |
#include <memory.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlutil.h>
#ifdef __cplusplus
extern "C"
#endif
SQL_API_RC SQL_API_FN inpsrv(void *reserved1, (1)
void *reserved2,
struct sqlda *inout_sqlda,
struct sqlca *ca)
{
/* Declare a local SQLCA */
EXEC SQL INCLUDE SQLCA;
/* Declare Host Variables */
EXEC SQL BEGIN DECLARE SECTION;
char table_stmt[80] = "CREATE TABLE ";
char insert_stmt[80] = "INSERT INTO ";
char insert_data[21];
EXEC SQL END DECLARE SECTION;
/* Declare Miscellanous Variables */
int cntr = 0;
char *table_name;
char *data_items[3];
short data_items_length[3];
int num_of_data = 0;
/*-----------------------------------------------------------------*/
/* Assign the data from the SQLDA to local variables so that we */
/* don't have to refer to the SQLDA structure further. This will */
/* provide better portability to other platforms such as DB2 MVS */
/* where they receive the parameter list differently. */
/*-----------------------------------------------------------------*/
table_name = inout_sqlda->sqlvar[0].sqldata;
num_of_data = inout_sqlda->sqld - 1;
for (cntr = 0; cntr < num_of_data; cntr++)
{
data_items[cntr] = inout_sqlda->sqlvar[cntr+1].sqldata;
data_items_length[cntr] = inout_sqlda->sqlvar[cntr+1].sqllen;
}
/*-----------------------------------------------------------------*/
/* Create President Table */
/* - For simplicity, we'll ignore any errors from the */
/* CREATE TABLE so that you can run this program even when the */
/* table already exists due to a previous run. */
/*-----------------------------------------------------------------*/
EXEC SQL WHENEVER SQLERROR CONTINUE;
strcat(table_stmt, table_name);
strcat(table_stmt, " (name CHAR(20))"); (2)
EXEC SQL EXECUTE IMMEDIATE :table_stmt;
EXEC SQL WHENEVER SQLERROR GOTO ext;
/*-----------------------------------------------------------------*/
/* Generate and execute a PREPARE for an INSERT statement, and */
/* then insert the three presidents. */
/*-----------------------------------------------------------------*/
strcat(insert_stmt, table_name );
strcat(insert_stmt, " VALUES (?)"); (3)
EXEC SQL PREPARE S1 FROM :insert_stmt;
for (cntr = 0; cntr < num_of_data; cntr++)
{
strncpy(insert_data, data_items[cntr], data_items_length[cntr]);
insert_data[data_items_length[cntr]] = '\0';
EXEC SQL EXECUTE S1 USING :insert_data; (4)
}
/*-----------------------------------------------------------------*/
/* Return to caller */
/* - Copy the SQLCA */
/* - Update the output SQLDA. Since there's no output to */
/* return, we are setting the indicator values to -128 to */
/* return only a null value. */
/*-----------------------------------------------------------------*/
ext: (5)
memcpy(ca, &sqlca, sizeof(struct sqlca));
if (inout_sqlda != NULL)
{
for (cntr = 0; cntr < inout_sqlda->sqld; cntr++)
{
*(inout_sqlda->sqlvar[cntr].sqlind) = -128;
}
}
return(SQLZ_DISCONNECT_PROC);
}