MYDAC Search questions

[mysqluser]

hello

i have a question about MYDAC and the search. My problem is, i don't know how i do a search in the following way:

I have a database table called Announces with 89 fields. This table has about 10'000 entries. Now what i like is, to search with mydac direct in the database table without getting all entries to the client, because the download time will be to long.

a) i dont know how to search with mydac in a table. how do i do that?

b) if a line from the table matches by the search process, i like to put the current field data (all 89 fields) into a class. How do i get from a specified line all datas and put the correct fields into the class?

hope you can understand me, otherwiese, please ask me.
thanks

[Antaeus]

You should use the TMyQuery component in this case. Just assign to SQL property a query with the necessary search condition and call MyQuery.Open. Only records that match the condition will be returned. After that you can assign field values of the record to corresponding class properties. This may look like the code bellow:

Code: Select all

  MyQuery.SQL.Clear;
  MyQuery.SQL.Add('SELECT * FROM AnyTable');
  MyQuery.SQL.Add('WHERE Field_0 = :Value_0');              // adding condition with parameter
  MyQuery.ParamByName('Value_0').AsString := 'Any_Value';   // assigning parameter value
  MyQuery.Open;
  AnyClass.Property_1 := MyQuery.FieldByName('Filed_1').AsString; // assigning string property
  AnyClass.Property_2 := MyQuery.FieldByName('Filed_2').AsInteger; // assigning integer property

[mysqluser]

hello and thank you

but i understand your reply 50%..


MyQuery.SQL.Clear; //understand
MyQuery.SQL.Add('SELECT * FROM AnyTable'); //understand

but, MyQuery.SQL.Add('WHERE Field_0 = :Value_0'); i understand not at all. do you mean it like (using TEdit as value source):

Code: Select all

MyQuery.SQL.Add('WHERE Name = ' + Edit1.Text + ', Gender = ' + Edit2.Text + ', Title = ' + Edit3.Text); (etc. etc.) ??

or how do i do it? I have some edits/comboboxes and need it as search value (but only if they are not empty) and then use it for the search process.

next one i dont understand right is:

Code: Select all

MyQuery.ParamByName('Value_0').AsString := 'Any_Value';   // assigning parameter value

for what is it and how do i have to understand/use it?

last one:

Code: Select all

  AnyClass.Property_1 := MyQuery.FieldByName('Filed_1').AsString; // assigning string property 
  AnyClass.Property_2 := MyQuery.FieldByName('Filed_2').AsInteger; // assigning integer property

this i understand not at all. There i see only from which field but not fro which line and i have there a question because this looks like it uses only one match search and put its into a class. But what i need is to creat for each matches (from 10000 table entries are like 20 entries matches) a new instance from a class and put the next matched data from table line into the instance. I know how to creat instances and so on, but i dont see how to do the search that each match can be used for that what i need.

Or is after MyQuery.Open; all found data (like 20 table entries) in TMyQuery? How do i get it then? So i could do a for loop and creat DynamicArrays and the needed instances and put the found data for each one. hope you can understand me

[Antaeus]

The following lines:

Code: Select all

  MyQuery.SQL.Add('WHERE Field_0 = :Value_0');              // adding condition with parameter
  MyQuery.ParamByName('Value_0').AsString := 'Any_Value';   // assigning parameter value 

are equal to this line:

Code: Select all

  MyQuery.SQL.Add('WHERE Field_0 = ' + 'Any_Value');

But in the first case parameter is used. Query with parameters has some advantages. For detailed information see topics concerned developing database applications in Delphi Help.

> last one:
Just change this code

Code: Select all

  AnyClass.Property_1 := MyQuery.FieldByName('Filed_1').AsString; // assigning string property 
  AnyClass.Property_2 := MyQuery.FieldByName('Filed_2').AsInteger; // assigning integer property

to the following one:

Code: Select all

  while not MyQuery.EOF do begin
    AnyClass := TAnyClass.Create;
    AnyClass.Property_1 := MyQuery.FieldByName('Filed_1').AsString; // assigning string property 
    AnyClass.Property_2 := MyQuery.FieldByName('Filed_2').AsInteger; // assigning integer property
    // ...
    // do something with the newly created and initialized AnyClass 
    MyQuery.Next; // move to the next 
  end;

[mysqluser]

thanks alot Antaeus

i'm using now this like:

MyQuery->SQL->Add("WHERE `Field1` = 'Value' AND `Field2` = 'Value' AND `Field3` = 'Value'");

is this correct syntax?

[[email protected]]

The way to use parameter is much better !!!

As first step you can load you SQL-Script from a file with
MyQuery.LoadFromFile('get_names_from_table.sql')
After that you are able to change you select without changing your program.

With the the line inside the SQL-Script
MyQuery.SQL.Add('WHERE name = :NAME');
you tell the component, to use a WHERE and search for the field name inside your table.

With the line inside the SQL-Script
MyQuery.ParamByName('name').AsString := 'paul';
you are able to change the search criteria inside the program.

I forget, if you use inside the SQL-Script
MyQuery.SQL.Add('WHERE name LIKE :NAME');
you are able to search for parts of the name or all with the '%' sign

I hope it was explained, to understand it...

Greetings Uwe

[mysqluser]

hi uwe

thank you, i have understand it by the way, im also from switzerland