Tim

I think this is going to be possible but want to check with you before I start banging my head against the wall. I was starting to mhack my conversion routine to 1) remove all field descriptions (several are now misleading anyway), and add a default and not null to all the varchars and I suddenly thought - "I should be able to do this in a script".

At this point I have no real idea of the code but was thinking along the lines of:

declare varchar (say X) and a few (not sure yet how many) cursors

open information.tables and loop through it

add to X the opening part of an alter table statement

for each table open/alter selection criteria for information.tablecolumns
loop though and add to X an alter column statement for each column setting description to null (or possibly and empty string
if its a VarChar then add a default and not null

finally execute X

Is that possible, and if, like Blue Peter you have one you prepared earlier......

Roy Lambert

Tim


I thought whilst I'm waiting for you lot over there to wake up I'd have a bit of a play. I've got so far

SCRIPT
BEGIN

DECLARE MainBlock VARCHAR;
DECLARE tblCursor CURSOR FOR tbls;
DECLARE fldCursor CURSOR FOR flds;
DECLARE tblName VARCHAR;
DECLARE fldName VARCHAR;
DECLARE fldType 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+'"'
PREPARE flds FROM 'SELECT * FROM Information.TableColumns WHERE TableName = '''+tblName+'''';
OPEN fldCursor;
FETCH FIRST FROM fldCursor (Name, Type) INTO fldName, fldType;
WHILE NOT EOF(fldCursor) DO  


FETCH NEXT FROM fldCursor (Name, Type) INTO fldName, fldType;
END WHILE; /* end of columns */
FETCH NEXT FROM tblCursor INTO tblName;
END WHILE; /* end of tables */

END

It runs (ie no errors and control is returned to the script). Playing with standalone sql for ALTER COLUMN AS it looks as though a column has to be extensively defined unlike the DBISAM version where I could miss out type etc. Is that correct? I'm sure I've seen references to it somewhere but can't now find it either in ngs, manuals or new version emails.

What is the minimum I can use in an alter column clause please.

Roy Lambert

Tim
I've made it this far but now need some assistance (I think)

ElevateDB Error #700 An error was found in the statement at line 2317 and column 20 (Missing INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, GRANT, REVOKE, BACKUP, RESTORE, REPAIR, OPTIMIZE, IMPORT, EXPORT, DISCONNECT, REMOVE)

SCRIPT
BEGIN

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;

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

 CASE
  WHEN fldType = 'BLOB' THEN
   BEGIN
    SET MainBlock = MainBlock + 'BLOB';
   END;
  WHEN fldType = 'Boolean' THEN
   BEGIN
    SET MainBlock = MainBlock + 'BOOLEAN';
   END;
  WHEN fldType = 'CLOB' THEN
   BEGIN
    SET MainBlock = MainBlock + 'CLOB COLLATE "ANSI"';
   END;
  WHEN fldType = 'Date' THEN
   BEGIN
    SET MainBlock = MainBlock + 'DATE';
   END;
  WHEN fldType = 'Decimal' THEN
   BEGIN  
    SET MainBlock = MainBlock + 'DECIMAL(' + CAST(fldLength AS VARCHAR)+','+ CAST(fldScale AS VARCHAR)+')';
   END;
  WHEN fldType = 'Float' THEN
   BEGIN
    SET MainBlock = MainBlock + 'FLOAT';
   END;
  WHEN fldType = 'Integer' THEN
    SET MainBlock = MainBlock + 'INTEGER';
   BEGIN
   END;
  WHEN fldType = 'Time' THEN
   BEGIN
    SET MainBlock = MainBlock + 'TIME';
   END;
  WHEN fldType = 'TimeStamp' THEN
   BEGIN           
    SET MainBlock = MainBlock + 'TIMESTAMP';
   END;
  WHEN fldType = 'VarChar' THEN
   BEGIN                                   
    SET MainBlock = MainBlock + 'VARCHAR(' + CAST(fldLength AS VARCHAR)+')';
   END;
 END CASE; /* of case */

 SET MainBlock = MainBlock + ' DESCRIPTION ''''';

 FETCH NEXT FROM fldCursor (Name, Type, Length, Scale) INTO fldName, fldType, fldLength, fldScale;
 IF NOT EOF(fldCursor) THEN
  SET MainBlock = MainBlock +',';
 END IF;
END WHILE; /* end of columns */
SET MainBlock = MainBlock + ' DESCRIPTION ''''';
EXECUTE IMMEDIATE MainBlock;
SET MainBlock = '';
FETCH NEXT FROM tblCursor INTO tblName;
END WHILE; /* end of tables */  

END


Roy Lambert

Roy,

<< I've made it this far but now need some assistance (I think)

ElevateDB Error #700 An error was found in the statement at line 2317 and
column 20 (Missing INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, GRANT,
REVOKE, BACKUP, RESTORE, REPAIR, OPTIMIZE, IMPORT, EXPORT, DISCONNECT,
REMOVE) >>

I'm not seeing that here.  I'm seeing an error about a missing data type,
and that's due to this line:

WHEN fldType = 'TimeStamp' THEN

which, if you're going to do a case-sensitive comparison, you should use
'Timestamp' instead.  However, in general I would recommend a
case-insensitive comparison on the data type variable by marking it as being
case-insensitive:

DECLARE fldType VARCHAR COLLATE ANSI_CI;

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

BTW, nice script.  You pretty much have the hang of things if you're writing
scripts like that.

--
Tim Young
Elevate Software
www.elevatesoft.com

Continuing to try. Hopefully this will help you explain where I'm going wrong. To test I thought I'd write the string I'm generating into the table's description (only way I could think of to get at it). If I comment out the CASE block it works. Leave the CASE block in and I get

ElevateDB Error #700 An error was found in the statement at line 2020 and column 20 (Missing INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, GRANT, REVOKE, BACKUP, RESTORE, REPAIR, OPTIMIZE, IMPORT, EXPORT, DISCONNECT, REMOVE)

I think I'm now best described as totally stuck.

SCRIPT
BEGIN
DECLARE AQuote VARCHAR;
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;

SET AQuote = '''';
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) INTO fldName, fldType, fldLength, fldScale, fldCollate;
WHILE NOT EOF(fldCursor) DO  
 SET FldBlock = FldBlock + 'ALTER COLUMN "'+fldName+'" AS ';

 CASE
  WHEN fldType = 'CLOB' THEN
   BEGIN
    SET FldBlock = FldBlock + 'CLOB COLLATE "' + fldCollate + '"';
   END;
  WHEN fldType = 'Decimal' THEN
   BEGIN  
    SET FldBlock = FldBlock + 'DECIMAL(' + CAST(fldLength AS VARCHAR)+','+ CAST(fldScale AS VARCHAR)+')';
   END;
  WHEN fldType = 'VarChar' THEN
   BEGIN                                   
    SET FldBlock = FldBlock + 'VARCHAR(' + CAST(fldLength AS VARCHAR)+') COLLATE "' + fldCollate + '"';
   END;
 ELSE
   BEGIN
    SET FldBlock = FldBlock + fldType;
   END;
 END CASE;

 SET FldBlock = FldBlock + ' DESCRIPTION '+AQuote+AQuote+AQuote+AQuote;
 FETCH NEXT FROM fldCursor (Name, Type, Length, Scale, Collation) INTO fldName, fldType, fldLength, fldScale, fldCollate;

 IF NOT EOF(fldCursor) THEN
  SET FldBlock = FldBlock +',';
 END IF;

