Anyone - how do I reference parameters in a script? The ramname doesn't seem to work and I can't find anything in the help.

SCRIPT
BEGIN
EXECUTE IMMEDIATE
'UPDATE BandA SET _Unread = NULL, _OnFile = NULL
WHERE _BoxNo BETWEEN :LowBox AND :HighBox';

EXECUTE IMMEDIATE
'CREATE TABLE "M1" AS
SELECT _BoxNo, COUNT(_MsgUnread) AS _Unread FROM EMails
WHERE _BoxNo BETWEEN :LowBox AND :HighBox AND _MsgUnread GROUP BY _BoxNo
WITH DATA';

EXECUTE IMMEDIATE
'UPDATE BandA SET _Unread = M1._Unread
FROM BandA JOIN M1 ON M1._BoxNo = BandA._BoxNo
WHERE _BoxNO BETWEEN :LowBox AND :HighBox';

EXECUTE IMMEDIATE 'DROP TABLE "M1"';

EXECUTE IMMEDIATE
'CREATE TABLE "M1" AS
SELECT _BoxNo, COUNT(_BoxNo) AS _OnFile FROM EMails
WHERE _BoxNo BETWEEN :LowBox AND :HighBox GROUP BY _BoxNo
WITH DATA';

EXECUTE IMMEDIATE
'UPDATE BandA SET _OnFile = M1._OnFile FROM
BandA JOIN M1 ON M1._BoxNo = BandA._BoxNo
WHERE _BoxNo BETWEEN :LowBox AND :HighBox';

EXECUTE IMMEDIATE 'DROP TABLE "M1"';
            
END


Roy Lambert

Roy,

<< Anyone - how do I reference parameters in a script? The ramname
doesn't seem to work and I can't find anything in the help. >>

Dynamic parameters are referenced with the USING clause in conjunction with
the EXECUTE statement:

SCRIPT
BEGIN
DECLARE stmt STATEMENT;

PREPARE stmt FROM 'UPDATE BandA SET _Unread = NULL, _OnFile = NULL
WHERE _BoxNo BETWEEN :LowBox AND :HighBox';

EXECUTE stmt USING 100, 200;

etc.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim
OK, but how do I apply them from outside the statement?

eg

Recount.Close;
Recount.ParamByName('LowBox').AsInteger := StdBoxLow;
Recount.ParamByName('HighBox').AsInteger := StdBoxHigh;
Recount.ExecScript;

Roy Lambert

Tim


Just to extend my knowledge I tried

SCRIPT
BEGIN
DECLARE stmt STATEMENT;
PREPARE stmt FROM 'SELECT * FROM BandA
WHERE _BoxNo BETWEEN :LowBox AND :HighBox';
EXECUTE stmt USING 100, 200;
END

in EDBManager and it doesn't like it. Replace :LowBox and :HighBox with ? and its happy. Every example I can spot just uses ? never a named parameter. Also I can't see any example of reusing parameters. I thought I had in one of Gordon Turner's posts but I lost the trail here

<<As for the issue, you need to include a USING clause when you execute
parameterized DML statements so that EDB has a parameter value to use for
the statement.>>

Does this mean I have to 1) alter all the parameter names in my script to ? and after each statement have an Execute stmt USING to give the parameter names and sequence so they can be supplied from outside?

I'd also read somewhere about a prompt popping up in EDBManager to get the parameter values but can't get that to work. I did find out as part of that that I couldn't use a : as a preface to the variable names in the USING clause.

A still very confused Roy Lambert

Tim


Trying out my idea I altered my script, but get Error 707 at line 919 column 22, expected variable name but instead found LowBox (see the

I then added a DECLARE for the parameters and that disappeared but I then got an variable LowBox not found so I created a couple of parameters (LowBox and HighBox) and the next error is 700 at line 21 column 2.  Expected end of expression but found FROM.

Apart from being well out of my depth I am completely at a loss as to where to start counting from, and more importantly when to stop.

This leads to an interesting thought (I can guess the answer) is there a way to step through these sort of scripts to aid in debugging?

I just tried it in EDBManager and got ElevateDB Error #700 An error was found in the statement at line 15 and column 21 (Expected SELECT, TABLE but instead found  )

Roy Lambert

SCRIPT
BEGIN
DECLARE LowBox INTEGER;
DECLARE HighBox INTEGER;
DECLARE Block1 STATEMENT;
DECLARE Block2 STATEMENT;
DECLARE Block3 STATEMENT;
DECLARE Block4 STATEMENT;
DECLARE Block5 STATEMENT;

PREPARE Block1 FROM
'UPDATE BandA SET _Unread = NULL, _OnFile = NULL WHERE _BoxNo BETWEEN ? AND ?';

PREPARE Block2 FROM
'CREATE TABLE "M1" AS  SELECT _BoxNo, COUNT(_MsgUnread) AS _Unread FROM EMails
WHERE _BoxNo BETWEEN ? AND ? AND _MsgUnread GROUP BY _BoxNo
WITH DATA';

PREPARE Block3 FROM
'UPDATE BandA SET _Unread = M1._Unread
FROM BandA JOIN M1 ON M1._BoxNo = BandA._BoxNo
WHERE _BoxNO BETWEEN ? AND ?';

EXECUTE IMMEDIATE 'DROP TABLE "M1"';

PREPARE Block4 FROM
'CREATE TABLE "M1" AS
SELECT _BoxNo, COUNT(_BoxNo) AS _OnFile FROM EMails
WHERE _BoxNo BETWEEN ? AND ? GROUP BY _BoxNo
WITH DATA';

PREPARE Block5 FROM
'UPDATE BandA SET _OnFile = M1._OnFile FROM
BandA JOIN M1 ON M1._BoxNo = BandA._BoxNo
WHERE _BoxNo BETWEEN ? AND ?';

EXECUTE Block1 USING LowBox, HighBox;
EXECUTE Block2 USING LowBox, HighBox;
EXECUTE Block3 USING LowBox, HighBox;
EXECUTE IMMEDIATE 'DROP TABLE "M1"';
EXECUTE Block4 USING LowBox, HighBox;
EXECUTE Block5 USING LowBox, HighBox;
EXECUTE IMMEDIATE 'DROP TABLE "M1"';

END

Tim

I think I'm getting nearer. I've spotted the JOIN's I left in. I now have the code below and EDBManager is objecting to the use of M1 in Block3

ElevateDB Error #700 An error was found in the statement at line 21 and column 21 (ElevateDB Error #401 The table or view M1 does not exist)

How do I tell it it does? The stuff I can find about DECLARE and CURSOR doesn't seem appropriate, or at least I can't get it to work.


Roy Lambert

SCRIPT
BEGIN
DECLARE LowBox INTEGER;
DECLARE HighBox INTEGER;
DECLARE Block1 STATEMENT;
DECLARE Block2 STATEMENT;
DECLARE Block3 STATEMENT;
DECLARE Block4 STATEMENT;
DECLARE Block5 STATEMENT;

PREPARE Block1 FROM
'UPDATE BandA SET _Unread = NULL, _OnFile = NULL WHERE _BoxNo BETWEEN ? AND ?';

PREPARE Block2 FROM
'CREATE TEMPORARY TABLE "M1" AS  SELECT _BoxNo, COUNT(_MsgUnread) AS _Unread FROM EMails
WHERE _BoxNo BETWEEN ? AND ? AND _MsgUnread GROUP BY _BoxNo
WITH DATA';

PREPARE Block3 FROM
'UPDATE BandA SET _Unread =
SELECT _Unread FROM M1 WHERE M1._BoxNo = BandA._BoxNo
WHERE _BoxNO BETWEEN ? AND ?';

EXECUTE IMMEDIATE 'DROP TABLE "M1"';

PREPARE Block4 FROM
'CREATE TEMPORARY TABLE "M1" AS
SELECT _BoxNo, COUNT(_BoxNo) AS _OnFile FROM EMails
WHERE _BoxNo BETWEEN ? AND ? GROUP BY _BoxNo
WITH DATA';

PREPARE Block5 FROM
'UPDATE BandA SET _OnFile =
SELECT _OnFile FROM M1 WHERE M1._BoxNo = BandA._BoxNo
WHERE _BoxNo BETWEEN ? AND ?';

EXECUTE Block1 USING LowBox, HighBox;
EXECUTE Block2 USING LowBox, HighBox;
EXECUTE Block3 USING LowBox, HighBox;
EXECUTE IMMEDIATE 'DROP TABLE "M1"';
EXECUTE Block4 USING LowBox, HighBox;
EXECUTE Block5 USING LowBox, HighBox;
EXECUTE IMMEDIATE 'DROP TABLE "M1"';

END

Tim


Latest iteration is below. I've gotten rid of the temporary tables a) I couldn't get them to work and b) I'm not sure how much if any faster it would be.

Still can't get EDBManager to prompt me for values so I have no idea what its actually doing. Off to try in my app now after a quick reboot cos D2006 went bonkers.

And my app doesn't recognise any parameters. I think the problem may be that I have defined them but when I go and look in the .dfm all I find is Params = <>, I've set this about 8 billion times but something has been zaping it.

Finally I have something working. Thank you for listening. Any thoughts, suggestions, or comments gratefully received, especially on how to get the parameters recognised in EDBManager.


Roy Lambert

SCRIPT
BEGIN
DECLARE LowBox INTEGER;
DECLARE HighBox INTEGER;
DECLARE Block1 STATEMENT;
DECLARE Block3 STATEMENT;
DECLARE Block5 STATEMENT;

PREPARE Block1 FROM
'UPDATE BandA SET _Unread = NULL, _OnFile = NULL
WHERE _BoxNo BETWEEN ? AND ?';

PREPARE Block3 FROM
'UPDATE BandA SET _Unread =
(SELECT COUNT(_MsgUnread) AS _Unread FROM EMails
WHERE EMails._BoxNo = BandA._BoxNo
AND _MsgUnread)
WHERE _BoxNO BETWEEN ? AND ?';

PREPARE Block5 FROM
'UPDATE BandA SET _OnFile =
(SELECT COUNT(_BoxNo) FROM EMails
WHERE EMails._BoxNo = BandA._BoxNo)
WHERE _BoxNo BETWEEN ? AND ?';

EXECUTE Block1 USING LowBox, HighBox;
EXECUTE Block3 USING LowBox, HighBox;
EXECUTE Block5 USING LowBox, HighBox;

END

Roy,

<< OK, but how do I apply them from outside the statement? >>

Two things.  I left in the : parameter names in there, and it should be this
instead:

SCRIPT
BEGIN
DECLARE stmt STATEMENT;

PREPARE stmt FROM 'UPDATE BandA SET _Unread = NULL, _OnFile = NULL
WHERE _BoxNo BETWEEN ? AND ?';

EXECUTE stmt USING 100, 200;

END

Secondly, if you want the parameters to be specified for the script itself,
then specify them after the SCRIPT keyword like this:

SCRIPT (LowBox INTEGER, HighBox INTEGER)
BEGIN
DECLARE stmt STATEMENT;

PREPARE stmt FROM 'UPDATE BandA SET _Unread = NULL, _OnFile = NULL
WHERE _BoxNo BETWEEN ? AND ?';

EXECUTE stmt USING LowBox, HighBox;

END

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


Eureka!!!!!

If and when you ave a mo I'd appreciate you comments about my final version (the one at the bottom of my ramblings) and any recommendations for speeding it up.

Roy Lambert

Roy,

<< If and when you ave a mo I'd appreciate you comments about my final
version (the one at the bottom of my ramblings) and any recommendations for
speeding it up. >>

Why, is it particularly slow ?

--
Tim Young
Elevate Software
www.elevatesoft.com