creating a dynamic table

Hi,

Can you use TQuery instead of TTable? If yes then use ALTER TABLE sql statement like:

procedure TForm1.Button1Click(Sender: TObject);
begin
  Fnshort := 'Field2'; // you already have this value
  try
    with Query1 do begin
      Close;
      SQL.Clear;
      SQL.Add('ALTER TABLE files'); // change the name of the table
      SQL.Add('ADD ' + Fnshort + ' CHAR(20)'); // change the field type and size
      ExecSQL;
    end;
  except
    // do nothing: column exists
  end;
end;

Regards, Geo

P.S.: As far as I remember DBase tables can hold up to 256 columns. If this is not enough for your purposes perhaps you'll need a new table design.

256 columns will be enough for sure..
I will try the TQuery, but I've never worked with it before, so I don't know if it can do everything I want..

After re-reading your comment, I think I see what you are trying to do.. You would have to get a number of fields predefined in the table creation script and then change one of them when a new file would be encountered.
That is not really what I want, because if I do not use those columns, they'll just be empty and they will slow the other processes down.
If my interpretation is wrong, please let me know..

You can alter the table using the query indicated as you encounter the need to add a field to the table. This will then decrease the load on the system.

Hi,

What I'm suggesting is:

1. Try to add a new column (using a filename as the name of the column to be added).
2. if there is no such column in the table then the table will be altered and the new column will be created with no data in that field.
3. else nothing will happen (except part of try..except block).

Using this logic I don't need to know whether the column exists beforehand because:
1. the ALTER TABLE statement will add the new column if it doesn't exist.
2. the ALTER TABLE statement will fail without changing anything if the column that already exists (no new columns, no modifications in table data, simply nothing).

Regards, Geo

