Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 4 of 4 total |
Scripts and COMMIT |
Mon, Sep 5 2016 7:19 PM | Permanent Link |
Andrew Hill | Please advise why this script fails:-
MyScript1.SQL.Clear; MyScript1.Params.Clear; MyScript1.SQL.Add('SCRIPT () '); MyScript1.SQL.Add('BEGIN '); MyScript1.SQL.Add('DELETE FROM FetchData WHERE Dn = :TXT1 AND DnType = 1 ;'); MyScript1.SQL.Add('INSERT INTO FetchData(Dn, DnType, MyCount) VALUES(:TXT2, 1, :NUM2 ;'); MyScript1.ParamByName('TXT1').AsString:= '000'; MyScript1.ParamByName('TXT2').AsString:= '000'; MyScript1.ParamByName('NUM2').AsInteger:= MyCount; MyScript1.SQL.Add('COMMIT '); MyScript1.SQL.Add('END '); MyScript1.ExecScript; |
Tue, Sep 6 2016 3:48 AM | Permanent Link |
Matthew Jones | Andrew Hill wrote:
> Please advise why this script fails:- I know nothing about this, but I have two thoughts. First, in what way does it fail? Might help us answer. And second, my recollection is that setting the params must be done at the end, as changing the script after setting them will clear them. And you add commit and end after setting the params. I may be wrong though on that. -- Matthew Jones |
Tue, Sep 6 2016 4:26 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Andrew
There are several problems. ElevateDB scripts are nothing like DBISAM scripts. For example this line DELETE FROM FetchData WHERE Dn = :TXT1 AND DnType = 1 ; The script has no idea what FetchData is. The simplest solution to to use EXECUTE IMMEDIATE which essentially runs a query so you could use MyScript1.SQL.Add('EXECUTE IMMEDIATE ''DELETE FROM FetchData WHERE Dn = '+QUOTEDSTR(TXT1)+ ' AND DnType = 1'''); The next problem is that the script needs to be fully built and prepared before assigning the parameters. You need a MyScript1.Prepare before MyScript1.ParamByName('TXT1').AsString:= '000'; You also need to move the additional statements to before the script is prepared. The next issue is that you're asking the script to perform a COMMIT when you haven't started a transaction. I don't know what ElevateDB will do here. Personally though, if building the script in code unless its going to be reused I'd just build the the statements directly and use EDBDatabase.Execute to run them There's a lot of good material/examples in the manual / on-line help Roy Lambert |
Tue, Sep 6 2016 7:53 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Andrew,
<< Please advise why this script fails:- >> As Roy indicated: 1) Not using EXECUTE IMMEDIATE for DML statements. 2) No START TRANSACTION, but a COMMIT. Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Tuesday, May 14, 2024 at 07:14 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |