The "Prepare" command in a script is great. However when a script contains multiple EXECUTE IMMEDIATEs any of these may fail to run ... which is slow to debug.

It would be great to have a "Prepare All" which iterated the internal SQL Statements called by the SCRIPT & tested / prepared them ... to speed up debugging.

Adam


Much as I like the idea I have a sneaky suspicion it will be near to impossible to implement. EXECUTE IMMEDIATE simply acts on a string. This may be in place at the time or built as part of the script, using data entirely from the text of the script, parameters, or results of actions in the script.

If Tim can produce something to usefully cope with this I for one will be more than a little astounded

Roy Lambert

Yup ... I realised it wouldn't be easy ... but it would be really, really nice.

In DBISAM if you had a multi-part SQL statement separated by ";" it ran a prepare on all the parts ... vastly easier I know ... but a similar process in some respects.

Adam

>In DBISAM if you had a multi-part SQL statement separated by ";" it ran a prepare on all the parts ... vastly easier I know ... but a similar process in some respects.

Unfortunately, according to my guesses, not similar at all

With the DBISAM scripts you have a semicolon separated list of valid (we hope) SQL statements with the only possibility of variation being parameter substitution. With ElevateDB scripts EXECUTE IMMEDIATE will end up (we again hope) as a valid SQL statement but along the way it can change from static text to parameter substitution to being built through the script to being the parameter.

Little example

SET Creator = 'SELECT ';
SET Creator = Creator + '_CallID, '
....
+'C._Website, '
+'IFNULL(P._Name THEN '''' ELSE ''Parent: ''+ P._Name) AS _Parent ';
....
IF Mode = 'Callbacks' THEN
SET Creator = Creator + ' _CallBack';
ELSEIF Mode = 'Alarms' THEN
....
ELSEIF (Mode = 'Refresh') OR (Mode = 'Singleton') THEN
SET Creator = Creator + ' _CallID = ' + CAST(CallID AS VARCHAR);
END IF;
EXECUTE IMMEDIATE 'CREATE TABLE "'+ tblName +'" AS '+ Creator + ' WITH DATA'; <<<<<<<<<< you want Tim to check this

The total script is c140 LOC

or how about this script (it creates an in-memory table)

SCRIPT (IN SQLStatement VARCHAR, IN TableName VARCHAR, IN IdxSet VARCHAR)
BEGIN     
DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt;
DECLARE ResultCursor SENSITIVE CURSOR FOR ResultStmt;

PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?';
OPEN InfoCursor USING TableName;

IF (ROWCOUNT(InfoCursor) > 0) THEN
 EXECUTE IMMEDIATE 'DROP TABLE "'+TableName+'"';
END IF;

CLOSE InfoCursor;

PREPARE ResultStmt FROM 'CREATE TABLE "'+TableName+'" AS '+SQLStatement+' WITH DATA';
EXECUTE ResultStmt;

IF IdxSet IS NOT NULL THEN
 PREPARE ResultStmt FROM 'CREATE INDEX Idx ON "'+TableName+'"('+IdxSet+')';
 EXECUTE  ResultStmt;
END IF;
END


Here the complete SQL select statement used in extracting the data from disk bound tables is passed in as a parameter.

Now I have no problems with winding Tim up a bit - but I do draw the line at driving him totally round the bend

Roy Lambert

Adam,

<< The "Prepare" command in a script is great. However when a script
contains multiple EXECUTE IMMEDIATEs any of these may fail to run ... which
is slow to debug. >>

Unfortunately, Roy is correct.  A script is like a program, and not just a
series of unrelated SQL statements like with DBISAM.  The only way for EDB
to prepare all of the dynamic statements in a script is to execute the
script.  As Roy points out, the PREPARE and EXECUTE IMMEDIATE statements can
prepare/execute SQL statements that are literal strings, or the SQL
statements could be variables or other types of expressions that may depend
upon the script execution up until the current point in the script.

--
Tim Young
Elevate Software
www.elevatesoft.com