On 11/10/2012 12:17 AM, Roy Lambert wrote:
> Phil
>
>
> And here's the "better way"
>
> SCRIPT
> BEGIN
> DECLARE Moder VARCHAR DEFAULT '';
> SET Moder = AddFieldIfNotExists('Adam','Test2',Moder,'BOOLEAN');
> SET Moder = Moder + AddFieldIfNotExists('Adam','Test3',Moder,'INTEGER');
> SET Moder = Moder + AddFieldIfNotExists('Adam','Test4',Moder,'VARCHAR(17) COLLATE ANSI');
> IF Moder <> '' THEN
> EXECUTE IMMEDIATE 'ALTER TABLE Adam ' + Moder;
> END IF;
> END
>
>
> ALTER FUNCTION "AddFieldIfNotExists" (IN "Tbl" VARCHAR COLLATE ANSI_CI, IN "Fld" VARCHAR COLLATE ANSI_CI, IN Current VARCHAR COLLATE ANSI_CI, IN NewFldDef VARCHAR COLLATE ANSI_CI)
> RETURNS VARCHAR COLLATE ANSI_CI
> BEGIN
> DECLARE Source CURSOR FOR Cat;
> DECLARE Checker VARCHAR COLLATE ANSI_CI;
> DECLARE Result VARCHAR COLLATE ANSI_CI DEFAULT NULL;
> PREPARE Cat FROM 'SELECT Name FROM Information.TableColumns WHERE TableName = ? AND Name = ?';
> OPEN Source USING Tbl,Fld;
> FETCH FIRST FROM Source('Name') INTO Checker;
> IF Checker IS NULL THEN
> IF Current <> '' THEN
>    SET Result = ', ';
> END IF;
> SET Result = Result +'ADD COLUMN '+Fld+' '+ NewFldDef;
> END IF;
> RETURN Result;
> END
>
>
>  From this you should be able to work out how to delete columns or alter them if they're already there.
>
> Roy Lambert [Team Elevate]
>

That's brilliant, thanks soooo much Roy!

Just a side note Roy... Fields where not getting added to the table and
I couldn't work out why, but I realised the Function was checking for <>
'' when the initial value was NULL.

So I changed:

IF Current <> '' THEN

to ...

IF Current IS NOT NULL THEN

and it worked perfectly.

Thanks again.

Phil


>Just a side note Roy... Fields where not getting added to the table and
>I couldn't work out why, but I realised the Function was checking for <>
>'' when the initial value was NULL.

That's interesting and I think its a bug.

DECLARE Moder VARCHAR DEFAULT '';
SET Moder = AddFieldIfNotExists('Adam','Test2',Moder,'BOOLEAN');

This to me means that Moder (which is passed in as Current) should be '' not NULL. I've sent an email to Tim for clarification.

Roy Lambert [Team Elevate]

Phil


Tim's pointed out the error of my ways. Alter

DECLARE Result VARCHAR COLLATE ANSI_CI DEFAULT NULL;

to

DECLARE Result VARCHAR COLLATE ANSI_CI DEFAULT '';

and change the test back and it should work.



Roy Lambert

On 12/10/2012 4:56 AM, Roy Lambert wrote:
> Phil
>
>
> Tim's pointed out the error of my ways. Alter
>
> DECLARE Result VARCHAR COLLATE ANSI_CI DEFAULT NULL;
>
> to
>
> DECLARE Result VARCHAR COLLATE ANSI_CI DEFAULT '';
>
> and change the test back and it should work.
>
>
>
> Roy Lambert
>

Thanks Roy,

Yes that makes sense now, and it works well.

Thank you again for your time on this.

Phil