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.

Recommended Answers

All 3 Replies

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

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.

commented: Thanks for sharing +13
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.21 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.