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;

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

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

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