creating a dynamic table

I'm trying to get data from different files into one table.
A dBASE table is created if it is not existing and further on in the program, for each new file that is opened, another column should be created having the name of the file that is being opened.
I've already tried it with:
  Table1.Active := false;
  if Table1.FindField(Fnshort) = nil then begin //where Fnshort is the current filename
    NewField := TStringField.Create(self);
    Newfield.FieldName := Fnshort;
    NewField.FieldKind := fkData;
    Table1.fields.Add(NewField);
this doesn't work.

Another way I've tried was with:
    Table1.FieldDefs.AddFieldDef.Name := Fnshort;
    Table1.FieldDefs.AddFieldDef.DataType := ftFloat;
but I guess that doesn't work because I am not using CreateTable afterwards (as that would erase the already added data).

Can anyone tell me whether it is possible to add a column to a table after it has been created (without creating a new table and copying the old one into the new one) & if so, how do I do that?
JoAnnaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

geobulCommented:
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.
0
JoAnnaAuthor Commented:
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..
0
JoAnnaAuthor Commented:
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..
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

LizaPaulCommented:
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.
0
geobulCommented:
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
0
NoturnoCommented:
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)
0
geobulCommented:
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
0
mocartsCommented:
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.
0
JoAnnaAuthor Commented:
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..
0
JoAnnaAuthor Commented:
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..
0
mocartsCommented:
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.
0
geobulCommented:
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
0
mocartsCommented:
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.
0
JoAnnaAuthor Commented:
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??

0
geobulCommented:
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
0
JoAnnaAuthor Commented:
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.
0
geobulCommented:
Hi,

You want to add the new varables and their values in Table1, don't you? And 'VARIA1' is the column name where varname has to be stored?

Your table contains fields like:
VARIA1 filename1 filename2 ...

Replace this part:
---
       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;
     except
     end;
---
with:
---
Table1.Close;
      try
        // check if the variable name already exists
        with Query1 do begin
          Close;
          SQL.Clear;
          SQL.Add('SELECT * FROM ' + QuotedStr(Table1.TableName) + ' WHERE VARIA1=' + QuotedStr(varname));
          Open;
          First;
          if EOF then begin // the varname doesn't exist - we must insert a new row
            Close;
            SQL.Clear;
            SQL.Add('INSERT INTO ' + QuotedStr(Table1.TableName) + '(VARIA1,' + fnshort + ')');
            SQL.Add('VALUES (' + QuotedStr(varname) + ','+ value + ')');
            ExecSQL;
          end else begin // the varname exists - we must update that row
            Close;
            SQL.Clear;
            SQL.Add('UPDATE ' + QuotedStr(Table1.TableName) + ' SET');
            SQL.Add(fnshort + '=' + value);
            SQL.Add('WHERE VARIA1=' + QuotedStr(varname));
            ExecSQL;
          end;
        end;
      except
        ShowMessage('Error adding ' + varname + ' for file ' + fnshort);
      end;
---

Regards, Geo
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
geobulCommented:
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
0
JoAnnaAuthor Commented:
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...
0
JoAnnaAuthor Commented:
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..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.