|
View previous topic :: View next topic
|
| Author |
Message |
Rahul_kumar
Warnings : 2
New User
Joined: 24 Jun 2006
Posts: 50
|
|
|
|
Hi All,
I am facing problem while fetching data from DB2 tables.The query is giving me correct output in SPUFI but while trying to do the same in the program I am able to fetch only one record for beta_nr but the Kunde_nr is being fetched correctly.Please suggest what might be the problem or any alternate workaround will also be appreciated.
The query is as below:
Declaration of Cursor
EXEC SQL DECLARE KUND_BETA CURSOR FOR
SELECT DISTINCT C.KUNDE_NR,B.BETA_NR
FROM RABAT_AFTALE A
,RABAT_MODTAGER B
,RABAT_CU_KUND C
WHERE (B.RABAT_MODTAGER_ID = A.RABAT_MODTAGER_ID
AND C.RABAT_KUNDEN_ID = A.RABAT_KUNDEN_ID)
QUERYNO 1
END-EXEC.
Opening the Cursor : before performing the fetch loop
EXEC SQL OPEN KUND_BETA END-EXEC.
Fetch :This is being done in a loop with the first fetch before the loop and then calling the fetch section in a inline perform untill sqlcode =+100.I am able to fetch the Kunde-Nr but Beta-nr is only getting fetched once.
EXEC SQL FETCH KUND_BETA
INTO :DCLRABAT-CU-KUND.KUNDE-NR,
:DCLRABAT-MODTAGER.BETA-NR
END-EXEC.
Closing the Cursor:This is being done once the loop for fetching is complete
EXEC SQL CLOSE KUND_BETA END-EXEC.
Can anyone please suggest what might be the problem.
Thanks in Advance,
Rahul |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Suggest the program code is incorrect.
Why are all of the fetches not done within the loop? |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10872
Location: italy
|
|
|
|
| Quote: |
| Why are all of the fetches not done within the loop? |
to avoid an if inside the loop
( general logic - not necessarily db2 related )
| Code: |
call some_function(retcode,parm1,parm2,.....)
do while ( retcode = 0 )
....
.... carry on some process
....
call some_function(retcode,parm1,parm2,.....)
end |
as opposed to
| Code: |
do forever
call some_function(retcode,parm1,parm2,.....)
if retcode <not_equal> 0 then leave
....
.... carry on some process
....
end
|
the language mimics rexx , but the logic is sound |
|
| Back to top |
|
|
Rahul_kumar
Warnings : 2
New User
Joined: 24 Jun 2006
Posts: 50
|
|
|
|
| I have even tried to perform the fetch section twice before the loop still it fetched the beta_nr only once.Kindly note that I am able to fetch the kunde number for each fetch. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Rahul_kumar wrote: |
| till it fetched the beta_nr only once |
Rahul_kumar,
the quoted phrase is confusing. What do you mean by this? |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
Rahul_kumar,
are you saying that the FETCH does not populate all host variables? That is BS.
I have no idea what your code looks like, but keep in mind that you are populating two different structures with your FETCH:
:DCLRABAT-CU-KUND for KUNDE-NR,
and
:DCLRABAT-MODTAGER for BETA-NR |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| I have even tried to perform the fetch section twice before the loop still it fetched the beta_nr only once |
How does this manifest itself? Why do you believe beta_nr was "fetched only once"?
Does the code check the sqlcode before using the host variables returned from a fetch?
Keep in mind that your code is not the same as the spufi. . .
It sounds like the program code to deal with the cursor is the problem. . .
You might isolate the sql from this program into a small test program that only declares and opens the cursor and in a loop fetchs all of the results from the cursor open and displays them. No real logic whatsoever - just the sql to process the cursor. |
|
| Back to top |
|
|
Succor
New User
Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)
|
|
|
|
Rahul_kumar, The best way to understand how your cursor is working is to go by what Dick has suggested in his last post.
If the results are not what you have seen in SPUFI then you might try looking for the database base your query is hitting in your program.
Remember the creator you would have given while fetching data in SPUFI.
WTH. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
keep in mind the TS is complaining that only one of the two host variables is being populated
that would have nothing to do with creator
- though that is often a problem in other threads. |
|
| Back to top |
|
|
Succor
New User
Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)
|
|
|
|
 ..i am getting so confused with this KUNDE_NR and BETA_NR, Obviously the FETCH must be populating both the fields , its just that one has a value and the other having spaces or anything depending on data type.
So, I just thought of the scenario where for one Creator the value is populated for both the variables and for other its only for one.
May be I am thinking too much  . |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| May be I am thinking too much |
Happens a lot when the TS does not follow up with needed info and we start using our imagination  |
|
| Back to top |
|
|
Succor
New User
Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)
|
|
|
|
| Quote: |
| Happens a lot when the TS does not follow up with needed info and we start using our imagination |
That is so true , having said that ,Dick do you really think my assumption is an impossible scenario which can never occur and it should not be considered while resolving this issue. |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Quote: |
| That is so true , having said that ,Dick do you really think my assumption is an impossible scenario which can never occur and it should not be considered while resolving this issue. |
Succor,
Since I made the original criticism, and my name is also Dick,
I will answer with my reasoning.
Actually, my criticism was for the TS, for making such a comment:
no way in hell will a FETCH populate only some of the host variables.
(I consider NULL-Indicators a host variable - without which a negative SQLCODE would be returned).
you got caught in my sights, because I did not want the TS
to even try to think that DB2 was selectively populating host variables.
I assumed that the TS was not looking in the proper structure for the host variable.
I will give you the fact that different CREATORs (different tables)
could/would return SPACES for one of the host variables
and
that is the reason the TS thinks that one of the host variables is not being populated. |
|
| Back to top |
|
|
Succor
New User
Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)
|
|
|
|
| Quote: |
| that would have nothing to do with creator |
I reckon people have mis understood you in past and they are still doing, irrespective of everything its great to learn things from you people. |
|
| Back to top |
|
|
|
|
