SQL and automatic update of tables

[Morten Brendefu]

Dear knowledgeable ones
A new problem have arisen.
I have now an SQL database file with about 100 tables in it.
I would like to automatically go through all of these tables and check if the column "Owner" exist.
If this field does not exist, then it should be created and hold a value of type Integer.
This value must also be set to "1" for all existing rows in the table, but not as a "default" value.
Whenever I try to work this functionality I always get an error if the column of the table does not exist.

I try doing this from Delphi via Firebird SQL version 1.5, but I find no functionality for checking for column ID without causing an exception.

What I am after could be programmatically described as:

FOR x:=1 TO TotalCountOfTablesInDataBase DO BEGIN
WITH Table x DO BEGIN
IF EXIST(Column("Owner"))=FALSE THEN BEGIN
CREATE Column("Owner", INTEGER);
SELECT ALL ROWS;
SET Column("Owner")=1;
END;
END;
END;

I don't need error exception handling, but can this functionality be made without?
Delphi uses InterBase components and the SQL part installed is Firebird version 1.5

Many thanks in advance.

[pritaeas]

Perhaps this can help: How to get a list of tables, views and columns in Firebird database?

[Morten Brendefu]

Amazing.
I have not tested this yet, but now I will.
I feel 100% confident that this can do the trick. It will be some extra delphi coding, but this is after all a delphi thread :D

Thank you so much for this link.
Best regards,
Morten

[Morten Brendefu]

As a last comment, I would like to add that above link gave me the input needed in order to solve my problem successfully.

Instead of trying to make SQL queries involving a column name that may not exist and by that create an error of which creates the need of exception handling, I now simply list the tables and column names and search via Delphi.
If the column is not found, then I can add it via SQL and that will not create errors and the need of exception handling.
All done with Delphi/TurboPascal logic.

Indeed a very useful link as it gives ideas of what other things we can do too.