Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB Extensions » View Thread |
Messages 1 to 4 of 4 total |
Converting Null Strings to Empty Strings and making them not nullable |
Thu, Jun 19 2008 1:15 PM | Permanent Link |
"John Hay" | 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 |
Thu, Jun 19 2008 1:23 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Thu, Jun 19 2008 2:04 PM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Fri, Jun 20 2008 10:03 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |