Running a 12.0.1.3324 engine, I have problems when displaying the single result set of a stored procedure with DBISQL 12.0.1.3324. This (quite simple) proc is basically defined as create procedure dbo.MyProc(in nValue int) result (nCol1 int, nCol2 int, nCol3 int) begin ... -- create a temp table LT to store the results -- and use a cursor to fill it declare local temporary table LT ( ... -- The final one and only SELECT select nCols1, nCol2, nCol3 from LT; end; When calling this procedure via call MyProc(1); using dbisqlc 12.0.1.3324 or DBISQL 11.0.1.2527 (with default options), the result set is displayed as expected. However, with DBISQL 12.0.1.3324, the result set is only displayed when option "Display multiple result sets" is set. (The other result set options don't seem to have an influence). Note that still only one result set is displayed (as the proc does not generate more)... In contrast, calling select * from MyProc(1); does work with that DBISQL version, too. Don't know whether this is a bug or a feature:) EDIT: The cause seems to be related to at least three points:
I have to correct myself w.r.t. v11: DBISQL 11.0.1.2527 does show the same behaviour when auto_commit is set to 'On' (which was not in my first tests). Here's a full sample taken from the v12 demo database. I added the following procedure, which is a simple variation of the sample ShowProductInfo() proc:
Note: When declaring the temporary table with NOT TRANSACTIONAL, the result set is shown immediately - independent of the setting of isql_show_multiple_result_sets. I'm still not sure why an INSERT (which does an COMMIT in auto_commit mode) would prevent the display of the only result set - I don't think the INSERT SELECT should be counted as a separate result set... Or have I just been trapped by another dbisqlc/DBISQL difference? |
The underlying follow-up questions w.r.t. to auto_commit behaviour in DBISQL/dbisqlc have been answered in depth by Karim. Lessons learnt:
|
AFAIK by default temporary tables get cleared after a commit or rollback. As you found out, that behaviour changes if you specify NOT TRANSACTIONAL. Another way would be to add ON COMMIT PRESERVE ROWS to the tempory table declaration. So IMHO the autocommit clears the temp table and the result set would be empty, if you don't apply one of the modifications above. I strongly agree that NOT TRANSACTIONAL would be helpful here - the temp. table is not (yet) declared that way as it is migrated from v8, and v8 does not allow that clause. Besides that, I would understand if the proc would always return an empty result set when auto_commit is set to 'On' (though I would not be sure if that would meet my expectation of auto_commit). But the obvious dependency on the isql_show_multiple_result_sets and the fact, that dbisqlc does display the desired result set even if auto_commit is set, and the desired result set when using the "SELECT FROM proc" syntax seem to contradict your suggestions. IMHO, auto_commit should add a commit after each statement sent to the engine - i.e. after the proc call in my case. It should however not commit every statement done within a proc.
(11 May '11, 11:51)
Volker Barth
|