Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Relational Databases
Expertise: Beginner
Mar 19, 1997

Adding and Removing Columns from Tables

Question:
How can I add and remove columns from a table at run time?

Answer:

While I'm an avid supporter of Delphi, one of the most annoying things about it is its lack of table management routines for local tables such as Paradox or dBase tables. Specifically, the ability to add or delete columns from a table at run time is non-existent in the set of Delphi native calls. Okay, okay, you can do it with the BDE. But that is a fairly complex process that doesn't lend itself to a simple one-liner call that you'd expect from some kind of wrapper function. So what do you do?

I've learned from experience that when all else fails, see if you can do it with SQL. After all, SQL is a data-definition language, so it should include a set of table management capabilities. Granted, Local SQL supported by the BDE and BDE32 is fairly pared down. But amazingly, facilities to add and delete columns are supported through the SQL command ALTER TABLE.

In ANSI-SQL, ALTER TABLE includes a large list of operations that can be performed with the call. However, Local SQL only supports ADDing and DROPping (deleting) columns from a table. Here's the syntax for doing so.

To add a column, type the following:

ALTER TABLE TableName ADD FieldName DataType*

For example, if you have a table called MyTable.DB and you wanted to add a string field of 25 bytes called MyField to it, you'd do the following:

ALTER TABLE "MyTable.DB" ADD MyField VARCHAR(25)

Don't worry we'll get into data types in just a moment. But to continue on, the syntax for dropping a table is similar, but you don't provide a data type after the field:

ALTER TABLE TableName DROP FieldName

That covers basic SQL syntax, but what about doing this in Delphi? As far as dropping an index is concerned, that's pretty straightforward, and can be done within a procedure as follows:

procedure SQLDropField(dbName,                     {Database Name}
                       tblName,                    {Table Name}
                       fldName         : String);  {Field Name to Drop}
var
  sqlDrpFld : TQuery;
begin
  sqlDrpFld := TQuery.Create(Application);
  with sqlDrpFld do begin
    DatabaseName := dbName;
    SQL.Add('ALTER TABLE ' + tblName + ' DROP ' + fldName);
    try
      try
        ExecSQL;
      except
        Abort; {Just raise a silent exception}
      end;
    finally
      Free;
    end;
  end;
end;

All I did in the procedure above was use a simple TQuery to drop a field. You can use this procedure on any field of any local table (dBase or Paradox). What about adding a field? Well, that's another matter entirely.

One of the things I try to do when creating utility procedures is make them as flexible as possible, and allow them to address as many factors revolving around the problem as possible. With respect to adding a field, it's not as easy as dropping a field because you have the field type to consider. In addition, sub-parameters required by some of the SQL data types compound the issue. So the process of building a call to encapsulate the field addition process is a bit tricky. Conceivably, you could take the easy way out and create field addition procedures for each of the 15 data types supported by local SQL. But if you want to be really elegant with your approach, create only one procedure. That procedure, however, has to know how to act flexibly and allow you to specify the type of field you want to add.

Keeping flexibility in mind, how do you approach this problem? One way is to use a formal parameter that tells the procedure what type of data type the field will be. It could be a string. In that case, you'd use a series of if..then..else.. statements to evaluate the data type to create. That makes for pretty long and confusing code. Another, and probably better, way to provide the flexibility is to use an enumerated type.

An enumerated type is essentially an array of named ordinal values. For instance:

TCardType = (Heart, Club, Diamond, Spade);

Each item in the list is assigned an ordinal value, ranging from 0 to the count of the list minus 1. So the ordinal values of the items in the list above would be: 0, 1, 2 and 3, respectively. The most common use for enumerated types is for conditional evaluation; that is, a method, function or procedure will act a certain way depending upon the type assigned to a parameter that was declared as a certain enumerated type. For example, take the TFieldType. If you pass a variable declared as some TFieldType to a particular method, the method would treat a ftString much differently than it would treat a ftFloat type variable.

For our purposes, we want to create an enumerated type list that reflects the type of field we want to add to a local table. As I mentioned above, Local SQL supports 15 different field types, so we'd have 15 elements in our list. Here's the enumerated type I created for this purpose:

  TCastType = (ctSmallInt, ctInteger, ctDecimal, ctNumeric, ctFloat,
               ctChar, ctVarChar, ctDate, ctBoolean, ctBLOB, ctTime,
               ctTimeStamp, ctMoney, ctAutoInc, ctBytes);

