Unexpected exception object raised

Hi,

I have a stored function that keeps raising a exception when run, could
anyone please show me where the problem may be.

So far I have only been able to test it with DB Workbench.

When I start commenting code out, it seems to be the update section
that is causing the problem, but its such a simple line of code I can
not understand what the problem is.

Many thanks in advance.

Kind Regards
Grant Brown

************ Exception Text **************

NexusDB: <unnamed TnxQuery instance>: Query execution failed:
Unexpected exception object raised: [Exception]
Functions that modify data may not be called in this
context:F_GetReffNumber [$3CA1/15521]

********* Stored function code *************

begin
Declare CurValue Integer default 1;

set CurValue = ((select CurrentValue
from ReferenceNumbers
where DetectID = 1) + 1);

update ReferenceNumbers
set CurrentValue = CurValue
where DetectID = 1;

RETURN CurValue;
end;

--

> When I start commenting code out, it seems to be the update section
> that is causing the problem, but its such a simple line of code I can
> not understand what the problem is.

The problem, as seen from the SQL engine, is spelled out within the error
message:

> ************ Exception Text **************
> Functions that modify data may not be called in this
> context:F_GetReffNumber [$3CA1/15521]

What is not clear from your post, however, is what context you are using it
in (as I said in the support group, you need to show us how you are using
the function). If it is not possible to understand your scenario, it is not
likely to result in any helpful hints.

--

Eivind Bakkestuen
Nexus Database Systems Pty Ltd

My applogies, but the problem is not spelled out by the error message
otherwise I would not be asking the question in the first place.

In fact the error message is completely un-helpful.

The context, which you insist on receiveing is really simple.

I tried to create a stored function inside DB Workbench and the
function compiled without error.

When I tried to run the function inside DB Workbench, by clicking on
the "Data/Results" tab I get the error message.

The purpose of the function is really simple,

1/ Read the current value stored in select record.

2/ Add 1 to the old value

3/ Replace the old value with the new value

4/ Return the new value to the user application as the result of the
function.

Please, could someone from NDB respond urgently.

Grant

Eivind Bakkestuen [NDD] wrote:

> > When I start commenting code out, it seems to be the update section
> > that is causing the problem, but its such a simple line of code I
> > can not understand what the problem is.
>
> The problem, as seen from the SQL engine, is spelled out within the
> error message:
>
> > ************ Exception Text **************
> > Functions that modify data may not be called in this
> > context:F_GetReffNumber [$3CA1/15521]
>
> What is not clear from your post, however, is what context you are
> using it in (as I said in the support group, you need to show us how
> you are using the function). If it is not possible to understand your
> scenario, it is not likely to result in any helpful hints.



--

Grant Brown wrote:
> My applogies, but the problem is not spelled out by the error message
> otherwise I would not be asking the question in the first place.
>
> In fact the error message is completely un-helpful.
>
> The context, which you insist on receiveing is really simple.
>
> I tried to create a stored function inside DB Workbench and the
> function compiled without error.
>
> When I tried to run the function inside DB Workbench, by clicking on
> the "Data/Results" tab I get the error message.
>
> The purpose of the function is really simple,
>
> 1/ Read the current value stored in select record.
>
> 2/ Add 1 to the old value
>
> 3/ Replace the old value with the new value
>
> 4/ Return the new value to the user application as the result of the
> function.
>
> Please, could someone from NDB respond urgently.
>
> Grant

Post the entire function. Post how your calling it.
There are several ways you can be in a context that doesn't
allow updates. Posting just the code inside a function
is leaving out everything that determines the context
that code runs in.

--
Brian Evans [NDX]
Ottawa, ON, CANADA
GMT-5

Heres the function,

I am NOT calling it from my Delphi code YET,

I AM trying to run it by clicking on the "Data/Results" tab I get the
error message.

Grant

======= Function code ==============

CREATE FUNCTION F_GetReffNumber()
RETURNS Integer
MODIFIES SQL DATA
begin
Declare CurValue Integer default 1;

set CurValue = ((select CurrentValue
from ReferenceNumbers
where DetectID = 1) + 1);

update ReferenceNumbers
set
CurrentValue = CurValue
where DetectID = 1;

RETURN CurValue;
end;


Brian Evans [NDX] wrote:

> Grant Brown wrote:
> > My applogies, but the problem is not spelled out by the error
> > message otherwise I would not be asking the question in the first
> > place.
> >
> > In fact the error message is completely un-helpful.
> >
> > The context, which you insist on receiveing is really simple.
> >
> > I tried to create a stored function inside DB Workbench and the
> > function compiled without error.
> >
> > When I tried to run the function inside DB Workbench, by clicking on
> > the "Data/Results" tab I get the error message.
> >
> > The purpose of the function is really simple,
> >
> > 1/ Read the current value stored in select record.
> >
> > 2/ Add 1 to the old value
> >
> > 3/ Replace the old value with the new value
> >
> > 4/ Return the new value to the user application as the result of the
> > function.
> >
> > Please, could someone from NDB respond urgently.
> >
> > Grant
>
> Post the entire function. Post how your calling it.
> There are several ways you can be in a context that doesn't
> allow updates. Posting just the code inside a function
> is leaving out everything that determines the context
> that code runs in.



--

> I AM trying to run it by clicking on the "Data/Results" tab I get the
> error message.

Thanks for posting the full function; now we can get to work.

Ok, the problem is the following:

In SQL, it is not allowed to call functions that modify data from an outer
execution context that doesnt modify data.

I am guessing that what Database Workbench does, is try to call your
function using something like the following:

SELECT F_GetReffNumber() FROM #DUMMY;

The problem is that SELECT implies read-only, and because the function is
flagged as MODIFIES SQL DATA, the error is thrown. This is in accordance
with SQL processing rules.

What you can do, and likely what DBW should do too (feel free to report the
problem to them) is to call the function using a construct like:

Declare CurValue Integer ;
set CurValue = F_GetReffNumber();
SELECT CurValue FROM #Dummy;


--

Eivind Bakkestuen
Nexus Database Systems Pty Ltd

> I am guessing that what Database Workbench does, is try to call your
> function using something like the following:
>
> SELECT F_GetReffNumber() FROM #DUMMY;
>
> The problem is that SELECT implies read-only, and because the function is
> flagged as MODIFIES SQL DATA, the error is thrown. This is in accordance
> with SQL processing rules.
>
> What you can do, and likely what DBW should do too (feel free to report
the
> problem to them) is to call the function using a construct like:
>
> Declare CurValue Integer ;
> set CurValue = F_GetReffNumber();
> SELECT CurValue FROM #Dummy;

Nail, head.

Thanks, it's fixed now and will be in the next update.


--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com