Ok, since I failed miserably in simplifying my problem, I'll just post
as much of it as I can to keep this message under 40KB.
Here's the stored procedure that I'm dealing with:
CREATE DBPROC search_results(IN search_type FIXED(1),
IN search_string VARCHAR(60),
IN i_id FIXED(10),
OUT i_related1 FIXED(10), ..., OUT i_related5 FIXED(10),
OUT i_thumbnail1 FIXED(10), ..., OUT i_thumbnail5 FIXED(10),
OUT items FIXED(2),
OUT i_id1 FIXED(10), OUT i_title1 VARCHAR(60),
OUT a_fname1 VARCHAR(20), OUT a_lname1 VARCHAR(20),
...
OUT i_id50 FIXED(10), OUT i_title50 VARCHAR(60),
OUT a_fname50 VARCHAR(20), OUT a_lname50 VARCHAR(20)) AS
BEGIN
SET i_id1 = 0;
SET i_title1 = '';
SET a_fname1 = '';
SET a_lname1 = '';
...
SET i_id50 = 0;
SET i_title50 = '';
SET a_fname50 = '';
SET a_lname50 = '';
CALL GetPromoImages(:i_id,
:i_related1, :i_thumbnail1,
:i_related2, :i_thumbnail2,
:i_related3, :i_thumbnail3,
:i_related4, :i_thumbnail4,
:i_related5, :i_thumbnail5);
SELECT i_id, i_title, a_fname, a_lname
FROM tpcw.item, tpcw.author
WHERE i_subject = :search_string
AND i_a_id = a_id
ORDER BY i_title ASC;
SET items = 0;
IF $rc = 0 THEN
BEGIN
FETCH INTO :i_id1, :i_title1, :a_fname1, :a_lname1;
END;
IF $rc = 0 THEN
BEGIN
FETCH INTO :i_id2, :i_title2, :a_fname2, :a_lname2;
SET items = items + 1;
END;
...
IF $rc = 0 THEN
BEGIN
FETCH INTO :i_id50, :i_title50, :a_fname50, :a_lname50;
SET items = items + 1;
END;
END;
Here is the code I use to bind and execute:
odbcd->search_results_odbc_data.search_type = 1;
strcpy(odbcd->search_results_odbc_data.search_string, "ARTS");
i = 1;
rc = SQLBindParameter(odbcc->hstmt,
i++, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0,
&odbcd->search_results_odbc_data.search_type, 0, NULL);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
odbc_error(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
rc = SQLBindParameter(odbcc->hstmt,
i++, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, 0, 0,
odbcd->search_results_odbc_data.search_string,
sizeof(odbcd->search_results_odbc_data.search_string), NULL);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
odbc_error(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
rc = SQLBindParameter(odbcc->hstmt,
i++, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER, 0, 0,
&odbcd->search_results_odbc_data.pp_data.i_id, 0, NULL);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
odbc_error(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
for (j = 0; j < PROMOTIONAL_ITEMS_MAX; j++)
{
rc = SQLBindParameter(odbcc->hstmt,
i++, SQL_PARAM_OUTPUT, SQL_C_ULONG, SQL_INTEGER, 0, 0,
&odbcd->search_results_odbc_data.pp_data.i_related[j], 0,
NULL);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
odbc_error(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
rc = SQLBindParameter(odbcc->hstmt,
i++, SQL_PARAM_OUTPUT, SQL_C_ULONG, SQL_INTEGER, 0, 0,
&odbcd->search_results_odbc_data.pp_data.i_thumbnail[j], 0,
NULL);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
odbc_error(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
}
rc = SQLBindParameter(odbcc->hstmt,
i++, SQL_PARAM_OUTPUT, SQL_C_LONG, SQL_INTEGER,
0, 0, &odbcd->search_results_odbc_data.items, 0, NULL);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
odbc_error(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
for (j = 0; j < SEARCH_RESULT_ITEMS_MAX; j++)
{
rc = SQLBindParameter(odbcc->hstmt,
i++, SQL_PARAM_OUTPUT, SQL_C_ULONG, SQL_INTEGER, 0, 0,
&odbcd->search_results_odbc_data.results_data[j].i_id,
0, NULL);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
odbc_error(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
rc = SQLBindParameter(odbcc->hstmt,
i++, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_VARCHAR, 0, 0,
odbcd->search_results_odbc_data.results_data[j].i_title,
sizeof(odbcd->search_results_odbc_data.results_data[j].i_title),
NULL);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
odbc_error(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
rc = SQLBindParameter(odbcc->hstmt,
i++, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_VARCHAR, 0, 0,
odbcd->search_results_odbc_data.results_data[j].a_fname,
sizeof(odbcd->search_results_odbc_data.results_data[j].a_fname),
NULL);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
odbc_error(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
rc = SQLBindParameter(odbcc->hstmt,
i++, SQL_PARAM_OUTPUT, SQL_C_CHAR, SQL_VARCHAR, 0, 0,
odbcd->search_results_odbc_data.results_data[j].a_lname,
sizeof(odbcd->search_results_odbc_data.results_data[j].a_lname),
NULL);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
odbc_error(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
}
/* Generate random number for Promotional Processing. */
odbcd->search_results_odbc_data.pp_data.i_id =
(UDWORD) get_random((long long) item_count) + 1;
/* Execute stored procedure. */
rc = SQLExecute(odbcc->hstmt);
if (rc != SQL_SUCCESS && rc != SQL_SUCCESS_WITH_INFO)
{
odbc_error(SQL_HANDLE_STMT, odbcc->hstmt);
return W_ERROR;
}
Here's the statement:
#define STMT_NEW_PRODUCTS \
"CALL new_products(?, ?, " \
"?, ?, ?, ?, ?, "\
"?, ?, ?, ?, ?, "\
"?, "\
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?, " \
"?, ?, ?, ?)"
So this is what I see when I try to execute the code:
SQLSTATE 22003
[SAP AG][LIBSQLOD SO]Numeric value out of range.
I can get around this problem by chaging the first parameter, "IN
search_type FIXED(1)", to "IN search_type FIXED(10)".
The following problem I see is when I removed the SELECT statement and
the subsequent if-statements from the stored procedure and changed the
line "SET items = 0;" to "SET items = search_type;" The returning value
for items is "search_type + 1". I have checked it for incoming values
of 0, 1, and 2.
I was unsuccessful in trying to reproduce this bahavior with a smaller
stored procedure (as some of you may have noticed) and it makes me
paranoid in thinking this may have something to do with the length of
it, but I do presume this does mostly work since I get the expected
results, with the exception of having my first parameter be "IN
search_type FIXED(1)" and retain its incoming value.
Thanks,
Mark
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