END WHILE; /* end of columns */

/*
SET MainBlock = MainBlock + FldBlock;
*/
SET MainBlock = MainBlock + ' DESCRIPTION ' + AQuote + FldBlock + AQuote;
EXECUTE IMMEDIATE MainBlock;
SET MainBlock = '';
FETCH NEXT FROM tblCursor INTO tblName;
END WHILE; /* end of tables */  

END



Roy Lambert

Tim


>BTW, nice script. You pretty much have the hang of things if you're writing
>scripts like that.

If that were true why is it beating me up?

It takes me back to my uni days - put in a card stack for an overnight run and you get back - mismatched brackets.......

Roy Lambert

Tim


A really interesting question is why are we getting different errors. Same script, same tables different error I don't like the sound of that. Any idea why.

Also if you look at the last script I sent you'll see I altered the CASE block (I realised that in several cases I could just feed through the type info) and still get the same errors.

>which, if you're going to do a case-sensitive comparison, you should use
>'Timestamp' instead. However, in general I would recommend a
>case-insensitive comparison on the data type variable by marking it as being
>case-insensitive:
>
>DECLARE fldType VARCHAR COLLATE ANSI_CI;

Didn't know you could do that - please add to my suggestions for the manual.

Roy Lambert

Tim


I've found the problem (lots of commenting and uncommenting) and I should have known its down to NULL <vbg>. When you create a decimal field to quote your code you get

ALTER TABLE "Billing"
ALTER COLUMN "_SubTotal" AS DECIMAL(20,2) NOT NULL

The 20 I would have expected to see stored in information.tablecolumns in Length but it isn't. That field is NULL which obviously generates some interesting results.

I altered

    SET FldBlock = FldBlock + 'DECIMAL(CAST(fldLength AS VARCHAR) + ',' '+ CAST(fldScale AS VARCHAR)+')';

to

    SET FldBlock = FldBlock + 'DECIMAL(20,'+ CAST(fldScale AS VARCHAR)+')';

and it works.

I can see where you've come from here - you don't allow it to be entered, its always going to be 20 but it might not be a bad idea to store it since it is a required parameter in creating the column.

Roy Lambert

Roy,

<< The 20 I would have expected to see stored in information.tablecolumns in
Length but it isn't. That field is NULL which obviously generates some
interesting results.>>

That's because Length is a length for variable and fixed-length types, not a
precision.  I'll see about adding a Precision column, however.

--
Tim Young
Elevate Software
www.elevatesoft.com