DBISQL 12.0.1.3324 does not display the single result set of a stored procedure by default

[VolkerBarth]

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?

[VolkerBarth · Accepted solution]

The underlying follow-up questions w.r.t. to auto_commit behaviour in DBISQL/dbisqlc have been answered in depth by Karim.

Lessons learnt:

1. In case you want to use a stored procedure with an internal local temporary table and want to built your result set based on that table, you should make sure it is used in manual commit mode only or make sure to declare that table with ON COMMIT PRESERVE ROWS or NOT TRANSACTIONAL.

2. If you can't assure manual commit mode and don't declare the table that way, you need to take care:
   The slight differences in auto commit mode (server vs. client) seem to give chance that your local temporary table is (or is not) emptied just before you built the result set - just because an implicit COMMIT may have taken place in-between. That will depend on the API, settings and programs you use.

3. An even better approach might be to get rid of a temporary table altogether, say by using a straight SELECT statement in the procedure's body. SQL Anywhere's rich SQL features - e.g. (recursive) CTEs and the like - might be of help here.