Adding permanent table field at runtime

[auditdi30]

Hello!

Is there a way of adding a new field to a table at runtime, and make the field permanent to the table?? In all the example I found, the field is erased after closing the application.

TIA
Kåre!

 

[lespaul]

you can run an SQL statement:

with qry1 do
begin
  SQL.Clear;
  SQL.Add('ALTER TABLE TableName ADD COLUMN FieldName VarChar(50)');
  ExecSQL;
end;

will add a VarChar field to a table....

Leslie

Have you met Hardy Heron?

 

[auditdi30]

Hello again!

Is it also possible to check if the field exists before it add it??

If I will add a integerfield, do you use varinteger?? And memo with varmemo(120)??

TIA
Kåre!

 

[lespaul]

No if you want different types of fields you just use those:

with qry1 do
begin
  SQL.Clear;
  SQL.Add('ALTER TABLE TableName ADD COLUMN FieldName integer)');
  ExecSQL;
end;

or

with qry1 do
begin
  SQL.Clear;
  SQL.Add('ALTER TABLE TableName ADD COLUMN FieldName memo)');
  ExecSQL;
end;

if you check out the Access site you can find out what kind of data types you can use

http://office.microsoft.com/en-us/access/HA100964501033.aspx?pid=CH100645681033

as far as checking if the field already exists, I'm not sure how you might do that...

Leslie

Have you met Hardy Heron?

 

[LucieLastic]

Is it also possible to check if the field exists before it add it??"


What database are you using?

If you are using SQL Server, you can find this out like this (unsure about Access though).

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = <Table>
and COLUMN_NAME = <Column>


www.radbmx.co.uk

 

[lespaul]

yeah I guess there are system tables in Access that you could also query...I would post in one of the Access forums to get the syntax!

Leslie

Have you met Hardy Heron?

 

[auditdi30]

I use Paradox for my database

Kåre!

 

[DjangMan]

Something like:

if Table.FieldByName('NewField') = nil then
AddNewField;

 

[LucieLastic]

if Table.FieldByName('NewField') = nil then
AddNewField; "

I thought after writing the other reply that you could just use the Delphi control like the above ). You could also list out all the fields like this if you want to, say, a memo:

for i := 0 to Table.fields.count-1 do
memo.lines.add(Table.fields.name);




www.radbmx.co.uk

 

[auditdi30]

Hello!

Try to make my code by using the above example; use it like this;

sql.add('alter table akonto.db add colum regdato date');

i wan't to add a date field, but crash and say; invalid use of keyword token: date

What is wrong??

 

[DjangMan]

Make sure that your database supports a DATE type vs. a DATETIME time.

 

[auditdi30]

Have read about Paradox, and "Date" is a fieldtype which is accepted. But when using SQL like in my last posting, it crash!

Maybee somebody have added a datefield and can give my a example??

 

[lespaul]

what's the ALTER SQL statement you're trying to run?



Leslie

Have you met Hardy Heron?

 

[roo0047]

Here is an untested example:

procedure CopyParadoxDateToSqlDate;
//add 2 fields from PDox to SQL
var
  InvNo: string;
  InvDate: TDateTime;
  DateStr: string;
  s: string;
begin
  //assume pardox table 'Invoice' is open for read
  //assume SQL table name is 'SqlInvoice'
  //assume both table have the same field names
  InvNo:= Invoice.FieldByName('InvNo').AsString;
  InvDate:= Invoice.FieldByName('InvDate').AsDateTime);
  DateStr:= FormatDateTime('yyyy-mm-dd', InvDate);  
  Query1.SQL.Clear;
  s:= 'INSERT INTO SqlInvoice (InvNo, InvDate)';
  Query1.SQL.Add(s);
  s:= 'VALUES (' + InvNo + ',' + DateStr + ')';
  Query1.SQL.Add(s);
  try  
    Query1.ExecSQL;
    Query1.ApplyUpdates
  except
    Query1.Cancel;
    ShowMessage('Error occured')
  end
end;

The above is broken down to show how it works. It could all be condensed like this:

procedure CopyParadoxDateToSqlDate;
begin
  //assume pardox table 'Invoice' is open for read
  //assume SQL table name is 'SqlInvoice'
  //assume both table have the same field names
  Query1.SQL.Clear;
  Query1.SQL.Add(
    'INSERT INTO SqlInvoice (InvNo, InvDate) VALUES (' +
       Invoice.FieldByName('InvNo').AsString +     ',' +
       FormatDateTime('yyyy-mm-dd', 
         Invoice.FieldByName('InvDate').AsDateTime)    +
    ')');
  try  
    Query1.ExecSQL;
    Query1.ApplyUpdates
  except
    Query1.Cancel;
    ShowMessage('Error occured')
  end
end;

Note the FormatDateTime('yyyy-mm-dd', <DateValue>) in the examples above.
This is the format MySQL expects dates to be in.
I did not see where you (auditdi30) mentioned what SQL database is used, but I thnk this is common.

Again, this is untested code, but I think it is correct.


Roo
Delphi Rules!

 

[auditdi30]

Hello!

Maybee this is taken a wrong way now, or do I misunderstand??

My question was that I have a paradox table called akonto.db, and in this I want to add a new datefield called regdato. And the field must be permanent not only so long as the program is running.

Looked like the last answer make 2 new field to a sql, which when the program is stopping, the field will also be gone. Or have I missed something??

My code I am trying is as follow;
with qry1 do
begin
SQL.Clear;
SQL.Add('ALTER TABLE akonto.db ADD COLUMN regdato date');
ExecSQL;
end;

But maybee this is also wrong, and don't update the table, just the sql??

 

[lespaul]

the command

ALTER TABLE akonto.db ADD COLUMN regdato date

alters the table and adds a date field called regdato to the table...it's a permanent change to the structure of the table.

http://www.thedbcommunity.com/index.php?option=com_content&task=view&id=138&Itemid=46

has some info specific to Paradox....

Leslie

Have you met Hardy Heron?

 

[auditdi30]

Ok, then I understand. But the code give me a crash, and tells me that the keyword date is not accepted!!

 

[roo0047]

My post addresses this and shows the correct way to do it. It assumes that the table structure is in place (and that all fields are permanent). However YOUR field names will be different as will the table names. It was in response to ...

Maybee somebody have added a datefield and can give my a example??

... and nothing more. It shows how to insert data into an existing date field.

(I thought you were past adding the field to the table record.)

To permanently add a field named 'InvDate' to a MySQL table named 'SqlInvoice':

  Query1.SQL.Clear;
  Query1.SQL.Add('ALTER TABLE SqlInvoice ADD COLUMN InvDate date');
  try  
    Query1.ExecSQL;
    Query1.ApplyUpdates
  except
    Query1.Cancel;
    ShowMessage('Error occurred')
  end;

Once again, this works for MySQL. If you are trying to add fields to a Paradox table, this will NOT work.

Roo
Delphi Rules!

 

[auditdi30]

Ok, but what could I do as I am using Paradox, and want to permanently add a date field to my table??

 

[lespaul]

you will need to find out what the specific syntax is for a paradox table....



Leslie

Have you met Hardy Heron?