Given that, how can we create a procedure that utilizes the enumerated type to add a field to a table? That's actually the easy part. Here's the code I use to do just that:

procedure SQLAddField(dbName,                      {Database Name}
                      tblName,                     {Table Name}
                      fldName         : String;    {Field Name to Add}
                      fldType         : TCastType; {Field Type as described above}
                      fldLength,                   {Length of Field}
                      precisOrBlobLen,
                      scaleOrBlobType : Integer);  {Blob definition type 1 = Memo, 2 = Binary,
                                                   3 = Formatted Memo, 4 = OLE Object, 5 = Graphic}
var
  sqlAddFld: TQuery;
  CastType : String;

begin
  case fldType of
    ctSmallInt  : CastType := 'SMALLINT';
    ctInteger   : CastType := 'INTEGER';
    ctDecimal   : CastType := 'DECIMAL(' + IntToStr(precisOrBlobLen) + ',' +
                                           IntToStr(scaleOrBlobType) + ')';
    ctNumeric   : CastType := 'NUMERIC(' + IntToStr(precisOrBlobLen) + ',' +
                                           IntToStr(scaleOrBlobType) + ')';
    ctFloat     : CastType := 'FLOAT('   + IntToStr(precisOrBlobLen) + ',' +
                                           IntToStr(scaleOrBlobType) + ')';
    ctChar      : CastType := 'CHARACTER(' + IntToStr(fldLength) + ')';
    ctVarChar   : CastType := 'VARCHAR(' + IntToStr(fldLength) + ')';
    ctDate      : CastType := 'DATE';
    ctBoolean   : CastType := 'BOOLEAN';
    ctBlob      : CastType := 'BLOB('    + IntToStr(precisOrBlobLen) + ',' +
                                           IntToStr(scaleOrBlobType) + ')';
    ctTime      : CastType := 'TIME';
    ctTimeStamp : CastType := 'TIMESTAMP';
    ctMoney     : CastType := 'MONEY';
    ctAutoInc   : CastType := 'AUTOINC';
    ctBytes     : CastType := 'BYTES(' + IntToStr(fldLength) + ')'
  end;

  sqlAddFld := TQuery.Create(Application);
  with sqlAddFld do begin
    DatabaseName := dbName;
    SQL.Add('ALTER TABLE ' + tblName + ' ADD ' + fldName + ' ' + CastType);
    try
      try
        ExecSQL;
      except
        Abort; {Just raise a silent exception}
      end;
    finally
      Free;
    end;
  end;
end;

Notice that I started the procedure off with a rather lengthy case statement. Given the SQL syntax for adding a field, each data type has to be handled differently, which accounts for the case statement.

So why did I go through all that trouble? Well, as I said, each data type has to be handled differently. Also, SQL syntax requires that you name the specific type of data type you're creating. Furthermore, depending upon the data type, you need to supply subparameters that define either the length of the field or, as in the case of a BLOB data type, the byte length and type of BLOB. Numeric, Decimal and Float data types all require that you specify precision and scale. So there's no choice but to create a case statement to get the proper syntax, save creating a separate procedure for each data type, and I wasn't willing to create a cluttered unit with a bunch of procedures that essentially did the exact same thing. But Delphi being an optimizing compiler, going through this case statement is blindingly fast, so it's not really a performance issue anyway.

Following the case statement is code that's practically the exact duplicate of dropping a field from a table, but with the data type added on to the end of the SQL statement. All in all, when you consider it, this is also a very simple procedure. I'm going to close with the entire code listing for the unit I created that houses the two procedures. Use it as you will.

{=========================================================================================
 Below are listed the datatypes for adding a column to a table defined by TSQLFldType.
 Typically, you'll make the call to SQLAddField as follows:

 SQLAddField('MyDB', 'MyTable', 'MyField', sfVarChar, 25, 0, 0);

 You can use this version of the call for all types of fields except for sfBLOB where you
 have to provide the blob type parameter:

                          1 = Memo
                          2 = Binary
                          3 = Formatted Memo
                          4 = OLE Object
                          5 = Graphic

 and for Decimal, Numeric, and Float fields, where you have to provide the precision and
 scale for the data type in the last two parameters, respectively.

 For those data types such as SmallInt, Integer, Date, Time, TimeStamp, Money, and AutoInc
 that don't require a length, if you include a length value, it will be ignored.
 =========================================================================================}

unit SQLAddDel;

interface

uses DB, DBTables, SysUtils, Forms;

type
  TCastType = (ctSmallInt, ctInteger, ctDecimal, ctNumeric, ctFloat,
                 ctChar, ctVarChar, ctDate, ctBoolean, ctBLOB, ctTime,
                 ctTimeStamp, ctMoney, ctAutoInc, ctBytes);


procedure SQLAddField(dbName,                      {Database Name}
                      tblName,                     {Table Name}
                      fldName         : String;    {Field Name to Add}
                      fldType         : TCastType; {Field Type as described above}
                      fldLength,                   {Length of Field}
                      precisOrBlobLen,
                      scaleOrBlobType : Integer);  {Blob definition type 1 = Memo, 2 = Binary,
                                                   3 = Formatted Memo, 4 = OLE Object, 5 = Graphic}

procedure SQLDropField(dbName,                     {Database Name}
                       tblName,                    {Table Name}
                       fldName         : String);  {Field Name to Drop}
implementation

procedure SQLAddField(dbName,                      {Database Name}
                      tblName,                     {Table Name}
                      fldName         : String;    {Field Name to Add}
                      fldType         : TCastType; {Field Type as described above}
                      fldLength,                   {Length of Field}
                      precisOrBlobLen,
                      scaleOrBlobType : Integer);  {Blob definition type 1 = Memo, 2 = Binary,
                                                   3 = Formatted Memo, 4 = OLE Object, 5 = Graphic}
var
  sqlAddFld: TQuery;
  CastType : String;

begin
  case fldType of
    ctSmallInt  : CastType := 'SMALLINT';
    ctInteger   : CastType := 'INTEGER';
    ctDecimal   : CastType := 'DECIMAL(' + IntToStr(precisOrBlobLen) + ',' +
                                           IntToStr(scaleOrBlobType) + ')';
    ctNumeric   : CastType := 'NUMERIC(' + IntToStr(precisOrBlobLen) + ',' +
                                           IntToStr(scaleOrBlobType) + ')';
    ctFloat     : CastType := 'FLOAT('   + IntToStr(precisOrBlobLen) + ',' +
                                           IntToStr(scaleOrBlobType) + ')';
    ctChar      : CastType := 'CHARACTER(' + IntToStr(fldLength) + ')';
    ctVarChar   : CastType := 'VARCHAR(' + IntToStr(fldLength) + ')';
    ctDate      : CastType := 'DATE';
    ctBoolean   : CastType := 'BOOLEAN';
    ctBlob      : CastType := 'BLOB('    + IntToStr(precisOrBlobLen) + ',' +
                                           IntToStr(scaleOrBlobType) + ')';
    ctTime      : CastType := 'TIME';
    ctTimeStamp : CastType := 'TIMESTAMP';
    ctMoney     : CastType := 'MONEY';
    ctAutoInc   : CastType := 'AUTOINC';
    ctBytes     : CastType := 'BYTES(' + IntToStr(fldLength) + ')'
  end;

  sqlAddFld := TQuery.Create(Application);
  with sqlAddFld do begin
    DatabaseName := dbName;
    SQL.Add('ALTER TABLE ' + tblName + ' ADD ' + fldName + ' ' + CastType);
    try
      try
        ExecSQL;
      except
        Abort; {Just raise a silent exception}
      end;
    finally
      Free;
    end;
  end;
end;

procedure SQLDropField(dbName,                     {Database Name}
                       tblName,                    {Table Name}
                       fldName         : String);  {Field Name to Drop}
var
  sqlDrpFld : TQuery;
begin
  sqlDrpFld := TQuery.Create(Application);
  with sqlDrpFld do begin
    DatabaseName := dbName;
    SQL.Add('ALTER TABLE ' + tblName + ' DROP ' + fldName);
    try
      try
        ExecSQL;
      except
        Abort; {Just raise a silent exception}
      end;
    finally
      Free;
    end;
  end;
end;

end.
DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date