Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Host variables will not be able to read NULL values

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
ranjani
Warnings : 1

New User


Joined: 25 Oct 2006
Posts: 13

PostPosted: Sun Feb 25, 2007 9:59 am    Post subject: Host variables will not be able to read NULL values
Reply with quote

in my table, i have a null value in the NAME field in the 2nd row. When i tried to read the values in the table, using cursor, my cursor fetch returned the same NAME field value as of the first row while fetching the second row 'NAME" field. So is that host variables will not be able to read NULL values. Do we have to use indicator variable and set the column value to SPACES if the indicator value is negative????
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sun Feb 25, 2007 10:24 am    Post subject:
Reply with quote

Hello,

Yes, if the null-indicator is set, either ignore the (previous) value for that field or move spaces (or whatever is appropriate for your situation).
Back to top
View user's profile Send private message
sachin_star3
Warnings : 1

New User


Joined: 30 Sep 2006
Posts: 78
Location: pune

PostPosted: Mon Feb 26, 2007 4:15 pm    Post subject:
Reply with quote

yes you have to move some value when you get null indicator value is negative
because host variable not able to detect the null value ,means host variavles means the variables structured as for hast language i.e. cobol and cobol cannot read the null values so the null indicator comes in picture
i given you programme
check it out
Code:
                                ID DIVISION
                                PROGRAME-ID.  SACHIN.
                                DATA DIVISION.
                                EXEC SQL
                                        INCLUDE MEMBER1
                                 END-EXEC.
                                  EXEC SQL
                                        INCLUDE SQLCA
                                  END-EXEC.
                                   EXEC SQL
                                          DECLARE CURSOR C1 FOR
                                     SELECT * FROM EMP
                                WORKING-STORAGE SECTION
                                  77  N1 PIC 9(4) USAGE COMP.
                       
                                 PROCEDURE DIVISION.
                                      EXEC SQL
                                          OPEN C1
                                      END-EXEC.
                                    PERFORM FETCH-PARA VARYING I FROPM 1 BY 1 UNTIL  SQLCODE NO=100
                                    EXEC SQL
                                       CLOSE C1
                                        END EXEC
                                      STOP RUN.
                                 [b]FETCH-PARA[/b]
                                     EXEC SQL
                                    FETCH INTO :ECODE,:ENAME,:ESAL:N1
                                      END-EXEC.
                                     IF SQLCODE=-1
                                       MOVE  "NULL" TO ESAL
================= FROM-
SACHIN BORASE
PUNE
Back to top
View user's profile Send private message
MoLong

New User


Joined: 07 Feb 2007
Posts: 32
Location: Chennai

PostPosted: Thu Mar 01, 2007 10:42 am    Post subject:
Reply with quote

Hi Sachin,
for the above prg i dont think we need to use the variable I.
Also it is better to compare N1 < 0 rather than SQLCODE.
Back to top
View user's profile Send private message
sachin_star3
Warnings : 1

New User


Joined: 30 Sep 2006
Posts: 78
Location: pune

PostPosted: Sun Mar 04, 2007 3:17 am    Post subject:
Reply with quote

yes that can also work fine
Back to top
View user's profile Send private message
vini_srcna

Active User


Joined: 26 May 2005
Posts: 178
Location: Copenhagen, Denmark

PostPosted: Tue Mar 06, 2007 3:56 pm    Post subject:
Reply with quote

IF SQLCODE=-1 MOVE "NULL" TO ESAL

I dont think you will get a SQLCODE of -1. DB2 just sets the NULL indicator. In the above example it will set NI to -1. SQLCODE would be still zero. (+100 if its end of cursor ).

When DB2 retrieves a NULL value for the query it sets the indicator to -1 and it does nothing to the host variable. So it would contain the same value.

Quote:
So is that host variables will not be able to read NULL values. Do we have to use indicator variable


You cannot read witout using the indicator variable. It would through negative sql code while during fetch (or select ) mostlikely -305.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts Overlaying one set of charater values... Kevin Lindsley SYNCSORT 7 Sat Nov 05, 2016 3:21 am
No new posts Low values Results from VARCHAR FORMAT balaji81_k DB2 10 Thu Oct 20, 2016 1:18 am
No new posts Protection Exception while move 0 to ... Kevin Vaz CICS 10 Tue Oct 18, 2016 4:19 pm
No new posts Syncsort - NULL in Integer field chec... nartcr SYNCSORT 4 Thu Oct 06, 2016 6:47 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us