Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 4 of 4 total |
"Error Creating Table Handle" when running scripts in my Delphi app, but not in EDBManager |
Tue, Apr 26 2011 6:15 PM | Permanent Link |
Adam Brett Orixa Systems | 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? |
Wed, Apr 27 2011 2:57 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Wed, Apr 27 2011 11:04 AM | Permanent Link |
Adam Brett Orixa Systems | >>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! |
Wed, Apr 27 2011 11:35 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |