|
View previous topic :: View next topic
|
| Author |
Message |
Pons
New User
Joined: 25 May 2007
Posts: 61
Location: Coimbatore
|
|
|
|
Hi,
In SPUFI; What ever row i select in the SQL Query return code is display as 100. But it is sussfully select the records and displayed. But why it gave 100 instead of 0 (100 - Row not found, 0 - Successful completion).
Please let me know... |
|
| Back to top |
|
 |
William Thompson
Global Moderator
Joined: 18 Nov 2006
Posts: 3156
Location: Tucson AZ
|
|
|
|
| Is it showing you all the rows? |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
I'd suggest downloading a copy of the DB2 manuals so you will have them closeby (or at least saving the direct link in your favorites).
This is from the DB2 Messages & Codes linked to from thes forums. . .
| Quote: |
| When a SELECT statement is executed using SPUFI, this SQLCODE indicates normal completion. |
Keep in mind that some results (like a 100 return code) depend on what was going at the time the return code was issued (i.e. SPUFI rather than an application program)
When you learn how to look up/verify things, you will get much quicker answers 
We're here when there are additional questions. |
|
| Back to top |
|
|
Pons
New User
Joined: 25 May 2007
Posts: 61
Location: Coimbatore
|
|
|
|
Hi William,
What ever row i select, depending on my condition it will display. But the SQL code is 100.
Thanks dick. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
| You're welcome |
|
| Back to top |
|
|
socker_dad
Active User
Joined: 05 Dec 2006
Posts: 177
Location: Seattle, WA
|
|
|
|
Yes, Pons. When you use SPUFI, your return code is 100 after the execution of the SQL, regardless of whether anything is returned or not.
When I execute SPUFI SQL where there are no matches, I get this:
DSNE610I NUMBER OF ROWS DISPLAYED IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
When there is a match, my row(s) are displayed along with this message:
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
It's a SPUFI thing, mate. It simply means that it found no more rows. |
|
| Back to top |
|
|
Phrzby Phil
Senior Member
Joined: 31 Oct 2006
Posts: 1049
Location: Richmond, Virginia
|
|
|
|
| It's not a SPUFI thing - it means no more records (satisfying the condition if there is a condition). |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Its displays sqlcode 100 because there are no more rows to be fetched
for ex I have a table with 4 records when I select * on that table it gave me all records and with sqlcode =100
| Code: |
---------+---------+---------+---------+---------+---------+-----
SELECT * FROM xxxx.TAB5;
---------+---------+---------+---------+---------+---------+-----
EMPNO EMPID EMPFIELD
---------+---------+---------+---------+---------+---------+-----
7 7 AAA
8 8 AAA
9 9 AAA
10 10 AAA
DSNE610I NUMBER OF ROWS DISPLAYED IS 4
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+-----
|
|
|
| Back to top |
|
|
Pons
New User
Joined: 25 May 2007
Posts: 61
Location: Coimbatore
|
|
|
|
Thanks to all.
So it means, 100 is the SQL code of success and failure for SPUFI. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
Not necessarily (at least not as worded).
In your case, you should get the 100 at "end" or if there are no rows found. The 100 is raised when "ROW NOT FOUND" which might be caused by a few other things also (look in the DB2 Messages and Codes linked to from the forums).
Other "failures" will not generate a 100. |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Pons,
The statement is getting SQLCODE = 100, because "no data" was found. For example, a FETCH statement returned no data because the cursor was positioned after the last row of the result table.It doesnt means that spufi has failed
.The spufi is getting SQLCODE = 100 when it is trying to fetch records from the table which is not there in the table.
see I have a table with no records but when i try to fetch some records using select statement I am getting statement executed succesful and
SQLCODE = 100.
| Code: |
---------+---------+---------+---------+---------+---------+---------+---------+
SELECT * FROM XXXX.ABST; 00010007
---------+---------+---------+---------+---------+---------+---------+---------+
EMPNO EMPID EMPFIELD
---------+---------+---------+---------+---------+---------+---------+---------+
DSNE610I NUMBER OF ROWS DISPLAYED IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
---------+---------+---------+---------+---------+---------+---------+---------+
|
Hope this helps.
Thanks,
Prem |
|
| Back to top |
|
|
Pons
New User
Joined: 25 May 2007
Posts: 61
Location: Coimbatore
|
|
|
|
Thanks dick.
Hi prem,
Thanks for your reply. But my question is why it gave 100 as a sql code which it get the selected fields. I knew if no rows found it will return 100. Even though i fetched my desired row it will gave as 100.
It's an SPUFI thing. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
You're welcome
And yes, SPUFI is designed to return 100 for either case.
There is more info in the DB2 Messages & Codes linked to from the "Manuals" link at the top of the page. |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Pons,
I have also given an example above select statement.Your above explanation has the answer for your doubt.See you know that the above statement has got your desired result.But how come a cursor which is fetching the records from the table would know that there are no more records 
So In my example I fetching 4 rows.But the cursor which is doing the job after the statement doesnt know that there are anymore records to be fetched from the table.So It assumes that there are some more records after fetching the only present 4 records.When it is doing so it passes the signal that there are no more records to fetch.So it stops there and returns SQLCODE = 100 |
|
| Back to top |
|
|
prasadvrk
Active User
Joined: 31 May 2006
Posts: 200
Location: Netherlands
|
|
|
|
| premkrishnan wrote: |
Pons,
I have also given an example above select statement.Your above explanation has the answer for your doubt.See you know that the above statement has got your desired result.But how come a cursor which is fetching the records from the table would know that there are no more records
So In my example I fetching 4 rows.But the cursor which is doing the job after the statement doesnt know that there are anymore records to be fetched from the table.So It assumes that there are some more records after fetching the only present 4 records.When it is doing so it passes the signal that there are no more records to fetch.So it stops there and returns SQLCODE = 100 |
and
the the execution of the statement itself is successful irrespective of the sqlcode that is why it says ?STATEMENT EXECUTION WAS SUCCESSFUL? |
|
| Back to top |
|
|
achittu
New User
Joined: 26 Feb 2007
Posts: 14
Location: chennai
|
|
|
|
If you have 4 rows in your table satisfying your condition , then set
the MAXIMUM ROWS TO BE FETCHED option in the SPUFI to 4. If you set to any value more than 4 it would give SQLCODE 100 at the end. |
|
| Back to top |
|
|
|
|
