We help IT Professionals succeed at work.

Delphi/SQL - adding database field at runtime

nickjfox
nickjfox asked
on
Medium Priority
6,025 Views
Last Modified: 2011-10-03
I have written the following SQL to add a field to an Access table at runtime.

////Get field name
field:='d'+IntToStr(spinEdit1.value)+'c'+IntToStr(SpinEdit2.value);


//Add field to table

Data.QPrices2.active:=False;
Data.QPrices2.SQL.Clear;
Data.QPrices2.SQL.Add('ALTER TABLE XXX');
Data.QPrices2.SQL.Add('ADD ' +field+ ' Float');
Data.QPrices2.ExecSQL;


This works fine, except when the field already exists when Delphi issues an error message.

I therefore need to change the SQL so that the program doesn't try to add a  field that already exists.

I believe there is SQL syntax IF NOT EXISTS that might do the trick but how I use it in the above is a mystery.


Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
Hello nickjfox,

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Nme = 'XXX' AND COLUMN_NAME  = 'Field' )
  ALTER TABLE XXX
  ADD Field float





Aneesh R

Author

Commented:
Hi,

Thanks for that. Maybe something is lost in translation between SQL and the way I have
written it in Delphi.

My code now reads:

Data.QPrices2.SQL.Add('IF NOT EXISTS');
Data.QPrices2.SQL.Add('(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Nme = XXX AND COLUMN_NAME  = '+Field+ ')');
Data.QPrices2.SQL.Add('ALTER TABLE XXX');
Data.QPrices2.SQL.Add('ADD ' +field+ ' Float');


But this produces the Delphi error message 'Invalid SQL Statement; expected 'DELETE, INSERT, PROCEDURE, SELECT OR UPDATE'
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
i am not sure abt the Deplhi Syntax

You should replace 'WHERE Table_Nme = XXX'   with

WHERE Table_NAME = 'XXX'   ----- note the single  quotes around the tableName ('xxx' )  also there should  filedName should also be surrounded by single quotes

Data.QPrices2.SQL.Add('IF NOT EXISTS ');
Data.QPrices2.SQL.Add('(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_NAme = ''XXX'' AND COLUMN_NAME  = ''+Field+ '')');
Data.QPrices2.SQL.Add(' ALTER TABLE XXX  ');
Data.QPrices2.SQL.Add(' ADD ' +field+ '  Float');

Commented:
Try selecting from the field, if field does not exists exception will occur and alter table code will be executed

try
Data.QPrices2.Close;
Data.QPrices2.SQL.Clear;
Data.QPrices2.SQL.Add('SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Nme = XXX AND COLUMN_NAME  = '+Field);
Data.QPrices2.Open;
except
Data.QPrices2.Close;
Data.QPrices2.SQL.Clear;
Data.QPrices2.SQL.Add('ALTER TABLE XXX');
Data.QPrices2.SQL.Add('ADD ' +field+ ' Float');
Data.QPrices2.ExecSQL;
end;

Commented:
I am not sure about this SQL string, I think there is more simple solution

try changing
Data.QPrices2.SQL.Add('SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Nme = XXX AND COLUMN_NAME  = '+Field);

with (this SQL string tries to select all the values from the specified column)
Data.QPrices2.SQL.Add('SELECT [XXX].['+Field+'] FROM [XXX]');

Author

Commented:
Aneesh,

I'm sure that's the right SQL. The trouble is it doesn't work in Delphi.

FactorB

Delphi looks for a file Information_Schema.mdb

Author

Commented:
FB,

Data.QPrices2.SQL.Add('SELECT [XXX].['+Field+'] FROM [XXX]');

 
Gives error parameter [xxx].[fieldname] has no default value

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
the syntax i gave was for SQL Server and not for Access, i am not sure abt the access syntax

Commented:
I suppose that before running this code you are already connected to Information_Schema.mdb (if not try using ADOConnection and ADOQuery or some other Connection/Query combination), it already contains table XXX and you want to find if there is a column named by the value contained in the string variable Field, then this will work

var
sField: String;
sTable: String;
begin
try
sField:='Name_of_the_field';
sTable:='Name_of_the_table';
Data.QPrices2.Close;
Data.QPrices2.SQL.Clear;
Data.QPrices2.SQL.Add('SELECT ['+sTable+'].['+sField+'] FROM ['+sTable+']');
Data.QPrices2.Open;
except
Data.QPrices2.Close;
Data.QPrices2.SQL.Clear;
Data.QPrices2.SQL.Add('ALTER TABLE ['+sTable+']');
Data.QPrices2.SQL.Add('ADD ['+sField+'] FLOAT(6,1)'); {6,1 for precision}
Data.QPrices2.ExecSQL;
end;
end;

Commented:
SQL Server, I don't have much experience but you can try
Data.QPrices2.SQL.Add('SELECT * From XXX Where (('+Field+' is null) or ('+Field+' is not null))');

Author

Commented:
I'm still struggling with this. To confirm,  I am using Delphi6, ADO connection to MSAccess.

I need to create a field only if it doesn't already exist in the table.
CERTIFIED EXPERT
Top Expert 2014

Commented:
1. there is no direct way to get the column names from the MSysObjects table, just the table/query/form/report/module names.

2. Try to retrieve one row with the column you are conditionally adding.  Since you are retrieving data, you will need to instantiate/populate a recordset variable.
Simplest SQL Example:
'Select Top 1 [fieldname] From [XXX]'

If building the recordset produces no error, you do not need to add the column.  Otherwise, you will raise a 3061 error.

3. Alternatively (to 2), you might use one of the following to 'examine' the table schema.  Then you iterate through the fields, looking for the field you want to conditionally add.
* DAO recordset populated from the table or
'Select Top 1 * From [XXX]'
* ADO connection object, retrieving the schema for the table.
* ADOX, similar to DAO, but allowing schema changes through the object interfaces.

4. trap the error by placing the ALTER statement execution within a Try...Except construct.
"This works fine, except when the field already exists when Delphi issues an error message."

========================
So the short answer is that since there isn't a single system table you can query to get column names, you have to look for the column names or trap the error when you issue the ALTER TABLE.
Top Expert 2007

Commented:
Try Like this.


////Get field name
field:='d'+IntToStr(spinEdit1.value)+'c'+IntToStr(SpinEdit2.value);


//Add field to table

Data.QPrices2.active:=False;
Data.QPrices2.SQL.Clear;
Data.QPrices2.SQL.Add('ALTER TABLE XXX');
Data.QPrices2.SQL.Add('ADD ' +field+ ' Float');
try
    Data.QPrices2.ExecSQL;
except end;

CERTIFIED EXPERT
Top Expert 2004
Commented:
a simple sample on how to evaluate if a field exists (db-independent version)

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, ADODB, StdCtrls;

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    Edit1: TEdit;
    Edit2: TEdit;
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
  private
    { Private-Deklarationen }
  public
    { Public-Deklarationen }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

function Field_Exists(AConnection : TADOConnection; ATableName : String; AFieldName : String) : Boolean;
const
  cQuery = 'Select * from %s where 1 = 2';
var
  vQueryString : String;
  vQuery : TADOQuery;
  i : Integer;
begin
  result := false;
  vQueryString := Format(cQuery,[ATableName]);
  vQuery := TADOQuery.Create(nil);
  try
    vQuery.Connection := AConnection;
    vQuery.SQL.Text := vQueryString;
    vquery.Open;
    i := 0;
    while (i < vQuery.FieldCount)
      and (uppercase(vQuery.Fields[i].FieldName) <> uppercase(AFieldName))
       do inc(i);
    result := i < vQuery.FieldCount;
    vquery.Close;
  finally
    vquery.Free;
  end;
end;




procedure TForm1.Button1Click(Sender: TObject);
begin
  if Field_Exists(ADOConnection1,edit1.Text,edit2.Text) then
    showmessage('Field '+edit2.Text+' already exists in Table '+edit1.Text)
  else
    showmessage('Field '+edit2.Text+' NOT exists in Table '+edit1.Text);
end;

end.

meikl ;-)

Author

Commented:
Thanks Meikl,

Used your function and

if Field_Exists(Data.ConnPrices,'FTSE',FIELD)=false then Create field


Problem solved.
CERTIFIED EXPERT
Top Expert 2004

Commented:
Glad you found it useful :-))

good luck again

meikl ;-)
CERTIFIED EXPERT
Top Expert 2014

Commented:
good one, meikl.  instantiating a no row result set is even better than a one row result set.
CERTIFIED EXPERT
Top Expert 2004

Commented:
hehe,

yep, because just the metadata are needed, so it forced an empty resultset.
but there is also an alternative:

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, DB, ADODB;

type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    Edit1: TEdit;
    Edit2: TEdit;
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
  private
    { Private-Deklarationen }
  public
    { Public-Deklarationen }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

function Field_Exists(AConnection : TADOConnection; ATableName : String; AFieldName : String) : Boolean;
var
  sl : TStringList;
begin
  sl := TstringList.Create;
  try
    AConnection.GetFieldNames(ATableName,sl);
    sl.Text := UpperCase(sl.Text);
    result := sl.IndexOf(Uppercase(AFieldName)) > -1;
  finally
    sl.Free;
  end;
end;


procedure TForm1.Button1Click(Sender: TObject);
begin
  if Field_Exists(AdoConnection1, edit1.Text, edit2.text) then
    showmessage(edit2.Text+' already exists in Table '+edit1.text)
  else
    showmessage(edit2.Text+' NOT exist in Table '+edit1.text);
end;

end.

meikl ;-)
CERTIFIED EXPERT
Top Expert 2014

Commented:
@Meikl

That's a good implementation of my second bulleted item under #3.

I have an interesting question for you.  Is it more efficient to change the case of all items in the string list than setting the .CaseSensitive property to False?  

Or does the efficiency comparison depend on the number of items and length of the text in the items. (not a trivial answer)

Fortunately, the MSAccess tables are limited to 256 columns of 64 character (or less) column names.
CERTIFIED EXPERT
Top Expert 2004

Commented:
:-))
so far i have it not examined, guess a case change at once would be faster, than the CaseSensitve set to false (which may force a Upper/lowercase per item during the compare )
(even also because within a userdialog some minor miliseconds performance-difference are not noticed by the user, of course for a like batch it would be significant)

will do a performance-compare

meikl ;-)
CERTIFIED EXPERT
Top Expert 2014

Commented:
The earlier the string is found in the list, the greater the efficiency.

I've been looking for some references on this and it may be that the IndexOf() method is case-insensitive by default, so no upper casing may be required to get the desired result.  This may vary, depending on Delphi version.

http://www.experts-exchange.com/Q_10213364.html
CERTIFIED EXPERT
Top Expert 2004

Commented:
puh, then the uppercase isn't needed  ...
. . . again learned something . . .

meikl ;-)