|
View previous topic :: View next topic
|
| Author |
Message |
shaktiprasad
New User
Joined: 20 Aug 2006
Posts: 34
Location: chennai
|
|
|
|
Hi ,
I am just trying to move the sqlerrd(3) to some other variable with datatype as 9(9) and displaying it after opening and then fetching one cursor.Though the cursor is returning some finite number of rows,but the display is showing 0 records in both the places(after opening and after fetching also).
I thought there would have been some problem with display so directly checked the variable sqlerrd(3) if it's not = 0 then display "non zero record" else display 'zero records". it's going to else part only.
I know that sqlerrd(3) should give number of records processed after successful completion of the query.But not sure why it's not working.
some body plz let me know if any possible cause of the scenario.
thanks in advance |
|
| Back to top |
|
 |
Ketan Varhade
Active User
Joined: 29 Jun 2009
Posts: 197
Location: Mumbai
|
|
|
|
| check with the get dignostic and compare the results? |
|
| Back to top |
|
|
shaktiprasad
New User
Joined: 20 Aug 2006
Posts: 34
Location: chennai
|
|
|
|
Hi Ketan ,
Thanks for the reply.Would plz let me know what do you exactly mean by comparing diagonostic results? |
|
| Back to top |
|
|
Ketan Varhade
Active User
Joined: 29 Jun 2009
Posts: 197
Location: Mumbai
|
|
|
|
| I am asking to use the get dignostic and then move the desired variable and check what value is it displaying. Get dignostic is used for getting the number of rows been fetch. Are you using this in Multi row set ? |
|
| Back to top |
|
|
shaktiprasad
New User
Joined: 20 Aug 2006
Posts: 34
Location: chennai
|
|
|
|
Yes I am using it for multi row fetch. Actually my cursor is only simple select statement and there is one constaraint if the number of rows fetch is more than some number then do not populate the records else if numebr of rows is less than or equal to the number then only populate.
So I just want to check the actual number rows fetched.
After successful Open or Fetch statement SQLERRD(3) should give the actual number of rows right! |
|
| Back to top |
|
|
shaktiprasad
New User
Joined: 20 Aug 2006
Posts: 34
Location: chennai
|
|
|
|
| Ketan Varhade wrote: |
| I am asking to use the get dignostic and then move the desired variable and check what value is it displaying. Get dignostic is used for getting the number of rows been fetch. Are you using this in Multi row set ? |
Again would plz let me know the syntax to use get dioagnostic. This can also solve my problem a su said it would give number of rows fetched. |
|
| Back to top |
|
|
Ketan Varhade
Active User
Joined: 29 Jun 2009
Posts: 197
Location: Mumbai
|
|
|
|
| Code: |
| GET DIAGNOSTICS rcount = ROW_COUNT |
use this rcount will have the number of rows |
|
| Back to top |
|
|
shaktiprasad
New User
Joined: 20 Aug 2006
Posts: 34
Location: chennai
|
|
|
|
| Ketan Varhade wrote: |
| Code: |
| GET DIAGNOSTICS rcount = ROW_COUNT |
use this rcount will have the number of rows |
so ican use it in this way:
exec sql
GET DIAGNOSTICS :rcount = ROW_COUNT
end-exec
where rcount is host variable. |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Shaktiprasad,
| Quote: |
| after opening and then fetching one cursor. |
If you use
| Code: |
| DISPLAY 'SQLERRD(3) = 'SQLERRD(3) |
after opening the cursor, you will get 0. Because, rows are fetched only after FETCH statement is executed.
Can you show us your code. That would be a lot easier to help.
Sushanth |
|
| Back to top |
|
|
Bharath Bhat
Active User
Joined: 20 Mar 2008
Posts: 283
Location: chennai
|
|
|
|
| Quote: |
| Though the cursor is returning some finite number of rows,but the display is showing 0 records in both the places |
How are you so sure that the query is returnig rows? did you execute the query in spufi/qmf? I would suggest you to display SQLERRD(3) after the successful fetch. If it still shows 0, I would believe that SQL is not fetching any row. |
|
| Back to top |
|
|
shaktiprasad
New User
Joined: 20 Aug 2006
Posts: 34
Location: chennai
|
|
|
|
| Bharath Bhat wrote: |
| Quote: |
| Though the cursor is returning some finite number of rows,but the display is showing 0 records in both the places |
How are you so sure that the query is returnig rows? did you execute the query in spufi/qmf? I would suggest you to display SQLERRD(3) after the successful fetch. If it still shows 0, I would believe that SQL is not fetching any row. |
Yeah I saw the records written in the spool. Actually I am fetching the records one by one in the loop and displaying the sqlerrd(3) after the successful fetch. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
| Back to top |
|
|
shaktiprasad
New User
Joined: 20 Aug 2006
Posts: 34
Location: chennai
|
|
|
|
Thanks you.
So sqlerrd(3) gives the number of rows fetched in case of rowset fetch only. Can I use any other variable ex. sqlerrd(1) or sqlerrd(2).What is sensitive static cursor?
or I have to use a host variable to get the count. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
If your code reads one row at a time, simply add 1 to some counter after each row is read and when there are no more rows, you have the number of rows processed. |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
| Quote: |
| Actually I am fetching the records one by one in the loop |
You might need to know the row count for a multiple row fetch, in order to process properly the receiving array;
but, why would You want to get the row count for single row fetch? |
|
| Back to top |
|
|
shaktiprasad
New User
Joined: 20 Aug 2006
Posts: 34
Location: chennai
|
|
|
|
| enrico-sorichetti wrote: |
| Quote: |
| Actually I am fetching the records one by one in the loop |
You might need to know the row count for a multiple row fetch, in order to process properly the receiving array;
but, why would You want to get the row count for single row fetch? |
Hi Please read the discussion from the starting.I have mentioned I have to process only if count is matching a certain number.I am not checking the count after each fetch,I am checking after the loop is over and in case of successful execution of the query. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| I have mentioned I have to process only if count is matching a certain number. |
Then you might consider counting the rows before reading them individually. . . |
|
| Back to top |
|
|
shaktiprasad
New User
Joined: 20 Aug 2006
Posts: 34
Location: chennai
|
|
|
|
| dick scherrer wrote: |
Hello,
| Quote: |
| I have mentioned I have to process only if count is matching a certain number. |
Then you might consider counting the rows before reading them individually. . . |
I can use count(*) ,but there is some performance issue.So planning to execute the fetch for the matching count and then will fetch once again outside the loop. If the outside loop is giving some results then I will process accordingly(discussed with my friends).thanks all for your time. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
You're welcome - good luck 
d |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
| Quote: |
| Hi Please read the discussion from the starting.I have mentioned I have to process only if count is matching a certain number.I am not checking the count after each fetch,I am checking after the loop is over and in case of successful execution of the query. |
why do You complain? reread my post, looks You had troubles understanding it...
do You use multi row fetching/retrieval ?
yes ==> sqlerrd(3) contains the number of rows returned
no ==> You must be using using single row fetch/retrieval
sqlerrd(3) is not set, full stop
and You will have to count the rows by Yourself
for some optimization,
You might get out of the fetch loop after target+1 fetches
how many rows constitute the target ??
if the number is <small> storing the data into an array will certainly give better results
| Quote: |
| sqlerrd(3) not behaving normally |
why blame on somebody else a misunderstanding of the docs ??? |
|
| Back to top |
|
|
|
|
