I run the same script in an EDBScript component in my application and in a new Script window in EDBManager.

In Delphi I get the error "Error Creating Table Handle", although the script seems to have run correctly.

In EDBManager the script runs without reporting an error.

Is this an error I can just ignore? What does it mean?

Typical Script:

--START

SCRIPT
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 'CurrentValuationsStocks';
IF (ROWCOUNT(InfoCursor) > 0) THEN
EXECUTE IMMEDIATE 'DROP TABLE CurrentValuationsStocks';
END IF;
PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?';
OPEN InfoCursor USING 'MaxIDsStocks';
IF (ROWCOUNT(InfoCursor) > 0) THEN
EXECUTE IMMEDIATE 'DROP TABLE MaxIDsStocks';
END IF;
PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?';
OPEN InfoCursor USING 'CurrentHoldings';
IF (ROWCOUNT(InfoCursor) > 0) THEN
EXECUTE IMMEDIATE 'DROP TABLE CurrentHoldings';
END IF;
PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?';
OPEN InfoCursor USING 'Accrued';
IF (ROWCOUNT(InfoCursor) > 0) THEN
EXECUTE IMMEDIATE 'DROP TABLE Accrued';
END IF;
PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?';
OPEN InfoCursor USING 'Divis';
IF (ROWCOUNT(InfoCursor) > 0) THEN
EXECUTE IMMEDIATE 'DROP TABLE Divis';
END IF;

EXECUTE IMMEDIATE
' CREATE TABLE MaxIDsStocks
(  ID,
 StockID)
AS SELECT
 MAX(ID) as ID,
 StockID
FROM Valuations
GROUP BY StockID
WITH DATA ';

EXECUTE IMMEDIATE
' CREATE TABLE CurrentValuationsStocks
( ID,
 StockID,
 Price)
AS SELECT
 ID,
 StockID,
 Price
FROM Valuations
WHERE ID IN
(SELECT ID FROM MaxIDsStocks)
WITH DATA ';

EXECUTE IMMEDIATE
' CREATE TABLE CurrentHoldings
( StockID,
 PriceHolding,
 QtyHolding)
AS SELECT
 StockID,
 SUM(UnitPrice*CurrentHolding) as PriceHolding,
 SUM(CurrentHolding) as QtyHolding

FROM Purchases
GROUP BY StockID
WITH DATA ';
EXECUTE IMMEDIATE
' CREATE TABLE Accrued
( StockID,
 ValueAccrued)
AS SELECT
 P.StockID,
 SUM(S.ValueAccrued) as ValueAccrued
FROM Purchases P
 LEFT JOIN Sales S ON S.PurchasesID=P.ID
GROUP BY P.StockID
WITH DATA ';

EXECUTE IMMEDIATE
' CREATE TABLE Divis
( StockID,
 TotalDivi)
AS SELECT
 P.StockID,
 SUM(D.TotalValue) as TotalDivi

FROM Dividends D
LEFT JOIN Purchases P ON (P.ID=D.PurchasesID)
GROUP BY P.StockID
WITH DATA ';

END

--END SCRIPT

--

I am guessing it may have something to do with CREATING & then QUERYING tables in a single script ... is there a need to call some extra function such as the old FLUSHBUFFERS from DBISAM?

Adam

The one thing I can see in your script that I'd do different is place an explicit CLOSE cursor prior to reusing it.


Remember EDBManager is just a Delphi app using ElevateDB. If there's a difference in behaviour somewhere there's a difference in coding. If the script runs cleanly in EDBManager I'd suspect something you're doing

Looking in the pdf help I find

Open
Use this method only when you know that the script specified
in the SQL property will return a result set. Using the Open
method with a script that does not return a result set will
result in an EDatabaseError exception being raised with an
error message "Error creating table handle".

The same is for a query rather than a script so I'd suspect that one or more of the statements isn't creating a result set.

So the standard question - is this against the same database. If not are there differences?

Are you running F/S or C/S. If the latter it may be network latency (a very wild guess)

Roy Lambert [Team Elevate]

>>Open
>>Use this method only when you know that the script specified
>>in the SQL property will return a result set. Using the Open
>>method with a script that does not return a result set will
>>result in an EDatabaseError exception being raised with an
>>error message "Error creating table handle".

Thanks Roy, I think it is a real school-boy error on my part ... calling

Script.Active:= true;

rather than

Script.ExecScript;

--

I guess I am still feeling my way into EDB as a product & trying to get a feel for how far it is possible to push it.

I am wondering just how long & convoluted SQL SCRIPTS can become before you start to hit issues like the DB not recognising the existence of a table you have created a few milli-seconds before & what operational changes need to be made to make SCRIPTS work in that sort of situation.

... so far I haven't hit these types of limits & EDB seems a fabulous product I am really enjoying working with it properly at last!

Adam

>I guess I am still feeling my way into EDB as a product & trying to get a feel for how far it is possible to push it.

My feeling was it was nice when I stopped trying to use my head as a battering ram

>I am wondering just how long & convoluted SQL SCRIPTS can become before you start to hit issues like the DB not recognising the existence of a table you have created a few milli-seconds before & what operational changes need to be made to make SCRIPTS work in that sort of situation.

Someone did post one recently - creating a procedure in a script and then trying to call it.

Roy Lambert [Team Elevate]