I'm hoping that my favourite DBISAM sql enhancement is about to make its reappearance just before I start work on converting all my sql statements. Any chance?

Roy Lambert

Roy,

<< I'm hoping that my favourite DBISAM sql enhancement is about to make its
reappearance just before I start work on converting all my sql statements.
Any chance? >>

No, that's a major change that did not make it into 1.07.

--
Tim Young
Elevate Software
www.elevatesoft.com

What does the If Exists function do? sounds like something I could be interested in



"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Roy,

<< I'm hoping that my favourite DBISAM sql enhancement is about to make its
reappearance just before I start work on converting all my sql statements.
Any chance? >>

No, that's a major change that did not make it into 1.07.

--
Tim Young
Elevate Software
www.elevatesoft.com

DavidS


>What does the If Exists function do? sounds like something I could be interested in

Its brilliant, probably the best single enhancement Tim could migrate from DBISAM. With it you can write statements (using the old DBISAM script format) like

Changer.SQL.Add('ALTER TABLE IF EXISTS "CallMenu"');
Changer.SQL.Add('REDEFINE COLUMN IF EXISTS _Number _ID AUTOINC,');
Changer.SQL.Add('ADD COLUMN IF NOT EXISTS _Folder BOOLEAN AT 4,');
....
....
Changer.SQL.Add('ADD COLUMN IF NOT EXISTS _Analysis MEMO,');
Changer.SQL.Add('REDEFINE COLUMN IF EXISTS _Stats _Result VARCHAR(5),');
Changer.SQL.Add('ADD PRIMARY KEY (_ID)');
Changer.SQL.Add('NOBACKUP;');

Changer.SQL.Add('CREATE INDEX IF NOT EXISTS "BelongsTo" ON "CallMenu" (_BelongsTo);');


It saves the messing about with mixing SQL and Pascal and having to do a query to check if a table is there before you create it. Since I make heavy use of memory tables the TABLE level one is in my code all over.

The nasty consequence is that you can no longer write sql and not care if a table or field is there knowing that the engine will take care of it and you now have to build the script in code checking the appropriate Information tables so the code is more complex, and a lot more work.

By the time it does make it back (if ever) it'll be to late for a lot of people migrating apps.

Roy Lambert

Roy,

I like this feature, and it would be great if it could be extended to values in field, IF Exists (select from tabel1 where id="4") but I think Tim has another way of doing this using row count.


Roy Lambert <roy.lambert@skynet.co.uk> wrote:

DavidS


>What does the If Exists function do? sounds like something I could be interested in

Its brilliant, probably the best single enhancement Tim could migrate from DBISAM. With it you can write statements (using the old DBISAM script format) like

Changer.SQL.Add('ALTER TABLE IF EXISTS "CallMenu"');
Changer.SQL.Add('REDEFINE COLUMN IF EXISTS _Number _ID AUTOINC,');
Changer.SQL.Add('ADD COLUMN IF NOT EXISTS _Folder BOOLEAN AT 4,');
....
....
Changer.SQL.Add('ADD COLUMN IF NOT EXISTS _Analysis MEMO,');
Changer.SQL.Add('REDEFINE COLUMN IF EXISTS _Stats _Result VARCHAR(5),');
Changer.SQL.Add('ADD PRIMARY KEY (_ID)');
Changer.SQL.Add('NOBACKUP;');

Changer.SQL.Add('CREATE INDEX IF NOT EXISTS "BelongsTo" ON "CallMenu" (_BelongsTo);');


It saves the messing about with mixing SQL and Pascal and having to do a query to check if a table is there before you create it. Since I make heavy use of memory tables the TABLE level one is in my code
all over.

The nasty consequence is that you can no longer write sql and not care if a table or field is there knowing that the engine will take care of it and you now have to build the script in code checking the
appropriate Information tables so the code is more complex, and a lot more work.

By the time it does make it back (if ever) it'll be to late for a lot of people migrating apps.

Roy Lambert

DavidS


That I think is already there. Here's Tim's example for replacing a JOIN in a DELETE sql

delete from MemoryData.MemTimeOff M
WHERE M.EmpID = 0 AND
EXISTS (SELECT * FROM TimeKeeper.Timeoff T
WHERE T.ReasonID = M.Reasonid  and T.WeekDate = M.WeekDate and T.EmpID
= -:EmpID)

Roy Lambert

David,

<< I like this feature, and it would be great if it could be extended to
values in field, IF Exists (select from tabel1 where id="4") but I think Tim
has another way of doing this using row count. >>

EDB already supports using the EXISTS function in this manner, but for some
reason I just noticed that it isn't documented anywhere.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


>EDB already supports using the EXISTS function in this manner, but for some
>reason I just noticed that it isn't documented anywhere.

Apart from these newsgroups

Roy Lambert