Borland SQL Query with Paradox DB

[dakkarin]

hi i have designed a program that runs with paradox db and one of the fields of my table is like that
Ex:
----
Field name:Genre
Field 1 Content:Action,Drama,Comedy,Thriller
Field 2 Content:Action,Mystery,War

what i want to do is when user entered Action and Thriller it will find the correct answer for this example field 1
but i couldnt find the appropriate sql query for that
i tried

SELECT Userid FROM divx where Genre LIKE ......

but i couldnt find how to finish
is there any way to do something like that?

My second question is after i do this sql query how can i take the data from a field
i mean i didnt understand how to use
query->GetFieldData()

i would be appreciated for any help

Liars Do Not Fear The Truth If There Are Enough Liars

 

[dakkarin]

any idea

Liars Do Not Fear The Truth If There Are Enough Liars

 

[2ffat]

Sorry, I got busy. I'm not sure about the first since I've never used SQL with Paradox. I'll post the solution to the second shortly (I hope).


James P. Cottingham
-----------------------------------------
[sup]I'm number 1,229!
I'm number 1,229![/sup]

 

[dakkarin]

its my mistake i dont use paradox i use bde(borland database engine)

Liars Do Not Fear The Truth If There Are Enough Liars

 

[2ffat]

Wellllll, here goes. Like I said, I've never used SQL with Paradox or BDE but to retrieve the data, you need to use a DataSet. Since the DataSet is the basis for "talking" to databases, most components include a DataSet, this includes TTable, ADOQuery, etc. What I don't know is if the BDE query does or not. If it does not, you may have to use a DataSet or Table.

An understanding of DataSets is essential to this process. Read everything you can about DataSets. By calling a dataset, you can retrieve the data in a variety of means. Instead of showing you some of my programs, I'm going to show you a segment of code from C++Builder's Developer Journal March 2005. (Great mag, BTW.) This segment calls a BDEQuery then gets some data from some fields. One table's name is "Borrow" which is called by the query. The other table's name is "CdDvdID" and it's fields' names are "CdDvdID" and "Available." This latter table is linked to a DataSet.

TQuery* Query = new TQuery(NULL);
Query->SQL->Text = "SELECT * FROM Borrow WHERE CdDvdID = " + DataSet->FieldByName("CdDvdID")->AsString;
Query->Active = true;
if (Query->RecordCount == 0)
    DataSet->FieldByName("Available")->AsBoolean = true;
else
    DataSet->FieldByName("Available")->AsBoolean = false;
Query->Close();
Query->Free();

If you look at the Select statement, you will see that it is pulling a field from "CdDvdID" named "CdDvdID." If a record exists in "Borrow" the DataSet then sets "Available" to true; otherwise, it sets the field to false.

James P. Cottingham
-----------------------------------------
[sup]I'm number 1,229!
I'm number 1,229![/sup]

 

[dakkarin]

i made my program like this

        genreliste = new TStringList();
        tablo->Active = false;
        sorgu->SQL->Clear();
        sorgu->DataSetField->Visible = true;
        sorgu->SQL->Add("SELECT Userid,Genre FROM divx");
        sorgu->Open();
                for (int i=0;i<sorgu->RecordCount;i++)
                {
                        genreliste->Add(sorgu->DataSetField->Fields->FieldByName("Genre")->AsString);
                }

but its giving the error

EAccessViolation with message 'AccesViolation at adress 405BC914 in VCLDB50.BPL....'

can u help me about this

Liars Do Not Fear The Truth If There Are Enough Liars

 

[2ffat]

Let's see if the problem is with the TListString or the SQL. Change the code and see if this works or not.

//genreliste = new TStringList();
tablo->Active = false;
sorgu->SQL->Clear();
sorgu->DataSetField->Visible = true;
sorgu->SQL->Add("SELECT Userid,Genre FROM divx");
sorgu->Open();
for (int i=0;i<sorgu->RecordCount;i++)
{
    //genreliste->Add(sorgu->DataSetField->Fields->FieldByName("Genre")->AsString);
    AnsiString GenreStr = sorgu->DataSetField->Fields->FieldByName("Genre")->AsString;
}

