SQLTYPE=448 out of SQLDA for column declared as CHAR

Mike O'Connell

SQLTYPE=448 out of SQLDA for column declared as CHAR

Hello, 

Disclaimer : I am fairly new to DB2.
Problem: I am looking at 

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/sqlref/src/tpc/db2z_sqltypeandsqlleninsqlda.html

which indicates sqltype  for CHAR columns should be 452/453. 

and my inquiry against Sysibm.syscolumns  for the table in question shows type=char for the column in question.

But my SQLDA info in a COBOL application indicates SQLTYPE=448 (which is varchar). My code is handling  SQLLEN wrong (i.e. 00128) instead of whatever method is needed in this situation (obvious DB2 knows it's a char and not a varchar).

Any advice here?

The column was defined as  

"USER"  char(4) NOT NULL  

I assume this has something to do with the double-quotes required by the reserved-name. 

Please know that this is not my DB2 system, and it is not an option to "stop using USER as a column name". 

 

Thanks in advance,

 

Mike

 

 

 

 

 

 

Mike in the UK 

Binyamin Dissen

SQLTYPE=448 out of SQLDA for column declared as CHAR
(in response to Mike O'Connell)
On Thu, 9 Nov 2017 09:20:05 -0700 (MST) Mike O'Connell <[login to unmask email]>
wrote:

:>Hello, 
:>Disclaimer : I am fairly new to DB2.
:>Problem: I am looking at 
:>https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/sqlref/src/tpc/db2z_sqltypeandsqlleninsqlda.html
:>which indicates sqltype  for CHAR columns should be 452/453. 
:>and my inquiry against Sysibm.syscolumns  for the table in question shows type=char for the column in question.
:>But my SQLDA info in a COBOL application indicates SQLTYPE=448 (which is varchar). My code is handling  SQLLEN wrong (i.e. 00128) instead of whatever method is needed in this situation (obvious DB2 knows it's a char and not a varchar).
:>Any advice here?
:>The column was defined as  
:>"USER"  char(4) NOT NULL  
:>I assume this has something to do with the double-quotes required by the reserved-name. 
:>Please know that this is not my DB2 system, and it is not an option to "stop using USER as a column name". 

Well, ......

DB2 knows what the column looks like and your SQLDA indicates how you would
like to see it. If DB2 can do the conversion, it will do it seamlessly.

Do you have a problem?

--
Binyamin Dissen <[login to unmask email]>
http://www.dissensoftware.com

Director, Dissen Software, Bar & Grill - Israel


Should you use the mailblocks package and expect a response from me,
you should preauthorize the dissensoftware.com domain.

I very rarely bother responding to challenge/response systems,
especially those from irresponsible companies.

Michael Hannan

RE: SQLTYPE=448 out of SQLDA for column declared as CHAR
(in response to Mike O'Connell)

Mike,

It might have been helpful if you included the actual SQL Statement, since certain manipulations of CHAR columns in SQL can result in VARCHAR answers.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Suresh Sane

SQLTYPE=448 out of SQLDA for column declared as CHAR
(in response to Mike O'Connell)
Mike,


Without confirming whether a scalar function you are using (e.g. SUBSTR) is changing the CHAR to VARCHAR, it is hard to know for sure.


When you state SQLDA, I assume you are referring to the "receiving" one (results from DB2) as opposed to "sending" (input to the SQL).


Although somewhat dated now, the best references for dynamic SQL in embedded applications with real examples of COBOL is this Redbook (see chapter 3). It will show you how to handle the length variable. Please take a look.


http://www.redbooks.ibm.com/redbooks/pdfs/sg246418.pdf


Thx

Suresh


________________________________
From: Mike O'Connell <[login to unmask email]>
Sent: Thursday, November 9, 2017 4:20 PM
To: [login to unmask email]
Subject: [DB2-L] - SQLTYPE=448 out of SQLDA for column declared as CHAR


Hello,

Disclaimer : I am fairly new to DB2.
Problem: I am looking at

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/sqlref/src/tpc/db2z_sqltypeandsqlleninsqlda.html

which indicates sqltype for CHAR columns should be 452/453.

and my inquiry against Sysibm.syscolumns for the table in question shows type=char for the column in question.

But my SQLDA info in a COBOL application indicates SQLTYPE=448 (which is varchar). My code is handling SQLLEN wrong (i.e. 00128) instead of whatever method is needed in this situation (obvious DB2 knows it's a char and not a varchar).

Any advice here?

The column was defined as

"USER" char(4) NOT NULL

I assume this has something to do with the double-quotes required by the reserved-name.

Please know that this is not my DB2 system, and it is not an option to "stop using USER as a column name".



Thanks in advance,



Mike













Mike in the UK

-----End Original Message-----

Michael Hannan

RE: SQLTYPE=448 out of SQLDA for column declared as CHAR
(in response to Suresh Sane)

 In Reply to Suresh Sane:

Mike,

Without confirming whether a scalar function you are using (e.g. SUBSTR) is changing the CHAR to VARCHAR, it is hard to know for sure.

As a curiosity, almost the only string functions that return a fixed length CHAR datatype are SUBSTR(expr, start, length) or CHAR(expr, length) (or CAST equivalent) where length is a literal constant value. Even then, I think length may have to be < 256. A trap to fall into on DSNTIAUL extracts is that LEFT(expr, length) and similar functions return VARCHAR (not desired).  

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Mike O'Connell

RE: SQLTYPE=448 out of SQLDA for column declared as CHAR
(in response to Mike O'Connell)

Gentlemen,

thanks for the feedback. To clarify, I am reading receiving-SQLDA. I will look into the DB2 redbook - although i'm a bit wary of a tech reference dated 2002!!!

Mike in the UK