I need to migrate my DBISAM data to ElevateDB and it must be in Unicode
format.

If for arguments sake we accept that the ElevateDB tables that result
from the migration are not formatted (if that is the word) like I want
then I need to find some other way of converting them.

So how about using the COMPARE DATABASE statement?

I compare the structure of a database that I have set up manually. (I
will have tweeked the SQL behind the tables created as a result of the
migration.)

I run the COMPARE DATABASE statement.

Then by Section 4.48 SchemaDifference Table document edb2sql.pdf

"This table can be used to generate a script for altering objects in a
database so that they are equivalent to the target databaes (sic)"


Is there an example of how this table can be used to generate a script?

Regards,
   Peter Evans

Hi Peter

"Peter Evans" <pgevans@melbpc.org.au> wrote in message
news:B8260FD6-C3D8-427E-B10B-5EE3BA479C18@news.elevatesoft.com...
>I need to migrate my DBISAM data to ElevateDB and it must be in Unicode
>format.
>
> If for arguments sake we accept that the ElevateDB tables that result from
> the migration are not formatted (if that is the word) like I want then I
> need to find some other way of converting them.
>

But they will be formatted!  At least that is my understanding - I'm
currently struggling with my own DBISAM/ElevateDB conversion.

Per the manual, section  3.2 Migrating ...:-
===========================
You can find these migrator modules as part of the ElevateDB Additional
Software and Utilities (EDBADD) installation in the \libs subdirectory under
the main installation directory. There are ANSI and
Unicode versions of each of the migrators.
===========================

Cheers

Jeff
--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

On 12/08/2013 12:24 PM, Jeff Cook wrote:

>
> But they will be formatted!  At least that is my understanding - I'm
> currently struggling with my own DBISAM/ElevateDB conversion.

You are correct in restating what the manual says.

Unfortunately the migrator for migrating from DBISAM 4 to ElevateDB
Unicode does not always work in practise.

This subject needs to be read in conjunction with my other Subject : How
to force Collate UNI when use Manager Unicode?

When I look at the migrated tables it is clear that some fields have
become COLLATE "ENU".

That is just plain wrong. They should be COLLATE "UNI".

The migrator I have been using is broken.

Regards,
  Peter Evans

Peter,

<< I need to migrate my DBISAM data to ElevateDB and it must be in Unicode
format. >>

If you're using the Unicode version of ElevateDB, then your databases are in
Unicode format.  I think you're confusing the collations with character
sets.  A collation is simply a sort order, and the UNI collation is a
*binary* comparison of the Unicode characters.  Other collations, like the
ENU collation, are a different type of sort, but they're still using the
Unicode character set.

<< Is there an example of how this table can be used to generate a script?
>>

You can see this in the edbutilcomps.pas unit that comes with the EDB
Manager source code.  The TEDBReverseEngineer component used by the EDB
Manager for reverse-engineering uses this table to generate an upgrade
script.  Of course, this is all accessible within the EDB Manager under the
<Database Name>/Reverse-Engineer Database task link, so you don't really
need to code anything if you want to generate database upgrade scripts.

Tim Young
Elevate Software
www.elevatesoft.com

On 12/08/2013 10:37 AM, Peter Evans wrote:
> I need to migrate my DBISAM data to ElevateDB and it must be in Unicode
> format.
>
> If for arguments sake we accept that the ElevateDB tables that result
> from the migration are not formatted (if that is the word) like I want
> then I need to find some other way of converting them.
>
> So how about using the COMPARE DATABASE statement?
>

I am now trying a different approach. The new approach is to run a
script that changes the Collation to UNI.

Perhaps the following script has bugs lurking? Is it general purpose enough?

Regards,
  Peter Evans

/*
Change collation to UNI.

This script runs against every table in a database and ensures that the
Collation  of its fields is UNI. The fields affected are VarChar.

Based on a script by Roy Lambert of 26May2012 in thread
"migrate DBISAM4 to ElevateDB (can field types be altered)"
*/

SCRIPT
BEGIN

