Another developer and I were tracing the Pro*C code for an error that was occurring in the production environment. The code was fetching the results of a cursor that was opened. It was expecting exactly one record back. Any other number caused an error to occur. Our users were getting the error. So we assumed that multiple records were coming back. Our mission was to figure out why.This was a difficult problem to resolve. We could not make the problem happen in the development environment. We were not authorized to execute the code in production. And every time there was an error, the application cleaned up after itself, deleting the records we needed to inspect in order to understand the problem.
My coworker came up with a theory. The code was using sqlerrd[2] from the SQL common area (SQLCA) to determine how many records were retrieved from the last fetch. This technique is sometimes used to check the count of the total number of records retrieved from multiple fetches. Therefore my coworker concluded that perhaps there were multiple fetches performed, and that those old fetches were being counted in sqlerrd[2].
This did not feel right to me. I know we have a lot of code where we do a fetch, and look to sqlerrd[2] to see how many records we just got back. However I was willing to entertain and test out whether this theory held any water. It was through this testing that I got a better appreciation of what sqlerrd[2] represents.
First I tested two fetches from different cursors. In this scenario, sqlerrd[2] held the count of the records from the latest fetch. I even tried this when both queries were executed by opening and closing the same cursor. The results were the same. The count held the number of records from the latest fetch. The only time sqlerrd[2] would have more than the latest fetch is if you opened the cursor, fetched some records, and finally fetched some more records from the same cursor that remained open.
The sqlerrd[2] value is maintained for each cursor that you have open. It has the total count of records that you have fetched (possibly through multiple fetches) since you have opened that cursor. Sometimes you really need to know what is going on to solve tough problems
