Can we use arrays to send a list from a cli app ( to stored proc)? I
was able to find an example in java and it works fine, but I am
looking for declaration in cli.

On 8/26/2010 7:23 AM, span3d wrote:
the skimpy docs.

Here's a quick example:

CREATE TYPE VARCHAR20_ARR AS VARCHAR(20) ARRAY[10] %

CREATE PROCEDURE ARRAY_RETRIEVE
(
IN LIST_OF_FIRST_NAMES VARCHAR20_ARR,
OUT LIST_LEN INTEGER,
OUT LAST_NAME_ARRAY VARCHAR20_ARR)

READS SQL DATA
DETERMINISTIC
LANGUAGE SQL

VAR: BEGIN

SELECT ARRAY_AGG(LAST_NAME), COUNT(LAST_NAME)
INTO LAST_NAME_ARRAY, LIST_LEN
FROM (SELECT LAST_NAME FROM EMPLOYEES,
UNNEST(LIST_OF_FIRST_NAMES) AS FNAME_LIST(FNAME)
WHERE FIRST_NAME=FNAME_LIST.FNAME);

COMMIT WORK ;

END
%


SQLHANDLE hdbc; /* connection handle */
SQLHSTMT stmt; /* statement handle*/
rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &stmt);
int i=0;
char first_name[10][20];
char last_name[10][20];
int list_len=0;
SQLHANDLE hIPD;
SQLHANDLE hAPD;
int size= 10;
int returned_size;
//Bind input/output parameters
if (SQLBindParameter(stmt, ++i, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_VARCHAR, 20, 0, first_name, 20, NULL)==SQL_SUCCESS

if( SQLGetStmtAttr(stmt,
SQL_ATTR_IMP_PARAM_DESC,&hIPD,SQL_IS_INTEGER,NULL) ==SQL_SUCCESS &&
SQLGetStmtAttr(stmt, SQL_ATTR_APP_PARAM_DESC,&hAPD,SQL_IS_INTEGER,NULL)
== SQL_SUCCESS{

if(SQLSetDescField(hIPD,1, SQL_DESC_CARDINALITY, &size,
SQL_IS_INTEGER==SQL_SUCCESS &&
SQLSetDescField(hAPD,3, SQL_DESC_CARDINALITY, &size,
SQL_IS_INTEGER==SQL_SUCCESS &&
SQLSetDescField(hAPD,3, SQL_DESC_CARDINALITY_PTR, &returned_size,
SQL_IS_INTEGER==SQL_SUCCESS){

if (SQLPrepare(stmt, (SQLCHAR*)"CALL ARRAY_RETRIEVE(?, ?, ?)",
SQL_NTS)==SQL_SUCCESS){

rc = SQLExecute(stmt);
}
}
}
}
rc = SQLFreeHandle(SQL_HANDLE_STMT,stmt);


Cheers
Serge
--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab