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?

asked 10 May '11, 05:24

Volker%20Barth's gravatar image

Volker Barth
30.3k300452659
accept rate: 32%

edited 10 May '11, 10:32


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.

permanent link

answered 13 May '11, 06:58

Volker%20Barth's gravatar image

Volker Barth
30.3k300452659
accept rate: 32%

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.

permanent link

answered 11 May '11, 11:25

Reimer%20Pods's gravatar image

Reimer Pods
4.2k334481
accept rate: 12%

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×409
×106
×76

question asked: 10 May '11, 05:24

question was seen: 3,887 times

last updated: 13 May '11, 06:58