Hi,

Is there a document anywhere describing the breaking differences between
DBISAM and EDB SQL syntax (as well as any other key differences which I
should look into)?

I've got a BIG project using DBISAM 4 which needs to be migrated but there
are *a lot* of complex SQL statements embedded in the code. To be honest I'm
really not looking forward to this, so any help would be much appreciated!

Thanks,

Hedley

Hedley,

There is a section in the manual about migrating DBISAM to EDB:
http://www.elevatesoft.com/manual?action=mancat&id=edb2&product=b&version=2006&category=2

If you want to directy go to the SQL changes, then:
http://www.elevatesoft.com/manual?action=mantopic&id=edb2&product=b&version=2006&category=2&topic=16

--
Fernando Dias
[Team Elevate]

Hedley


There's a reasonable chunk in the manual about it differences (download the trial version to get it) but here's my (from memory so probably incomplete) list (I'm sure others will have different ones)

Case sensitivity - all change syntax like UPPER(field) = UPPER(value) has been replaced by COLLATE

IF EXISTS has gone

NULL operates "properly" ie is no longer the same as emptystring, anything joined to it becomes NULL etc

TEXTSEARCH has been replaced by CONTAINS which no longer works on a non full text indexed column

SELECT INTO has been replaced by CREATE TABLE AS which doesn't drop the table being created, and doesn't respect any ORDER BY clause

You no longer qualify a table by a path you have to use the catalog database

VARCHAR fields are no longer right trimmed

Canned result sets are no longer editable

Scripts are now a different component to queries.

Because of differences between the parameter type used switching between TDBISAMQuery and TEDBQuery can cause them to go walkabout.

INTERVALS which affect time & date maths

RECORDID if you used it has gone the way of the dinosaurs

Even with the above I've not thrown in the towel and reverted to DBISAM (although it was a close thing on occasions , there are a lot of new features, and it feels faster.

Roy Lambert [Team Elevate]

Thanks Fernando - those links are very helpful.

"Fernando Dias" <fernandodias.removthis@easygate.com.pt> wrote in message
news:305F81CE-8129-4D5E-A3D1-63250E6BFF27@news.elevatesoft.com...
> Hedley,
>
> There is a section in the manual about migrating DBISAM to EDB:
> http://www.elevatesoft.com/manual?action=mancat&id=edb2&product=b&version=2006&category=2
>
> If you want to directy go to the SQL changes, then:
> http://www.elevatesoft.com/manual?action=mantopic&id=edb2&product=b&version=2006&category=2&topic=16
>
> --
> Fernando Dias
> [Team Elevate]
>

Thanks Roy - I really appreciate you taking the time to mention your
experiences. The biggest difficulty for me looks like it's going to be the
lack of "IF NOT EXISTS" - I use that a lot for creating/dropping temp memory
tables :-/

Thanks again.

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:A5E38FAB-7DB3-477C-8A9A-500765CA7FD0@news.elevatesoft.com...
> Hedley
>
>
> There's a reasonable chunk in the manual about it differences (download
> the trial version to get it) but here's my (from memory so probably
> incomplete) list (I'm sure others will have different ones)
>
> Case sensitivity - all change syntax like UPPER(field) = UPPER(value) has
> been replaced by COLLATE
>
> IF EXISTS has gone
>
> NULL operates "properly" ie is no longer the same as emptystring, anything
> joined to it becomes NULL etc
>
> TEXTSEARCH has been replaced by CONTAINS which no longer works on a non
> full text indexed column
>
> SELECT INTO has been replaced by CREATE TABLE AS which doesn't drop the
> table being created, and doesn't respect any ORDER BY clause
>
> You no longer qualify a table by a path you have to use the catalog
> database
>
> VARCHAR fields are no longer right trimmed
>
> Canned result sets are no longer editable
>
> Scripts are now a different component to queries.
>
> Because of differences between the parameter type used switching between
> TDBISAMQuery and TEDBQuery can cause them to go walkabout.
>
> INTERVALS which affect time & date maths
>
> RECORDID if you used it has gone the way of the dinosaurs
>
> Even with the above I've not thrown in the towel and reverted to DBISAM
> (although it was a close thing on occasions , there are a lot of new
> features, and it feels faster.
>
> Roy Lambert [Team Elevate]
>

Hedley


Tim did give me a script for it (below sig). The only problem is you'll need to set the active index after creating the table.

Roy Lambert [Team Elevate]

SCRIPT (IN SQLStatement VARCHAR, IN TableName VARCHAR, IN IdxSet VARCHAR)
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 TableName;

IF (ROWCOUNT(InfoCursor) > 0) THEN
 EXECUTE IMMEDIATE 'DROP TABLE "'+TableName+'"';
END IF;

CLOSE InfoCursor;

PREPARE ResultStmt FROM 'CREATE TABLE "'+TableName+'" AS '+SQLStatement+' WITH DATA';
EXECUTE ResultStmt;

IF IdxSet IS NOT NULL THEN
 PREPARE ResultStmt FROM 'CREATE INDEX Idx ON "'+TableName+'"('+IdxSet+')';
 EXECUTE  ResultStmt;
END IF;
END