SQLDA corrupted when using VARGRAPHIC type parameters

[weirdwoolly]

Hopefully someone will be able to help.

I have written a stored procedure in C++ called from a Java test
harness to validate the graphic data types in C++ and their use.

I have declared the vargraphic input parameters along the following
lines
in i_vargraphic100 vargraphic(100)

and they are populated from String's in java.

The respective host variable's are simialr to
struct {short Length; sqldbchar Data[100] ; } i_vargraphic100 ;

The code follows the same paradigm we have used for VARCHAR which have
worked successfuly.

We are using DB2 v8.2.5 and AIX 5.3 and xlC v6

I have performed the same tests using a SQL stored procedure without a
problem however we need the C++ mechanim to work due to the code base
we have.

>From the tests run I have experienced the following problems with

VARGRAPHIC:

1. corruption of other sqlda entries

When printing out the contents of the sqlda all parameters come
through to the C++ stored procedure correctly provided I do not
overfill the i_vargraphic100 parameter.

For example if it is past a 63 character input string it comes through
with a zero length as do the the preceeding input parameters. But 62
comes through fine.
If the in parameter is changed such that it is a VARGRAPHIC(40), I can
only get 32 characters to pass through correctly. There does not
appear to be an algorithm to how much data is lost.
2. inability to return an OUT parameter passed in as a VARGRAPHIC even
though it is the same size, type and structure etc

A SQL0450N is returned regardless.

3. LONG VARGRAPHIC is sqltype 465 whereas documentation suggests it is
473

However the sqllen is defined correctly as 16350
Has anyone got a C++ stored procedure successfully working with full
VARGRAPHICS using sqldbchar and VARGRAPHIC variables or have any
suggestions as to what is causing issues with the VARGRAPHIC type.

I have no issues with GRAPHIC types.

The only way around the problem at the moment is to use either LONG
VARGRAPHIC or specify a VARGRAPHIC larger than required and basically
relying on the code to enforce the data size required.

All help greatly appreciated.

Regards,
Paul.

[Knut Stolze]

we*********@googlemail.com wrote:

I have written a stored procedure in C++ called from a Java test
harness to validate the graphic data types in C++ and their use.

I have declared the vargraphic input parameters along the following
lines
in i_vargraphic100 vargraphic(100)

and they are populated from String's in java.

The respective host variable's are simialr to
struct {short Length; sqldbchar Data[100] ; } i_vargraphic100 ;

I'm not sure I fully understand the situation yet. Is this host variable on
the Java side or the stored procedure side?

The code follows the same paradigm we have used for VARCHAR which have
worked successfuly.

>>From the tests run I have experienced the following problems with

VARGRAPHIC:

1. corruption of other sqlda entries