DECLARE FldBlock VARCHAR;
DECLARE MainBlock VARCHAR;
DECLARE tblCursor CURSOR FOR tbls;
DECLARE fldCursor CURSOR FOR flds;
DECLARE tblName VARCHAR;
DECLARE fldName VARCHAR;
DECLARE fldType VARCHAR;
DECLARE fldLength INTEGER;
DECLARE fldScale INTEGER;
DECLARE fldCollate VARCHAR;
DECLARE fldGenerated BOOLEAN;
DECLARE fldNullable BOOLEAN;
DECLARE Dropper CURSOR FOR DropRID;
DECLARE RID VARCHAR;
DECLARE CountFieldsToAlter INTEGER;

SET MainBlock = '';

PREPARE tbls FROM 'SELECT * FROM Information.Tables';
OPEN tblCursor;

FETCH FIRST FROM tblCursor INTO tblName;

WHILE NOT EOF(tblCursor) DO
SET MainBlock = MainBlock + 'ALTER Table "'+tblName+'"';
SET FldBlock = '';
SET CountFieldsToAlter = 0;

PREPARE flds FROM 'SELECT * FROM Information.TableColumns WHERE
TableName = '''+tblName+'''';
OPEN fldCursor;

FETCH FIRST FROM fldCursor ('Name', 'Type', 'Length', 'Scale',
'Collation', 'Generated', 'Nullable')
            INTO fldName, fldType, fldLength, fldScale, fldCollate,
fldGenerated, fldNullable;
WHILE NOT EOF(fldCursor) DO


/*                              */
 SET fldCollate = 'UNI';
/*                              */

 CASE
  WHEN fldType = 'VarChar' THEN
    BEGIN
    IF NOT CountFieldsToAlter = 0 THEN
      SET FldBlock = FldBlock + ',';
    END IF;
    SET CountFieldsToAlter = CountFieldsToAlter + 1;

    SET FldBlock = FldBlock + 'ALTER COLUMN "'+fldName+'" AS ';

    CASE
      WHEN fldNullable = True THEN
        SET FldBlock = FldBlock + 'VARCHAR(' + CAST(fldLength AS
VARCHAR)+') COLLATE "' + fldCollate + '"';
      ELSE
        SET FldBlock = FldBlock + 'VARCHAR(' + CAST(fldLength AS
VARCHAR)+') COLLATE "' + fldCollate + '"' + ' NOT NULL';
    END CASE;

    SET FldBlock = FldBlock + ' DESCRIPTION ''''';

   END;

  ELSE
   BEGIN

   END;
 END CASE;

 FETCH NEXT FROM fldCursor ('Name', 'Type', 'Length', 'Scale',
'Collation', 'Generated', 'Nullable')
            INTO fldName, fldType, fldLength, fldScale, fldCollate,
fldGenerated, fldNullable;

END WHILE; /* end of columns */

SET MainBlock = MainBlock + FldBlock;

SET MainBlock = MainBlock + ' DESCRIPTION ''''';
EXECUTE IMMEDIATE MainBlock;
SET MainBlock = '';

FETCH NEXT FROM tblCursor INTO tblName;
END WHILE; /* end of tables */

END

Peter


Naturally the script starts from a point of brilliance but whilst I haven't looked at your script in detail you seem to be just altering VarChar columns. What about GUID, CHAR & CLOB?

Also whilst I don't use column etc descriptions if you do this script will zap them.

Roy Lambert [Team Elevate]

Peter

Couple of suggestions.

DECLARE NewCollate VARCHAR DEFAULT 'ANSI_CI';

This allows it to be used for to/from various collations with one small change

Since not all columns will have a collation you can reduce the amount of work by


