Elevate Software

Login Login

ProductsBulletSalesBulletSupportBulletDownloadsBulletAbout



Home » Technical Support » ElevateDB Technical Support » Support Forums » Search Forums » Search Results » View Thread

Icon View Thread

The following is the text of the current message along with any replies.
Messages 11 to 20 of 24 total
Thread migrate DBISAM4 to ElevateDB (can field types be altered)
Fri, May 25 2012 5:44 PMPermanent Link

IQA

Thanks Roy and Jan for all your input.

I think Jan exaplains the problem I'm seeing, originally there was no
problem with the Currency (BCD) type in DBISAM, but using the Elevate
Migration it appears at least to cause a few problems when it converts
the Currency to floats. (I'm not saying the Migration is at fault, more
that others may have experienced the problem and had good ideas on the
best way to get around it)

I'm sure I will get around it, by doing a further conversion on the
float fields prior to changing the field to DECIMAL (19,2)

Cheers,

Phil.


> Roy,
>
> I never noticed any rounding errors with the BCD data type however when
> I converted tables from DBISAM v4.x to EDB, it converted all the BCD
> data types to floats. It's easy enough to change them but I didn't
> notice it  at first when I began my conversions back when.
Fri, May 25 2012 11:01 PMPermanent Link

IQA

Just in summary I have 2 questions.