Where do you use the SQLDA? Are you using PARAMETER STYLE DRDA in your
procedure? If so, I would suggest that you use a different style, e.g.
SQL. The style DRDA is deprecated (and I'm not sure if the GRAPHIC data
types are/were actually supported.

When printing out the contents of the sqlda all parameters come
through to the C++ stored procedure correctly provided I do not
overfill the i_vargraphic100 parameter.

For example if it is past a 63 character input string it comes through
with a zero length as do the the preceeding input parameters. But 62
comes through fine.
If the in parameter is changed such that it is a VARGRAPHIC(40), I can
only get 32 characters to pass through correctly. There does not
appear to be an algorithm to how much data is lost.

2. inability to return an OUT parameter passed in as a VARGRAPHIC even
though it is the same size, type and structure etc

A SQL0450N is returned regardless.

Could you show us the code of the SP and how you access/set the SQLVAR
structures in the SQLDA?

3. LONG VARGRAPHIC is sqltype 465 whereas documentation suggests it is
473

464 is VARGRAPHIC w/o NULL
465 is VARGRAPHIC with NULL
472 is LONG VARGRAPHIC w/o NULL
472 is LONG VARGRAPHIC with NULL

You should make sure that you don't mix up LONG VARGRAPHIC and VARGRAPHIC.
I suspect that your problem may be found in that.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

[weirdwoolly]

Knut - meant to say thanks for responding so fast, much appreciated.

[weirdwoolly]

Thanks again - I did make a posting replying to this with sample code
and everything but somehow it's got lost and the one saying thanks is
all that remains.

Anyway I obviously did something wrong when posting the reply ...

we use DB2DARI as the parameter style and all access to the sqlda
(directly) is in the C++ code, java uses jdbc drivers supplied by DB2.

The incorrect data is held in the sqldata fields and appears to have
been corrupted by the jdbc driver. As when the sizes are less than
those mentioned in the posting earlier, all data comes through
correctly.

The comment about sqltypes being 465 vs 473 is that when I used
LONGVARGRAPHIC I expected to see an sqltype of 473 but got 465 instead
which I thought was an interesting observation. Anyway I know which
types to use, just didn't see the correct sqltype being passed - again
incorrectly by the jdbc driver.

I'll provide samples next week when back in the office to show more
detail.

Regards,
Paul

[weirdwoolly]

Apologies for the delay in getting the code and output.

Below you will find
* the DDL definition for the test stored procedure
* the code in the stored procedure which is only tracing the contents
of the SQLDA
* input and output from a run with 100 characters in the
i_vargraphic100 input parameter
* input and output from a run with 32 characters in the
i_vargraphic100 input parameter - all other parameters identical to
the 100 character run

In the 100 character sqlda output you will see the corruption of
entries 24, 25 and 26 which is believed to be in the jdbc driver.
Note, the tracing works fine for the 32 character input so incorrect
access of the SQLDA is not a high probability - but I could be wrong.

You will also see that the o_vargraphic100 column has a definition of
LONG VARGRAPHIC to demonstrate the unexpected sqltype being passed as
well.

All help appreciated in clarifying where the bug is i.e. jdbc driver
or my access of the sqlda.

====
DDL
====
drop procedure gfx.cfTestGraphicSP;
create procedure gfx.cfTestGraphicSP(
-- standard parameters
inout errorcode varchar(55),
inout fulldeferrcd varchar(2048),
inout statusind smallint,
inout userid bigint,
inout usertrace smallint,
inout memberid bigint,
inout transtime varchar(26),
inout language char(2),
inout country char(2),
inout variant char(2),
inout currency char(3),
inout unload char(1),
inout rsvda integer,
inout rsvdb integer,
inout rsvdc char(20),
inout rsvdd varchar(20),
inout rsvde varchar(100),
-- end of standard parameters
in i_char1
CHARACTER(1),
in i_char10
CHARACTER(10),
in i_varchar10
VARCHAR(10),
in i_varchar100
VARCHAR(100),
in i_bigint
BIGINT,
in i_graphic1
GRAPHIC(1),
in i_graphic10
GRAPHIC(10),
in i_smallint
SMALLINT,
in i_vargraphic10
VARGRAPHIC(10),
in i_integer
INTEGER,
in i_vargraphic100
VARGRAPHIC(100),
in i_date
CHAR(10),
in i_time
CHAR(8),
in i_timestamp
CHAR(26),

out o_char1
CHARACTER(1),
out o_char10
CHARACTER(10),
out o_varchar10
VARCHAR(10),
out o_varchar10_to5
VARCHAR(10),
out o_varchar100
VARCHAR(100),
out o_bigint
BIGINT,
out o_smallint
SMALLINT,
out o_integer
INTEGER,
out o_graphic1
GRAPHIC(1),
out o_graphic10
GRAPHIC(10),
out o_graphic10_to5
GRAPHIC(10),
out o_vargraphic10
VARGRAPHIC(10),
out o_vargraphic10_to5
GRAPHIC(10),
out o_vargraphic100 LONG
VARGRAPHIC,

out o_date
CHAR(10),
out o_time
CHAR(8),
out o_timestamp
CHAR(26),
out o_errormessage
VARCHAR(200)
)
language c parameter style db2dari fenced
result sets 1
external name 'cfTestGraphicSP!cfTestGraphicSP'
;
=====
CODE
=====
#include <stdio.h>
#include <stdlib.h>
#include <sqlenv.h>
#include <sqlda.h>
#include <string>

EXEC SQL INCLUDE SQLCA ;

const short NoData = -1;
const short NotForReturn = -128;
const short HasData = 0;

short maxLength = 13 ;

enum listoftypes {
Type_SQL_TYP_DATE = 384,
Type_SQL_TYP_NDATE = 385,
Type_SQL_TYP_TIME = 388,
Type_SQL_TYP_NTIME = 389,
Type_SQL_TYP_STAMP = 392,
Type_SQL_TYP_NSTAMP = 393,
Type_SQL_TYP_DATALINK = 396,
Type_SQL_TYP_NDATALINK = 397,
Type_SQL_TYP_CGSTR = 400,
Type_SQL_TYP_NCGSTR = 401,
Type_SQL_TYP_BLOB = 404,
Type_SQL_TYP_NBLOB = 405,
Type_SQL_TYP_CLOB = 408,
Type_SQL_TYP_NCLOB = 409,
Type_SQL_TYP_DBCLOB = 412,
Type_SQL_TYP_NDBCLOB = 413,
Type_SQL_TYP_VARCHAR = 448,
Type_SQL_TYP_NVARCHAR = 449,
Type_SQL_TYP_CHAR = 452,
Type_SQL_TYP_NCHAR = 453,
Type_SQL_TYP_LONG = 456,
Type_SQL_TYP_NLONG = 457,
Type_SQL_TYP_CSTR = 460,
Type_SQL_TYP_NCSTR = 461,
Type_SQL_TYP_VARGRAPH = 464,
Type_SQL_TYP_NVARGRAPH = 465,
Type_SQL_TYP_GRAPHIC = 468,
Type_SQL_TYP_NGRAPHIC = 469,
Type_SQL_TYP_LONGRAPH = 472,
Type_SQL_TYP_NLONGRAPH = 473,
Type_SQL_TYP_LSTR = 476,
Type_SQL_TYP_NLSTR = 477,
Type_SQL_TYP_FLOAT = 480,
Type_SQL_TYP_NFLOAT = 481,
Type_SQL_TYP_DECIMAL = 484,
Type_SQL_TYP_NDECIMAL = 485,
Type_SQL_TYP_ZONED = 488,
Type_SQL_TYP_NZONED = 489,
Type_SQL_TYP_BIGINT = 492,
Type_SQL_TYP_NBIGINT = 493,
Type_SQL_TYP_INTEGER = 496,
Type_SQL_TYP_NINTEGER = 497,
Type_SQL_TYP_SMALL = 500,
Type_SQL_TYP_NSMALL = 501,
Type_SQL_TYP_NUMERIC = 504,
Type_SQL_TYP_NNUMERIC = 505,
Type_SQL_TYP_BLOB_FILE_OBSOLETE = 804,
Type_SQL_TYP_NBLOB_FILE_OBSOLETE = 805,
Type_SQL_TYP_CLOB_FILE_OBSOLETE = 808,
Type_SQL_TYP_NCLOB_FILE_OBSOLETE = 809,
Type_SQL_TYP_DBCLOB_FILE_OBSOLETE = 812,
Type_SQL_TYP_NDBCLOB_FILE_OBSOLETE = 813,
Type_SQL_TYP_BLOB_FILE = 916,
Type_SQL_TYP_NBLOB_FILE = 917,
Type_SQL_TYP_CLOB_FILE = 920,
Type_SQL_TYP_NCLOB_FILE = 921,
Type_SQL_TYP_DBCLOB_FILE = 924,
Type_SQL_TYP_NDBCLOB_FILE = 925,
Type_SQL_TYP_BLOB_LOCATOR = 960,
Type_SQL_TYP_NBLOB_LOCATOR = 961,
Type_SQL_TYP_CLOB_LOCATOR = 964,
Type_SQL_TYP_NCLOB_LOCATOR = 965,
Type_SQL_TYP_DBCLOB_LOCATOR = 968,
Type_SQL_TYP_NDBCLOB_LOCATOR = 969
} ;

#ifdef __cplusplus
extern "C"
#endif

SQL_API_RC
SQL_API_FN cfTestGraphicSP(
void* reserved1,
void* reserved2,
struct sqlda* inout_sqlda,
struct sqlca* ca
)
{
try
{
FILE *debug=fopen("/gfx/logs/oltpdb/cfTestGraphicSP.log", "a+");

char otherBuffer[5000];
char numBuffer[127];

static std::string Buffer;
static std::string dataPortion ;

Buffer.assign("\n\tThis is the SQLDA on input\n") ;

for (int pos=0;pos < inout_sqlda->sqld;pos++ )
{
dataPortion.assign("");

sprintf(numBuffer,"\tEntry [%d]\tType [%d]\tLength [%d]\tData
[",pos, inout_sqlda->sqlvar[pos].sqltype, inout_sqlda-

>sqlvar[pos].sqllen) ;

Buffer.append(numBuffer) ;

if ( *((inout_sqlda)->sqlvar[pos].sqlind) == NoData )
{
dataPortion.assign("NULL") ;
}
else if ( *((inout_sqlda)->sqlvar[pos].sqlind) ==
NotForReturn )
{
dataPortion.assign("Not For Return") ;
}
else if ( *((inout_sqlda)->sqlvar[pos].sqlind) == HasData )
{
if ( inout_sqlda->sqlvar[pos].sqltype == Type_SQL_TYP_LONG
||
inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_NLONG )
{
dataPortion.assign("\"") ;
if ( (int)(*((short *)((inout_sqlda)-

>sqlvar[pos].sqldata)) ) <= 0 )

{
//
// no action required
//
}
if ( (int)(*((short *)((inout_sqlda)-

>sqlvar[pos].sqldata)) ) <= 127 )

{
dataPortion.append( (char *) (inout_sqlda)-

>sqlvar[pos].sqldata+2 ,

(int)(*((short *)((inout_sqlda)-

>sqlvar[pos].sqldata))) );

sprintf( numBuffer, "[%ld]", strlen( (char *)
(inout_sqlda)->sqlvar[pos].sqldata+2 ) ) ;
dataPortion.append( numBuffer ) ;
}
else
{
dataPortion.append( (char *) (inout_sqlda)-

>sqlvar[pos].sqldata+2 , 127 ) ;

dataPortion.append(" ...truncated... ") ;

sprintf( numBuffer, "[%ld]", strlen( (char *)
(inout_sqlda)->sqlvar[pos].sqldata+2 ) ) ;
dataPortion.append( numBuffer ) ;
}

dataPortion.append("\"") ;
}
else if ( inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_BIGINT ||
inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_NBIGINT )
{
sprintf( numBuffer, "%lld", *( (long long*)
(inout_sqlda)->sqlvar[pos].sqldata ) ) ;
dataPortion.assign(numBuffer) ;
}
else if ( inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_SMALL ||
inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_NSMALL )
{
sprintf( numBuffer, "%d", *( (short *) (inout_sqlda)-

>sqlvar[pos].sqldata ) ) ;

dataPortion.assign(numBuffer) ;
}
else if ( inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_FLOAT ||
inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_NFLOAT ||
inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_NUMERIC ||
inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_NNUMERIC ||
inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_DECIMAL ||
inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_NDECIMAL )
{
sprintf( numBuffer, "%.20le", *( (double *)
(inout_sqlda)->sqlvar[pos].sqldata ) ) ;
dataPortion.assign(numBuffer) ;
}
else if ( inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_INTEGER ||
inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_NINTEGER )
{
sprintf( numBuffer, "%ld", *( ( sqlint32 * )
(inout_sqlda)->sqlvar[pos].sqldata ) ) ;
dataPortion.assign(numBuffer) ;
}
else if ( inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_CHAR ||
inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_NCHAR)
{
dataPortion.assign("\"") ;
if ( *((inout_sqlda)->sqlvar[pos].sqldata) != 0 )
{
dataPortion.append( (inout_sqlda)-

>sqlvar[pos].sqldata , (int)((inout_sqlda)->sqlvar[pos].sqllen) );

}
else
{
dataPortion.append( "empty" ) ;
}
dataPortion.append("\"") ;
}
else if ( inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_CSTR ||
inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_NCSTR)
{
dataPortion.assign("\"") ;
if ( *((inout_sqlda)->sqlvar[pos].sqldata) != 0 )
{
dataPortion.append( (inout_sqlda)-

>sqlvar[pos].sqldata ) ;

}
else
{
dataPortion.append( "empty" ) ;
}
dataPortion.append("\"") ;
}
else if ( inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_VARCHAR ||
inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_NVARCHAR )
{
dataPortion.assign("\"") ;
if ( (int)(*((short *)((inout_sqlda)-

>sqlvar[pos].sqldata)) ) <= 127 )

{
dataPortion.append( (char *) (inout_sqlda)-

>sqlvar[pos].sqldata+2 ,

(int)(*((short *)((inout_sqlda)-

>sqlvar[pos].sqldata))) );

}
else
{
dataPortion.append( (char *) (inout_sqlda)-

>sqlvar[pos].sqldata+2 , 127 ) ;

dataPortion.append(" ...truncated... ") ;
}
dataPortion.append("\"") ;
}
else if ( inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_GRAPHIC ||
inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_NGRAPHIC )
{
dataPortion.assign(" G - ") ;

dataPortion.append("\"") ;

if ( (inout_sqlda)->sqlvar[pos].sqllen == 1 )
{
if ( *( (sqldbchar *) (inout_sqlda)-

>sqlvar[pos].sqldata ) == 0 )

{
dataPortion.append( "[terminator found]" ) ;
}
else
{
sprintf( otherBuffer, "[%#x]", *( (sqldbchar *)
(inout_sqlda)->sqlvar[pos].sqldata ) ) ;
dataPortion.append( otherBuffer ) ;
}
}
else
{
for ( short i = 0 ; i < maxLength && i <
(inout_sqlda)->sqlvar[pos].sqllen ; i++ )
{
if ( *( (sqldbchar *) (inout_sqlda)-

>sqlvar[pos].sqldata + i ) == 0 )

{
dataPortion.append( "[terminator found]" ) ;
i = maxLength ;
}
else
{
sprintf( otherBuffer, "[%#x]", *( (sqldbchar
*) (inout_sqlda)->sqlvar[pos].sqldata + i ) ) ;
dataPortion.append( otherBuffer ) ;
}
}
}
dataPortion.append("\"") ;
}
else if ( inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_VARGRAPH ||
inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_NVARGRAPH )
{
dataPortion.assign("VG - length ") ;

sprintf( otherBuffer, "%d ", *( (short *) (inout_sqlda)-

>sqlvar[pos].sqldata ) ) ;

dataPortion.append( otherBuffer ) ;

dataPortion.append("\"") ;

for ( short i = 0 ; i < maxLength && i < (inout_sqlda)-

>sqlvar[pos].sqllen ; i++ )

{
if ( *( (sqldbchar *) (inout_sqlda)-

>sqlvar[pos].sqldata + (i+1) ) == 0 )

{
dataPortion.append( "[terminator found]" ) ;
i = maxLength ;
}
else
{
sprintf( otherBuffer, "[%#x]", *( (sqldbchar *)
(inout_sqlda)->sqlvar[pos].sqldata + (i+1) ) ) ;
dataPortion.append( otherBuffer ) ;
}
}

if ( maxLength < *( (short *) (inout_sqlda)-

>sqlvar[pos].sqldata ) )

{
dataPortion.append("...truncated...") ;
}

dataPortion.append("\"") ;
}
else
{
dataPortion.assign("Unknown or Unknown type") ;
}
}
else
{
sprintf( numBuffer, "Invalid Indicator - %d", *( (short *)
(inout_sqlda)->sqlvar[pos].sqlind ) ) ;
dataPortion.assign(numBuffer) ;
}

Buffer.append( dataPortion ) ;
Buffer.append( "]\n" ) ;
}

fprintf( debug, Buffer.c_str() ) ;
fclose( debug ) ;

//
// default all the return indicators for the moment

for (int pos=0;pos < inout_sqlda->sqld;pos++ )
{
*((inout_sqlda)->sqlvar[ pos ].sqlind) = -1 ;
}

// simplify the exit using 0 to indicate we got through the
tracing

ca->sqlcode = 0 ;
return SQLZ_DISCONNECT_PROC ;
}
catch( ... )
{
// simplify the exit - use 100 to indicate any problem

ca->sqlcode = 100 ;
return SQLZ_DISCONNECT_PROC ;
}
}
================
100 Character run
================
================================================== ===========
About to call sp gfx.cfTestGraphicSP(c++ sp) for testLimits
================================================== ===========
In Param C1 = 'C'
In Param C10 = '1234567890'
In Param VC10 = '1234567890'
In Param VC100 =
'1234567890123456789012345678901234567890123456789 01234567890123456789012345678901234567890123456789 0'
In Param Bigint = 128000
In Param Smalli = 32000
In Param Intege = 64000
In Param G1 = '£'
In Param G10 = 'iŤ£££0ŤŤ££'
In Param VG10 = 'iŤŤŤŤ££ 10'
In Param VG100 = 'iŤŤ£
£123456789012345678901234567890123456789012345678 90123456789012345£££££
££££££££££££££££££Ť0 100'
In Param Date = '2008-02-19'
In Param Time = '10:01:14'
In Param TimeS = '2007-02-19-10.01.12.000000'

Call stored procedure named gfx.cfTestGraphicSP
gfx.cfTestGraphicSP completed successfully

This is the SQLDA on input
Entry [0] Type [449] Length [55] Data ["A"]
Entry [1] Type [449] Length [2048] Data ["A"]
Entry [2] Type [501] Length [2] Data [32000]
Entry [3] Type [493] Length [8] Data [128000]
Entry [4] Type [501] Length [2] Data [1]
Entry [5] Type [493] Length [8] Data [128000]
Entry [6] Type [449] Length [26] Data ["A"]
Entry [7] Type [453] Length [2] Data ["A "]
Entry [8] Type [453] Length [2] Data ["A "]
Entry [9] Type [453] Length [2] Data ["A "]
Entry [10] Type [453] Length [3] Data ["A "]
Entry [11] Type [453] Length [1] Data ["A"]
Entry [12] Type [497] Length [4] Data [64000]
Entry [13] Type [497] Length [4] Data [64000]
Entry [14] Type [453] Length [20] Data
["A "]
Entry [15] Type [449] Length [20] Data ["A"]
Entry [16] Type [449] Length [100] Data ["c++
sp"]
Entry [17] Type [453] Length [1] Data ["C"]
Entry [18] Type [453] Length [10] Data
["1234567890"]
Entry [19] Type [449] Length [10] Data
["1234567890"]
Entry [20] Type [449] Length [100] Data
["1234567890123456789012345678901234567890123456789 01234567890123456789012345678901234567890123456789 0"]
Entry [21] Type [493] Length [8] Data [128000]
Entry [22] Type [469] Length [1] Data [ G -
"[0xa3]"]
Entry [23] Type [469] Length [10] Data [ G -
"[0x69][0x164][0xa3][0xa3][0xa3][0x30][0x164][0x164][0xa3][0xa3]"]
Entry [24] Type [501] Length [2] Data [0]
Entry [25] Type [465] Length [10] Data [VG -
length 0 "[terminator found]"]
Entry [26] Type [497] Length [4] Data [0]
Entry [27] Type [465] Length [100] Data [VG -
length 200 "[0x69][0x164][0x164][0xa3][0xa3][0x31][0x32][0x33][0x34]
[0x35][0x36][0x37][0x38]...truncated..."]
Entry [28] Type [453] Length [10] Data
["2008-02-19"]
Entry [29] Type [453] Length [8] Data
["10:01:14"]
Entry [30] Type [453] Length [26] Data
["2007-02-19-10.01.12.000000"]
Entry [31] Type [453] Length [1] Data ["empty"]
Entry [32] Type [453] Length [10] Data ["empty"]
Entry [33] Type [449] Length [10] Data [""]
Entry [34] Type [449] Length [10] Data [""]
Entry [35] Type [449] Length [100] Data [""]
Entry [36] Type [493] Length [8] Data [0]
Entry [37] Type [501] Length [2] Data [0]
Entry [38] Type [497] Length [4] Data [0]
Entry [39] Type [469] Length [1] Data [ G -
"[terminator found]"]
Entry [40] Type [469] Length [10] Data [ G -
"[terminator found]"]
Entry [41] Type [469] Length [10] Data [ G -
"[terminator found]"]
Entry [42] Type [465] Length [10] Data [VG -
length 0 "[terminator found]"]
Entry [43] Type [469] Length [10] Data [ G -
"[terminator found]"]
Entry [44] Type [465] Length [16350] Data [VG -
length 0 "[terminator found]"]
Entry [45] Type [453] Length [10] Data ["empty"]
Entry [46] Type [453] Length [8] Data ["empty"]
Entry [47] Type [453] Length [26] Data ["empty"]
Entry [48] Type [449] Length [200] Data [""]
================
32 Character run
================
================================================== ===========
About to call sp gfx.cfTestGraphicSP(c++ sp) for testLimits2
================================================== ===========
In Param C1 = 'C'
In Param C10 = '1234567890'
In Param VC10 = '1234567890'
In Param VC100 =
'1234567890123456789012345678901234567890123456789 01234567890123456789012345678901234567890123456789 0'
In Param Bigint = 128000
In Param Smalli = 32000
In Param Intege = 64000
In Param G1 = '£'
In Param G10 = 'iŤ£££0ŤŤ££'
In Param VG10 = 'iŤŤŤŤ££ 10'
In Param VG100 = '23123451234561£££££££££££Ť0 32'
In Param Date = '2008-02-19'
In Param Time = '10:01:14'
In Param TimeS = '2007-02-19-10.01.12.000000'

Call stored procedure named gfx.cfTestGraphicSP
gfx.cfTestGraphicSP completed successfully

This is the SQLDA on input
Entry [0] Type [449] Length [55] Data ["A"]
Entry [1] Type [449] Length [2048] Data ["A"]
Entry [2] Type [501] Length [2] Data [32000]
Entry [3] Type [493] Length [8] Data [128000]
Entry [4] Type [501] Length [2] Data [1]
Entry [5] Type [493] Length [8] Data [128000]
Entry [6] Type [449] Length [26] Data ["A"]
Entry [7] Type [453] Length [2] Data ["A "]
Entry [8] Type [453] Length [2] Data ["A "]
Entry [9] Type [453] Length [2] Data ["A "]
Entry [10] Type [453] Length [3] Data ["A "]
Entry [11] Type [453] Length [1] Data ["A"]
Entry [12] Type [497] Length [4] Data [64000]
Entry [13] Type [497] Length [4] Data [64000]
Entry [14] Type [453] Length [20] Data
["A "]
Entry [15] Type [449] Length [20] Data ["A"]
Entry [16] Type [449] Length [100] Data ["c++
sp"]
Entry [17] Type [453] Length [1] Data ["C"]
Entry [18] Type [453] Length [10] Data
["1234567890"]
Entry [19] Type [449] Length [10] Data
["1234567890"]
Entry [20] Type [449] Length [100] Data
["1234567890123456789012345678901234567890123456789 01234567890123456789012345678901234567890123456789 0"]
Entry [21] Type [493] Length [8] Data [128000]
Entry [22] Type [469] Length [1] Data [ G -
"[0xa3]"]
Entry [23] Type [469] Length [10] Data [ G -
"[0x69][0x164][0xa3][0xa3][0xa3][0x30][0x164][0x164][0xa3][0xa3]"]
Entry [24] Type [501] Length [2] Data [32000]
Entry [25] Type [465] Length [10] Data [VG -
length 20 "[0x69][0x164][0x164][0x164][0x164][0xa3][0xa3][0x20][0x31]
[0x30]...truncated..."]
Entry [26] Type [497] Length [4] Data [64000]
Entry [27] Type [465] Length [100] Data [VG -
length 64 "[0x32][0x33][0x31][0x32][0x33][0x34][0x35][0x31][0x32][0x33]
[0x34][0x35][0x36]...truncated..."]
Entry [28] Type [453] Length [10] Data
["2008-02-19"]
Entry [29] Type [453] Length [8] Data
["10:01:14"]
Entry [30] Type [453] Length [26] Data
["2007-02-19-10.01.12.000000"]
Entry [31] Type [453] Length [1] Data ["empty"]
Entry [32] Type [453] Length [10] Data ["empty"]
Entry [33] Type [449] Length [10] Data [""]
Entry [34] Type [449] Length [10] Data [""]
Entry [35] Type [449] Length [100] Data [""]
Entry [36] Type [493] Length [8] Data [0]
Entry [37] Type [501] Length [2] Data [0]
Entry [38] Type [497] Length [4] Data [0]
Entry [39] Type [469] Length [1] Data [ G -
"[terminator found]"]
Entry [40] Type [469] Length [10] Data [ G -
"[terminator found]"]
Entry [41] Type [469] Length [10] Data [ G -
"[terminator found]"]
Entry [42] Type [465] Length [10] Data [VG -
length 0 "[terminator found]"]
Entry [43] Type [469] Length [10] Data [ G -
"[terminator found]"]
Entry [44] Type [465] Length [16350] Data [VG -
length 0 "[terminator found]"]
Entry [45] Type [453] Length [10] Data ["empty"]
Entry [46] Type [453] Length [8] Data ["empty"]
Entry [47] Type [453] Length [26] Data ["empty"]
Entry [48] Type [449] Length [200] Data [""]
=================
End of post

[Weirdwoolly]

On 28 Feb, 12:17, [e-mail] wrote:

Apologies for the delay in getting the code and output.

Below you will find
* the DDL definition for the test stored procedure
* the code in the stored procedure which is only tracing the contents
of theSQLDA
* input and output from a run with 100 characters in the
i_vargraphic100 input parameter
* input and output from a run with 32 characters in the
i_vargraphic100 input parameter - all other parameters identical to
the 100 character run

In the 100 charactersqldaoutput you will see the corruption of
entries 24, 25 and 26 which is believed to be in the jdbc driver.
Note, the tracing works fine for the 32 character input so incorrect
access of theSQLDAis not a high probability - but I could be wrong.

You will also see that the o_vargraphic100 column has a definition of
LONGVARGRAPHICto demonstrate the unexpected sqltype being passed as
well.

All help appreciated in clarifying where the bug is i.e. jdbc driver
or my access of thesqlda.

====
DDL
====
drop procedure gfx.cfTestGraphicSP;
create procedure gfx.cfTestGraphicSP(
-- standard parameters
inout errorcode varchar(55),
inout fulldeferrcd varchar(2048),
inout statusind smallint,
inout userid bigint,
inout usertrace smallint,
inout memberid bigint,
inout transtime varchar(26),
inout language char(2),
inout country char(2),
inout variant char(2),
inout currency char(3),
inout unload char(1),
inout rsvda integer,
inout rsvdb integer,
inout rsvdc char(20),
inout rsvdd varchar(20),
inout rsvde varchar(100),
-- end of standard parameters
in i_char1
CHARACTER(1),
in i_char10
CHARACTER(10),
in i_varchar10
VARCHAR(10),
in i_varchar100
VARCHAR(100),
in i_bigint
BIGINT,
in i_graphic1
GRAPHIC(1),
in i_graphic10
GRAPHIC(10),
in i_smallint
SMALLINT,
in i_vargraphic10VARGRAPHIC(10),
in i_integer
INTEGER,
in i_vargraphic100VARGRAPHIC(100),
in i_date
CHAR(10),
in i_time
CHAR(8),
in i_timestamp
CHAR(26),

out o_char1
CHARACTER(1),
out o_char10
CHARACTER(10),
out o_varchar10
VARCHAR(10),
out o_varchar10_to5
VARCHAR(10),
out o_varchar100
VARCHAR(100),
out o_bigint
BIGINT,
out o_smallint
SMALLINT,
out o_integer
INTEGER,
out o_graphic1
GRAPHIC(1),
out o_graphic10
GRAPHIC(10),
out o_graphic10_to5
GRAPHIC(10),
out o_vargraphic10VARGRAPHIC(10),
out o_vargraphic10_to5
GRAPHIC(10),
out o_vargraphic100 LONGVARGRAPHIC,

out o_date
CHAR(10),
out o_time
CHAR(8),
out o_timestamp
CHAR(26),
out o_errormessage
VARCHAR(200)
)
language c parameter style db2dari fenced
result sets 1
external name 'cfTestGraphicSP!cfTestGraphicSP'
;
=====
CODE
=====
#include <stdio.h>
#include <stdlib.h>
#include <sqlenv.h>
#include <sqlda.h>
#include <string>

EXEC SQL INCLUDE SQLCA ;

const short NoData = -1;
const short NotForReturn = -128;
const short HasData = 0;

short maxLength = 13 ;

enum listoftypes {
Type_SQL_TYP_DATE = 384,
Type_SQL_TYP_NDATE = 385,
Type_SQL_TYP_TIME = 388,
Type_SQL_TYP_NTIME = 389,
Type_SQL_TYP_STAMP = 392,
Type_SQL_TYP_NSTAMP = 393,
Type_SQL_TYP_DATALINK = 396,
Type_SQL_TYP_NDATALINK = 397,
Type_SQL_TYP_CGSTR = 400,
Type_SQL_TYP_NCGSTR = 401,
Type_SQL_TYP_BLOB = 404,
Type_SQL_TYP_NBLOB = 405,
Type_SQL_TYP_CLOB = 408,
Type_SQL_TYP_NCLOB = 409,
Type_SQL_TYP_DBCLOB = 412,
Type_SQL_TYP_NDBCLOB = 413,
Type_SQL_TYP_VARCHAR = 448,
Type_SQL_TYP_NVARCHAR = 449,
Type_SQL_TYP_CHAR = 452,
Type_SQL_TYP_NCHAR = 453,
Type_SQL_TYP_LONG = 456,
Type_SQL_TYP_NLONG = 457,
Type_SQL_TYP_CSTR = 460,
Type_SQL_TYP_NCSTR = 461,
Type_SQL_TYP_VARGRAPH = 464,
Type_SQL_TYP_NVARGRAPH = 465,
Type_SQL_TYP_GRAPHIC = 468,
Type_SQL_TYP_NGRAPHIC = 469,
Type_SQL_TYP_LONGRAPH = 472,
Type_SQL_TYP_NLONGRAPH = 473,
Type_SQL_TYP_LSTR = 476,
Type_SQL_TYP_NLSTR = 477,
Type_SQL_TYP_FLOAT = 480,
Type_SQL_TYP_NFLOAT = 481,
Type_SQL_TYP_DECIMAL = 484,
Type_SQL_TYP_NDECIMAL = 485,
Type_SQL_TYP_ZONED = 488,
Type_SQL_TYP_NZONED = 489,
Type_SQL_TYP_BIGINT = 492,
Type_SQL_TYP_NBIGINT = 493,
Type_SQL_TYP_INTEGER = 496,
Type_SQL_TYP_NINTEGER = 497,
Type_SQL_TYP_SMALL = 500,
Type_SQL_TYP_NSMALL = 501,
Type_SQL_TYP_NUMERIC = 504,
Type_SQL_TYP_NNUMERIC = 505,
Type_SQL_TYP_BLOB_FILE_OBSOLETE = 804,
Type_SQL_TYP_NBLOB_FILE_OBSOLETE = 805,
Type_SQL_TYP_CLOB_FILE_OBSOLETE = 808,
Type_SQL_TYP_NCLOB_FILE_OBSOLETE = 809,
Type_SQL_TYP_DBCLOB_FILE_OBSOLETE = 812,
Type_SQL_TYP_NDBCLOB_FILE_OBSOLETE = 813,
Type_SQL_TYP_BLOB_FILE = 916,
Type_SQL_TYP_NBLOB_FILE = 917,
Type_SQL_TYP_CLOB_FILE = 920,
Type_SQL_TYP_NCLOB_FILE = 921,
Type_SQL_TYP_DBCLOB_FILE = 924,
Type_SQL_TYP_NDBCLOB_FILE = 925,
Type_SQL_TYP_BLOB_LOCATOR = 960,
Type_SQL_TYP_NBLOB_LOCATOR = 961,
Type_SQL_TYP_CLOB_LOCATOR = 964,
Type_SQL_TYP_NCLOB_LOCATOR = 965,
Type_SQL_TYP_DBCLOB_LOCATOR = 968,
Type_SQL_TYP_NDBCLOB_LOCATOR = 969
} ;

#ifdef __cplusplus
extern "C"
#endif

SQL_API_RC
SQL_API_FN cfTestGraphicSP(
void* reserved1,
void* reserved2,
structsqlda* inout_sqlda,
struct sqlca* ca
)
{
try
{
FILE *debug=fopen("/gfx/logs/oltpdb/cfTestGraphicSP.log", "a+");

char otherBuffer[5000];
char numBuffer[127];

static std::string Buffer;
static std::string dataPortion ;

Buffer.assign("\n\tThis is theSQLDAon input\n") ;

for (int pos=0;pos < inout_sqlda->sqld;pos++ )
{
dataPortion.assign("");

sprintf(numBuffer,"\tEntry [%d]\tType [%d]\tLength [%d]\tData
[",pos, inout_sqlda->sqlvar[pos].sqltype, inout_sqlda->sqlvar[pos].sqllen) ;

Buffer.append(numBuffer) ;

if ( *((inout_sqlda)->sqlvar[pos].sqlind) == NoData )
{
dataPortion.assign("NULL") ;
}
else if ( *((inout_sqlda)->sqlvar[pos].sqlind) ==
NotForReturn )
{
dataPortion.assign("Not For Return") ;
}
else if ( *((inout_sqlda)->sqlvar[pos].sqlind) == HasData )
{
if ( inout_sqlda->sqlvar[pos].sqltype == Type_SQL_TYP_LONG
||
inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_NLONG )
{
dataPortion.assign("\"") ;
if ( (int)(*((short *)((inout_sqlda)->sqlvar[pos].sqldata)) ) <= 0 )

{
//
// no action required
//
}
if ( (int)(*((short *)((inout_sqlda)->sqlvar[pos].sqldata)) ) <= 127 )

{
dataPortion.append( (char *) (inout_sqlda)->sqlvar[pos]..sqldata+2 ,

(int)(*((short *)((inout_sqlda)-

sqlvar[pos].sqldata))) );

sprintf( numBuffer, "[%ld]", strlen( (char *)
(inout_sqlda)->sqlvar[pos].sqldata+2 ) ) ;
dataPortion.append( numBuffer ) ;
}
else
{
dataPortion.append( (char *) (inout_sqlda)->sqlvar[pos]..sqldata+2 , 127 ) ;

dataPortion.append(" ...truncated... ") ;

sprintf( numBuffer, "[%ld]", strlen( (char *)
(inout_sqlda)->sqlvar[pos].sqldata+2 ) ) ;
dataPortion.append( numBuffer ) ;
}

dataPortion.append("\"") ;
}
else if (
...

read more »

Has anyone tried the code.
It repeatedly produces the error so either I have done something wrong
or the jdbc driver has a bug.
Our next course of action is a PMR with IBM, I had just hoped that
after the initial speedy response I might have had some feedback
especially considering the sample code has been provided and all
questions answered.

[Knut Stolze]

Weirdwoolly wrote:

On 28 Feb, 12:17, [e-mail] wrote:

>Apologies for the delay in getting the code and output.

Below you will find
* the DDL definition for the test stored procedure
* the code in the stored procedure which is only tracing the contents
of theSQLDA
* input and output from a run with 100 characters in the
i_vargraphic100 input parameter
* input and output from a run with 32 characters in the
i_vargraphic100 input parameter - all other parameters identical to
the 100 character run

In the 100 charactersqldaoutput you will see the corruption of
entries 24, 25 and 26 which is believed to be in the jdbc driver.
Note, the tracing works fine for the 32 character input so incorrect
access of theSQLDAis not a high probability - but I could be wrong.

You will also see that the o_vargraphic100 column has a definition of
LONGVARGRAPHICto demonstrate the unexpected sqltype being passed as
well.

All help appreciated in clarifying where the bug is i.e. jdbc driver
or my access of thesqlda.

====
DDL
====
drop procedure gfx.cfTestGraphicSP;
create procedure gfx.cfTestGraphicSP(
-- standard parameters
inout errorcode varchar(55),
inout fulldeferrcd varchar(2048),
inout statusind smallint,
inout userid bigint,
inout usertrace smallint,
inout memberid bigint,
inout transtime varchar(26),
inout language char(2),
inout country char(2),
inout variant char(2),
inout currency char(3),
inout unload char(1),
inout rsvda integer,
inout rsvdb integer,
inout rsvdc char(20),
inout rsvdd varchar(20),
inout rsvde varchar(100),
-- end of standard parameters
in i_char1
CHARACTER(1),
in i_char10
CHARACTER(10),
in i_varchar10
VARCHAR(10),
in i_varchar100
VARCHAR(100),
in i_bigint
BIGINT,
in i_graphic1
GRAPHIC(1),
in i_graphic10
GRAPHIC(10),
in i_smallint
SMALLINT,
in i_vargraphic10VARGRAPHIC(10),
in i_integer
INTEGER,
in i_vargraphic100VARGRAPHIC(100),
in i_date
CHAR(10),
in i_time
CHAR(8),
in i_timestamp
CHAR(26),

out o_char1
CHARACTER(1),
out o_char10
CHARACTER(10),
out o_varchar10
VARCHAR(10),
out o_varchar10_to5
VARCHAR(10),
out o_varchar100
VARCHAR(100),
out o_bigint
BIGINT,
out o_smallint
SMALLINT,
out o_integer
INTEGER,
out o_graphic1
GRAPHIC(1),
out o_graphic10
GRAPHIC(10),
out o_graphic10_to5
GRAPHIC(10),
out o_vargraphic10VARGRAPHIC(10),
out o_vargraphic10_to5
GRAPHIC(10),
out o_vargraphic100
LONGVARGRAPHIC,

out o_date
CHAR(10),
out o_time
CHAR(8),
out o_timestamp
CHAR(26),
out o_errormessage
VARCHAR(200)
)
language c parameter style db2dari fenced

You shouldn't use the DB2DARI parameter style. It is still supported for
backward compatibility only.

>result sets 1
external name 'cfTestGraphicSP!cfTestGraphicSP'
;
=====
CODE
=====
#include <stdio.h>
#include <stdlib.h>
#include <sqlenv.h>
#include <sqlda.h>
#include <string>

EXEC SQL INCLUDE SQLCA ;

const short NoData = -1;
const short NotForReturn = -128;
const short HasData = 0;

I would use the SQLUDF_NULLIND macros instead of the definitions here.

>short maxLength = 13 ;

enum listoftypes {
Type_SQL_TYP_DATE = 384,
Type_SQL_TYP_NDATE = 385,
Type_SQL_TYP_TIME = 388,
Type_SQL_TYP_NTIME = 389,
Type_SQL_TYP_STAMP = 392,
Type_SQL_TYP_NSTAMP = 393,
Type_SQL_TYP_DATALINK = 396,
Type_SQL_TYP_NDATALINK = 397,
Type_SQL_TYP_CGSTR = 400,
Type_SQL_TYP_NCGSTR = 401,
Type_SQL_TYP_BLOB = 404,
Type_SQL_TYP_NBLOB = 405,
Type_SQL_TYP_CLOB = 408,
Type_SQL_TYP_NCLOB = 409,
Type_SQL_TYP_DBCLOB = 412,
Type_SQL_TYP_NDBCLOB = 413,
Type_SQL_TYP_VARCHAR = 448,
Type_SQL_TYP_NVARCHAR = 449,
Type_SQL_TYP_CHAR = 452,
Type_SQL_TYP_NCHAR = 453,
Type_SQL_TYP_LONG = 456,
Type_SQL_TYP_NLONG = 457,
Type_SQL_TYP_CSTR = 460,
Type_SQL_TYP_NCSTR = 461,
Type_SQL_TYP_VARGRAPH = 464,
Type_SQL_TYP_NVARGRAPH = 465,
Type_SQL_TYP_GRAPHIC = 468,
Type_SQL_TYP_NGRAPHIC = 469,
Type_SQL_TYP_LONGRAPH = 472,
Type_SQL_TYP_NLONGRAPH = 473,
Type_SQL_TYP_LSTR = 476,
Type_SQL_TYP_NLSTR = 477,
Type_SQL_TYP_FLOAT = 480,
Type_SQL_TYP_NFLOAT = 481,
Type_SQL_TYP_DECIMAL = 484,
Type_SQL_TYP_NDECIMAL = 485,
Type_SQL_TYP_ZONED = 488,
Type_SQL_TYP_NZONED = 489,
Type_SQL_TYP_BIGINT = 492,
Type_SQL_TYP_NBIGINT = 493,
Type_SQL_TYP_INTEGER = 496,
Type_SQL_TYP_NINTEGER = 497,
Type_SQL_TYP_SMALL = 500,
Type_SQL_TYP_NSMALL = 501,
Type_SQL_TYP_NUMERIC = 504,
Type_SQL_TYP_NNUMERIC = 505,
Type_SQL_TYP_BLOB_FILE_OBSOLETE = 804,
Type_SQL_TYP_NBLOB_FILE_OBSOLETE = 805,
Type_SQL_TYP_CLOB_FILE_OBSOLETE = 808,
Type_SQL_TYP_NCLOB_FILE_OBSOLETE = 809,
Type_SQL_TYP_DBCLOB_FILE_OBSOLETE = 812,
Type_SQL_TYP_NDBCLOB_FILE_OBSOLETE = 813,
Type_SQL_TYP_BLOB_FILE = 916,
Type_SQL_TYP_NBLOB_FILE = 917,
Type_SQL_TYP_CLOB_FILE = 920,
Type_SQL_TYP_NCLOB_FILE = 921,
Type_SQL_TYP_DBCLOB_FILE = 924,
Type_SQL_TYP_NDBCLOB_FILE = 925,
Type_SQL_TYP_BLOB_LOCATOR = 960,
Type_SQL_TYP_NBLOB_LOCATOR = 961,
Type_SQL_TYP_CLOB_LOCATOR = 964,
Type_SQL_TYP_NCLOB_LOCATOR = 965,
Type_SQL_TYP_DBCLOB_LOCATOR = 968,
Type_SQL_TYP_NDBCLOB_LOCATOR = 969
} ;

Why are you doing that? Just include <sql.hand use the definitions there.
Much safer...

>#ifdef __cplusplus
extern "C"
#endif

SQL_API_RC
SQL_API_FN cfTestGraphicSP(
void* reserved1,
void* reserved2,
structsqlda* inout_sqlda,
struct sqlca* ca
)
{
try
{
FILE *debug=fopen("/gfx/logs/oltpdb/cfTestGraphicSP.log", "a+");

char otherBuffer[5000];
char numBuffer[127];

static std::string Buffer;
static std::string dataPortion ;

Buffer.assign("\n\tThis is theSQLDAon input\n") ;

for (int pos=0;pos < inout_sqlda->sqld;pos++ )
{

I recommend that you declare the following variable here:

struct sqlvar *sqlvar = inout_sqlda->sqlvar[pos];

Then use "sqlvar" instead of the long expression. First, you have a good
chance for better optimized code (because the compiler doesn't have to
wonder if the inout_sqlda structure may change in between and, therefore,
the pointer has to be chased each time. Next, it simplifies the code.

> dataPortion.assign("");

sprintf(numBuffer,"\tEntry [%d]\tType [%d]\tLength [%d]\tData
[",pos, inout_sqlda->sqlvar[pos].sqltype,
[inout_sqlda->sqlvar[pos].sqllen) ;

Buffer.append(numBuffer) ;

if ( *((inout_sqlda)->sqlvar[pos].sqlind) == NoData )

I would use a switch/case statement here instead of the long if/elseif
construct. Besides, you avoid mistakes in the repetition of the access to
sqlind.

Also, you are aware of this sentence in the manuals: "If sqltype is an even
number value, the sqlind field is ignored." Thus, you should first test
for the sqltype before interpreting anything into the null indicator value.

http://tinyurl.com/3ctjy8

> {
dataPortion.assign("NULL") ;
}
else if ( *((inout_sqlda)->sqlvar[pos].sqlind) ==
NotForReturn )
{
dataPortion.assign("Not For Return") ;
}
else if ( *((inout_sqlda)->sqlvar[pos].sqlind) == HasData )
{
if ( inout_sqlda->sqlvar[pos].sqltype == Type_SQL_TYP_LONG
||
inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_NLONG )

A switch would be better here as well.

> {
dataPortion.assign("\"") ;
if ( (int)(*((short
*)((inout_sqlda)->sqlvar[pos].sqldata)) ) <= 0 )

{
//
// no action required
//
}
if ( (int)(*((short
*)((inout_sqlda)->sqlvar[pos].sqldata)) ) <= 127 )

{
dataPortion.append( (char *)
(inout_sqlda)->sqlvar[pos].sqldata+2 ,

(int)(*((short *)((inout_sqlda)-

>sqlvar[pos].sqldata))) );

sprintf( numBuffer, "[%ld]", strlen( (char *)
(inout_sqlda)->sqlvar[pos].sqldata+2 ) ) ;
dataPortion.append( numBuffer ) ;
}
else
{
dataPortion.append( (char *)
(inout_sqlda)->sqlvar[pos].sqldata+2 , 127 ) ;

dataPortion.append(" ...truncated... ") ;

sprintf( numBuffer, "[%ld]", strlen( (char *)
(inout_sqlda)->sqlvar[pos].sqldata+2 ) ) ;
dataPortion.append( numBuffer ) ;
}

dataPortion.append("\"") ;
}
else if (
...

read more »

> else if ( inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_GRAPHIC ||
inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_NGRAPHIC )
{
dataPortion.assign(" G - ") ;

dataPortion.append("\"") ;

if ( (inout_sqlda)->sqlvar[pos].sqllen == 1 )

Why do you have the special handling for sqllen == 1? The loop below covers
that case as well.

> {
if ( *( (sqldbchar *) (inout_sqlda)-

>>>sqlvar[pos].sqldata ) == 0 )

{
dataPortion.append( "[terminator found]" ) ;
}
else
{
sprintf( otherBuffer, "[%#x]", *( (sqldbchar *)
(inout_sqlda)->sqlvar[pos].sqldata ) ) ;
dataPortion.append( otherBuffer ) ;

> }
}
else
{
for ( short i = 0 ; i < maxLength && i <
(inout_sqlda)->sqlvar[pos].sqllen ; i++ )
{
if ( *( (sqldbchar *) (inout_sqlda)-

>>>sqlvar[pos].sqldata + i ) == 0 )

{
dataPortion.append( "[terminator found]" ) ;
i = maxLength ;
}
else
{
sprintf( otherBuffer, "[%#x]", *( (sqldbchar
*) (inout_sqlda)->sqlvar[pos].sqldata + i ) ) ;
dataPortion.append( otherBuffer ) ;
}
}
}
dataPortion.append("\"") ;
}
else if ( inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_VARGRAPH ||
inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_NVARGRAPH )
{
dataPortion.assign("VG - length ") ;

sprintf( otherBuffer, "%d ", *( (short *) (inout_sqlda)-

>>>sqlvar[pos].sqldata ) ) ;

What exactly are you doing here with the "sqldata" attribute? Don't you
want to use "sqllen" here instead?

> dataPortion.append( otherBuffer ) ;

dataPortion.append("\"") ;

for ( short i = 0 ; i < maxLength && i < (inout_sqlda)-

>>>sqlvar[pos].sqllen ; i++ )

{
if ( *( (sqldbchar *) (inout_sqlda)-

>>>sqlvar[pos].sqldata + (i+1) ) == 0 )

{
dataPortion.append( "[terminator found]" ) ;
i = maxLength ;
}
else
{
sprintf( otherBuffer, "[%#x]", *( (sqldbchar *)
(inout_sqlda)->sqlvar[pos].sqldata + (i+1) ) ) ;

This whole loop looks strange to me. You ignore the first byte completely
in your output due to the "i+1". So you probably interpret the wrong two
bytes (sqldbchar is a short).

> dataPortion.append( otherBuffer ) ;
}
}

if ( maxLength < *( (short *) (inout_sqlda)-

>>>sqlvar[pos].sqldata ) )

{
dataPortion.append("...truncated...") ;
}

dataPortion.append("\"") ;
}

Has anyone tried the code.

I may be mistaken on that because I don't remember exactly how the SQLDA is
used in DB2DARI stored procedures. But I think you are
misinterpreting "sqldata" quite often. For example, what is that supposed
to do for the VARCHAR case?

if ( (int)(*((short *)(sqlvar->sqldata)) ) <= 127 )

I think that "sqldata" points directly to the actual string data and is not
prepended by some sort of length or code page information.

It repeatedly produces the error so either I have done something wrong
or the jdbc driver has a bug.

--
Knut Stolze
DB2 z/OS Admin Enablement
IBM Germany

[Weirdwoolly]

On Mar 6, 12:23 pm, Knut Stolze <[e-mail]:

Weirdwoolly wrote:

On 28 Feb, 12:17, [e-mail] wrote:

Apologies for the delay in getting the code and output.

Below you will find
* the DDL definition for the test stored procedure
* the code in the stored procedure which is only tracing the contents
of theSQLDA
* input and output from a run with 100 characters in the
i_vargraphic100 input parameter
* input and output from a run with 32 characters in the
i_vargraphic100 input parameter - all other parameters identical to
the 100 character run

In the 100 charactersqldaoutput you will see the corruption of
entries 24, 25 and 26 which is believed to be in the jdbc driver.
Note, the tracing works fine for the 32 character input so incorrect
access of theSQLDAis not a high probability - but I could be wrong.

You will also see that the o_vargraphic100 column has a definition of
LONGVARGRAPHICto demonstrate the unexpected sqltype being passed as
well.

All help appreciated in clarifying where the bug is i.e. jdbc driver
or my access of thesqlda.

====
DDL
====
drop procedure gfx.cfTestGraphicSP;
create procedure gfx.cfTestGraphicSP(
-- standard parameters
inout errorcode varchar(55),
inout fulldeferrcd varchar(2048),
inout statusind smallint,
inout userid bigint,
inout usertrace smallint,
inout memberid bigint,
inout transtime varchar(26),
inout language char(2),
inout country char(2),
inout variant char(2),
inout currency char(3),
inout unload char(1),
inout rsvda integer,
inout rsvdb integer,
inout rsvdc char(20),
inout rsvdd varchar(20),
inout rsvde varchar(100),
-- end of standard parameters
in i_char1
CHARACTER(1),
in i_char10
CHARACTER(10),
in i_varchar10
VARCHAR(10),
in i_varchar100
VARCHAR(100),
in i_bigint
BIGINT,
in i_graphic1
GRAPHIC(1),
in i_graphic10
GRAPHIC(10),
in i_smallint
SMALLINT,
in i_vargraphic10VARGRAPHIC(10),
in i_integer
INTEGER,
in i_vargraphic100VARGRAPHIC(100),
in i_date
CHAR(10),
in i_time
CHAR(8),
in i_timestamp
CHAR(26),

out o_char1
CHARACTER(1),
out o_char10
CHARACTER(10),
out o_varchar10
VARCHAR(10),
out o_varchar10_to5
VARCHAR(10),
out o_varchar100
VARCHAR(100),
out o_bigint
BIGINT,
out o_smallint
SMALLINT,
out o_integer
INTEGER,
out o_graphic1
GRAPHIC(1),
out o_graphic10
GRAPHIC(10),
out o_graphic10_to5
GRAPHIC(10),
out o_vargraphic10VARGRAPHIC(10),
out o_vargraphic10_to5
GRAPHIC(10),
out o_vargraphic100
LONGVARGRAPHIC,

out o_date
CHAR(10),
out o_time
CHAR(8),
out o_timestamp
CHAR(26),
out o_errormessage
VARCHAR(200)
)
language c parameter style db2dari fenced

You shouldn't use the DB2DARI parameter style. It is still supported for
backward compatibility only.

result sets 1
external name 'cfTestGraphicSP!cfTestGraphicSP'
;
=====
CODE
=====
#include <stdio.h>
#include <stdlib.h>
#include <sqlenv.h>
#include <sqlda.h>
#include <string>

EXEC SQL INCLUDE SQLCA ;

const short NoData = -1;
const short NotForReturn = -128;
const short HasData = 0;

I would use the SQLUDF_NULLIND macros instead of the definitions here.

short maxLength = 13 ;

enum listoftypes {
Type_SQL_TYP_DATE = 384,
Type_SQL_TYP_NDATE = 385,
Type_SQL_TYP_TIME = 388,
Type_SQL_TYP_NTIME = 389,
Type_SQL_TYP_STAMP = 392,
Type_SQL_TYP_NSTAMP = 393,
Type_SQL_TYP_DATALINK = 396,
Type_SQL_TYP_NDATALINK = 397,
Type_SQL_TYP_CGSTR = 400,
Type_SQL_TYP_NCGSTR = 401,
Type_SQL_TYP_BLOB = 404,
Type_SQL_TYP_NBLOB = 405,
Type_SQL_TYP_CLOB = 408,
Type_SQL_TYP_NCLOB = 409,
Type_SQL_TYP_DBCLOB = 412,
Type_SQL_TYP_NDBCLOB = 413,
Type_SQL_TYP_VARCHAR = 448,
Type_SQL_TYP_NVARCHAR = 449,
Type_SQL_TYP_CHAR = 452,
Type_SQL_TYP_NCHAR = 453,
Type_SQL_TYP_LONG = 456,
Type_SQL_TYP_NLONG = 457,
Type_SQL_TYP_CSTR = 460,
Type_SQL_TYP_NCSTR = 461,
Type_SQL_TYP_VARGRAPH = 464,
Type_SQL_TYP_NVARGRAPH = 465,
Type_SQL_TYP_GRAPHIC = 468,
Type_SQL_TYP_NGRAPHIC = 469,
Type_SQL_TYP_LONGRAPH = 472,
Type_SQL_TYP_NLONGRAPH = 473,
Type_SQL_TYP_LSTR = 476,
Type_SQL_TYP_NLSTR = 477,
Type_SQL_TYP_FLOAT = 480,
Type_SQL_TYP_NFLOAT = 481,
Type_SQL_TYP_DECIMAL = 484,
Type_SQL_TYP_NDECIMAL = 485,
Type_SQL_TYP_ZONED = 488,
Type_SQL_TYP_NZONED = 489,
Type_SQL_TYP_BIGINT = 492,
Type_SQL_TYP_NBIGINT = 493,
Type_SQL_TYP_INTEGER = 496,
Type_SQL_TYP_NINTEGER = 497,
Type_SQL_TYP_SMALL = 500,
Type_SQL_TYP_NSMALL = 501,
Type_SQL_TYP_NUMERIC = 504,
Type_SQL_TYP_NNUMERIC = 505,
Type_SQL_TYP_BLOB_FILE_OBSOLETE = 804,
Type_SQL_TYP_NBLOB_FILE_OBSOLETE = 805,
Type_SQL_TYP_CLOB_FILE_OBSOLETE = 808,
Type_SQL_TYP_NCLOB_FILE_OBSOLETE = 809,
Type_SQL_TYP_DBCLOB_FILE_OBSOLETE = 812,
Type_SQL_TYP_NDBCLOB_FILE_OBSOLETE = 813,
Type_SQL_TYP_BLOB_FILE = 916,
Type_SQL_TYP_NBLOB_FILE = 917,
Type_SQL_TYP_CLOB_FILE = 920,
Type_SQL_TYP_NCLOB_FILE = 921,
Type_SQL_TYP_DBCLOB_FILE = 924,
Type_SQL_TYP_NDBCLOB_FILE = 925,
Type_SQL_TYP_BLOB_LOCATOR = 960,
Type_SQL_TYP_NBLOB_LOCATOR = 961,
Type_SQL_TYP_CLOB_LOCATOR = 964,
Type_SQL_TYP_NCLOB_LOCATOR = 965,
Type_SQL_TYP_DBCLOB_LOCATOR = 968,
Type_SQL_TYP_NDBCLOB_LOCATOR = 969
} ;

Why are you doing that? Just include <sql.hand use the definitions there.
Much safer...

#ifdef __cplusplus
extern "C"
#endif

SQL_API_RC
SQL_API_FN cfTestGraphicSP(
void* reserved1,
void* reserved2,
structsqlda* inout_sqlda,
struct sqlca* ca
)
{
try
{
FILE *debug=fopen("/gfx/logs/oltpdb/cfTestGraphicSP.log", "a+");

char otherBuffer[5000];
char numBuffer[127];

static std::string Buffer;
static std::string dataPortion ;

Buffer.assign("\n\tThis is theSQLDAon input\n") ;

for (int pos=0;pos < inout_sqlda->sqld;pos++ )
{

I recommend that you declare the following variable here:

struct sqlvar *sqlvar = inout_sqlda->sqlvar[pos];

Then use "sqlvar" instead of the long expression. First, you have a good
chance for better optimized code (because the compiler doesn't have to
wonder if the inout_sqlda structure may change in between and, therefore,
the pointer has to be chased each time. Next, it simplifies the code.

dataPortion.assign("");

sprintf(numBuffer,"\tEntry [%d]\tType [%d]\tLength [%d]\tData
[",pos, inout_sqlda->sqlvar[pos].sqltype,
[inout_sqlda->sqlvar[pos].sqllen) ;

Buffer.append(numBuffer) ;

if ( *((inout_sqlda)->sqlvar[pos].sqlind) == NoData )

I would use a switch/case statement here instead of the long if/elseif
construct. Besides, you avoid mistakes in the repetition of the access to
sqlind.

Also, you are aware of this sentence in the manuals: "If sqltype is an even
number value, the sqlind field is ignored." Thus, you should first test
for the sqltype before interpreting anything into the null indicator value.

http://tinyurl.com/3ctjy8

{
dataPortion.assign("NULL") ;
}
else if ( *((inout_sqlda)->sqlvar[pos].sqlind) ==
NotForReturn )
{
dataPortion.assign("Not For Return") ;
}
else if ( *((inout_sqlda)->sqlvar[pos].sqlind) == HasData)
{
if ( inout_sqlda->sqlvar[pos].sqltype == Type_SQL_TYP_LONG
||
inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_NLONG )

A switch would be better here as well.

{
dataPortion.assign("\"") ;
if ( (int)(*((short
*)((inout_sqlda)->sqlvar[pos].sqldata)) ) <= 0 )

{
//
// no action required
//
}
if ( (int)(*((short
*)((inout_sqlda)->sqlvar[pos].sqldata)) ) <= 127 )

{
dataPortion.append( (char *)
(inout_sqlda)->sqlvar[pos].sqldata+2 ,

(int)(*((short *)((inout_sqlda)-

sqlvar[pos].sqldata))) );

sprintf( numBuffer, "[%ld]", strlen( (char *)
(inout_sqlda)->sqlvar[pos].sqldata+2 ) ) ;
dataPortion.append( numBuffer ) ;
}
else
{
dataPortion.append( (char *)
(inout_sqlda)->sqlvar[pos].sqldata+2 , 127 ) ;

dataPortion.append(" ...truncated... ") ;

sprintf( numBuffer, "[%ld]", strlen( (char *)
(inout_sqlda)->sqlvar[pos].sqldata+2 ) ) ;
dataPortion.append( numBuffer ) ;
}

dataPortion.append("\"") ;
}
else if (
...

read more »
else if ( inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_GRAPHIC ||
inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_NGRAPHIC )
{
dataPortion.assign(" G - ") ;

dataPortion.append("\"") ;

if ( (inout_sqlda)->sqlvar[pos].sqllen == 1 )

Why do you have the special handling for sqllen == 1? The loop belowcovers
that case as well.

{
if ( *( (sqldbchar *) (inout_sqlda)-
sqlvar[pos].sqldata ) == 0 )
{
dataPortion.append( "[terminator found]" ) ;
}
else
{
sprintf( otherBuffer, "[%#x]", *( (sqldbchar *)
(inout_sqlda)->sqlvar[pos].sqldata ) ) ;
dataPortion.append( otherBuffer ) ;
}
}
else
{
for ( short i = 0 ; i < maxLength && i <
(inout_sqlda)->sqlvar[pos].sqllen ; i++ )
{
if ( *( (sqldbchar *) (inout_sqlda)-
sqlvar[pos].sqldata + i ) == 0 )
{
dataPortion.append( "[terminator found]" ) ;
i = maxLength ;
}
else
{
sprintf( otherBuffer, "[%#x]", *( (sqldbchar
*) (inout_sqlda)->sqlvar[pos].sqldata + i ) ) ;
dataPortion.append( otherBuffer ) ;
}
}
}
dataPortion.append("\"") ;
}
else if ( inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_VARGRAPH ||
inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_NVARGRAPH )
{
dataPortion.assign("VG - length ") ;

sprintf( otherBuffer, "%d ", *( (short *) (inout_sqlda)-
sqlvar[pos].sqldata ) ) ;

What exactly are you doing here with the "sqldata" attribute? Don't you
want to use "sqllen" here instead?

dataPortion.append( otherBuffer ) ;

dataPortion.append("\"") ;

for ( short i = 0 ; i < maxLength && i < (inout_sqlda)-
sqlvar[pos].sqllen ; i++ )
{
if ( *( (sqldbchar *) (inout_sqlda)-
sqlvar[pos].sqldata + (i+1) ) == 0 )
{
dataPortion.append( "[terminator found]" ) ;
i = maxLength ;
}
else
{
sprintf( otherBuffer, "[%#x]", *( (sqldbchar *)
(inout_sqlda)->sqlvar[pos].sqldata + (i+1) ) ) ;

This whole loop looks strange to me. You ignore the first byte completely
in your output due to the "i+1". So you probably interpret the wrong two
bytes (sqldbchar is a short).

dataPortion.append( otherBuffer ) ;
}
}

if ( maxLength < *( (short *) (inout_sqlda)-
sqlvar[pos].sqldata ) )
{
dataPortion.append("...truncated...") ;
}

dataPortion.append("\"") ;
}

Has anyone tried the code.

I may be mistaken on that because I don't remember exactly how theSQLDAis
used in DB2DARI stored procedures. But I think you are
misinterpreting "sqldata" quite often. For example, what is that supposed
to do for the VARCHAR case?

if ( (int)(*((short *)(sqlvar->sqldata)) ) <= 127 )

I think that "sqldata" points directly to the actual string data and is not
prepended by some sort of length or code page information.

It repeatedly produces the error so either I have done something wrong
or the jdbc driver has a bug.

--
Knut Stolze
DB2 z/OS Admin Enablement
IBM Germany

Can you take a look at the trace output that was provided at the end
of the post which showed it working and the issue in question - thanks
- appreciated.

[Knut Stolze]

Weirdwoolly wrote:

On Mar 6, 12:23 pm, Knut Stolze <[e-mail]:

>Weirdwoolly wrote:

On 28 Feb, 12:17, [e-mail] wrote:
Apologies for the delay in getting the code and output.

>Below you will find
* the DDL definition for the test stored procedure
* the code in the stored procedure which is only tracing the contents
of theSQLDA
* input and output from a run with 100 characters in the
i_vargraphic100 input parameter
* input and output from a run with 32 characters in the
i_vargraphic100 input parameter - all other parameters identical to
the 100 character run

>In the 100 charactersqldaoutput you will see the corruption of
entries 24, 25 and 26 which is believed to be in the jdbc driver.
Note, the tracing works fine for the 32 character input so incorrect
access of theSQLDAis not a high probability - but I could be wrong.

>You will also see that the o_vargraphic100 column has a definition of
LONGVARGRAPHICto demonstrate the unexpected sqltype being passed as
well.

>All help appreciated in clarifying where the bug is i.e. jdbc driver
or my access of thesqlda.

>====
DDL
====
drop procedure gfx.cfTestGraphicSP;
create procedure gfx.cfTestGraphicSP(
-- standard parameters
inout errorcode varchar(55),
inout fulldeferrcd varchar(2048),
inout statusind smallint,
inout userid bigint,
inout usertrace smallint,
inout memberid bigint,
inout transtime varchar(26),
inout language char(2),
inout country char(2),
inout variant char(2),
inout currency char(3),
inout unload char(1),
inout rsvda integer,
inout rsvdb integer,
inout rsvdc char(20),
inout rsvdd varchar(20),
inout rsvde varchar(100),
-- end of standard parameters
in i_char1
CHARACTER(1),
in i_char10
CHARACTER(10),
in i_varchar10
VARCHAR(10),
in i_varchar100
VARCHAR(100),
in i_bigint
BIGINT,
in i_graphic1
GRAPHIC(1),
in i_graphic10
GRAPHIC(10),
in i_smallint
SMALLINT,
in i_vargraphic10VARGRAPHIC(10),
in i_integer
INTEGER,
in
i_vargraphic100VARGRAPHIC(100),
in i_date
CHAR(10),
in i_time
CHAR(8),
in i_timestamp
CHAR(26),

> out o_char1
CHARACTER(1),
out o_char10
CHARACTER(10),
out o_varchar10
VARCHAR(10),
out o_varchar10_to5
VARCHAR(10),
out o_varchar100
VARCHAR(100),
out o_bigint
BIGINT,
out o_smallint
SMALLINT,
out o_integer
INTEGER,
out o_graphic1
GRAPHIC(1),
out o_graphic10
GRAPHIC(10),
out o_graphic10_to5
GRAPHIC(10),
out o_vargraphic10VARGRAPHIC(10),
out o_vargraphic10_to5
GRAPHIC(10),
out o_vargraphic100
LONGVARGRAPHIC,

> out o_date
CHAR(10),
out o_time
CHAR(8),
out o_timestamp
CHAR(26),
out o_errormessage
VARCHAR(200)
)
language c parameter style db2dari fenced

You shouldn't use the DB2DARI parameter style. It is still supported for
backward compatibility only.

>result sets 1
external name 'cfTestGraphicSP!cfTestGraphicSP'
;
=====
CODE
=====
#include <stdio.h>
#include <stdlib.h>
#include <sqlenv.h>
#include <sqlda.h>
#include <string>

>EXEC SQL INCLUDE SQLCA ;

>const short NoData = -1;
const short NotForReturn = -128;
const short HasData = 0;

I would use the SQLUDF_NULLIND macros instead of the definitions here.

>short maxLength = 13 ;

>enum listoftypes {
Type_SQL_TYP_DATE = 384,
Type_SQL_TYP_NDATE = 385,
Type_SQL_TYP_TIME = 388,
Type_SQL_TYP_NTIME = 389,
Type_SQL_TYP_STAMP = 392,
Type_SQL_TYP_NSTAMP = 393,
Type_SQL_TYP_DATALINK = 396,
Type_SQL_TYP_NDATALINK = 397,
Type_SQL_TYP_CGSTR = 400,
Type_SQL_TYP_NCGSTR = 401,
Type_SQL_TYP_BLOB = 404,
Type_SQL_TYP_NBLOB = 405,
Type_SQL_TYP_CLOB = 408,
Type_SQL_TYP_NCLOB = 409,
Type_SQL_TYP_DBCLOB = 412,
Type_SQL_TYP_NDBCLOB = 413,
Type_SQL_TYP_VARCHAR = 448,
Type_SQL_TYP_NVARCHAR = 449,
Type_SQL_TYP_CHAR = 452,
Type_SQL_TYP_NCHAR = 453,
Type_SQL_TYP_LONG = 456,
Type_SQL_TYP_NLONG = 457,
Type_SQL_TYP_CSTR = 460,
Type_SQL_TYP_NCSTR = 461,
Type_SQL_TYP_VARGRAPH = 464,
Type_SQL_TYP_NVARGRAPH = 465,
Type_SQL_TYP_GRAPHIC = 468,
Type_SQL_TYP_NGRAPHIC = 469,
Type_SQL_TYP_LONGRAPH = 472,
Type_SQL_TYP_NLONGRAPH = 473,
Type_SQL_TYP_LSTR = 476,
Type_SQL_TYP_NLSTR = 477,
Type_SQL_TYP_FLOAT = 480,
Type_SQL_TYP_NFLOAT = 481,
Type_SQL_TYP_DECIMAL = 484,
Type_SQL_TYP_NDECIMAL = 485,
Type_SQL_TYP_ZONED = 488,
Type_SQL_TYP_NZONED = 489,
Type_SQL_TYP_BIGINT = 492,
Type_SQL_TYP_NBIGINT = 493,
Type_SQL_TYP_INTEGER = 496,
Type_SQL_TYP_NINTEGER = 497,
Type_SQL_TYP_SMALL = 500,
Type_SQL_TYP_NSMALL = 501,
Type_SQL_TYP_NUMERIC = 504,
Type_SQL_TYP_NNUMERIC = 505,
Type_SQL_TYP_BLOB_FILE_OBSOLETE = 804,
Type_SQL_TYP_NBLOB_FILE_OBSOLETE = 805,
Type_SQL_TYP_CLOB_FILE_OBSOLETE = 808,
Type_SQL_TYP_NCLOB_FILE_OBSOLETE = 809,
Type_SQL_TYP_DBCLOB_FILE_OBSOLETE = 812,
Type_SQL_TYP_NDBCLOB_FILE_OBSOLETE = 813,
Type_SQL_TYP_BLOB_FILE = 916,
Type_SQL_TYP_NBLOB_FILE = 917,
Type_SQL_TYP_CLOB_FILE = 920,
Type_SQL_TYP_NCLOB_FILE = 921,
Type_SQL_TYP_DBCLOB_FILE = 924,
Type_SQL_TYP_NDBCLOB_FILE = 925,
Type_SQL_TYP_BLOB_LOCATOR = 960,
Type_SQL_TYP_NBLOB_LOCATOR = 961,
Type_SQL_TYP_CLOB_LOCATOR = 964,
Type_SQL_TYP_NCLOB_LOCATOR = 965,
Type_SQL_TYP_DBCLOB_LOCATOR = 968,
Type_SQL_TYP_NDBCLOB_LOCATOR = 969
} ;

Why are you doing that? Just include <sql.hand use the definitions
there. Much safer...

>#ifdef __cplusplus
extern "C"
#endif

>SQL_API_RC
SQL_API_FN cfTestGraphicSP(
void* reserved1,
void* reserved2,
structsqlda* inout_sqlda,
struct sqlca* ca
)
{
try
{
FILE *debug=fopen("/gfx/logs/oltpdb/cfTestGraphicSP.log", "a+");

> char otherBuffer[5000];
char numBuffer[127];

> static std::string Buffer;
static std::string dataPortion ;

> Buffer.assign("\n\tThis is theSQLDAon input\n") ;

> for (int pos=0;pos < inout_sqlda->sqld;pos++ )
{

I recommend that you declare the following variable here:

struct sqlvar *sqlvar = inout_sqlda->sqlvar[pos];

Then use "sqlvar" instead of the long expression. First, you have a good
chance for better optimized code (because the compiler doesn't have to
wonder if the inout_sqlda structure may change in between and, therefore,
the pointer has to be chased each time. Next, it simplifies the code.

> dataPortion.assign("");

> sprintf(numBuffer,"\tEntry [%d]\tType [%d]\tLength [%d]\tData
[",pos, inout_sqlda->sqlvar[pos].sqltype,
[inout_sqlda->sqlvar[pos].sqllen) ;

> Buffer.append(numBuffer) ;

> if ( *((inout_sqlda)->sqlvar[pos].sqlind) == NoData )

I would use a switch/case statement here instead of the long if/elseif
construct. Besides, you avoid mistakes in the repetition of the access
to sqlind.

Also, you are aware of this sentence in the manuals: "If sqltype is an
even
number value, the sqlind field is ignored." Thus, you should first test
for the sqltype before interpreting anything into the null indicator
value.

http://tinyurl.com/3ctjy8

> {
dataPortion.assign("NULL") ;
}
else if ( *((inout_sqlda)->sqlvar[pos].sqlind) ==
NotForReturn )
{
dataPortion.assign("Not For Return") ;
}
else if ( *((inout_sqlda)->sqlvar[pos].sqlind) == HasData )
{
if ( inout_sqlda->sqlvar[pos].sqltype == Type_SQL_TYP_LONG
||
inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_NLONG )

A switch would be better here as well.

> {
dataPortion.assign("\"") ;
if ( (int)(*((short
*)((inout_sqlda)->sqlvar[pos].sqldata)) ) <= 0 )

> {
//
// no action required
//
}
if ( (int)(*((short
*)((inout_sqlda)->sqlvar[pos].sqldata)) ) <= 127 )

> {
dataPortion.append( (char *)
(inout_sqlda)->sqlvar[pos].sqldata+2 ,

> (int)(*((short *)((inout_sqlda)-

>sqlvar[pos].sqldata))) );

> sprintf( numBuffer, "[%ld]", strlen( (char *)
(inout_sqlda)->sqlvar[pos].sqldata+2 ) ) ;
dataPortion.append( numBuffer ) ;
}
else
{
dataPortion.append( (char *)
(inout_sqlda)->sqlvar[pos].sqldata+2 , 127 ) ;

> dataPortion.append(" ...truncated... ") ;

> sprintf( numBuffer, "[%ld]", strlen( (char *)
(inout_sqlda)->sqlvar[pos].sqldata+2 ) ) ;
dataPortion.append( numBuffer ) ;
}

> dataPortion.append("\"") ;
}
else if (
...

>read more »
else if ( inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_GRAPHIC ||
inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_NGRAPHIC )
{
dataPortion.assign(" G - ") ;

> dataPortion.append("\"") ;

> if ( (inout_sqlda)->sqlvar[pos].sqllen == 1 )

Why do you have the special handling for sqllen == 1? The loop below
covers that case as well.

> {
if ( *( (sqldbchar *) (inout_sqlda)-
sqlvar[pos].sqldata ) == 0 )
{
dataPortion.append( "[terminator found]" ) ;
}
else
{
sprintf( otherBuffer, "[%#x]", *( (sqldbchar *)
(inout_sqlda)->sqlvar[pos].sqldata ) ) ;
dataPortion.append( otherBuffer ) ;
}
}
else
{
for ( short i = 0 ; i < maxLength && i <
(inout_sqlda)->sqlvar[pos].sqllen ; i++ )
{
if ( *( (sqldbchar *) (inout_sqlda)-
sqlvar[pos].sqldata + i ) == 0 )
{
dataPortion.append( "[terminator found]" ) ;
i = maxLength ;
}
else
{
sprintf( otherBuffer, "[%#x]", *( (sqldbchar
*) (inout_sqlda)->sqlvar[pos].sqldata + i ) ) ;
dataPortion.append( otherBuffer ) ;
}
}
}
dataPortion.append("\"") ;
}
else if ( inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_VARGRAPH ||
inout_sqlda->sqlvar[pos].sqltype ==
Type_SQL_TYP_NVARGRAPH )
{
dataPortion.assign("VG - length ") ;

> sprintf( otherBuffer, "%d ", *( (short *) (inout_sqlda)-
sqlvar[pos].sqldata ) ) ;

What exactly are you doing here with the "sqldata" attribute? Don't you
want to use "sqllen" here instead?

> dataPortion.append( otherBuffer ) ;

> dataPortion.append("\"") ;

> for ( short i = 0 ; i < maxLength && i < (inout_sqlda)-
sqlvar[pos].sqllen ; i++ )
{
if ( *( (sqldbchar *) (inout_sqlda)-
sqlvar[pos].sqldata + (i+1) ) == 0 )
{
dataPortion.append( "[terminator found]" ) ;
i = maxLength ;
}
else
{
sprintf( otherBuffer, "[%#x]", *( (sqldbchar *)
(inout_sqlda)->sqlvar[pos].sqldata + (i+1) ) ) ;

This whole loop looks strange to me. You ignore the first byte
completely
in your output due to the "i+1". So you probably interpret the wrong two
bytes (sqldbchar is a short).

> dataPortion.append( otherBuffer ) ;
}
}

> if ( maxLength < *( (short *) (inout_sqlda)-
sqlvar[pos].sqldata ) )
{
dataPortion.append("...truncated...") ;
}

> dataPortion.append("\"") ;
}
Has anyone tried the code.

I may be mistaken on that because I don't remember exactly how theSQLDAis
used in DB2DARI stored procedures. But I think you are
misinterpreting "sqldata" quite often. For example, what is that
supposed to do for the VARCHAR case?

if ( (int)(*((short *)(sqlvar->sqldata)) ) <= 127 )

I think that "sqldata" points directly to the actual string data and is
not prepended by some sort of length or code page information.

It repeatedly produces the error so either I have done something wrong
or the jdbc driver has a bug.

Can you take a look at the trace output that was provided at the end
of the post which showed it working and the issue in question - thanks
- appreciated.

Could you scale the scenario (signature of the SP) down to the mere basics
so that the error is still triggered? Quite frankly, I don't want to type
in an SP call with a huge number of parameters...

Also, you haven't answered my question on why you are using the DB2DARI
parameter style. This is deprecated and no longer supported, except for
backward compatibility. You should use PARAMETER STYLE SQL if possible.

--
Knut Stolze
DB2 z/OS Admin Enablement
IBM Germany

[Weirdwoolly]

I have simplified the logic and attached the code for you. I have made
some modifications however, the access and usage of the sqlda
structure has not changed. Your comments about sqlda usage and missing
the first entry are incorrect (and documentation for coding
vargraphics confirms that the first part of the sqldata for
vargraphics is the length - as it is for varchars) and when the
shorter vargraphic strings are passed the logic behaves impeccably.

I will look into changing parameter style, however as you say it is
still supported for backward compatability and is the style used by
all of our code base.

=========== Firt run with 20 graphic characters ===========
Call stored procedure named gfx.cfTestGraphicSP
In Param Smallint (4 of) = 32000
In Param Integer = 64000
In Param VARGRAPHIC(100) = '1ŤŤ£££££££££Ť0 20'

This is the SQLDA on input
Entry [0] Type [501] Length [2] Data [32000]
Entry [1] Type [501] Length [2] Data [32000]
Entry [2] Type [501] Length [2] Data [32000]
Entry [3] Type [501] Length [2] Data [32000]
Entry [4] Type [497] Length [4] Data [64000]
Entry [5] Type [465] Length [100] Data [VG(100)
length of 40 "[0x31][0x164][0x164][0xa3][0xa3][0xa3][0xa3][0xa3][0xa3]
[0xa3][0xa3][0xa3][0x164]...truncated..."]

=========== Firt run with 100 graphic characters ===========
Call stored procedure named gfx.cfTestGraphicSP
In Param Smallint (4 of) = 32000
In Param Integer = 64000
In Param VARGRAPHIC(100) = '1ŤŤ£
£123456789012345678901234567890123456789012345678 90123456789012345£££££
££££££££££££££££££Ť0 100'

This is the SQLDA on input
Entry [0] Type [501] Length [2] Data [32000]
Entry [1] Type [501] Length [2] Data [0]
Entry [2] Type [501] Length [2] Data [0]
Entry [3] Type [501] Length [2] Data [0]
Entry [4] Type [497] Length [4] Data [0]
Entry [5] Type [465] Length [100] Data [VG(100)
length of 200 "[0x31][0x164][0x164][0xa3][0xa3][0x31][0x32][0x33][0x34]
[0x35][0x36][0x37][0x38]...truncated..."]

=========== Firt run with 32 graphic characters ===========
Call stored procedure named gfx.cfTestGraphicSP
In Param Smallint (4 of) = 32000
In Param Integer = 64000
In Param VARGRAPHIC(100) = '1ŤŤ££123456789012345678901234 32'

This is the SQLDA on input
Entry [0] Type [501] Length [2] Data [32000]
Entry [1] Type [501] Length [2] Data [32000]
Entry [2] Type [501] Length [2] Data [32000]
Entry [3] Type [501] Length [2] Data [32000]
Entry [4] Type [497] Length [4] Data [64000]
Entry [5] Type [465] Length [100] Data [VG(100)
length of 64 "[0x31][0x164][0x164][0xa3][0xa3][0x31][0x32][0x33][0x34]
[0x35][0x36][0x37][0x38]...truncated..."]
#include <stdio.h>
#include <stdlib.h>
#include <sqlenv.h>
#include <sqlda.h>
#include <string>
#include <sql.h>

EXEC SQL INCLUDE SQLCA ;

#ifdef __cplusplus
extern "C"
#endif

SQL_API_RC
SQL_API_FN cfTestGraphicSP(
void* reserved1,
void* reserved2,
struct sqlda* inout_sqlda,
struct sqlca* ca
)
{
try
{
ca->sqlcode = 0 ;
FILE *debug=fopen("/gfx/logs/oltpdb/cfTestGraphicSP.log", "a+");

char otherBuffer[5000];
char numBuffer[127];

static std::string Buffer;
static std::string dataPortion ;

Buffer.assign("\n\tThis is the SQLDA on input\n") ;

for (int pos=0;pos < inout_sqlda->sqld;pos++ )
{
struct sqlvar *sqlvar = &(inout_sqlda->sqlvar[pos]) ;

dataPortion.assign("");

sprintf(numBuffer,"\tEntry [%d]\tType [%d]\tLength [%d]\tData
[",pos, sqlvar->sqltype, sqlvar->sqllen) ;
Buffer.append(numBuffer) ;

if ( *(sqlvar->sqlind) == -1 )
{
dataPortion.assign("NULL") ;
}
else if ( *(sqlvar->sqlind) == -128 )
{
dataPortion.assign("Not For Return") ;
}
else if ( *(sqlvar->sqlind) == 0 )
{
switch ( sqlvar->sqltype )
{
case SQL_TYP_SMALL:
case SQL_TYP_NSMALL:
sprintf( numBuffer, "%d", *( (short *) sqlvar-

>sqldata ) ) ;

dataPortion.assign(numBuffer) ;
break ;

case SQL_TYP_INTEGER:
case SQL_TYP_NINTEGER:
sprintf( numBuffer, "%ld", *( ( sqlint32 * ) sqlvar-

>sqldata ) ) ;

dataPortion.assign(numBuffer) ;
break ;
case SQL_TYP_VARGRAPH:
case SQL_TYP_NVARGRAPH:
sprintf( otherBuffer, "VG(%d) length of %d \"", sqlvar-

>sqllen, *( (short *) sqlvar->sqldata ) ) ;

dataPortion.assign( otherBuffer ) ;

for ( short i = 0 ; i < 13 && i < sqlvar->sqllen ; i++ )
{
// use +1 to avoid the first short at the address
pointed to by sqldata

if ( *( (sqldbchar *) sqlvar->sqldata + (i+1) ) == 0 )
{
dataPortion.append( "[terminator found]" ) ;
i = 13 ;
}
else
{
sprintf( otherBuffer, "[%#x]", *( (sqldbchar *) sqlvar-

>sqldata + (i+1) ) ) ;

dataPortion.append( otherBuffer ) ;
}
}

if ( 13 < *( (short *) sqlvar->sqldata ) )
{
dataPortion.append("...truncated...") ;
}

dataPortion.append("\"") ;
break ;
default:
dataPortion.assign("Unknown or Unknown type") ;
}
}
else
{
sprintf( numBuffer, "Invalid Indicator - %d", *( (short *)
sqlvar->sqlind ) ) ;
dataPortion.assign(numBuffer) ;
}

Buffer.append( dataPortion ) ;
Buffer.append( "]\n" ) ;
}

fprintf( debug, Buffer.c_str() ) ;
fclose( debug ) ;

//
// all the return indicators to "Not For Return"

for (int pos=0;pos < inout_sqlda->sqld;pos++ )
{
struct sqlvar *sqlvar = &(inout_sqlda->sqlvar[pos]) ;
*(sqlvar->sqlind) = -1 ;
}

return SQLZ_DISCONNECT_PROC ;
}
catch( ... )
{
FILE *debug=fopen("/gfx/logs/oltpdb/cfTestGraphicSP.log", "a+");
fprintf( debug, "An unexpected error has occured" ) ;
fclose( debug ) ;
return SQLZ_DISCONNECT_PROC ;
}
}

drop procedure gfx.cfTestGraphicSP;
create procedure gfx.cfTestGraphicSP(
in i_smallint1
SMALLINT,
in i_smallint2
SMALLINT,
in i_smallint3
SMALLINT,
in i_smallint4
SMALLINT,
in i_integer
INTEGER,
in i_vargraphic100
VARGRAPHIC(100)
)
language c parameter style db2dari fenced
external name 'cfTestGraphicSP!cfTestGraphicSP'
;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;

// Note Db is a class provided in Util.java of the DB2 samples

class TestGraphicSP
{
public static void main(String argv[])
{
Connection con = null;
try
{
Db db = new Db(argv);
db.connect();
con = db.con;

short inoutSmallint ;
int inoutInteger ;
String vargraphic100 ;

inoutSmallint = 32000;
inoutInteger = 64000;

vargraphic100 = new String ("1ŤŤ£££££££££Ť0 20") ;
callCfGraphicSP(con, "gfx.cfTestGraphicSP", "c++ sp",
inoutSmallint, inoutInteger, vargraphic100 );

vargraphic100 = new String ("1ŤŤ£
£123456789012345678901234567890123456789012345678 90123456789012345£££££
££££££££££££££££££Ť0 100") ;
callCfGraphicSP(con, "gfx.cfTestGraphicSP", "c++ sp",
inoutSmallint, inoutInteger, vargraphic100 );

vargraphic100 = new String ("1ŤŤ££123456789012345678901234
32") ;
callCfGraphicSP(con, "gfx.cfTestGraphicSP", "c++ sp",
inoutSmallint, inoutInteger, vargraphic100 );

con.rollback();

con.close();
}
catch (Exception e)
{
try
{
con.rollback();
con.close();
}
catch (Exception x)
{ }

e.printStackTrace();
}
} // end main

public static void callCfGraphicSP( Connection con, String procName,
String addString,
short inoutSmallint, int
inoutInteger, String vargraphic100 )
{
try
{
String sql = "CALL " + procName + "( ?, ?, ?, ?, ?, ? )";

CallableStatement callStmt = con.prepareCall(sql);

System.out.println();
System.out.println( "Call stored procedure named " + procName );
System.out.println( " In Param Smallint (4 of) = " +
inoutSmallint ) ;
System.out.println( " In Param Integer = " +
inoutInteger ) ;
System.out.println( " In Param VARGRAPHIC(100) = '" +
vargraphic100 + "'" ) ;

short p = 1 ;

callStmt.setShort( p, inoutSmallint); p++ ;
callStmt.setShort( p, inoutSmallint); p++ ;
callStmt.setShort( p, inoutSmallint); p++ ;
callStmt.setShort( p, inoutSmallint); p++ ;
callStmt.setInt( p, inoutInteger); p++ ;
callStmt.setString( p, vargraphic100); p++ ;

callStmt.execute();

callStmt.close();
}
catch (SQLException e)
{
System.out.println(e.getMessage());
}
}

}