I thought it was about time to look at ElevateDB scripts and was pleasantly
surprised at how easy it is to use the metadata to create a useful utility.

The following script updates all char and varchar fields to "not null" after
updating any null strings to empty strings.  It seems to do what is required
but if you are going to run it back up the database first!

John


SCRIPT
BEGIN
DECLARE Temp CURSOR FOR Stmt1;
DECLARE TableName VARCHAR(64);
DECLARE OldTableName VARCHAR(64);
DECLARE FName VARCHAR(64);
DECLARE FType VARCHAR(64);
DECLARE FLength VARCHAR(4);
DECLARE AlterScript VARCHAR(4096);

SET OldTableName='';

PREPARE Stmt1 FROM
   'SELECT * FROM information.tablecolumns WHERE type=''Char'' OR type
=''VarChar'' ORDER BY TableName';
OPEN Temp;
FETCH FIRST FROM Temp (TableName,Name,Type,Length) INTO
TableName,FName,FType,FLength;
SET AlterScript = 'ALTER TABLE "'+Tablename+'" ALTER COLUMN "'+
Fname+'" AS '+FType+'('+FLength+') NOT NULL ';
WHILE NOT EOF(Temp) DO
 EXECUTE IMMEDIATE 'UPDATE '+TableName+' SET '+FName+' = '''' WHERE
'+Fname+' IS NULL';
 IF (OldTableName <> TableName) AND (OldTableName <> '') THEN
   EXECUTE IMMEDIATE AlterScript;
   SET AlterScript = 'ALTER TABLE "'+Tablename+'" ALTER COLUMN "'+
   Fname+'" AS '+FType+'('+FLength+') DEFAULT '''' NOT NULL ';
 ELSE
   SET AlterScript = AlterScript+', ALTER COLUMN "'+
   Fname+'" AS '+FType+'('+FLength+') DEFAULT '''' NOT NULL ';
 END IF;
 SET OldTableName=TableName;
 FETCH NEXT FROM Temp (TableName,Name,Type,Length) INTO
TableName,FName,FType,FLength;
END WHILE;
IF (OldTableName <> '') THEN
 EXECUTE IMMEDIATE AlterScript;
END IF;
END

John,

<< The following script updates all char and varchar fields to "not null"
after updating any null strings to empty
strings.  It seems to do what is required but if you are going to run it
back up the database first! >>

Very nice, thank you.

One tip - you don't have to specify lengths for VARCHAR parameters or
variables in procedures, functions, scripts, etc.  They act just like Delphi
and long strings in that you can just declare them as VARCHAR and they'll
expand/contract as necessary.

--
Tim Young
Elevate Software
www.elevatesoft.com

John


That reminded me to find mine - written back in January, haven't tried it recently - I was trying to be able to set each type of field. I think it worked, but reviewing my posts from back then I was having trouble with DESCRIPTION.


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;

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 FldBlock = FldBlock + 'ALTER COLUMN "'+fldName+'" AS ';

 CASE  
  WHEN fldType = 'Integer' THEN
   BEGIN
    SET FldBlock = FldBlock + 'INTEGER ';
    IF fldGenerated THEN     
     SET FldBlock = FldBlock + ' GENERATED ALWAYS 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
    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';
   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 */

END



Roy Lambert

John

From a post by Tim way back in January in response to me

<< That's the easy (decision not doing) one. I'm still working on what to do
with integers, floats, blobs, clobs, dates, times and timestamps. >>

<<Except for BLOBS and CLOBS, which work like strings in terms of NULLs, the
other types work exactly the same as DBISAM, therefore you shouldn't have to
do anything.>>

So both our scripts need a bit more work

Roy Lambert