Error: Explicit transaction in progress, nested transaction are not supported ...

Hi team,

Please enlighten me on this error ... I understood that nested transactions
ARE supported in NexusDB 2.07.06 ... So why can't I use nested START
TRANSACTION statements? Is that a flaw in de SQL engine?

TIA

Bert Moorthaemer

"Bert Moorthaemer" <bert.moorthaemer@blumex.com> wrote in message
news:483bf9da@77-37-8-25....
> Hi team,
>
> Please enlighten me on this error ... I understood that nested
> transactions ARE supported in NexusDB 2.07.06 ... So why can't I use
> nested START TRANSACTION statements? Is that a flaw in de SQL engine?

It's a limitation. The back-end supports nested transactions - the SQL
engine currently does not.

- Per

Hi Per,

If it's being implemented I suppose this will not be part of the current
version?

Cheers

Bert

"Per Larsen" <per@trash.can> wrote in message news:483c573a$1@77-37-8-25....
>
> "Bert Moorthaemer" <bert.moorthaemer@blumex.com> wrote in message
> news:483bf9da@77-37-8-25....
>> Hi team,
>>
>> Please enlighten me on this error ... I understood that nested
>> transactions ARE supported in NexusDB 2.07.06 ... So why can't I use
>> nested START TRANSACTION statements? Is that a flaw in de SQL engine?
>
> It's a limitation. The back-end supports nested transactions - the SQL
> engine currently does not.
>
> - Per
>
>

"Bert Moorthaemer" <bert.moorthaemer@blumex.com> wrote in message
news:483d8843$1@77-37-8-25....
> Hi Per,
>
> If it's being implemented I suppose this will not be part of the current
> version?

I'm not aware of any immediate plans. Hannes/Eivind will have the final
word.

- Per

Bert Moorthaemer wrote:

> Hi Per,
>
> If it's being implemented I suppose this will not be part of the current
> version?

That's correct. It won't be added to V2

--

Hi Hannes,

Bummer ... For this particular project I have to switch to another RDBMS
then ...

Thx,

Bert

"Hannes Danzl[NDD]" <hannes@nexusdb.dbnexus.com> wrote in message
news:xn0fqr2ez2ox7nj000@news.nexusdb.com...
> Bert Moorthaemer wrote:
>
>> Hi Per,
>>
>> If it's being implemented I suppose this will not be part of the current
>> version?
>
> That's correct. It won't be added to V2
>
> --
>
>

Bert Moorthaemer wrote:

> Hi Hannes,
>
> Bummer ... For this particular project I have to switch to another RDBMS
> then ...

Hm. What are the requirements for this? Just wondering.

--

Hannes,

I need to be able to nest transactions inside stored procedures called from
triggers, e.g.

proc2
begin
start transaction;
try
do something;
commit;
catch all
rollback;
signal;
end
end

proc1
begin
start transaction;
try
do something;
commit;
catch <some condition>
rollback;
call proc2; // do something else by calling proc2
catch all
rollback;
signal;
end
end

before_trigger
begin
call proc1
end


if I do this, and an transaction is already in progress I get the error
mentioned although I did not explicit call Database.StartTransaction. The
transaction is an implicit transaction started by the server itself ... all
I do is call either Table.Append or Table.Edit followed with a Table.Post;

Bert.

"Hannes Danzl[NDD]" <hannes@nexusdb.dbnexus.com> wrote in message
news:xn0fqskwz49rlvf002@news.nexusdb.com...
> Bert Moorthaemer wrote:
>
>> Hi Hannes,
>>
>> Bummer ... For this particular project I have to switch to another RDBMS
>> then ...
>
> Hm. What are the requirements for this? Just wondering.
>
> --
>
>

Bert Moorthaemer wrote:

> Hannes,
>
> I need to be able to nest transactions inside stored procedures called from
> triggers, e.g.

hm. i don't want to question your design BUT (you knew that's coming )
relying on such a kind of construct is in the best case going to be a hog on
the server. I would think that the base problem can be solved with a different
design that does not rely on exception handling.

--

Hannes,

Ha! But what if I want to put ALL business logic inside the database ???
Essentially I do not want to put it inside my code because the database has
to be used by all kinds of different clients (even ones I do not control)

Getting to the "different" design ... In case an update to a table that has
no *direct* relation to the table being updated fails, I still need the
record to be written and absolutely no rollback to occur ... for that only
exception handling is the solution. And secondly, all major database updates
should through functions and procedures (enables me to verify the data
before its being written) Therefore I need the possibility to nest
transactions very very urgently ... (pls pls)

You see, I took a look at MySQL, but that is not a candidate because of the
lack of proper exception handling (very very "Basic" like "ON ERROR GOTO"
constructs) and MSSQL is no option either, because you get a bunch of stuff
that I absolutely not need and it is expensive also (not even to forget the
NON-ANSI dialect of Transact/SQL)

Thx,

Bert.

"Hannes Danzl[NDD]" <hannes@nexusdb.dbnexus.com> wrote in message
news:xn0fqt66g52j59l00a@news.nexusdb.com...
> Bert Moorthaemer wrote:
>
>> Hannes,
>>
>> I need to be able to nest transactions inside stored procedures called
>> from
>> triggers, e.g.
>
> hm. i don't want to question your design BUT (you knew that's coming )
> relying on such a kind of construct is in the best case going to be a hog
> on
> the server. I would think that the base problem can be solved with a
> different
> design that does not rely on exception handling.
>
> --
>
>