Topic: check if column exist in table
Hi all, How can I check by script if a column exist in a table. and if not how to create it ?
Re: check if column exist in table
Attached is a sample way of doing it through script. The table is named TestTable with one column named Field1. The form has a button to check for Field2 column existence and if it does not exist, it can add it. I am using the form for user interaction, but typically you would probably do this without user interaction. You can do this behind the scenes without showing the form at all.
Post's attachments
Column Exists.zip 336.45 kb, 222 downloads since 2021-08-18
Re: check if column exist in table
thank you ehwanger. I am not so familiar with SQL
I do not understand the line SqlQuery('pragma table_info(TestTable)',ColResults);
as I understand the Test_table is the name of the table and the Field2 is the name of the record
what is the pragma??
thank you
4 2021-08-19 03:28:30 (edited by ehwagner 2021-08-19 03:41:37)
Re: check if column exist in table
Pragma is a sqlite function that can be used to retrieve various technical info about the database. Below is a link for more information about its specific uses.
[https://sqlite.org/pragma.html]
The SqlQuery using the pragma function retrieves all the columns of a table and puts them in a dataset called ColResults. Then using the while statement I go through each row of the dataset which in this case are the columns in the specified table. I check for the col name of Field2. If it finds the column name Field2 it sets the boolean field FieldExists to TRUE otherwise it remains FALSE and can be added to the table in the ALTER statement. Hope this helps.
Re: check if column exist in table
Yes now it's clear. Thank's again
Re: check if column exist in table
v_pozidis wrote:
Yes now it's clear. Thank's again
I converted the procedure from the project attached to this topic into a function, as follows:
Function Add_newfield(tabela,novo_campo,tipo,e_default,dado: string):nil;
var
ColResults: TDataset;
FieldExists: Boolean;
begin
SqlQuery('pragma table_info(['+tabela+']) ',ColResults);
while not ColResults.Eof do
begin
if UpperCase( ColResults.FieldByName('name').asString ) = UpperCase( novo_campo ) then
FieldExists := True;
ColResults.Next;
End;
ColResults.Free;
If FieldExists = False then
Begin
SqlExecute('ALTER Table ['+tabela+'] ADD COLUMN ['+novo_campo+'] '+tipo+' '+E_DEFAULT+' '+dado+' ');
if length(trim(dado)) > 0 then
sqlexecute('UPDATE ['+tabela+'] SET ['+novo_campo+'] ='+dado+' ');
End;
end;
procedure Form1_OnShow (Sender: TObject; Action: string);
begin
//***************** CRIAR CAMPO NO SQLITE.DB ************************
// TEXT - INTEGER - REAL - BOOLEAN - CURRENCY -DATE/TIME - DATE - TIME - IMAGE
// Add_newfield(TABELA,NOVO_CAMPO,TIPO,E_DEFAULT,DADO);
Add_newfield('tasks','favorito','BOOLEAN','DEFAULT','0');
Add_newfield('tasks','dias_faltam','INTEGER','DEFAULT','0');
Add_newfield('tasks','dias_adiar','INTEGER','DEFAULT','0');
Add_newfield('tasks','link_recno','INTEGER','','');
//***************** CRIAR CAMPO NO SQLITE.DB ************************
end;Roberto Alencar
Re: check if column exist in table
Procedures and functions, referred to collectively as routines, are self-contained statement blocks that can be called from different locations in a program. A function is a routine that returns a value when it executes. A procedure is a routine that does not return a value.
- k245
- Member
- Offline
Re: check if column exist in table
sparrow wrote:
Procedures and functions, referred to collectively as routines, are self-contained statement blocks that can be called from different locations in a program. A function is a routine that returns a value when it executes. A procedure is a routine that does not return a value.
Let's change the code a little so that there is no cognitive dissonance. )) The function will return true if the field is added, and false if the field already exists.
Function Add_newfield(tabela,novo_campo,tipo,e_default,dado: string):boolean;
var
ColResults: TDataset;
begin
Result := True;
SqlQuery('pragma table_info(['+tabela+']) ',ColResults);
while not ColResults.Eof do
begin
if UpperCase( ColResults.FieldByName('name').asString ) = UpperCase( novo_campo ) then
begin
Result := False;
Break;
end;
ColResults.Next;
End;
ColResults.Free;
If Result then
Begin
SqlExecute('ALTER Table ['+tabela+'] ADD COLUMN ['+novo_campo+'] '+tipo+' '+E_DEFAULT+' '+dado+' ');
if length(trim(dado)) > 0 then
sqlexecute('UPDATE ['+tabela+'] SET ['+novo_campo+'] ='+dado+' ');
End;
end;Визуальное программирование: блог и телеграм-канал.
Re: check if column exist in table
k245 wrote:
sparrow wrote:Procedures and functions, referred to collectively as routines, are self-contained statement blocks that can be called from different locations in a program. A function is a routine that returns a value when it executes. A procedure is a routine that does not return a value.
Let's change the code a little so that there is no cognitive dissonance. )) The function will return true if the field is added, and false if the field already exists.
Function Add_newfield(tabela,novo_campo,tipo,e_default,dado: string):boolean; var ColResults: TDataset; begin Result := True; SqlQuery('pragma table_info(['+tabela+']) ',ColResults); while not ColResults.Eof do begin if UpperCase( ColResults.FieldByName('name').asString ) = UpperCase( novo_campo ) then begin Result := False; Break; end; ColResults.Next; End; ColResults.Free; If Result then Begin SqlExecute('ALTER Table ['+tabela+'] ADD COLUMN ['+novo_campo+'] '+tipo+' '+E_DEFAULT+' '+dado+' '); if length(trim(dado)) > 0 then sqlexecute('UPDATE ['+tabela+'] SET ['+novo_campo+'] ='+dado+' '); End; end;
Sparrow and K245, tankyou.
Roberto Alencar