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

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]

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.

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.

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]

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

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

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

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)
---------------------------------------

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