Issue with SQLDA Structure

akhi007 · New User Joined: 30 Jan 2006 Posts: 35 Location: Richmond

Hi,

I have defined a SQLDA for calling a stored procedure which has one of the parameters as VARCHAR. SQLIND is showing values as -3856.

SQLNAME = OUT_REC
SQLTYPE = 449
SQLLEN = 32000
SQLIND = -3856
SQLDATA = djfdfdfkdnfjdfndjfndjfdfjdkfndjfdfnkjfdnfjdjffnkdfdfdj

As far as I understand SQLIND will tell us about the nature of data in SQLDATA field, whether it is null (-1) or truncated (+1) or good fetch (0)

Did anyone have insight over SQLIND parameter in SQLDA or faced these sort of issues before?

Thanks,
Akhil

dbzTHEdinosauer · Posted: Fri Sep 10, 2010 11:00 am

actually, SQLIND is a pointer.

google 'SQLIND parameter in SQLDA',
and read some of the 2080 references.

akhi007 · New User Joined: 30 Jan 2006 Posts: 35 Location: Richmond

@ dbzTHEdinosauer :

I already googled for SQLDA structures and understood the meaning of SQLIND parameter.... I'm wondering about the weird value coming in SQLIND parameter... Ideally it should be +1, 0, -1 , -2 from the references I got from DB2 books and google.

All,

I am calling the stored procedure through REXX using SQLDA parameter. SQLDATA for varchar field gets overlapped with data from subsequent parameter.

For e.g. If my stored procedure declaration is as follows

INPUT CHAR (10)
OUTREC1 VARCHAR 32000
OUTREC2 CHAR(150)

Data from OUTREC2 is getting overlapped in OUTREC1 varchar field for first 150 bytes.

Lets say if the expected output of both parameters are

OUTREC1 = "Hello, What is your name?"
OUTREC2 = "James"

Output coming out of SQLDATA field is
OUTREC1 = "James, What is your name?"
OUTREC2 = "James"

When I call the same stored procedure through JAVA, I am getting the desired output. I would like to know if anybody have faced any issues like this before. Please help !! Thanks in advance.

Thanks,
Akhil

dbzTHEdinosauer · Posted: Fri Sep 10, 2010 6:57 pm

I would suggest that your JAVA is coded correctly and your REXX is not.

dick scherrer · Posted: Fri Sep 10, 2010 7:19 pm

Hello,

And if you don't post the bit of the rexx code that fails, no one here will be able to help. . .

Have you TRACEd the rexx execution?

akhi007 · New User Joined: 30 Jan 2006 Posts: 35 Location: Richmond

@ dbzTHEdinosauer:

REXX program is working fine for other stored procedures which does not have a VARCHAR field.

@dick scherrer

I have traced the rexx execution and everything is looking good until call. After procedure is called, SQLIND turned to a negative value -3856 and data gets overlapped with subsequent parameter data

Here is the piece of code that fails
SQLDA. = ''

SQLDA.1.SQLNAME = INREC
SQLDA.1.SQLTYPE = 453
SQLDA.1.SQLLEN = 10
SQLDA.1.SQLIND = 0

SQLDA.2.SQLNAME = OUTREC1
SQLDA.2.SQLTYPE = 449
SQLDA.2.SQLLEN = 32000
SQLDA.2.SQLIND = 0

SQLDA.3.SQLNAME = OUTREC2
SQLDA.3.SQLTYPE = 453
SQLDA.3.SQLLEN = 150
SQLDA.3.SQLIND = 0

ADDRESS DSNREXX "EXECSQL CALL SCHEMA.PROCNAME USING DESCRIPTOR :SQLDA"

IF SQLCODE = 0 THEN DO
DO I = 1 TO SQLDA.SQLD
SAY SQLDA.I.SQLNAME " = " SQLDA.I.SQLDATA
SAY "TYPE = " SQLDA.I.SQLTYPE
SAY "IND = " SQLDA.I.SQLIND
SAY "LENGTH = " SQLDA.I.SQLLEN
END
END

Two things are floating in my mind...

1) Will there be any issue with SQLDA structures supported by REXX-DB2
2) What are the possible values for SQLIND field

Hope this helps,

Thanks,
Akhil

akhi007 · New User Joined: 30 Jan 2006 Posts: 35 Location: Richmond

Forget to copy the initialization of SQLDATA before calling...

SQLDA.1.SQLNAME = INREC
SQLDA.1.SQLTYPE = 453
SQLDA.1.SQLLEN = 10
SQLDA.1.SQLIND = 0
SQLDA.1.SQLDATA = "input "

SQLDA.2.SQLNAME = OUTREC1
SQLDA.2.SQLTYPE = 449
SQLDA.2.SQLLEN = 32000
SQLDA.2.SQLIND = 0
SQLDA.2.SQLDATA = " "

SQLDA.3.SQLNAME = OUTREC2
SQLDA.3.SQLTYPE = 453
SQLDA.3.SQLLEN = 150
SQLDA.3.SQLIND = 0
SQLDA.3.SQLDATA = " "

dbzTHEdinosauer · Posted: Fri Sep 10, 2010 8:40 pm

thx for getting back with the resolution.

akhi007 · New User Joined: 30 Jan 2006 Posts: 35 Location: Richmond

@ dbzTHEdinosauer

I did not copy paste the initialize statements for SQLDATA in my earlier post.

That is why I posted it again... Issue still persists..