Hi,

I want to get a float value from Table "Saetze" and update Table
"Posten" with this value. I tried this code in a SP:

BEGIN
  DECLARE NewSatzA float;
  DECLARE NewSatzT float;
  DECLARE NewSatzL float;
  DECLARE Result CURSOR WITH RETURN FOR Stmt;
    PREPARE Stmt FROM
     'select A,T,L from saetze where saetzeID = ?';
    Execute Stmt using SaetzeID;
    OPEN Result;
      Fetch FIRST from Result(A) into NewSatzA;
      Fetch FIRST from Result(T) into NewSatzT;
      Fetch FIRST from Result(L) into NewSatzL;
    CLOSE Result;

    PREPARE Stmt FROM
      'update posten set faktor = ' + cast(NewSatzA as VarChar(5))
       + ' where RechnungenID = ?';
    Execute stmt using RechnungenID;
END

When executing it I get this error:

ElevateDB Error #700 An error was found in the statement at line 18 and
column 10 (Missing INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, GRANT,
REVOKE, BACKUP, RESTORE, REPAIR, OPTIMIZE, IMPORT, EXPORT, DISCONNECT,
REMOVE)

What am I doing wrong?

Thanks Uli.

Uli


This one works

SCRIPT
BEGIN

DECLARE USERID VARCHAR;
DECLARE Source CURSOR FOR Selector;

PREPARE Selector FROM 'SELECT _ID FROM Users WHERE _Title IS NULL';

OPEN Source;

FETCH FIRST FROM Source(_ID) INTO USERID;

EXECUTE IMMEDIATE 'UPDATE Cardex SET _fkUsers = '''+USERID+'''';

END

Roy Lambert

Roy

You are using a string variable.

It has to do with the fetch-line. When I assign a value manually, it works:

   OPEN Result;
     Fetch FIRST from Result(A) into NewSatzA;
     Fetch FIRST from Result(T) into NewSatzT;
     Fetch FIRST from Result(L) into NewSatzL;
   CLOSE Result;

==> Set NewSatzA = 2.3;

   PREPARE Stmt FROM...

I have no idea what's wrong here.

Regards Uli

Uli


Just tried this and it works

SCRIPT
BEGIN

DECLARE Tst FLOAT;
DECLARE Source CURSOR FOR Selector;

PREPARE Selector FROM 'SELECT _Probability FROM Baysian WHERE _Token = ''qaz3''';

OPEN Source;

FETCH FIRST FROM Source(_Probability) INTO Tst;

EXECUTE IMMEDIATE 'UPDATE Bayesian SET _Probability = ' + CAST(Tst AS VARCHAR);

END

Roy Lambert

Uli,

<< When executing it I get this error: >>

Is line 18 the UPDATE statement ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< Is line 18 the UPDATE statement ?>>

Yes, it is. I just found the reason for the problem myself. When I replace:

 Execute Stmt using SaetzeID;
 OPEN Result;

by

 OPEN Result using SaetzeID;

everything works (though I do not completely understand why). Otherwise there is a null
result. The following error message is a bit misleading.

Regards Uli

Uli,

<< by

 OPEN Result using SaetzeID;

everything works (though I do not completely understand why). Otherwise
there is a null result. The following error message is a bit misleading. >>

Yes, it is.  I'll try it here and see why it's issuing that error message.
It should just try and re-exeute the statement when you call OPEN after
calling EXECUTE.

--
Tim Young
Elevate Software
www.elevatesoft.com

Uli,

<< Yes, it is. I just found the reason for the problem myself. >>

Actually, the reason for the problem is that the NewSatzA variable is NULL
in some cases, which causes the concatenation to result in a NULL statement
to prepare, hence the error.

--
Tim Young
Elevate Software
www.elevatesoft.com

<<
Actually, the reason for the problem is that the NewSatzA variable is NULL
in some cases, which causes the concatenation to result in a NULL statement
to prepare, hence the error.
>>

Hmm... Actually the NewSatzA variable cannot be Null in no case. So I don't understand it.

Uli

Uli,

<< Hmm... Actually the NewSatzA variable cannot be Null in no case. So I
don't understand it. >>

You're executing the OPEN without the USING clause, and since you're
executing it *after* the EXECUTE clause, it is freeing the old result set
and generating a new, empty one.

The key here is that you were using EXECUTE *and* OPEN, when you only needed
to use OPEN:

SCRIPT (IN SaetzeID INTEGER, IN RechnungenID INTEGER)
BEGIN
 DECLARE NewSatzA float;
 DECLARE NewSatzT float;
 DECLARE NewSatzL float;
 DECLARE Result CURSOR WITH RETURN FOR Stmt;
   PREPARE Stmt FROM
    'select A,T,L from saetze where saetzeID = ?';
   OPEN Result using saetzeid;
     Fetch FIRST from Result(A) into NewSatzA;
     Fetch FIRST from Result(T) into NewSatzT;
     Fetch FIRST from Result(L) into NewSatzL;
   CLOSE Result;

   PREPARE Stmt FROM
     'update posten set faktor = ' + cast(NewSatzA as VarChar(20)) + '
where RechnungenID = ?';
   Execute stmt using RechnungenID;
END


--
Tim Young
Elevate Software
www.elevatesoft.com