Running a 188.8.131.5224 engine, I have problems when displaying the single result set of a stored procedure with DBISQL 184.108.40.20624.
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
using dbisqlc 220.127.116.1124 or DBISQL 18.104.22.1687 (with default options), the result set is displayed as expected.
However, with DBISQL 22.214.171.12424, 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 126.96.36.1997 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.
answered 13 May '11, 06:58
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.
answered 11 May '11, 11:25