LookupFields+DBGrid+MySQL+ZeosDBO. Isn't working!

[anterior1]

Hello! I am using ZeosDBO_6.6.1_beta and MySQL5. I have a following tables:

mysql> select * from student_group;
+----+--------------+----------------+-----------+
| id | idProfession | education_year | order_num |
+----+--------------+----------------+-----------+
| 1 | NULL | 2007 | 1 |
| 2 | NULL | 2007 | 2 |
| 3 | NULL | 2007 | 3 |
| 4 | 2 | 2005 | 1 |
| 5 | 2 | 2005 | 2 |
+----+--------------+----------------+-----------+

mysql> select * from profession;
+----+------+-------------+
| id | name | description |
+----+------+-------------+
| 2 | IIPM | I-I-P-M |
+----+------+-------------+

mysql>

My Delphi code is:
<...>
procedure TForm1.Button1Click(Sender: TObject);
var
connection : TZConnection;
table_stgroup : TZTable;
table_profession : TZTable;
begin
connection := TZConnection.Create(self);
with connection do
begin
User := 'root';
Password := 'ABCDEFG';
Protocol := 'mysql';
HostName := '127.0.0.1';
Database := 'deanery';
Connect;
end;
if not connection.Connected then
ShowMessage('Error!');

table_stgroup := TZTable.Create(self);
table_stgroup.Connection := connection;
table_stgroup.TableName := 'student_group';

table_profession := TZTable.Create(self);
table_profession.Connection := connection;
table_profession.TableName := 'profession';

with TStringField.Create(table_stgroup) do
begin
FieldName := 'MyLookup';
FieldKind := fkLookup;
DataSet := table_stgroup;
Name := Dataset.Name + FieldName;
KeyFields := 'idProfession';
LookupDataSet := table_profession;
LookupKeyFields := 'id';
LookupResultField := 'MyResultField';
table_stgroup.FieldDefs.Add(Name, ftString, 20, false);
end;

table_profession.Active := True;
table_stgroup.Active := True;
dsMain.DataSet := table_stgroup;

end;


After execute it on a my project a have error:
EDatabaseError with message "Field 'idProfession' not found".

Why? What i can fix this problem? Sanks.

[mdaems]

Please... Can you pack database dump and your sample program and attach it?
As you are using standard components it should be very easy to check what's wrong exactly. I hate setting up environments that I must guess from textual descriptions. Usually the error is in the parameter that's not in the description.

Mark

[anterior1]

Please... Can you pack database dump and your sample program and attach it?

[anterior1]

Can you help me?

[mdaems]

Not now... I really have it on my todo list, but I can only do it when I'm at home and have some time.

The content of the zip looks good at first view.


Mark

[mdaems]

I debugged it...

Here's my proposal:

Code: Select all

  table_stgroup := TZTable.Create(self);
  table_stgroup.Connection := connection;
  table_stgroup.TableName := 'student_group';

  table_profession := TZTable.Create(self);
  table_profession.Connection := connection;
  table_profession.TableName := 'profession';

  with TIntegerField.Create(table_stgroup) do
    begin
      FieldName := 'idProfession';
      FieldKind := fkData;
      DataSet := table_stgroup;
      Name := FieldName;
      Visible := False;
    end;

  with TStringField.Create(table_stgroup) do
    begin
      FieldName := 'MyLookup';
      FieldKind := fkLookup;
      DataSet := table_stgroup;
      Name := Dataset.Name + FieldName;
      KeyFields := 'idProfession';
      LookupDataSet := table_profession;
      LookupKeyFields := 'id';
      LookupResultField := 'description';
    end;

  table_profession.Active := True;
  table_stgroup.Active := True;
  dsMain.DataSet := table_stgroup;

And some explanation:
- Keyfields = the fields in your dataset you want to explain by 'lookingup' It should refer to a field definition that exists in your dataset. That's why I added a TIntegerField.Create block
- LookupKeyFields : the fields in the lookup table that correspond to the Keyfields. Typically the primary key of your lookup table.
- LookupResultField : the column in the lookup table that should be shown in the TLookupField

Why did you try this creating all stuff in runtime? I think testing this would have been a lot easier when you had created this setting by adding the components to the form and setting every property using the property palet. This would probably have given you a better overwiew of the available properties and the way they should be used.

Mark

[anterior1]

Oh.. thank you very much! It works good

Why did you try this creating all stuff in runtime?

I try creating it in a run-time because i have many similar tables and not want create for each of it own form. Also i think that it will be save a machine resources

[mdaems]

I agree this is a good solution for that specific problem. However, when in trouble, I would have tried to get it working 'the visual way' to see which properties should be set to which value. And then afterwards I would have tried to code the runtime stuff. (Which has it's own problems like not having to add the fields to the Fielddefs colection, I know.)

You can also create your table/query components and connect everything to each other by click-and-play and just modify the necessary properties depending on the table you want to use. That way of working should be safer when it comes to memory problems. Only 1 pointer to the query component that's created at startup instead of the creates/destroys you probably (should) do in your code to avoid memory leaks.

No deep knowledge about machine resources. But my first impression is 'why should it be better to create at runtime?' Visually designed components are also created only once with the same memory requirements. When you start creating/destroying this also eats resources. I think the only extra resources are required at design time by your IDE, but usually that's not a point.

Good luck...

Mark