PREPARE tbls FROM 'SELECT * FROM Information.Tables T JOIN Information.TableColumns C ON C.TableName = T.Name WHERE C.Collation IS NOT NULL AND C.Collation <> '''+NewCollate+'''';


PREPARE flds FROM 'SELECT * FROM Information.TableColumns WHERE TableName = '''+tblName+''' AND Collation IS NOT NULL AND Collation <> '''+NewCollate+'''';

I'll add code in for the other datatypes and post the whole thing back in a few days.

Roy Lambert [Team Elevate]

Peter


SCRIPT
BEGIN
     
DECLARE NewCollate VARCHAR DEFAULT 'ANSI_CI';

DECLARE FldBlock VARCHAR;
DECLARE MainBlock VARCHAR;
DECLARE tblCursor CURSOR FOR tbls;
DECLARE fldCursor CURSOR FOR flds;
DECLARE tblName VARCHAR;
DECLARE fldName VARCHAR;
DECLARE fldType VARCHAR;
DECLARE fldLength INTEGER;
DECLARE fldCollate VARCHAR;
DECLARE fldGenerated BOOLEAN;
DECLARE fldNullable BOOLEAN;
DECLARE Dropper CURSOR FOR DropRID;
DECLARE RID VARCHAR;
DECLARE CountFieldsToAlter INTEGER;

SET MainBlock = '';

PREPARE tbls FROM 'SELECT * FROM Information.Tables T JOIN Information.TableColumns C ON C.TableName = T.Name WHERE C.Collation IS NOT NULL AND C.Collation <> '''+NewCollate+'''';
OPEN tblCursor;

FETCH FIRST FROM tblCursor INTO tblName;

WHILE NOT EOF(tblCursor) DO
SET MainBlock = MainBlock + 'ALTER Table "'+tblName+'"';
SET FldBlock = '';
SET CountFieldsToAlter = 0;

PREPARE flds FROM 'SELECT * FROM Information.TableColumns WHERE TableName = '''+tblName+''' AND Collation IS NOT NULL AND Collation <> '''+NewCollate+'''';
OPEN fldCursor;

FETCH FIRST FROM fldCursor ('Name', 'Type', 'Length','Collation', 'Generated', 'Nullable')
            INTO fldName, fldType, fldLength, fldCollate,fldGenerated, fldNullable;
WHILE NOT EOF(fldCursor) DO


/*                              */
 SET fldCollate = NewCollate;
/*                              */

 CASE
  WHEN (fldType = 'VarChar') OR (fldType = 'Char') THEN
    BEGIN
    IF NOT CountFieldsToAlter = 0 THEN
      SET FldBlock = FldBlock + ',';
    END IF;
    SET CountFieldsToAlter = CountFieldsToAlter + 1;

    SET FldBlock = FldBlock + 'ALTER COLUMN "'+fldName+'" AS ';

    CASE
      WHEN fldNullable = True THEN
        SET FldBlock = FldBlock + fldType+'(' + CAST(fldLength AS VARCHAR)+') COLLATE "' + fldCollate + '"';
      ELSE
        SET FldBlock = FldBlock + FldType+'(' + CAST(fldLength AS VARCHAR)+') COLLATE "' + fldCollate + '"' + ' NOT NULL';
    END CASE;

    SET FldBlock = FldBlock + ' DESCRIPTION ''''';

   END;
  WHEN (fldType = 'CLOB') OR (fldType = 'GUID') THEN
    BEGIN
    IF NOT CountFieldsToAlter = 0 THEN
      SET FldBlock = FldBlock + ',';
    END IF;
    SET CountFieldsToAlter = CountFieldsToAlter + 1;

    SET FldBlock = FldBlock + 'ALTER COLUMN "'+fldName+'" AS ';

    CASE
      WHEN fldNullable = True THEN
        SET FldBlock = FldBlock + fldType +' COLLATE "' + fldCollate + '"';
      ELSE
        SET FldBlock = FldBlock + FldType+' COLLATE "' + fldCollate + '"' + ' NOT NULL';
    END CASE;

    SET FldBlock = FldBlock + ' DESCRIPTION ''''';

   END;
  ELSE
   BEGIN

   END;
 END CASE;

 FETCH NEXT FROM fldCursor ('Name', 'Type', 'Length','Collation', 'Generated', 'Nullable')
            INTO fldName, fldType, fldLength, fldCollate,fldGenerated, fldNullable;

END WHILE; /* end of columns */

SET MainBlock = MainBlock + FldBlock;

SET MainBlock = MainBlock + ' DESCRIPTION ''''';
EXECUTE IMMEDIATE MainBlock;
SET MainBlock = '';

FETCH NEXT FROM tblCursor INTO tblName;
END WHILE; /* end of tables */

END



Roy Lambert

On 16/08/2013 10:39 PM, Roy Lambert wrote:

> Naturally the script starts from a point of brilliance but whilst I haven't

looked at your script in detail you seem to be just altering VarChar
columns.

What about GUID, CHAR & CLOB?

You are right about the starting point for the script! That is why I
choose it.

Will run your enhancements as soon as I can.

Regards,
  Peter Evans

On 16/08/2013 11:24 PM, Roy Lambert wrote:

Many thanks for that response Roy.

I have been able to run your script and can confirm it works in my
situation. Great.

Regards,
  Peter Evans