Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 11 total |
Assistance needed with SP |
Mon, Mar 17 2008 12:35 PM | Permanent Link |
Uli Becker | 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. |
Mon, Mar 17 2008 2:29 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, Mar 18 2008 7:00 AM | Permanent Link |
Uli Becker | 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 |
Tue, Mar 18 2008 7:27 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Tue, Mar 18 2008 11:51 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Uli,
<< When executing it I get this error: >> Is line 18 the UPDATE statement ? -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Mar 18 2008 11:55 AM | Permanent Link |
Uli Becker | 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 |
Tue, Mar 18 2008 4:59 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Mar 24 2008 1:22 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Mon, Mar 24 2008 2:09 PM | Permanent Link |
Uli Becker | <<
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 |
Mon, Mar 24 2008 9:17 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |