OUT and INOUT parameters

HCL OneDB™ Client Software Development Kit supports the use of OUT and INOUT parameters during execution of SPL.

The following data types are supported:

BIGINT
BLOB
BOOLEAN
DATETIME
CHAR
CLOB
DECIMAL
FLOAT
INT8
INTEGER
INTERVAL
LVARCHAR
MONEY
NCHAR
NVARCHAR
SMALLFLOAT
SMALLINT
VARCHAR

These restrictions exist when using OUT or INOUT parameters in SPL execution:

Collection data types such as LIST, MULTISET, ROW, and SET are not supported.
Returning result sets is not supported. After executing SPL with OUT or INOUT parameters, you cannot call SQLFetch or SQLGetData.
Only one value can be returned; that is, only one set of OUT or INOUT parameters can be returned per individual SPL execution.
The following SPL execution example creates one OUT, one INOUT, and one IN (default) parameter and one return value.
```
create procedure myproc(OUT intparam INT, INOUT charparam char(20), 
  inparam int) returns int
<body of SPL>
end procedure;
```

The following code example, outinoutparamblob.c, shows how to use OUT and INOUT parameters with BLOB, INTEGER, and VARCHAR data types.

 /* Drop procedure */
    SQLExecDirect(hstmt, (UCHAR *)"drop procedure spl_out_param_blob;", SQL_NTS);
	SQLExecDirect(hstmt, (UCHAR *)"drop table tab_blob;", SQL_NTS);
	
	/* Create table with BLOB column */
	rc = SQLExecDirect(hstmt, (UCHAR *)"create table tab_blob(c_blob BLOB,
    c_int INTEGER, c_char varchar(20));", SQL_NTS);
    if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error in Step 2 -- 
    SQLExecDirect failed\n"))
		goto Exit;

	/* Insert one row into the table */
	rc = SQLExecDirect(hstmt, (UCHAR *)"insert into tab_blob 
  values(filetoblob('insert.data', 'c'), 10, 'blob_test');", SQL_NTS);
    if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error in Step 2 
    -- SQLExecDirect failed\n"))
		goto Exit;

	/* Create procedure */
	rc = SQLExecDirect(hstmt, "CREATE PROCEDURE spl_out_param_blob(inParam int, 
   OUT blobparam BLOB, OUT intparam int, OUT charparam varchar(20)) \n"
		                      "returning integer; \n"
                              "select c_blob, c_int, c_char into blobparam, 
                              intparam, charparam from tab_blob; \n"
                              "return inParam; \n"
                              "end procedure; ",
                              SQL_NTS);
    if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) "Error in Step 2 
    -- SQLExecDirect failed\n"))
		goto Exit;

	/* Prepare stored procedure to be executed */
    rc = SQLPrepare(hstmt, (UCHAR *)"{? = call spl_out_param_blob
    (?, ?, ?, ?)}", SQL_NTS);
    if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) 
    "Error in Step 2 -- SQLPrepare failed\n"))
		goto Exit;

	/* Bind the required parameters */
    rc = SQLBindParameter(hstmt, 1, SQL_PARAM_OUTPUT, SQL_C_LONG, 
    SQL_INTEGER, 3, 0, &sParm1, 0, &cbParm1);
    if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) 
    "Error in Step 2 -- SQLBindParameter 1 failed\n"))
 		goto Exit;

     rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, 
     SQL_INTEGER, 10, 0, &sParm2, 0, &cbParm2);
     if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) 
     "Error in Step 2 -- SQLBindParameter 2 failed\n"))
 		goto Exit;      

		rc = SQLBindParameter(hstmt, 3, SQL_PARAM_OUTPUT, SQL_C_BINARY, 
     SQL_LONGVARBINARY, sizeof(blob_buffer), 0, blob_buffer, 
     sizeof(blob_buffer), &cbParm3);
     if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) 
     "Error in Step 2 -- SQLBindParameter 3 failed\n"))
 		goto Exit;

      rc = SQLBindParameter(hstmt, 4, SQL_PARAM_OUTPUT, SQL_C_LONG, 
      SQL_INTEGER, 10, 0, &sParm3, 0, &cbParm4);
     if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) 
     "Error in Step 2 -- SQLBindParameter 4 failed\n"))
   		goto Exit;

      rc = SQLBindParameter (hstmt, 5, SQL_PARAM_OUTPUT, SQL_C_CHAR,
       SQL_VARCHAR, sizeof(schar), 0, schar, sizeof(schar), &cbParm6);
      if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) 
      "Error in Step 2 -- SQLBindParameter 5 failed\n"))
   		goto Exit;  	

			/* Exeute the prepared stored procedure */     
			rc = SQLExecute(hstmt);     
			if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) 
       "Error in Step 2 -- SQLExecute failed\n"))
      		goto Exit;

         	len = 
       strlen("123456789abcdefghijklmnopqrstuvwxyz
            1234567890123456789012345678901234567890 ");

       	if( (sParm2 != sParm1) || (10 != sParm3) || 
         (strcmp("blob_test", schar)) || (cbParm3 != len) ) 	
         { 		
             fprintf(stdout, "\n 1st Data compare failed!");
             		goto Exit; 
        	}
     else     
     {
         		fprintf(stdout, "\n 1st Data compare successful");     
         }

        	/* Reset the parameters */
        rc = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
        if (checkError (rc, SQL_HANDLE_STMT, hstmt, (SQLCHAR *) 
        "Error in Step 3 -- SQLFreeStmt failed\n"))
     		goto Exit;

       /* Reset variables */
       sParm1 = 0;
            	cbParm6 = cbParm1 = SQL_NTS;
       cbParm3 = SQL_NULL_DATA;
       schar[0]=0;
           	blob_buffer[0]=0;