[SOLVED] New field to the existing table with TSqlite3Dataset
lazarus / 16-Databases

----------------------------------------------------------------------
totya · 2015-05-10 16:16

Hi!

If I want add new filed(s) to the existing table without data loss,
under runtime, this is possible with TSqlite3Dataset?

For example, fields are in the table now:
auto_fd
year_fd

and if I want add field, for example this:

sample_fd.

Thanks!

----------------------------------------------------------------------
rvk · 2015-05-10 18:09

As far as I know, you can't do this with TSqlite3Dataset directly.
(The FieldDefs.Add only works for creating a table)

You'll have to do it directly using the correct SQL-command.

If your table is named CONTACTS (and tbContacts is your
TSqlite3Dataset) and you want to add sample_fd you can do it like
this:
Code: [Select]  tbContacts.Close;
  Sql := 'ALTER TABLE CONTACTS ADD COLUMN sample_fd VARCHAR;';
  tbContacts.ExecSQL(Sql);
  tbContacts.Open;

----------------------------------------------------------------------
totya · 2015-05-10 18:47

Thank you, it's working!

SQL variable isn't a good idea, because this is field of Dataset. So
the "open" command execute this again, then error messages come in:
"already exists field". But this is little problem, doesn't matter,
thanks for the quick answer, master!

Clean way:

Code: [Select]  with Sqlite3Dataset do
  begin
    if FindField('sample_fd')=nil then
    begin
      Close;
        ExecSQL('ALTER TABLE '+TableName+' ADD COLUMN sample_fd
VARCHAR;');
      Open;
    end;
  end;

----------------------------------------------------------------------
rvk · 2015-05-10 19:07

Quote from: totya on May 10, 2015, 08:47:11 pmSQL variable isn't a
good idea, because this is field of Dataset.Well, I try to use the
"with"-construct as little as possible. If you make excessive use of
it, I can guarantee you it will, one time, bite you in the ass

So I would write your code like this:
Code: [Select]if Sqlite3Dataset.FindField('sample_fd') = nil then
begin
  Sqlite3Dataset.Close;
  Sqlite3Dataset.ExecSQL('ALTER TABLE '+TableName+' ADD COLUMN
sample_fd VARCHAR;');
  Sqlite3Dataset.Open;
end;

There are whole discussions about using (or not using) with-construct.

There are some moments where I would use it (for readability) but
always make sure you keep the with-blocks as small as possible. The
larger the blocks get the more problems you might face.

For instance:
Like: http://stackoverflow.com/a/515101/1037511
Code: [Select]with Button1 do
begin
  Height := 20;
end;
Is that Height the Height of the Form or the Height of the Button?
So, don't use it just "out of habit" but only use it when you know it
will improve readability.

Happy coding

----------------------------------------------------------------------
totya · 2015-05-10 19:22

Quote from: rvk on May 10, 2015, 09:07:23 pmCode: [Select]with Button1
do
begin
  Height := 20;
end;
Is that Height the Height of the Form or the Height of the Button?
So, don't use it just "out of habit" but only use it when you know it
will improve readability.

Happy coding

Hi!

I haven't problem with "with", I like it. In this example the "height"
is a "button" height, because this is the owner (the first).
If you want change the Form height, use the "Self.Height" command.

Somtimes I use "two" level with code  But I know the precedence order,
see this:

Code: [Select]procedure TForm1.Button1Click(Sender: TObject);
begin
  with Form1 do
  with button3 do
  with button2 do
  with Button1 do
  begin
    Height := 100;
  end;
end;

Only the button1 height changed, because this is the owner (the
first).

Edit:
Don't forget the with  See:

Quote from: rvk on May 10, 2015, 09:07:23 pmCode: [Select]if
Sqlite3Dataset.FindField('sample_fd') = nil then
begin
  Sqlite3Dataset.Close;
  Sqlite3Dataset.ExecSQL('ALTER TABLE '+TableName+' ADD COLUMN
sample_fd VARCHAR;');
  Sqlite3Dataset.Open;
end;

In my example, this is rename "own" table:

Code: [Select]if Sqlite3Dataset.FindField('sample_fd') = nil then
begin
  Sqlite3Dataset.Close;
  Sqlite3Dataset.ExecSQL('ALTER TABLE '+Sqlite3Dataset.TableName+' ADD
COLUMN sample_fd VARCHAR;');
  Sqlite3Dataset.Open;
end;



Thanks!