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:

1. The usage of the local temporary table
2. The (non-default) setting of ISQL's option auto_commit = On
3. The default setting of ISQL's option isql_show_multiple_result_sets = Off

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:

• It shows all products, not a particular one

• It uses a local temp. table to store the results temporarily for the sake of this sample.

  :::SQL CREATE PROCEDURE "GROUPO"."ShowProductInfo1"()
  RESULT(ID integer,
  Name char(15),
  Description char(30),
  Size char(18),
  Color char(18),
  Quantity integer,
  UnitPrice decimal(15,2))
  BEGIN
  DECLARE LOCAL TEMPORARY TABLE LT (
  ID integer PRIMARY KEY,
  Name char(15),
  Description char(30),
  Size char(18),
  Color char(18),
  Quantity integer,
  UnitPrice decimal(15,2)
  );
  

  INSERT LT
  SELECT ID, Name, Description, Size, Color, Quantity, UnitPrice
  FROM GROUPO.Products
  ORDER BY ID;
  

  SELECT * FROM LT
  ORDER BY ID;
  END;
  

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?