Lazarus

Programming => Databases => Topic started by: totya on May 10, 2015, 06:16:21 pm

Title: [SOLVED] New field to the existing table with TSqlite3Dataset
Post by: totya on May 10, 2015, 06:16:21 pm

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!

Title: Re: New field to the existing table with TSqlite3Dataset
Post by: rvk on May 10, 2015, 08:09:47 pm

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;


Title: Re: New field to the existing table with TSqlite3Dataset
Post by: totya on May 10, 2015, 08:47:11 pm

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;


Title: Re: New field to the existing table with TSqlite3Dataset
Post by: rvk on May 10, 2015, 09:07:23 pm

Quote from: totya on May 10, 2015, 08:47:11 pm

SQL 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 :)

Title: Re: New field to the existing table with TSqlite3Dataset
Post by: totya on May 10, 2015, 09:22:30 pm

Quote from: rvk on May 10, 2015, 09:07:23 pm

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 :)

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 pm

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;


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!