CODEWITHSUNDEEP
sqlstored-procedures
user739447
user739447

Reputation: 11

IBM SolidDB, fetching result fails due to no result en execute statement

In my stored procedures I'm doing a lot of:

EXEC SQL EXECUTE ...

EXEC SQL FETCH ...

However, I'm getting some errors (Error 23506: End of table in cursor) when the execute statement doesn't return anything and I'm trying to fetch the result. Are there anyway to check if the result contains anything before trying to fetch it?

I've tried SQLSUCCESS and SQLROWCOUNT, but SQLSUCCESS only tells me if the statement doesn't fail, not if it returns anything, and SQLROWCOUNT apparently only works for inserts, updates and deletes. Not select statements.

Upvotes: 1

Views: 437

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95612

This reference compares DB2 and SolidDB procedures.

In the section "Listing 15. SQLSUCCESS showing end of result set", the IBM reference shows this snippet.

EXEC SQL FETCH sel_tab;
WHILE SQLSUCCESS LOOP
    EXEC SQL FETCH sel_tab;
END LOOP

It also says

When the FETCH cursor statement fails and does not find another row to retrieve, the value of SQLSUCCESS is set to 0 and the WHILE LOOP ends.

That pretty much tells me that the WHILE loop shouldn't even be entered if that first EXEC SQL FETCH doesn't find a row. But you seem to be suggesting that isn't happening in your code.

Later, in "Table 7. solidDB SQLERROR of cursorname statement", it shows this code. (My annotations.)

"CREATE PROCEDURE tabs_in_schema (schema_nm 
     VARCHAR) RETURNS (nr_of_rows INTEGER)
BEGIN
   DECLARE tab_nm VARCHAR;
   EXEC SQL PREPARE sel_tab                     -- A SELECT statement
    SELECT table_name FROM sys_tables
                   WHERE table_schema = ?;
  EXEC SQL PREPARE ins_tab
        INSERT INTO my_table (table_name, 
           schema) VALUES (?,?);
  nr_of_rows := 0;
  EXEC SQL EXECUTE sel_tab USING                -- Executes the SELECT
         (schema_nm)INTO (tab_nm);
  EXEC SQL FETCH sel_tab;                       -- EXEC SQL FETCH first row
  WHILE SQLSUCCESS LOOP                         -- Like listing 15
      nr_of_rows := nr_of_rows + 1;
     EXEC SQL EXECUTE ins_tab USING
           (tab_nm, schema_nm);
     IF SQLROWCOUNT <> 1 THEN
         RETURN SQLERROR OF ins_tab;
    END IF;
    EXEC SQL FETCH sel_tab;                     -- FETCH subsequent rows
  END LOOP
END";

I suppose you could execute something like

SELECT COUNT(your_column_name) 
FROM your_table
WHERE ...;

COUNT() will always return at least one row as long as your query is valid. But that involves more round trips to the database. I think you're better off sticking to the idiom of trying to fetch a row, and trapping the error.

Upvotes: 1

Related Questions