Big Problem with DataBases and TQuery

[luistsousa]

Hi

I have a problem with databases that I think it is easy for gurus resolve , but not for me.

I have the following function. After the function create a new field in my database, I receive a error message that said that the new field don't exist. However it was created.
How can I resolve this?

Query1.Close();
Query1.SQL.Clear();
Query1.SQL.Add(Format('ALTER TABLE alter_exp, ADD new_field CHAR(25)', ['Deg']));
Query1.ExecSQL();
Table1.Insert;
Table1.FieldsByName("new_field&quot.Value="created";
Table1.Post;



REgards

 

[Svanhooft]

hi,

Query1.Close();
Query1.SQL.Clear();
Query1.SQL.Add(Format('ALTER TABLE alter_exp, ADD new_field CHAR(25)', ['Deg']));
Query1.ExecSQL();
// new
Table1.close; // close the table
Table1.open; //open the table with the new structure
Table1.Insert;
Table1.FieldsByName("new_field&quot.Value="created";
Table1.Post;

i think that should do the trick

Steph [Bigglasses]

 

[luistsousa]

Hi

Not work. I also tried with Table1.Refresh; but nothing. Perhaps it needs to add the new field as a persistent field? If yes, as I will be able to make it?

REgards

 

[towerbase]

What exactly was the error message that you got? I am surprised that the Format statement worked.

Andrew

 

[luistsousa]

Hi

The message is "...Table1: Field 'new_field' not found...".
However in my mysql admin, the new_field is created, and when I restart the delphi program I can insert records to the new_field. Only after the creation of the new_field, the error message appear.

Regards

 

[towerbase]

Have you tried closing and reopening Table1 after you have done the SQL Alter statement? This should allow Table1 to get at the new field information.

Andrew

 

[luistsousa]

Yes, I try, but don't work.

REgards

 

[towerbase]

Whoops, just noticed that Steph suggested closing and opening.

You could try closing and opening the database itself. Can you show us the actual code you are using (copy and paste)?

Andrew

 

[luistsousa]

The is this:

Query1.Close();
Query1.SQL.Clear();
Query1.SQL.Add(Format('ALTER TABLE alter_exp, ADD new_field CHAR(25)', ['Deg']));
Query1.ExecSQL();
// new
Table1.close; // close the table
Table1.open; //open the table with the new structure
Table1.Insert;
Table1.FieldsByName("new_field&quot.Value="created";
Table1.Post;

 

[towerbase]

Can you explain to me how the Format statement works in the Query1.Add statement? What is the 'Deg' for ?

Surely there should not be a comma after alter_exp ?

How does FieldsByName work ? Shouldn't it be FieldByName ?

I suspect that you are not copying and pasting the code out of your program!!!

Can you copy and paste the actual Pascal statements from your program, please?

 

[luistsousa]

Hi

This code is a example code to do what I want. THe original code is very long. I think it is correct because create the new_field. The problem is other. I don´t understand why after do the Table1.Close and Table1.Open, The program send the message error and don´t write to the new_field.

Regards

 

[towerbase]

When you have a problem like this the best way to tackle it is to write a small test piece of code that demonstrates the problem.

A program to demonstrate your problem should not be very long.

Unless you show us the actual code that is failing people on this forum can only guess at what is going wrong.


Andrew

 

[LucieLastic]

hi

Has this been solved? If not and you're using a Tdatabase, set connected := false and then connected:= true, in between adding the new field and opening the new query.

lou

 

[luistsousa]

Hi

The code that I work to try resolving the problem is this one.
I tried with Table1.active:=false and after created the new_field, with Table1.active:=true, but also don't work.
I tried other thing, I had delete the Table1 and created other Table1 after the creation of the new_field. This is very strange but also don't work.
Why it don't update the information of the table? Only after I reboot the program he know that the new field has created.

This is desperating.

REgards

 

[towerbase]

We are finding it frustrating too. I've written a test program and it works perfectly okay for me.

For my test, I used Delphi 7 Pro and the table was a Paradox table.

I am sure the error is in your code. But if you won't show us your real code then how are we going to help you?

Here is my code. Test is the TTable.

procedure TForm1.Button5Click(Sender: TObject);
var
  Q: TQuery;
begin
  Q := TQuery.Create(nil);
  Q.DatabaseName := 'TBASE';
  try
    test.close;
    Q.SQL.Append('ALTER TABLE test ADD G4 integer' );
    Q.ExecSQL;
    test.Open;
    test.Edit;
    test.FieldByName('G4').AsInteger := 999;
    Test.Post;
  finally
    Q.Free;
  end;
end;

Andrew

 

[luistsousa]

Hi

I tested YOur code, but belive or not, don't work.
I think the problem is not in the code but maybe in mysql database.

The other strange thing is this. I created other button to insert a record in a field id. This work ok before i create the G4 field. After I manual delete the G4 field and reboot the delphi application, no more work because appear a error message that said "unknown field "G4" in a field list...". What field list?

REgards

 

[towerbase]

This is the first time you have mentioned MySQL as being your database! You still haven't given the real code that you are using so all I can do is guess.

Assuming you are using the Borland recommended way of accessing MySQL databases then you will be using dbExpress technology.

It works and it is very simple. Simply drop an SQLConnection component on your form / data module, configure the various connection properties for your database (user name, password, drivers and so on) and the code is as follows (which I have tested using Delphi 7 Pro)

procedure TForm1.Button1Click(Sender: TObject);
begin
 SQLC.ExecuteDirect('ALTER TABLE test ADD G1 integer' );
 SQLC.ExecuteDirect('INSERT INTO test VALUES(1,"Test1",99)');
end;

The original table was set up as:

CREATE TABLE test ( id INT, name VARCHAR(40) )

Andrew

 

[LucieLastic]

hi

>error message that said "unknown field "G4" in a field list...". What field list?

I'm assuming this is the list in the Fields editor. I think(but not 100% sure) when you use 'FieldByName', the said field has to be in the Fields list: Double click the TQuery and then right click the empty box and select 'Add all fields'. As you are adding fields at runtime and so don't know the table structure at design time, you may need something like this:

* n is the index of the new field.
test.Edit;
test.Fields.Field[n].AsInteger := 999;
Test.Post;


Can you post your exact code you have at the mo?

lou

 

[luistsousa]

Hi

I tried with dbexpress and YOur code and works. Thanks for YOur patience.
I also tried with this:

Query1.SQL.Append('ALTER TABLE test ADD G1 integer' );
Query1.SQL.Append('INSERT INTO test VALUES(1,"Test1",99)';
Query1.ExecSQL;

and works too.

Which are the differences between BDE and DBexpress?

HOw can I read the value of the new records? I tried with SQLTable1, but it don't acceded to the new fields. I must use SQL language?

Many thanks
Luis