?
Solved

creating a dynamic table

Posted on 2003-03-14
20
Medium Priority
?
219 Views
Last Modified: 2010-04-04
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?
0
Comment
Question by:JoAnna
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 3
  • +2
20 Comments
 
LVL 17

Expert Comment

by:geobul
ID: 8140424
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
 

Author Comment

by:JoAnna
ID: 8140434
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
 

Author Comment

by:JoAnna
ID: 8140538
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Expert Comment

by:LizaPaul
ID: 8140935
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
 
LVL 17

Expert Comment

by:geobul
ID: 8142161
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
 
LVL 1

Expert Comment

by:Noturno
ID: 8142620
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
 
LVL 17

Expert Comment

by:geobul
ID: 8142672
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
 
LVL 9

Expert Comment

by:mocarts
ID: 8144041
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
 

Author Comment

by:JoAnna
ID: 8144076
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
 

Author Comment

by:JoAnna
ID: 8144558
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
 
LVL 9

Expert Comment

by:mocarts
ID: 8145978
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
 
LVL 17

Expert Comment

by:geobul
ID: 8151368
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
 
LVL 9

Expert Comment

by:mocarts
ID: 8151456
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
 

Author Comment

by:JoAnna
ID: 8154129
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
 
LVL 17

Expert Comment

by:geobul
ID: 8154833
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
 

Author Comment

by:JoAnna
ID: 8155003
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
 
LVL 17

Accepted Solution

by:
geobul earned 200 total points
ID: 8157742
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
 
LVL 17

Expert Comment

by:geobul
ID: 8157784
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
 

Author Comment

by:JoAnna
ID: 8162097
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
 

Author Comment

by:JoAnna
ID: 8399816
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question