Delphi/SQL - adding database field at runtime

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.


nickjfoxAsked:
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.

Aneesh RetnakaranDatabase AdministratorCommented:
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
0
nickjfoxAuthor 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'
0
Aneesh RetnakaranDatabase AdministratorCommented:
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');
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

FactorBCommented:
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;
0
FactorBCommented:
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]');
0
nickjfoxAuthor 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
0
nickjfoxAuthor Commented:
FB,

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

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

0
Aneesh RetnakaranDatabase AdministratorCommented:
the syntax i gave was for SQL Server and not for Access, i am not sure abt the access syntax
0
FactorBCommented:
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;
0
FactorBCommented:
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))');
0
nickjfoxAuthor 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.
0
aikimarkCommented:
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.
0
diniludCommented:
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;

0
kretzschmarCommented:
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 ;-)
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
nickjfoxAuthor Commented:
Thanks Meikl,

Used your function and

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


Problem solved.
0
kretzschmarCommented:
Glad you found it useful :-))

good luck again

meikl ;-)
0
aikimarkCommented:
good one, meikl.  instantiating a no row result set is even better than a one row result set.
0
kretzschmarCommented:
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 ;-)
0
aikimarkCommented:
@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.
0
kretzschmarCommented:
:-))
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 ;-)
0
aikimarkCommented:
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
0
kretzschmarCommented:
puh, then the uppercase isn't needed  ...
. . . again learned something . . .

meikl ;-)
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.