For give me for my poor english (i'm a brazilian guy still learning your language)

One interesting solution is maintain a table (let's call T1) with the names of the files in each line. When you search the files, you look for its names in the T1 table. If there isn't in this table then add to the dynamic table using ALTER TABLE and add a line to the table T1 with the file name.
Using this logic you will work with speed and security.

P.S.: Don't forget of use try..except.

Regards, Noturno (I'm copying parts of texts for a english learning proposit)

Hi JoAnna,

Forgot to say that there are NO predefined fields in the table. 'ALTER TABLE .. ADD' means 'add a new column' not 'modify an existing one'.

1. Table 'files' has one field in the beginning: Field1.

2. Execute the code above with 'Fnshort := 'Field2';'. Then 'files' will have two fields: Field1 and Field2.

3. Execute the code above with 'Fnshort := 'Field2';' again. Then 'files' will still have two fields: Field1 and Field2.

4. Execute the code above with 'Fnshort := 'Field3';'. Then 'files' will have three fields: Field1, Field2 and Field3.

I don't think that there will be an impact on the performance when the desired field already exists because no actual modification will be performed. The SQL server (or database engine) will get all field definitions for you, will check if the new one exists and will raise an exception. (which we will handle in try..except)

Regards, Geo

hi, JoAnna.
can you tell us purpose of this table? how do you want to use it?
generally it's not good design to change fields of table in program (for this action tables have records).
maybe you need two talbes or some other structure of your table?

wbr, mo.

Sorry I had gone cycling as the weather was so nice :)..
The table is used to store values for a number of variables.
From each file I read the variables and the values, have Delphi checking whether the variables already exist (if not, they are added) but first it will check if this file has been read before (as can be seen from the column name).
It will look like this:

varname  file1    file2    ...
var1     0,001    0,002    ...
var2     1,4      1,2      ...
prec     2.5      3.6      ...
...      ...      ....     ...

In another part of the program, the values will be read again and converted into the format required by one of the environmental models.
In general it is way more suitable to create a database for this purpose, but my supervisor insists I have to use dBASE files, so that is what I'm doing..

Okay, Geo's script works fine for the first file, but when attempting to read the second file, Delphi will give a "key violation" error...
Second problem I encountered was that I couldn't do another query after the first one. For sure I'm blond (so forgive me if this question is really dumb), does it have anything to with the ALTER TABLE statement? (Does it require some kind of UPDATE afterwards to get the status back to normal?) I briefly went through some documents on the internet, but couldn't find anything there, nor in the Interbase SQL Reference Help..

i see..
I tell you how I would preffer to design tables in such case - one table for files, another for variables. or, if I have requirement to have only one table - one table with filenames and variable values, but not filenames as columns.
one table solution:

create table filevar (FileName char(32), VarName char(32), VarValue yourtype(..))

two table solution (normalized form):
create table FILES (PK_FILE int not null, FILE_NAME char(32) not null, constraint PK_FILES primary key (PK_FILE))

create table FILEVARS (PK_FILE int not null, VAR_NAME char
(32) not null, VAR_VALUE char(32), constraint PK_FILEVARS primary key (PK_FILE, VAR_NAME))

synatx is not checked and can be different for dbase driver.

for one table solution you simply add records with file name, variable name and variable value as needed.
when you need to select variable (or variables) for concrete file you can use TTable's filter property or by using TQuery component /SQL.Text := 'SELECT VarName, VarValue FROM filevar WHERE FileName = '+QuotedStr(sFileName)/

and interate through result set to get all var names and values for file.

wbr, mo.

Hi,

I'd like to say first that I agree with mocarts. I've used ALTER TABLE statement creating an 'upgrade' program to a newer version of an already existing database. You may go further normalizing your database (if VAR_NAMEs are greater then 4 bytes) creating a third table with VAR names and their IDs (PK_VAR int not null). Then FILEVARS table would contain PK_FILE, PK_VAR and VAR_VALUE.

Leaving that aside, you have to close any TTable and TQuery components that are using that table before altering it (ExecSQL with ALTER TABLE statement) and reopen them afterwards.

What is the filename when you are executing the statement for the second time? Already existing field or a new one? If it's an existing one then running the app from Delphi IDE will show an exception. Save your app, build it, run it as a separate exe and that exception won't appear anymore. Or from inside the IDE, after the exception, press 'Run' again to continue the execution.

Regards, Geo

or before executing alter table statement check for field presence in table ;)
procedure TForm1.Button1Click(Sender: TObject);
begin
 Fnshort := 'Field2'; // you already have this value
 // if exists you don't need to execute statement
 if assigned(TTable1.FindField(Fnshort)) then exit;

 try
   with Query1 do begin
   ..
 except
 end;
end;

and, yes, geobul, for full normalization form there must be 3d table for variable names.

wbr, mo.

hi people,
Hope you've had a wonderful weekend too..

The problem does not occur when the field already exists, just when you try to add new field, it will give the "key violation" error and I have no idea why as I think I've done exactly what you've told me to:

procedure TFcent.CvtbtnClick(Sender: TObject);
var i, a, Posreturn, P2, Psp : integer;
temp, value, varname : string;
begin
  Table1.Active := false;
  try
  with Query1 do begin
    Close;
    SQL.Clear;
    SQL.Add('ALTER TABLE ' + Table1.TableName);              
    SQL.Add('ADD ' + Fnshort + ' FLOAT');            
    ExecSQL;
  end;
  except
  end;
//more

Any clues??

Hi JoAnna,

Try this change, please:

SQL.Add('ALTER TABLE ' + QuotedStr(Table1.TableName));

I have a TTable, TQuery, TDataSource and TDBGrid on a form. The grid shows the table via DataSource. Using the following code I can add many columns one after another (the names of the coulmns come from an edit box) and see them immediately:

procedure TForm1.Button1Click(Sender: TObject);
begin
  Table1.Close;
  try
    with Query1 do begin
      SQL.Clear;
      SQL.Add('ALTER TABLE '+ QuotedStr(Table1.TableName));
      SQL.Add('ADD '+Edit1.Text+' FLOAT');
      ExecSQL;
    end;
  except
  end;
  Table1.Open;
end;

Regards, Geo

No idea why it doesn't work, but now I cannot even get the first file to be read..
I'll past the entire thing & pray that you can see what is wrong. It must be something else, like the connection, I don't know :(.

unit centu;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, Menus, StdCtrls, DB, Grids, DBGrids, DBTables;

type
  TFcent = class(TForm)
    MainMenu1: TMainMenu;
    Table1: TTable;
    Database1: TDatabase;
    DBGrid1: TDBGrid;
    DataSource1: TDataSource;
    OpenDialog1: TOpenDialog;
    SaveDialog1: TSaveDialog;
    Memo1: TMemo;
    File1: TMenuItem;
    Open1: TMenuItem;
    Save1: TMenuItem;
    Close1: TMenuItem;
    Cvtbtn: TButton;
    Query1: TQuery;
    procedure Close1Click(Sender: TObject);
    procedure Open1Click(Sender: TObject);
    procedure CvtbtnClick(Sender: TObject);
  private
    { Private declarations }
  public
    procedure createTable(Tname : String);
    function RemExt(Filen : string) : string;
    { Public declarations }
  end;

var
  Fcent: TFcent;
  Fname, Fnshort : string;

implementation

{$R *.dfm}

procedure TFcent.Close1Click(Sender: TObject);
begin
  Close;
end;

procedure TFcent.Open1Click(Sender: TObject);
begin
  OpenDialog1.Filter :='Century 4 site files (*.100)|*.100';
  if OpenDialog1.Execute then
    begin
      Memo1.Clear;
      Fname := ExtractFileName(OpenDialog1.Filename);
      Fnshort := RemExt(Fname);
      Fcent.Caption := 'Opening ' + Fname;
      Memo1.Lines.LoadFromFile(OpenDialog1.Filename);
      CreateTable('whatever');
    end;
end;

procedure TFcent.CvtbtnClick(Sender: TObject);
var i, a, Posreturn, P2, Psp : integer;
temp, value, varname : string;
begin
  Table1.Close;
  try
  with Query1 do begin
    Close;
    SQL.Clear;
    SQL.Add('ALTER TABLE ' + QuotedStr(Table1.TableName));
    SQL.Add('ADD ' + Fnshort + ' FLOAT');
    ExecSQL;
  end;
  except
  end;
    for i := 0 to Memo1.Lines.Count - 1 do
    begin
    Memo1.SetFocus;
    Memo1.SelStart := 0;
    Memo1.SelLength := 0;
    temp := Memo1.Lines[i];
    a := Memo1.SelStart + Memo1.SelLength;
    Posreturn := Pos(temp, Copy(Memo1.Text, a + 1, Length(Memo1.Text) - a)) - 1;
    if Posreturn <> 0 then
    begin
      P2 := Pos('.',temp);
      if P2 > 0 then
      begin
        value := '';
        varname := '';
        Psp := Pos(' ',temp);
        Memo1.SetFocus;
        Memo1.SelStart := Posreturn;
        Memo1.SelLength := Psp;
        value := Memo1.SelText;
        Memo1.SetFocus;
        Memo1.SelStart := Posreturn + 18;
        Memo1.SelLength := Length(temp) - 19;
        varname := Memo1.SelText;
        Table1.Close;
      try
        with Query1 do begin
          Close;
           Sql.Add('select ' + varname + ' from table ' + Table1.TableName + ' where ' + fnshort + ' = '''' ');
           Sql.Add('insert into ' + table1.tablename + '(' + varname + ')' + ' values (' + value + ')');
           ExecSQL;
        end;
      except
      end;
        With Table1 do begin
          Active := true;
          Append;
          Insert;
          Fieldbyname('VARIA1').AsString := varname;
          Fieldbyname(fnshort).asFloat := StrToFloat(value);
          Next;
        end;
      end;
    end;
  end;
  Table1.StoreDefs := true;
  Table1.Close;
end;

procedure TFcent.createTable(Tname: String);
begin
  Database1.LoginPrompt := false;
  Table1.TableName := Tname;
  if Table1.exists then begin
  end
  else begin
    With Table1 do begin
    Databasename := Database1.databasename;
    Database1.Connected := true;
    Active := false;
    TableName := Tname;
    Tabletype := ttDBase;
    Tablelevel := 3;
      with FieldDefs do begin
        Clear;
        with AddFieldDef do begin
          Name := 'VARIA1';
          DataType := ftString;
        end;
      end;
    CreateTable;
    end;
  end;
end;

function TFcent.RemExt(Filen: string): string;
var t : integer;
begin
  Result := '';
  for t := 1 to Length(Filen) - 4 do
    Result := Result + Filen[t];
end;

end.

Remove the line:
Table1.Close;
at the beginning of the above code. It's useless.
---
Perhaps you'll want to open the table at the end in order to view the new rows/columns in the grid.

Replace the last line at the end of the procedure:
  Table1.Close;
with:
  Table1.Open;

Regards, Geo

Geo, thank you very much for your help so far...
I keep having problems with the SQL though.
The program seems to be somewhat inconsistent, sometimes it will give an error when trying to create a new column (but usually it works) & it always give an error (General SQL or Key violation) after executing this line (so at the Query1.Open-statement):
SQL.Add('SELECT * FROM ' + QuotedStr(Table1.TableName) + ' WHERE VARIA1=' + QuotedStr(varname));
The datasource for the query = Datasource1 & the dataset for the datasource = Table1.
Does anyone have a magic spell to make it work, 'cause I really think it should be working, it just doesn't...

Sorry!!! I had completely forgotten that I had still had this question open, as I had started doing something completely different (no programming) & of course that didn't go well right away either (they had given me the wrong datasets).
I haven't been able (yet) to locate the exact problem why the silly program didn't work correctly, but I'm sure that your solution was supposed to do everything I asked for.
So, sorry again for the delay & many thanks for all of your help..