1. Is it possible to change ALL fields of a certain type to another type
(i.e use information.tablecolumns to list all float fields and change
them to say decimal (19,2)

OR

2. Somehow have the migrator change any Currency fields from DBISAM 4
into decimal (19,2)

Reason I ask is there are over 175 Currency fields in the old DBISAM
database, so if neither of the above are possible I will have to write
some code to manually do this.

Thanks muchly, I'm very happy to be moving over out last few software
programs from DBISAM to ElevateDB, its worth the move for sure.
Fri, May 25 2012 11:13 PMPermanent Link

IQA

Sorry only last thing...

If a value is say: -10.655 then changing its type to DECIMAL(19,2) makes
it -10.65

How can I have it round UP when .5 or more ?
Sat, May 26 2012 3:31 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Jan


>I never noticed any rounding errors with the BCD data type however when
>I converted tables from DBISAM v4.x to EDB, it converted all the BCD
>data types to floats. It's easy enough to change them but I didn't
>notice it at first when I began my conversions back when.

That is the problem. The "error" occurs intermittently  due to different rounding schemes (I think). There's been an awful lot of posts about rounding on the Embarcadero newsgroups (especially from John Herbster)

Roy Lambert [Team Elevate]
Sat, May 26 2012 3:36 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Phil

>1. Is it possible to change ALL fields of a certain type to another type
>(i.e use information.tablecolumns to list all float fields and change
>them to say decimal (19,2)

Sure is. The way I did it was a script (below)  AFTER conversion. You can do it using ElevateDB's own programming language or use Delphi. One reason I did it this was was to help me learn.


Roy Lambert [Team Elevate]



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 Dropper CURSOR FOR DropRID;
DECLARE RID VARCHAR;

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 = '';
PREPARE flds FROM 'SELECT * FROM Information.TableColumns WHERE TableName = '''+tblName+'''';
OPEN fldCursor;
FETCH FIRST FROM fldCursor ('Name', 'Type', 'Length', 'Scale', 'Collation', 'Generated') INTO fldName, fldType, fldLength, fldScale, fldCollate, fldGenerated;
WHILE NOT EOF(fldCursor) DO
 SET fldCollate = 'ANSI_CI';
 SET FldBlock = FldBlock + 'ALTER COLUMN "'+fldName+'" AS ';

 CASE
  WHEN fldType = 'Integer' THEN
   BEGIN
    SET FldBlock = FldBlock + 'INTEGER ';
    IF fldGenerated THEN
     SET FldBlock = FldBlock + ' GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL ';
/*  I'm only bothered about autoincs here - I don't have anything else in the transfer */
    END IF;
   END;
  WHEN fldType = 'Boolean' THEN
   BEGIN
    SET FldBlock = FldBlock + ' BOOLEAN DEFAULT FALSE';
   END;
  WHEN fldType = 'CLOB' THEN
   BEGIN
   /*
    EXECUTE IMMEDIATE 'UPDATE "' +tblName + '" SET "' + fldName + '" = '''' WHERE "' + fldName + '" IS NULL';
    SET FldBlock = FldBlock + 'CLOB COLLATE "' + fldCollate + '" DEFAULT '''' NOT NULL';
   */
        SET FldBlock = FldBlock + 'CLOB COLLATE "' + fldCollate + '"';
   END;
  WHEN fldType = 'Decimal' THEN
   BEGIN
    SET FldBlock = FldBlock + 'DECIMAL(20,'+ CAST(fldScale AS VARCHAR)+')';
   END;
  WHEN fldType = 'VarChar' THEN
    BEGIN
/*
    EXECUTE IMMEDIATE 'UPDATE "' +tblName + '" SET "' + fldName + '" = '''' WHERE "' + fldName + '" IS NULL';
    SET FldBlock = FldBlock + 'VARCHAR(' + CAST(fldLength AS VARCHAR)+') COLLATE "' + fldCollate + '" DEFAULT '''' NOT NULL';
*/
    SET FldBlock = FldBlock + 'VARCHAR(' + CAST(fldLength AS VARCHAR)+') COLLATE "' + fldCollate + '"';
   END;
  ELSE
   BEGIN
    SET FldBlock = FldBlock + fldType;
   END;
 END CASE;

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

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

 IF NOT EOF(fldCursor) THEN
  SET FldBlock = FldBlock +',';
 END IF;
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 */

/* now the custom stuff */

/*
EXECUTE IMMEDIATE 'ALTER TABLE "BandA"
  ALTER COLUMN "_BoxType" AS VARCHAR(1) DEFAULT ''M'' DESCRIPTION ''P=primary mailbox(eg IN), I=Individual primary mailbox M=mailbox, N=newsgroup, S=server''';
*/

EXECUTE IMMEDIATE 'ALTER TABLE "Calls"
  ALTER COLUMN "_DateAdded" AS DATE DEFAULT CURRENT_DATE';

EXECUTE IMMEDIATE 'ALTER TABLE "Career"
  ALTER COLUMN "_Created" AS DATE DEFAULT CURRENT_DATE';

EXECUTE IMMEDIATE 'ALTER TABLE "Companies"
  ALTER COLUMN "_fkSandT_Turnover" AS VARCHAR(1) DEFAULT ''?'',
  ALTER COLUMN "_fkSandT_Staffing" AS VARCHAR(1) DEFAULT ''?'',
  ALTER COLUMN "_Created" AS DATE DEFAULT CURRENT_DATE';

EXECUTE IMMEDIATE 'ALTER TABLE "DialControl"
  ALTER COLUMN "_Action" AS VARCHAR(5) DESCRIPTION ''Block=do not autodial, Hide=hide originator''''s phone number, Show=reveal originator''''s phone number (1470)'',
  ALTER COLUMN "_DateAdded" AS DATE DEFAULT CURRENT_DATE,
  ALTER COLUMN "_AddedBy" AS VARCHAR(3) DEFAULT CURRENT_USER';

EXECUTE IMMEDIATE 'ALTER TABLE "emAddress"
  ALTER COLUMN "_Mode" AS INTEGER DEFAULT 0,
  ALTER COLUMN "_InactiveMode" AS INTEGER DEFAULT 2';

EXECUTE IMMEDIATE 'ALTER TABLE "ELN"
  ALTER COLUMN "_TimeStamp" AS TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  ALTER COLUMN "_fkStaff" AS VARCHAR(5) DEFAULT CURRENT_USER,
  ALTER COLUMN "_DisplayFormat" AS VARCHAR(4 )DEFAULT ''????'' DESCRIPTION ''ANSI, RTF, HTML''';

EXECUTE IMMEDIATE 'UPDATE "ELN" SET _ELNType = ''Mailshot'' WHERE _ELNType = ''Mailsh''';

EXECUTE IMMEDIATE 'ALTER TABLE "EMails"
  ALTER COLUMN "_Colour" AS INTEGER DEFAULT 0,
  ALTER COLUMN "_DisplayFormat" AS VARCHAR(4) DEFAULT ''ANSI'' DESCRIPTION ''ANSI, RTF, HTML''';

EXECUTE IMMEDIATE 'ALTER TABLE "Introductions"
  ALTER COLUMN "_Initiated" AS DATE DEFAULT CURRENT_DATE';

EXECUTE IMMEDIATE 'ALTER TABLE "OrderBook"
  ALTER COLUMN "_fkStaff" AS VARCHAR(5) DEFAULT CURRENT_USER,
  ALTER COLUMN "_Currency" AS VARCHAR(3) DEFAULT ''GBP''';

EXECUTE IMMEDIATE 'ALTER TABLE "CallStats"
  ALTER COLUMN "_Date" AS DATE DEFAULT CURRENT_DATE,
  ALTER COLUMN "_Time" AS TIME DEFAULT CURRENT_TIME';

EXECUTE IMMEDIATE 'ALTER TABLE "UserStats"
  ALTER COLUMN "_Date" AS DATE DEFAULT CURRENT_DATE,
  ALTER COLUMN "_Dialings" AS INTEGER DEFAULT 0,
  ALTER COLUMN "_Connected" AS INTEGER DEFAULT 0,
  ALTER COLUMN "_Positive" AS INTEGER DEFAULT 0,
  ALTER COLUMN "_Negative" AS INTEGER DEFAULT 0';

EXECUTE IMMEDIATE 'ALTER TABLE "ProjectStats"
  ALTER COLUMN "_Date" AS DATE DEFAULT CURRENT_DATE,
  ALTER COLUMN "_Dialings" AS INTEGER DEFAULT 0,
  ALTER COLUMN "_Connected" AS INTEGER DEFAULT 0,
  ALTER COLUMN "_Positive" AS INTEGER DEFAULT 0,
  ALTER COLUMN "_Negative" AS INTEGER DEFAULT 0';

EXECUTE IMMEDIATE 'ALTER TABLE "Projects"
  ALTER COLUMN "_fkStaff" AS VARCHAR(5) DEFAULT CURRENT_USER,
  ALTER COLUMN "_Status" AS VARCHAR(1)  DEFAULT ''L'' DESCRIPTION ''Status indicator L=Live, H=hold,D=dead''';


EXECUTE IMMEDIATE 'ALTER TABLE "Templates"
  ALTER COLUMN "_Usage" AS VARCHAR(2) DEFAULT ''EL'',
  ADD CONSTRAINT "Usage" CHECK (_Usage IN (''E'',''L'',''EL''))';

EXECUTE IMMEDIATE 'CREATE TABLE "LetterQueue"
(
"_ID" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"_fkmShots" INTEGER NOT NULL,
"_fkContacts" INTEGER NOT NULL,
"_fkCompanies" INTEGER,
"_fkStaff" VARCHAR(5) COLLATE "ANSI" NOT NULL,
"_LetterPrinted" BOOLEAN DEFAULT FALSE  NOT NULL,
"_LabelPrinted" BOOLEAN DEFAULT FALSE  NOT NULL,
"_Letter" CLOB COLLATE "ANSI" NOT NULL,
CONSTRAINT "PK" PRIMARY KEY ("_ID")
)';

EXECUTE IMMEDIATE 'CREATE INDEX "mShot"
ON "LetterQueue"
("_fkmShots" ASC)';

EXECUTE IMMEDIATE 'ALTER TABLE "PopBoxes"
  ALTER COLUMN "_POPPort" AS INTEGER DEFAULT 110,
  ALTER COLUMN "_SMTPPort" AS INTEGER DEFAULT 25';

EXECUTE IMMEDIATE 'ALTER TABLE "Bayesian"
  ALTER COLUMN "_Spam" AS INTEGER DEFAULT 0,
  ALTER COLUMN "_NonSpam" AS INTEGER DEFAULT 0,
  ALTER COLUMN "_Total" AS INTEGER DEFAULT 0,
  ALTER COLUMN "_Probability" AS FLOAT DEFAULT 0.0';

EXECUTE IMMEDIATE 'ALTER TABLE "Staff" DROP COLUMN "_Check"';

EXECUTE IMMEDIATE 'CREATE TABLE "elnArchive"
(
"_ID" INTEGER,
"_MsgID" VARCHAR(80) COLLATE "ANSI_CI",
"_fkCompanies" INTEGER,
"_fkContacts" INTEGER,
"_Subject" VARCHAR(100) COLLATE "ANSI_CI",
"_Comments" VARCHAR(100) COLLATE "ANSI_CI",
"_Size" INTEGER,
"_ELNtype" VARCHAR(8) COLLATE "ANSI_CI",
"_TimeStamp" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
"_fkStaff" VARCHAR(5) COLLATE "ANSI" DEFAULT CURRENT_USER,
"_DisplayFormat" VARCHAR(4) COLLATE "ANSI" DEFAULT ''ANSI''  DESCRIPTION ''ANSI, RTF, HTML'',
"_Encoding" VARCHAR(5) COLLATE "ANSI_CI",
"_InOutInd" VARCHAR(2) COLLATE "ANSI_CI",
"_aList" CLOB COLLATE "ANSI_CI",
"_Message" CLOB COLLATE "ANSI_CI",
"_Graphics" BLOB,
"_Recipients" CLOB COLLATE "ANSI_CI",
"_Flags" CLOB COLLATE "ANSI_CI",
"_Encoded" CLOB COLLATE "ANSI_CI"
)';

EXECUTE IMMEDIATE 'CREATE TABLE "TabularReports"
(
"_ID" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,
"_Title" VARCHAR(50) COLLATE "ANSI_CI" NOT NULL,
"_fkStaff" VARCHAR(5) COLLATE "ANSI" NOT NULL,
"_Private" BOOLEAN DEFAULT FALSE  NOT NULL,
"_Report" CLOB COLLATE "ANSI_CI" COMPRESSION 6,
"_ColumnHeaders" CLOB COLLATE "ANSI_CI" COMPRESSION 6,
CONSTRAINT "PK" PRIMARY KEY ("_ID")
)';

EXECUTE IMMEDIATE 'CREATE INDEX "Title" ON "TabularReports" ("_Title" COLLATE "ANSI_CI")';

EXECUTE IMMEDIATE 'ALTER TABLE Sites
ADD COLUMN "_cSTDCode" VARCHAR(5) COLLATE "ANSI_CI" COMPUTED ALWAYS AS SUBSTR(_Switchboard,1,POS('' '' IN _Switchboard)-1)';

END
Sat, May 26 2012 3:46 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Phil

>If a value is say: -10.655 then changing its type to DECIMAL(19,2) makes
>it -10.65
>
>How can I have it round UP when .5 or more ?

This one is marginally more difficult. What I would do these days is

1. decide on what rounding scheme you want to use (may sound silly but there are several variants)
2. use a script as suggested in the other post which:
a) creates a new DECIMAL(0,2) column for each currency column (I assumed you have some way of determining which is a currency column otherwise a bit more pre-processing is needed)
b) copies the existing data with suitable conversion into the new column(s)
c) drop the old column(s)
d) rename the new column(s)

What I actually did since I was more familiar with DBISAM was do a LOT of preprocessing (restructuring and data changing) using DBISAM before conversion and then some more afterwards using ElevateDB both as a learning exercise and because the migrator wouldn't do just what I wanted.

Roy Lambert [Team Elevate]
Sat, May 26 2012 4:04 AMPermanent Link

IQA

As always Roy, your advice and examples are fantastic.

Thanks so much, really can't say enough good things about these forums /
support.

Hey, I hope you're having some time off this weekend!

Cheers,

Phil.
Sat, May 26 2012 8:42 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Phil

>Hey, I hope you're having some time off this weekend!

This is my hobby - I work in recruitment in real life.

I live in the Scottish Highlands and we're into our fourth glorious sunny day. I'm starting to get very worried about what its going to do to get us back.

Roy
Sat, May 26 2012 8:57 PMPermanent Link

IQA

Roy,

> I live in the Scottish Highlands and we're into our fourth glorious sunny day. I'm starting to get very worried about what its going to do to get us back.

4 days in a row, that's not like Scotland Wink

Scotland is a beautiful spot, there's a place called 'Lochgoilhead' I
had a holiday there many moons ago, was awesome. I remember going for
walks in glorious warm weather then 2 minutes later it was raining and cold.

I live in Tasmania, which reminds me of Scotland in many ways.

Phil.
Sun, May 27 2012 9:08 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Phil


>4 days in a row, that's not like Scotland Wink

Day 5 now !

>Scotland is a beautiful spot, there's a place called 'Lochgoilhead' I
>had a holiday there many moons ago, was awesome. I remember going for
>walks in glorious warm weather then 2 minutes later it was raining and cold.

Been there when we holidayed up here before moving - on the drive to it there's a spring / fountain sort of thing - a lion's head on a rock with water coming out of its mouth - I had a drink there while we watched the buzzards doing courtship dances overhead.

>I live in Tasmania, which reminds me of Scotland in many ways.

My wife has a pen pal in Ulverstone - Penguin Road Smiley

Roy

« Previous PagePage 2 of 3Next Page »
Jump to Page:  1 2 3


This web page was last updated on
Saturday, June 22, 2024 at 05:51 PM

Privacy PolicyBulletSite Map

© 2024 Elevate Software, Inc. All Rights Reserved
Questions or comments ? E-mail E-mail us at info@elevatesoft.com

Image