Hello,

I have encountered a problem with "iAnywhere.Data.SqlAnywhere"
Provider.

Using:
- Sybase Sql Anywhere Developer Edition (v 11.0.1.2250)
- .NET framework 2.0

Case:
When calling a stored procedure, the resultset is empty!
This only occurs when using a 'DECLARE LOCAL TEMPORARY TABLE...'
inside the stored procedure.

When using "oledb" instead, the resultset is correct.

Does anyone have any thoughts or experience with this kind of
behavior.

Thank you in advance

Martin

By default, .NET connections run in autocommit mode. Can you define the
temporary table with an ON COMMIT PRESERVE ROWS clause. If this
addresses the issue, you will likely want to start using SATransaction -
this will adds transactional support to the .NET implementation. Or you
can leave the ON COMMIT clause.

-chris
Etroid wrote:

Ok, now I know where the problem lies and can work around it. However,
It seems like an odd decision to autocommit or put a transaction block
around it whereas only a select query (with a derived table put into a
temp table) is executed. May I ask if this is because of a .net
restriction?

CREATE PROCEDURE "DBA"."FM_Dummy_Dummy"()
BEGIN
//Temporary Table
DECLARE LOCAL TEMPORARY TABLE Temp_Dummy(
Dummy1 Integer NOT NULL,
Dummy2 Char(3) NOT NULL,
Dummy3 Char(5) NOT NULL);

//Insert into Temp_DocumentSaldo
INSERT INTO Temp_Dummy(Dummy1, Dummy2, Dummy3)
SELECT 1, '111', '11111'
FROM DUMMY;

SELECT *
FROM Temp_Dummy td;
END


Anyways,

Thank you for the help

Martin

On 21 sep, 14:34, "Chris Keating (Sybase iAnywhere)"
<keating_nos... (AT) sybase (DOT) com> wrote:

The .NET framework operates in autocommit mode by default. Transactional
support requires the developer to conscientiously choose a transaction
model (starting in .Net 2, there are three models to choose
SqlTransaction, Enterprise services transactions, System.Transactions).

By default, the temporary table design opts to remove rows at commit
time - thus limiting the lifetime of the temporary data. Generally, this
works well in a transactional based application where the data is only
of interest for the current transaction. However, autocommit mode means
that each statement is commit'd so the temporary table rows are deleted
as part of the statement that puts that data into place.

-chris




Etroid wrote: