Create a Field at Run-Time

This weekend, I have changed an app that is currently in use by about 200 people at work.  The change is great, but it requires that various databases (about 100 discrete Dbase tables) need to have a new field.

Therefore, I want to check each database as they are opened and see if the new field exists.  If the field does not exist, I want to add it.  The code shown below doesn't bomb out, but the field isn't added either!  

If you see the problem, please let me know.

procedure ...
var TableTemp : TTable;
    bFlag : Boolean;
    iCounter : Integer;
begin

   ....

// Create and open existing table
TableTemp := TTable.Create(Self);
TableTemp.DatabaseName := '<< PATH >>';
TableTemp.TableName := 'INDEX.DBF';
TableTemp.Open;

// Check fields for one called 'REF'
For iCounter := 0 to TableTemp.FieldCount-1 do
begin
  If UpperCase(TableTemp.Fields[iCounter].Fieldname = 'REF' then
     bFlag := True;
end;

// Flag not set = Field not found
If bFlag = False then
  TableTemp.FieldDefs.Add('REF',ftString,20,False);

// All done - tidy up.
TableTemp.Close;
TableTemp.Free;

...

End;

LVL 4
wkhaysAsked:
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.

rwilson032697Commented:
I don't see you initialising bFlag to False...

Cheers,

Raymond.
0
wkhaysAuthor Commented:
Good point... I do initialise it near the top, though.

Thanks,
WKHays
0
rwilson032697Commented:
You could do it with an SQL statement.  

Here is a code snippet for you: (must use DB, DBTables)============================
  with TQuery.Create(Application) do
    try
      SQL.Add('ALTER TABLE "<your tables path full path.dbf>"
              ADD <your field name> <data type');
      ExecSQL;
    finally
      free;
    end;
========================
See CREATE TABLE in your Borland\Common Files\BDE\LocalSQL.hlp for the allowed data_type's.

An example for the add statement would be:
  SQL.Add('ALTER TABLE "C:\test.dbf" ADD NAME CHAR(15)');


You could probably also do it with a DBI call... but it would be much more difficult.

This Q: http://www.experts-exchange.com/jsp/qShow.jsp?ta=delphi&qid=10026571  shows how you can do it using dbidorestructure

Cheers,

Raymond.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

kretzschmarCommented:
Raymond,
this points are yours :-)

i prefer the sql-method.

wkhays,

the fielddefs do not force a restructure of the table, they are only used for:

- get fieldinformation
- create a table
- add a calculated field

meikl
0
wkhaysAuthor Commented:
Very nice.

Rather than going through all of the pain of determining whether the field exists (as I do in my example), is there any risk in running your SQL statement as follows to ensure that the fields exist?

with TQuery.Create(Application) do
try
  SQL.ADD('ALTER TABLE "C:\dir\index.dbf" ADD REF CHAR(20), ADD ARCHIVE BOOLEAN');
  ExecSQL;
  free;
except
  free;
end;

In this example, the resource is freed no matter how the SQL terminates (normally or with an exception).

The only problem this seems to give me is that when I am debugging the app in Delphi, the exception error still pops up.  When the app runs outside of delphi, no exception error pops up.  Is this expected?

Thanks,
WKHays
0
rwilson032697Commented:
I would expect the SQL statement not to do anything if the field exists... But then how hard is it to do this:

if Assigned(MyTable.FieldByName['FRED']) then // add the field...

I would code your snippet like this:

with TQuery.Create(Application) do
try
  try
    SQL.ADD('ALTER TABLE "C:\dir\index.dbf" ADD REF CHAR(20), ADD ARCHIVE BOOLEAN');
    ExecSQL;
  finally
    free;
  end;
except
  // Deal with the exception in whatever way you want...
end;

You can turn 'break on exception' off in the debugger options|Language exceptions (Stop on delphi Exceptions) for D5, and (I think) in environment options for D4. This stops the annoying exception messages (at the point the exception occurs) in the IDE...

Cheers,

Raymond.
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
wkhaysAuthor Commented:
I think you have helped me come up with something fairly elegant now.  Thanks!

Here's the final code I settled on.  It's essentially what you put in your last message.

Thanks!
WKHays

with TQuery.Create(Application) do
try
   try
      SQL.ADD('ALTER TABLE ' + chr(34) + sPathWIP + '\db\index.dbf' + chr(34) +
         'ADD REF CHAR(20)');
      ExecSQL;
   finally
      free;
   end;
except
   // Field Already Exists - this exception section suppresses error messages.
end;
0
rwilson032697Commented:
Your welcome!

Cheers,

Raymond.
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.