Hi,
that is the old 'problem'.
During select which does not result in a copied (physically stored) resultset,
a check is made whether at least one record will fulfill the expected
restrictions.
During this check, the resultrecord is prepared, the qualification handled AND
your function called once.
Then the select 'knows' that its result is NOT 'row not found'. This answer is
returned to the client/the user.
Then the fetching of the resultrows starts, thus preparing the first
resultrecord for the second time,
but now returning it to the client/the user.
--> Only for the first resultrecord the double-handling is made
--> In case your resultset consists of only one resultrecord it looks like
calling the function twice
in every case. But this is not true. You will see if more than one
resultrecord exists.
Then Alexander's advice is a good one.
If you can assure that 0 or 1 resultrecord exists, a select ... into
:parameter_list from ...
will help, to avoid the second call of the function (and will be faster as no
resultset-preparation
has to take place)
Elke
SAP Labs Berlin
Sitz der Gesellschaft/Registered Office: Walldorf, Germany
Vorstand/SAP Executive Board: Henning Kagermann (Sprecher/CEO), Shai Agassi,
Léo Apotheker, Werner Brandt, Claus Heinrich, Gerhard Oswald, Peter Zencke
Vorsitzender des Aufsichtsrats/Chairperson of the SAP Supervisory Board: Hasso
Plattner
Registergericht/Commercial Register Mannheim No HRB 350269
Diese E-Mail kann Betriebs- oder Geschäftsgeheimnisse oder sonstige
vertrauliche Informationen enthalten. Sollten Sie diese E-Mail irrtümlich
erhalten haben, ist Ihnen eine Kenntnisnahme des Inhalts, eine Vervielfältigung
oder Weitergabe der E-Mail ausdrücklich untersagt.
Bitte benachrichtigen Sie uns und vernichten Sie die empfangene E-Mail. Vielen
Dank.
This e-mail may contain trade secrets or privileged, undisclosed, or otherwise
confidential information. If you have received this e-mail in error, you are
hereby notified that any review, copying, or distribution of it is strictly
prohibited. Please inform us immediately and destroy the original transmittal.
Thank you for your cooperation.
> -----Original Message-----
> From: Martin Cordova [mailto:[EMAIL PROTECTED]
> Sent: Mittwoch, 21. März 2007 00:00
> To: maxdb
> Subject: Re: UPDATE T SET X = X + 1 increments by 2 when
> executed inside a function
>
> Hi Alexander,
>
> In fact using FOR REUSE in a SELECT that calls the function, makes it
> work normally. Also using the function in an INSERT statement yields
> the expected result.
>
> Thank you,
> Martin
> --
> Dinamica - RADical J2EE framework
> open source, easy and powerful
> http://www.martincordova.com
>
> On 3/20/07, Schroeder, Alexander <[EMAIL PROTECTED]> wrote:
> > Hello Martin,
> >
> > can you try to execute the SELECT that triggers the
> function with a FOR REUSE added? Does it
> > change anything?
> >
> > Alexander Schröder
> > MaxDB & liveCache
> > SAP AG
> >
> > Sitz der Gesellschaft/Registered Office: Walldorf, Germany
> > Vorstand/SAP Executive Board: Henning Kagermann
> (Sprecher/CEO), Shai Agassi, Léo Apotheker, Werner Brandt,
> Claus Heinrich, Gerhard Oswald, Peter Zencke
> > Vorsitzender des Aufsichtsrats/Chairperson of the SAP
> Supervisory Board: Hasso Plattner
> > Registergericht/Commercial Register Mannheim No HRB 350269
> >
> > Diese E-Mail kann Betriebs- oder Geschäftsgeheimnisse oder
> sonstige vertrauliche Informationen enthalten. Sollten Sie
> diese E-Mail irrtümlich erhalten haben, ist Ihnen eine
> Kenntnisnahme des Inhalts, eine Vervielfältigung oder
> Weitergabe der E-Mail ausdrücklich untersagt.
> > Bitte benachrichtigen Sie uns und vernichten Sie die
> empfangene E-Mail. Vielen Dank.
> >
> > This e-mail may contain trade secrets or privileged,
> undisclosed, or otherwise confidential information. If you
> have received this e-mail in error, you are hereby notified
> that any review, copying, or distribution of it is strictly
> prohibited. Please inform us immediately and destroy the
> original transmittal. Thank you for your cooperation.
> >
> > -----Original Message-----
> > From: Martin Cordova [mailto:[EMAIL PROTECTED]
> > Sent: Dienstag, 20. März 2007 16:14
> > To: maxdb
> > Subject: UPDATE T SET X = X + 1 increments by 2 when
> executed inside a function
> >
> > Hi, I am new to MAXDB functions, today I tried to create one that:
> >
> > 1) updates a table and increments a certain column value
> > 2) returns a string that incorporates that value, combined with
> > another piece of data
> >
> > The thing is that I execute a simple update to increment
> the value by
> > 1, but it get incremented by 2!!!
> >
> > I am missing something, any help will be appreciated.
> >
> > I am using MaxDB 7.6.0.34 on WinXP SP1.
> >
> > The code:
> >
> > CREATE FUNCTION XXX.getNumCorrespE (dependencia_id INTEGER)
> RETURNS VARCHAR AS
> >
> > VAR ano INTEGER; cnum INTEGER; retval VARCHAR(20);
> >
> > TRY
> >
> > SET ano = 0;
> > SET cnum = 0;
> >
> > update XXX.dependencia set num_corresp_e = num_corresp_e + 1
> > where dependencia_id = :dependencia_id;
> >
> > DECLARE functionresult CURSOR FOR
> >
> > select
> > ano,
> > num_corresp_e cnum
> > from XXX.dependencia
> > where
> > dependencia_id = :dependencia_id;
> >
> > WHILE $rc = 0 DO BEGIN
> > FETCH functionresult INTO :ano, :cnum;
> > END;
> > CATCH
> > IF $rc <> 100 THEN STOP ($rc, 'unexpected error');
> > CLOSE functionresult;
> >
> > set retval = chr(ano) || '-' || LFILL(chr(cnum),'0',6);
> >
> > RETURN retval;
> >
> >
>
> > --
> > MaxDB Discussion Mailing List
> > For list archives: http://lists.mysql.com/maxdb
> > To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
>
> --
> MaxDB Discussion Mailing List
> For list archives: http://lists.mysql.com/maxdb
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