James P. Cottingham
-----------------------------------------
[sup]I'm number 1,229!
I'm number 1,229![/sup]

 

[dakkarin]

still same error

Liars Do Not Fear The Truth If There Are Enough Liars

 

[2ffat]

So it isn't the StringList. I'm not familar with DataSetField so let me do some research this weekend.

James P. Cottingham
-----------------------------------------
[sup]I'm number 1,229!
I'm number 1,229![/sup]

 

[dakkarin]

ok thanks
ill be waiting for your directions

Liars Do Not Fear The Truth If There Are Enough Liars

 

[dakkarin]

are there any progress?
were you be able to find anything

Liars Do Not Fear The Truth If There Are Enough Liars

 

[2ffat]

I haven't found anything yet but I'm still looking.


James P. Cottingham
-----------------------------------------
[sup]I'm number 1,229!
I'm number 1,229![/sup]

 

[2ffat]

Is the database you are trying to reach a nested detail set of another dataset?

When setting the DataSetField property, the dataset must be of (or a descendant of) the class specified by the master dataset’s NestedDataSetClass property. Otherwise, setting DataSetField throws an exception. Further, setting DataSetField to a TDataSetField that is contained in the same dataset throws an exception (A dataset can’t be nested in itself).

If it is not, just use the DataSet itself. The following is from one of my programs that use the ADO DataSet.

TDateTime MachineDate = ProductionADODataSet->FieldByName("MachineDate")->AsDateTime;
AnsiString MachineStr = ProductionADODataSet->FieldByName("Machine")->AsString;

James P. Cottingham
-----------------------------------------
[sup]I'm number 1,229!
I'm number 1,229![/sup]

 

[dakkarin]

no it is not according to your message i have changed my code like this but nothing changed

TDataSet *datareader;
tablo->Active = false;
sorgu->SQL->Clear();
sorgu->SQL->Add("SELECT Userid,Genre FROM divx where Izlendi=False");
sorgu->Open();
datareader->DataSetField = sorgu->DataSetField;


for (int i=0;i<sorgu->RecordCount;i++)
{
if(sorgu->DataSetField->DataSet->FindNext())
{
genreliste->Add(datareader->FieldByName("Genre")->AsString);
}
else
{
MessageDlg("Ekleme Tamamlandi",mtWarning,TMsgDlgButtons() << mbOK,0);
}
}
sorgu->Close();
tablo->Active = true;


and i also dont know how to use ado conections

Liars Do Not Fear The Truth If There Are Enough Liars

 

[dakkarin]

ok i gave up from sql,its too complicated in borland im trying to do it with table
but i have some problems when i search a string in a field and then i want the table look to other records but its not doing this it always finds the same record
is there something wrong with my new code

Variant localvalues;
TLocateOptions opts;

        opts.Clear();
        opts << loCaseInsensitive;
        opts << loPartialKey;


                        localvalues.Clear();
                        localvalues = tavsiye[0];


                   for (int i=0;i<tablo->RecordCount;i++)
                   {
                          tablo->Locate("Genre",localvalues,opts);
                        tablo->FindNext();
                        genreliste->Add(tablo->FieldByName("Moviename")->AsString);

                   }

Liars Do Not Fear The Truth If There Are Enough Liars

 

[2ffat]

Like I said, I've never used SQL with BDE. ADO is Microsoft's way of connecting to databases.

Let's rewrite your code a little bit and see if it will work better.

Variant localvalues;
TLocateOptions opts;

opts.Clear();
opts << loCaseInsensitive;
opts << loPartialKey;

localvalues.Clear();
localvalues = tavsiye[0];

tablo->Locate("Genre",localvalues,opts); // Get records
int RecNo = tablo>RecordCount; // Get number of records
if (RecNo == 0) return; // Nothing to get so return to calling function
else // We have records
{
   tablo->FindFirst(); //Make certain you are at beginning
   for (int i = 0; i < RecNo; i++) // loop
   {
      genreliste->Add(tablo->FieldByName("Moviename")->AsString);
      tablo->FindNext();
   } // loop
} // We have records

James P. Cottingham
-----------------------------------------
[sup]I'm number 1,229!
I'm number 1,229![/sup]