Jeff wrote:
"Raquel" <ra****@nospam.com> wrote in message
news:<85******************************@localhost.t alkaboutdatabases.com>...
Thanks Knut. You are the co-author of the above very useful article!!
Wow!! So, at last I find a feature that is readily available in BIG DB2
(DB2 for OS/390) and not in small DB2 (aka UDB for LUW).
Raquel.
Knut, yes this article is very helpful. We have implemented it on both
V8 and V7. The V8 version works just as intended. The only thing we
have trouble with is on the TRUNCATE stored procedure on V7. When an
invalid table name is entered, we get the following error:
SQL1131N DARI (Stored Procedure) process has been terminated
abnormally. SQLST
ATE=38503
Anything we can do to fix this problem in V7? We often get calls on
this abend which is only caused when a non-existent table name is
entered. I have no C++ skills
Found the problem... The PARAMETER STYLE SQL that is available in V8 expects
a different set of parameters than the PARAMETER STYLE DB2SQL that you
(most probably) used in the V7 procedures. For the V7 procedures, you need
to provide an array (or pointer) for the null indicators and not separate
arguments. The separate arguments result in too many parameters expected
on the stack (which aren't provided by DB2) so that the last one, which
happens to be the error message text is an invalid parameter. When it is
dereferenced to write the error message, you get a segmentation violation
(signal 11). DB2 detects that and signals the abend.
Ok, so much for the problem description... The fix is not difficult. Just
change the function code as shown below. I marked the corrections
everywhere - please remove the marks before using this.
#if defined(__cplusplus)
extern "C"
#endif
SQL_API_RC SQL_API_FN truncate_table(
/* fully qualified table name */
SQLUDF_VARCHAR *schemaName, SQLUDF_VARCHAR *tableName,
/* null indicators */
!! here we need a single parameter for the NULL indicator array
SQLUDF_NULLIND nulls[2],
SQLUDF_TRAIL_ARGS)
{
int rc = 0;
struct sqldcol dataDescriptor;
unsigned char actionBuffer[sizeof(struct sqlchar) + 20+130+1+130+1];
struct sqlchar *pAction = (struct sqlchar *)actionBuffer;
struct sqluimpt_in inputInfo;
struct sqluimpt_out outputInfo;
!! we define the macros to make the code more readable when dealing with the
!! null indicators
#define SCHEMA_NAME_IND 0
#define TABLE_NAME_IND 1
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
char hSchemaName[129] = { '\0' };
char hTableName[129] = { '\0' };
sqlint32 count = 0;
EXEC SQL END DECLARE SECTION;
/* get default schema name if parameter was NULL */
!! change the test for a NULL in the schema name
if (SQLUDF_NULL(&nulls[SCHEMA_NAME_IND])) {
EXEC SQL
VALUES CURRENT SCHEMA
INTO :hSchemaName;
if (SQLCODE) {
memcpy(SQLUDF_STATE, sqlca.sqlstate, SQLUDF_SQLSTATE_LEN);
memcpy(SQLUDF_MSGTX, sqlca.sqlerrmc, sqlca.sqlerrml);
goto cleanup;
}
}
else {
/* unquote given schema name */
rc = UnquoteName(schemaName, hSchemaName, sizeof hSchemaName);
if (rc) {
char errMsg[131 + 50];
memcpy(SQLUDF_STATE, "38000", SQLUDF_SQLSTATE_LEN);
sprintf(errMsg, "Invalid schema name '%s'.", schemaName);
memcpy(SQLUDF_MSGTX, errMsg, SQLUDF_MSGTEXT_LEN);
goto cleanup;
}
}
/* check and unquote table name */
!! change the test for a NULL in the table name
if (SQLUDF_NULL(&nulls[TABLE_NAME_IND])) {
memcpy(SQLUDF_STATE, "38001", SQLUDF_SQLSTATE_LEN);
strcpy(SQLUDF_MSGTX, "No table name specified.");
goto cleanup;
}
Everything following the above snippet can remain as before.
p.s: I'll take care of updating the article.
--
Knut Stolze
Information Integration
IBM Germany / University of Jena
