Using the embedded CALL statement with an SQLDA

Edit online

In either type of embedded CALL statement (where a procedure definition might or might not exist), an SQLDA or an SQL descriptor, rather than a parameter list, can be passed.

The following C examples illustrates executing a CALL statement using an SQLDA structure. Assume that the stored procedure is expecting 2 parameters, the first of type SHORT INT and the second of type CHAR with a length of 4.

Note: By using the code examples, you agree to the terms of the Code license and disclaimer information.

#define SQLDA_HV_ENTRIES 2
#define SHORTINT 500
#define NUL_TERM_CHAR 460

exec sql include sqlca;
exec sql include sqlda;
...
typedef struct sqlda Sqlda;
typedef struct sqlda* Sqldap;
...
main()
{
 Sqldap dap;
 short col1;
 char col2[4];
 int bc;
 dap = (Sqldap) malloc(bc=SQLDASIZE(SQLDA_HV_ENTRIES));
        /* SQLDASIZE is a macro defined in the sqlda include */
 col1 = 431;
 strcpy(col2,"abc");
 strncpy(dap->sqldaid,"SQLDA   ",8);
 dap->sqldabc = bc;         /* bc set in the malloc statement above */
 dap->sqln = SQLDA_HV_ENTRIES;
 dap->sqld = SQLDA_HV_ENTRIES;
 dap->sqlvar[0].sqltype = SHORTINT;
 dap->sqlvar[0].sqllen  = 2;
 dap->sqlvar[0].sqldata = (char*) &col1;
 dap->sqlvar[0].sqlname.length = 0;
 dap->sqlvar[1].sqltype = NUL_TERM_CHAR;
 dap->sqlvar[1].sqllen  = 4;
 dap->sqlvar[1].sqldata = col2;
 ...
 EXEC SQL CALL P1 USING DESCRIPTOR :*dap;
 ...
}

The name of the called procedure may also be stored in a host variable and the host variable used in the CALL statement, instead of the hard-coded procedure name. For example:

...
main()
{
 char proc_name[15];
 ...
 strcpy (proc_name, "MYLIB.P3");
 ...
 EXEC SQL CALL :proc_name ...;
 ...
}

In the above example, if MYLIB.P3 is expecting parameters, either a parameter list or an SQLDA passed with the USING DESCRIPTOR clause may be used, as shown in the previous example.

When a host variable containing the procedure name is used in the CALL statement and a CREATE PROCEDURE catalog definition exists, it will be used. The procedure name cannot be specified as a parameter marker.