Creating a Table on the Fly

[tjc240e]

Is there a best way to create a table on the fly?

Basically what I have done is manually made a few DBF tables that I write pulled information into and then use those temperary tables to create a report. What I ultimately would like to do is when they click a button it would create the TEMP.DBF table and then populate all the fields with the pulled information and then run my report and then delete the table once the report is finished.

Suggestions for creating tables on the fly greatly appreciated.

TIA

 

[rcloutie]

SQL CREATE TABLE

 

[tjc240e]

What if i'm not using a SQL database?
I also don't see anything in Delphi Help regarding SQL CREATE TABLE...

Is this similar to CreateTable, TTable?

 

[jbpelletier]

hi,

he means create a table via a querry. I dont think u can find such a thing in the help of delphi, since it's a SQL statement and it depend on the database system (would diffent in Oracle, Dbase, paradox etc) but if u google "create table" with the database system u'll use u gonna find

jb

 

[towerbase]

It is fairly simple to create a temporary DBF table using the TQuery component.

The following code assumes that you have a TQuery component called Q on your form. It assumes that the table you want to create is called Scrap. Scrap has two fields an integer id and a character field called name.

procedure TForm1.Button1Click(Sender: TObject);
begin
  Q.Close;
  Q.SQL.Text := 'CREATE TABLE scrap ( id int, name char(40), primary key (id) )';
  Q.ExecSQL;
end;

Your temporary table is likely to be more complex than this but you should get the idea.

Andrew
Hampshire, UK

 

[tjc240e]

is scrap actually created somewhere or is it just in memory?

 

[tjc240e]

Why does the ExecSQL seem to alway create DB (paradox) files? I was hoping to have a .DBF file so i could then link to it using my CodeBase CBTableSet...

Would there be an easier way to create a temp holder for my compiled and pulled data?

 

[towerbase]

You define what kind of table (dBASE, Paradox and so on) is to be created by using the BDEADMIN.EXE program. The default is set to Paradox but this can be changed on a database by database level.

You can use BDEADMIN.EXE to define where the physical file is created.

I doubt if there is an easier way to create a (temporary) .DBF file.

Andrew
Hampshire, UK

 

[tjc240e]

Does anyone know how to use the CBMemSet instead of creating a temp table?

Does anyone think this would be a better option to use?

 

[tjc240e]

I've tried the following code:

procedure CreateATable(DBName,                //Alias or path
                       TblName : String;     //Table Name to Create
                       TblType : TTableType); //ttDefault, ttParadox, ttDBase, ttASCII
var
  tbl : TTable;
begin
  tbl := TTable.Create(Application);
  with tbl do begin
    Active := False;
    DatabaseName := DBName;
    TableName := TblName;
    TableType := TblType;
    with FieldDefs do begin
      Clear;
      Add('LastName', ftString, 30, False);
      Add('FirstName', ftString, 30, False);
      Add('Address1', ftString, 40, False);
      Add('Address2', ftString, 40, False);
      Add('City', ftString, 30, False);
      Add('ST', ftString, 2, False);
      Add('Zip', ftString, 10, False);
    end;

    {Add a Primary Key to the table}
    with IndexDefs do begin
      Clear;
      Add('Field1Index', 'LastName;FirstName', [ixPrimary, ixUnique]);
    end;
    
    CreateTable; {Make the table}
  end;
end;

using the following call statement:

CreateATable('C:\TEMP\', 'MYTEMP.DBF', ttDBase);

and I get the following error message:

Project Designer.exe raised exception class EDBEngineError with message 'Invalid index descriptor.
Table does not exist.
Table: C:\TEMP\MYTEMP.DBF'. Process stopped. Use Step or Run to continue.

I only seem to get this error when changing the TTableType from ttParadox to ttDBase...

Anyone know what i need to change to make this work with the ttDBase TTableType?

TIA

 

[towerbase]

The reason why your last effort failed is because there is no concept of a primary key in dBASE. It would probably work if you removed ixPrimary from the index creation code.

However, it is much easier to use a TQuery than TTable:-

var
  Q: TQuery;
begin
  Q := TQuery.Create(nil);
  try
    Q.DatabaseName := 'Scrap';
    Q.SQL.Text := 'CREATE TABLE "mytemp.dbf" ' +
                  '( LastName varchar(30)' +
                  ', FirstName varchar(30)' +
                  ', Address1 varchar(40)' +
                  ', Address2 varchar(40)' +
                  ', City varchar(30)' +
                  ', ST char(2)' +
                  ', Zip varchar(10)' +
                  ')';
    Q.ExecSQL;
    Q.SQL.Text := 'CREATE UNIQUE INDEX Field1Index' +
                  ' ON "mytemp.dbf" (LastName)';
    Q.ExecSQL;
  finally
    Q.Free;
  end;
end;

Andrew
Hampshire, UK

 

[safraser]

Forgiveme if I'm wrong here, I'm still learning this whole Delphi database programing myself.

If it's going to be a temporty table would it not be better to use SQL Select instead.

q.sql.text:='Select field1, field2, ..fieldn '+
from Mytable.dbf
q.open
//Do you report
q.close