SEO

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.

weirdwoolly@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

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

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

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

On 28 Feb, 12:17, weirdwoo...@googlemail.com 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.

Weirdwoolly wrote:

> On 28 Feb, 12:17, weirdwoo...@googlemail.com 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.h> and 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

On Mar 6, 12:23 pm, Knut Stolze <sto...@de.ibm.com> wrote:
> Weirdwoolly wrote:
> > On 28 Feb, 12:17, weirdwoo...@googlemail.com 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.h> and 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