Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 10 of 11 total |
DBISAM to EDB Conversion work, more findings |
Mon, Apr 25 2011 6:14 PM | Permanent Link |
Adam Brett Orixa Systems | Hi All.
I am converting number of apps from DBISAM to EDB. I had hoped to keep the process simple, mapping 1 DBISAM component to 1 EDB component, i.e. DBISAMQuery -> EDBQuery. This is mostly very effective. However, I have quite a lot of multi-step SQL in DBISAM, of a type noted elsewhere: DROP TABLE IF EXISTS MEMORY\[something]; SELECT [fields] INTO MEMORY\[something] FROM Tables; ... i.e. The SQL has a series of steps, creating result-sets, these are then summed or unioned or grouped to produce a final result set which the application uses. -- I have written a set of code to convert my DBISAM Statements into EDB Scripts (I've posted this in the binaries) However, EDBScript components don't see to be returning datasets with this way of working (??) ... anyway I get "error creating table handle" messages when I actually run the scripts in my app, although the scripts run fine in EDBMgr (though I don't see a result-set there either). -- ORIGINAL DBISAM STATEMENT: DROP TABLE IF EXISTS MEMORY\CurrentValuationsStocks ; DROP TABLE IF EXISTS MEMORY\MaxIDsStocks ; DROP TABLE IF EXISTS MEMORY\CurrentHoldings ; DROP TABLE IF EXISTS MEMORY\Accrued ; DROP TABLE IF EXISTS MEMORY\Divis ; SELECT MAX(ID) as ID, StockID INTO MEMORY\MaxIDsStocks FROM Valuations GROUP BY StockID ; SELECT ID, StockID, Price INTO MEMORY\CurrentValuationsStocks FROM Valuations WHERE ID IN ( SELECT ID FROM MEMORY\MaxIDsStocks ) ; SELECT StockID, SUM(UnitPrice*CurrentHolding) as PriceHolding, SUM(CurrentHolding) as QtyHolding INTO MEMORY\CurrentHoldings FROM Purchases GROUP BY StockID ; SELECT P.StockID, SUM(S.ValueAccrued) as ValueAccrued INTO MEMORY\Accrued FROM Purchases P LEFT JOIN Sales S ON S.PurchasesID=P.ID GROUP BY P.StockID ; SELECT P.StockID, SUM(D.TotalValue) as TotalDivi INTO MEMORY\Divis FROM Dividends D LEFT JOIN Purchases P ON (P.ID=D.PurchasesID) GROUP BY P.StockID ; SELECT S.ID, S.Symbol, S.Name, S.Description, S.Dividend, S.SharesInIssue*CV.Price as Capitalization, CAST(CAST(CAST(S.Dividend/CV.Price*100 as FLOAT(7,2)) AS VARCHAR(5)) as FLOAT(3,2)) as DividendRate, CAST(CAST(CAST((CV.Price-MyBuyPrice)/MyBuyPrice*100 as FLOAT(7,2)) AS VARCHAR(5)) as FLOAT(3,2)) as FromMyBuy, CAST(CAST(CAST((MySellPrice-CV.Price)/MySellPrice*100 as FLOAT(7,2)) AS VARCHAR(5)) as FLOAT(3,2)) as FromMySell, T.Name as StockType, T1.Name as BaseCurrency, T2.Name as PerceivedRisk, CAST(S1.LogicalOrder AS VARCHAR(2)) +'. ' + S1.Name as Status, CV.Price as CurrentPrice, CH.PriceHolding, CH.QtyHolding, CV.Price*CH.QtyHolding as HoldingValue, CV.Price*CH.QtyHolding-CH.PriceHolding as GainLoss, DV.TotalDivi, A.ValueAccrued, CV.Price*CH.QtyHolding-CH.PriceHolding + IF(A.ValueAccrued=null then 0.00 else A.ValueAccrued) + IF(DV.TotalDivi=Null then 0.00 else DV.TotalDivi) as NetPosition FROM Stocks S LEFT JOIN MEMORY\CurrentValuationsStocks CV ON (CV.StockID=S.ID) LEFT JOIN Status S1 ON (S.StatusID = S1.ID) LEFT JOIN Types T ON (S.StockTypeID = T.ID) LEFT JOIN Types T1 ON (S.BaseCurrencyID = T1.ID) LEFT JOIN Types T2 ON (S.PerceivedRiskID = T2.ID) LEFT JOIN MEMORY\CurrentHoldings CH ON (CH.StockID=S.ID) LEFT JOIN MEMORY\Accrued A ON (A.StockID=S.ID) LEFT JOIN MEMORY\Divis DV ON (DV.StockID=S.ID) %s -- --EDB SCRIPT: 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 '; EXECUTE IMMEDIATE 'SELECT S.ID, S.Symbol, S.Name, S.Description, S.Dividend, S.SharesInIssue*CV.Price as Capitalization, ROUND(S.Dividend/CV.Price*100 TO 2) as DividendRate, ROUND((CV.Price-MyBuyPrice)/MyBuyPrice*100 TO 2) as FromMyBuy, ROUND((MySellPrice-CV.Price)/MySellPrice*100 TO 2) as FromMySell, T.Name as StockType, T1.Name as BaseCurrency, T2.Name as PerceivedRisk, CAST(S1.LogicalOrder AS VARCHAR(2)) +''. '' + S1.Name as Status, CV.Price as CurrentPrice, CH.PriceHolding, CH.QtyHolding, CV.Price*CH.QtyHolding as HoldingValue, CV.Price*CH.QtyHolding-CH.PriceHolding as GainLoss, DV.TotalDivi, A.ValueAccrued, CV.Price*CH.QtyHolding-CH.PriceHolding + IF(A.ValueAccrued=null then 0.00 else A.ValueAccrued) + IF(DV.TotalDivi=Null then 0.00 else DV.TotalDivi) as NetPosition FROM Stocks S LEFT JOIN CurrentValuationsStocks CV ON (CV.StockID=S.ID) LEFT JOIN Status S1 ON (S.StatusID = S1.ID) LEFT JOIN Types T ON (S.StockTypeID = T.ID) LEFT JOIN Types T1 ON (S.BaseCurrencyID = T1.ID) LEFT JOIN Types T2 ON (S.PerceivedRiskID = T2.ID) LEFT JOIN CurrentHoldings CH ON (CH.StockID=S.ID) LEFT JOIN Accrued A ON (A.StockID=S.ID) LEFT JOIN Divis DV ON (DV.StockID=S.ID) '; END -- Any help gratefully received! Thanks Adam |
Tue, Apr 26 2011 3:08 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
Unlike DBISAM where the last result set became the result set for the script in ElevateDB you have to explicitly tell it to give you a result set eg SCRIPT(IN ContactID INTEGER, CompanyID INTEGER, IgnoreContact BOOLEAN, IgnoreCompany BOOLEAN) BEGIN DECLARE Query VARCHAR; DECLARE Test INSENSITIVE CURSOR WITH RETURN FOR stmt; <<<<<<<<<<<<<<<<<<<<<<<<<< SET Query = 'SELECT _ID, _fkSites, _fkCompanies, _fkContacts FROM Career WHERE _Left IS NULL'; IF NOT IgnoreContact THEN SET Query = Query + ' AND _fkContacts = '+ CAST(ContactID AS VARCHAR); END IF; IF NOT IgnoreCompany THEN SET Query = Query + ' AND _fkCompanies = '+ CAST(CompanyID AS VARCHAR); END IF; PREPARE stmt FROM Query; OPEN Test; END How you automate a conversion from DBISAM to ElevateDB with this construct I'm not sure (I'm assuming here you're automating the conversion rather than doing it by hand) but I'd probably check the last statement and if its not a SELECT INTO but just a SELECT assume that you want a result set.. Try this (I can't without your tables . If it works its "just" a matter of figuring out the automation. Let me know and I'll have a go if its successful. SCRIPT BEGIN DECLARE Query VARCHAR;/*new code*/ DECLARE Wanted INSENSITIVE CURSOR WITH RETURN FOR stmt;/*new code*/ 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 '; SET Query = 'SELECT S.ID, S.Symbol, S.Name, S.Description, S.Dividend, S.SharesInIssue*CV.Price as Capitalization, ROUND(S.Dividend/CV.Price*100 TO 2) as DividendRate, ROUND((CV.Price-MyBuyPrice)/MyBuyPrice*100 TO 2) as FromMyBuy, ROUND((MySellPrice-CV.Price)/MySellPrice*100 TO 2) as FromMySell, T.Name as StockType, T1.Name as BaseCurrency, T2.Name as PerceivedRisk, CAST(S1.LogicalOrder AS VARCHAR(2)) +''. '' + S1.Name as Status, CV.Price as CurrentPrice, CH.PriceHolding, CH.QtyHolding, CV.Price*CH.QtyHolding as HoldingValue, CV.Price*CH.QtyHolding-CH.PriceHolding as GainLoss, DV.TotalDivi, A.ValueAccrued, CV.Price*CH.QtyHolding-CH.PriceHolding + IF(A.ValueAccrued=null then 0.00 else A.ValueAccrued) + IF(DV.TotalDivi=Null then 0.00 else DV.TotalDivi) as NetPosition FROM Stocks S LEFT JOIN CurrentValuationsStocks CV ON (CV.StockID=S.ID) LEFT JOIN Status S1 ON (S.StatusID = S1.ID) LEFT JOIN Types T ON (S.StockTypeID = T.ID) LEFT JOIN Types T1 ON (S.BaseCurrencyID = T1.ID) LEFT JOIN Types T2 ON (S.PerceivedRiskID = T2.ID) LEFT JOIN CurrentHoldings CH ON (CH.StockID=S.ID) LEFT JOIN Accrued A ON (A.StockID=S.ID) LEFT JOIN Divis DV ON (DV.StockID=S.ID) '; /*altered code*/ PREPARE stmt FROM Query; /*new code*/ OPEN Wanted;/*new code*/ END Roy Lambert [Team Elevate] |
Tue, Apr 26 2011 2:20 PM | Permanent Link |
Adam Brett Orixa Systems | Thank you so much Roy you are a star. I will scratch my head & try to figure out what to do ...
I have to say that these new features of EDB are a big leap from DBISAM & could be better documented! A help file consisting of a series of more or less complex DBISAM queries with EDB equivalents would be _really_ useful as a reference. ... as I now have quite a few of these (... I could create a document like this Tim could put on the site, if he wanted. -- In terms of my own progression DBISAM -> EDB, the great majority of the SQL strings I am passing from my old DBISAM apps are simple selects. I think I will _probably_ figure out a way of testing for these using something like POS('SCRIPT', SQLStr) & only pass the longer multi-part Scripts to an EDB Script component. Extremely useful to know how to return a result set from an EDBScript though! Thanks. |
Tue, Apr 26 2011 4:00 PM | Permanent Link |
Adam Brett Orixa Systems | A lumpy awkward area I am finding in the conversion process is statements like:
SELECT [fields] FROM Tables WHERE DateDone BETWEEN Current_Date - 120 AND Current_Date - 90 -- In EDB this needs to be: SELECT [fields] FROM Tables WHERE DateDone BETWEEN Current_Date - INTERVAL '120' DAYS AND Current_Date - INTERVAL '90' DAYS -- It is hard to write a Delphi script to spot the places where this substitution needs to be done, and also hard to write the code to substitute in the extra pieces of script reliably. ... will probably have to do it by hand! though a whole series of variations on: StringReplace('Current_Date - 30', 'Current_Date - INTERVAL ''30'' DAYS', [rfReplaceAll, rfIgnoreCase]); Would do parts of the work. |
Wed, Apr 27 2011 3:04 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
I have enough trouble coping with that manually! The big problem with it is determining that its a date column rather than integer or numeric. When I was playing with the stuff for the other Adam I was pleased that his would be created automatically because the only way I could see to sort out the date arithmetic stuff was to query the database for the column type. My other thought was to write a custom function (probably two) to cope with all arithmetic stuff. I never got round to testing things out but my guess was that a date would be passed through as a float so that inside a Delphi procedure your could just do standard arithmetic. I may have a play this weekend Roy Lambert [Team Elevate] |
Thu, Apr 28 2011 10:43 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< The big problem with it is determining that its a date column rather than integer or numeric. >> This is why I got rid of this in ElevateDB - in DBISAM the parser had to figure out if a string was a date or not by how it was used, and this process wasn't always reliable. -- Tim Young Elevate Software www.elevatesoft.com |
Thu, Apr 28 2011 1:53 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | I've been giving this some thought. I know I can come up with something for fairly simple cases such as the one posted by Adam
DateDone BETWEEN Current_Date - 120 AND Current_Date - 90 even though it will involve CASTing all the dates/numbers to VARCHARs and converting back to something usable in a Delphi function. BUT if its something like Col1 + Col2 or even DateDone + LasteDate I'm lost because I have no idea what the types are Col1 - Col2 I can interpret as involving some sort of number (hopefully SQL will never allow string sugtraction) and I can sort out what inside the Delphi function. Any comments or ideas from anyone? Is it worth it? Roy Lambert |
Wed, May 4 2011 4:10 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Roy,
<< Any comments or ideas from anyone? Is it worth it? >> What is really needed here is some bridge code that takes DBISAM-compiled intermediate output and translates it into ElevateDB-intermediate output, and then writes it back out in a pre-formatted way. Once EDB 3 is done, I'll see about working on such a beast. -- Tim Young Elevate Software www.elevatesoft.com |
Wed, May 4 2011 4:40 PM | Permanent Link |
Terry Swiers | Tim,
> Once EDB 3 is done, I'll see about working on such a beast. Nice subtle teaser there about EDB 3. -- --------------------------------------- Terry Swiers Millennium Software, Inc. http://www.1000years.com http://www.atrex.com Now shipping Atrex 13. For more information go to http://www.atrex.com/news.asp Atrex Electronic Support Options: Atrex Knowledgebase: http://support.atrex.com/KB/root.aspx Email: support@atrex.com Newsgroup: news://news.1000years.com/millennium.atrex Fax: 1-925-829-1851 Phone: 1-925-828-5892 (M-F, 9a-5p Pacific) --------------------------------------- |
Thu, May 5 2011 2:48 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Tim
>What is really needed here is some bridge code that takes DBISAM-compiled >intermediate output and translates it into ElevateDB-intermediate output, >and then writes it back out in a pre-formatted way. Once EDB 3 is done, >I'll see about working on such a beast. Brilliant offer. If you don't know what's going on at that stage we're stuffed anyway. I assume this would be a separate stand alone utility? Roy Lambert |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Tuesday, May 14, 2024 at 07:14 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |