Hi,

I have written an ESQL/C program and should any error occur; the same
should be displayed as error message. Wrote a test script and expected
an exception but DB2 did not throw any such exception rather
displayed return = 0

Below is the snippet for ESQL/C

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <errno.h>

#include <sqludf.h>
#include <sqlca.h>
#include <sqlda.h>

.....
#define FILE_ISDIRECTORY "38921"
.....

SQL_API_RC SQL_API_FN
write_line (
SQLUDF_VARCHAR *file_name
, SQLUDF_VARCHAR *file_open_mode
, SQLUDF_VARCHAR *subject_matter
, SQLUDF_CHAR *sqludf_sqlstate
, SQLUDF_VARCHAR *routine_name
, SQLUDF_VARCHAR *specific_name
, SQLUDF_VARCHAR *sqludf_msgtext
)
{
.....

file_pointer = fopen( file_name, file_open_mode );

if( file_pointer == NULL )
{
switch( errno )
{
case EISDIR :
strcpy( sqludf_sqlstate, FILE_ISDIRECTORY );
strcpy( sqludf_msgtext, strerror( errno ) );
break;
.....
}
}
}

Above was compiled sucessfully.

Below is the CREATE PROCEDURE SNIPPET

DROP procedure utils.WRITE_LINE@
CREATE PROCEDURE utils.WRITE_LINE(IN FILENAME VARCHAR(50),
IN FILEMODE CHAR(1),
IN PRINTLN VARCHAR(1000))
DYNAMIC RESULT SETS 0
LANGUAGE C
PARAMETER STYLE DB2SQL
NO DBINFO
FENCED
MODIFIES SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'utlfile!write_line'@


----- Test run

db2 "call utils.write_line( '/tmp', 'w', 'discover db2!!!' ) "

Return Status = 0

instead should report exception with
sqlstate as : 38921
sqlmsg as : 'Is a directory'


Correct call is first argument with filename with/without directory
name
rather not directory name.

for eg.,
db2 "call utils.write_line( '/tmp/file.txt', 'w', 'discover db2!!!' )
"


--- Wrote a db2 procedure driver declaring EXCEPTION HANDLERS
to call utils.write_line but in vain.


Any insight, Idea, Please let me know. Thanks. Should there be any
links
please let me know.

Thanks in advance and Regards,
Dev

N.V.Dev <devnv@yahoo.com> wrote:

> Hi,
>
> I have written an ESQL/C program and should any error occur; the same
> should be displayed as error message. Wrote a test script and expected
> an exception but DB2 did not throw any such exception rather
> displayed return = 0
>
> Below is the snippet for ESQL/C
>
> #include <stdio.h>
> #include <string.h>
> #include <stdlib.h>
> #include <errno.h>
>
> #include <sqludf.h>
> #include <sqlca.h>
> #include <sqlda.h>
>
> .....
> #define FILE_ISDIRECTORY "38921"
> .....
>
> SQL_API_RC SQL_API_FN
> write_line (
> SQLUDF_VARCHAR *file_name
> , SQLUDF_VARCHAR *file_open_mode
> , SQLUDF_VARCHAR *subject_matter

I recommend to use the macro SQLUDF_TRAIL_ARGS instead of the parameters
below.

> , SQLUDF_CHAR *sqludf_sqlstate
> , SQLUDF_VARCHAR *routine_name
> , SQLUDF_VARCHAR *specific_name
> , SQLUDF_VARCHAR *sqludf_msgtext
> )
> {
> .....
>
> file_pointer = fopen( file_name, file_open_mode );
>
> if( file_pointer == NULL )
> {
> switch( errno )
> {
> case EISDIR :
> strcpy( sqludf_sqlstate, FILE_ISDIRECTORY );
> strcpy( sqludf_msgtext, strerror( errno ) );

You should really use "strncpy" to prevent buffer overflows in case that the
string returned by strerror() is too long.

> break;
> .....
> }
> }
> }
>
> Above was compiled sucessfully.
>
> Below is the CREATE PROCEDURE SNIPPET
>
> DROP procedure utils.WRITE_LINE@
> CREATE PROCEDURE utils.WRITE_LINE(IN FILENAME VARCHAR(50),
> IN FILEMODE CHAR(1),
> IN PRINTLN VARCHAR(1000))
> DYNAMIC RESULT SETS 0
> LANGUAGE C
> PARAMETER STYLE DB2SQL

That, combined with the parameter list above is the problem. The parameter
style DB2SQL requires the presence of a vector for all the null indicators,
i.e. "SQLUDF_SMALLINT *nullind[3]" in your case.

Because you did not specify this parameter, you are writing the sqlstate to
what would be the null indicators, and the message text to the specific
name parameter. Luckily, the first does work without problems, but the
second will easily produce a buffer overflow. (And you are lucky that the
whole thing didn't simply crash. DB2 did not jump at the exact beginning
of the function, given that your stack is too small.)

> NO DBINFO
> FENCED

I would also define the procedure as NOT THREADSAFE because I don't rely on
the OS to handle the errno on a thread-specific manner.

> MODIFIES SQL DATA
> PROGRAM TYPE SUB
> EXTERNAL NAME 'utlfile!write_line'@
>
>
> ----- Test run
>
> db2 "call utils.write_line( '/tmp', 'w', 'discover db2!!!' ) "
>
> Return Status = 0
>
> instead should report exception with
> sqlstate as : 38921
> sqlmsg as : 'Is a directory'

You might want to use a debugger to verify what's happening. For that,
place a call to sleep() inside your code, then attach the debugger to the
db2agent or db2fmp, depending on NOT FENCED vs FENCED. Then you can see if
the function name and specific name are in the correct parameters. If not,
then you have a problem in the parameter list for your C function.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena