• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5027
  • Last Modified:

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.


0
nickjfox
Asked:
nickjfox
  • 5
  • 5
  • 4
  • +3
1 Solution
 
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
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.

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

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 5
  • 5
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now