The TQuery class allows you to execute Structured Query Language (SQL) programs on a table. These programs can can retrieve items related by a designated property from a table, or manipulate data in the table. The class TQuery is a descendent of TDataSet, so you can use a TQuery as a table. This will allow you to find all records which share a property that must be changed and then change the property directly in the TQuery.
Create a TQuery by selecting the TQuery button 
from the Data Access tab of the component pallet. Click on the form that will use the query. Rename the query with a name that reflects its purpose and select the Database Name from which the query will be made. If you want to actually modify records found by a Query you must set the RequestLive property to true.
Frequently programmers are asked to find all records from a data structure (file) that have some property. Suppose that you are asked to find all sections taught by a given professor in our course database . You could just search the Table from beginning to end and store each item found in an array that could be shown in a listbox. There is a way to use the database directly. If you issue the SQL command
Select * from Sections where professor like 124where 124 is the id of the professor, you would get the list of all sections with 124 as their "professor" field. Note that "Sections" is the name of the table being queried.
In this program the user is first asked to select a professor from a combobox. You have a local list of professors that is initialized from the ProfTable. The class ProfList has a function getNames that returns all of the professors' names in a format suitable for a listbox. They are in the same order as the entries in the database, so once you have the ItemIndex returned from the dialog, you can move directly to that entry and retrieve the Id for the professor in question. Use this number to make the query. .
The query is a TDataSet, so can be sent to the retrieve function for SectionList. This allows you to create sections which has a function getDescriptions that returns a listing suitable for a listbox The list of sections taught by that professor appears in a window that has a listbox.
void _fastcall TMainForm::ProfessorsCourses1Click(TObject *Sender)
{
//Retrieve the name of the professor whose courses you wish to list.
ProfList profs;
profs.retrieve(ProfTable);
SelectDialog->TypeGroupBox->Caption = "Sections for which Professor?";
SelectDialog->TypeComboBox->Items->Text = profs.getNames();
SelectDialog->TypeComboBox->ItemIndex = 0;
if (SelectDialog->ShowModal()== IDOK)
{
int whichProf = SelectDialog->TypeComboBox->ItemIndex;
ProfTable->First();
ProfTable->MoveBy(whichProf);
String profId = ProfTable->FieldByName("Id")->AsString; //Get the professors Id as a String
MainQuery->Close();
MainQuery->SQL->Clear();
MainQuery->SQL->Add("Select * from Sections where professor like "+profId);
MainQuery->Open();
SectionList sections;
sections.retrieve(MainQuery);
QueryList->Header->Caption = "Section Course Professor";
QueryList->QueryListBox->Items->Text = sections.getDescription(ProfTable,CourseTable1);
QueryList->ShowModal();
}
In cases where you want to delete all records with a given property, you may use the SQL delete command. If you delete a course from you database, all sections of that course will automatically be removed. You may use the SQL command:
delete from Sections where course =
This command may be inserted into your Query the same way the select command was above. The course number to be deleted is retrieved from the course. Note the course was selected in the course window and the ItemIndex told you which of the courses was to be deleted. Before it is deleted, you must retrieve its Id, so you can delete all sections of the from the Sections table. The sequence of class to TQuery functions is the same as that shown above, except instead of Open you use ExecSQL. This is used on insert, delete and update SQL commands. Notice after the delete, you call on the SectionWindow Repaint function. This shows the changes in the SectionWindow.
void __fastcall TCourseWindow::DeleteCourse1Click(TObject *Sender)
{
int where = CourseListBox1->ItemIndex;
if(where >=0)
{
String prompt = "Are you sure you want to delete course number "+
_courses[where].number()+"?";
if(Application->MessageBox(prompt.c_str(),"Delete This",MB_YESNO)==IDYES)
{
CourseTable1->First();
CourseTable1->MoveBy(where); //Move to the course that will be deleted
// Delete sections of this course
SectionDeleteQuery->Close();
SectionDeleteQuery->SQL->Clear();
SectionDeleteQuery->SQL->Add("delete from Sections where course = "+ CourseTable1->FieldByName("Id")->AsString);
SectionDeleteQuery->ExecSQL();
SectionDeleteQuery->Close();
CourseTable1->Delete();
_courses.deleteItemAt(where);
CourseListBox1->Items->Text = _courses.getDescription();
SectionWindow->Repaint();
}
}
}
If a professor is deleted in the course example, you will still have the course sections. These sections will now refer to a non-existant professor id, an error. Before you can delete the professor, you must change the instructor for all of the professors courses. You will use a TQuery that finds these courses as a small TDataSet. This TQuery must have its RequestLive set to be true in the Object Inspector.
This code is pretty well documented, but there are some major ideas that should be presented. The first section of code up to the Open call for the query is straight forward. You retrieve the selected professor from the ProfTable. Next get the professors id and formulate the query that will be a list of all sections taught by this professor. The first interesting problem is how to handle the changes. It might be the case that you allow the user to deside not to delete even while editing the course list. To achieve this flexiblity, you delete the professor from the list of professors (but save the information in a local variable). Then create a section list from the SectionQuery. This list may be modifed and nothing changes in the database.
Actual changes require that you use the SectionDialog. You may use the updated professor list of initialize the list of professors, but you also need to have access to the CourseTable for course numbers. The Lookup function retrieves the "number" of the course shown in the window. The value returned by Lookup is a Variant type. For a single field specifed in the last parameter, the variant is a variable of that type. If more than one field is specified, the variant is an array. You must use the function GetElement to dereference the array. In the section window when you edit a record, you have to find a specific professor. To create a Professor you need the firstName, lastName and initial. The following code retrieves this.
long int profId = _sections[where].profLink();
Variant v = ProfTable->Lookup("Id",profId,"lastName;firstName;initial");
Professor p(v.GetElement(0),v.GetElement(1),v.GetElement(2));
int profLoc = profs.find(p);
Finally, after changing the Query values in the local section array, use an iterator of that array to update the SectionTable by updating the Query as if it were any TDataSet .
void __fastcall TProfWindow::DeleteProfessor1Click(TObject *Sender)
{
int whichProf = ProfListBox1->ItemIndex;
if(whichProf >=0)
{
String prompt = "Are you sure you want to delete "+_professors[whichProf].name();
if (Application->MessageBoxA(prompt.c_str(),"Delete This?",MB_YESNO)==IDYES)
{
//Find the database location of the professor deleted
ProfTable->First();
ProfTable->MoveBy(whichProf);
// Find all sections taught by that professor
String profId = ProfTable->FieldByName("Id")->AsString;
SectionQuery->Close();
SectionQuery->SQL->Clear();
SectionQuery->SQL->Add("Select * from Sections where professor like "+profId);
SectionQuery->Open();
// Save the professor info if you have to add back into proflist
Professor saveIt = _professors[whichProf];
// delete the prof from the proflist so doesn't show up in the
// professor combobox in the section dialog
_professors.deleteItemAt(whichProf);
// Get an array of sections that are found in the query
SectionList sections;
sections.retrieve(SectionQuery);
// get the information about the courses
CourseList courses;
courses.retrieve(CourseTable);
// initialize the constant fields in the section dialog
SectionDialog->ProfComboBox1->Items->Text = _professors.getNames();
SectionDialog->CourseComboBox1->Items->Text = courses.getNumbers();
// go through the array of sections taught by
// deleted prof and find new instructors
ArrayIteratornext(sections);
bool notCanceled = true;
while (next && notCanceled)
{
// don't know which professor will be chosen
SectionDialog->ProfComboBox1->ItemIndex = 0;
// need to set the course selection and section number
long int courseId = next().courseLink();
// get the number of the course
Variant v1 = CourseTable->Lookup("Id",courseId,"number");
Course c(v1);
// look up the course in the course list
int courseLoc = courses.find(c);
// set the select box so that the course is selected
SectionDialog->CourseComboBox1->ItemIndex = courseLoc;
// set the section number
SectionDialog->SectionNumber->Text= next().number();
if (SectionDialog->ShowModal()==IDOK)
{
// find the id of the new prof
ProfTable->First();
ProfTable->MoveBy(SectionDialog->ProfComboBox1->ItemIndex );
int profId = ProfTable->FieldByName("Id")->AsInteger;
// set the prof number in our query array of sections
next().setProfessor(profId);
}
else // if canceled restore _professors and don't allow changes
{
notCanceled= false;
_professors.insertItemAt(saveIt,whichProf);
}
next++;
}
if (notCanceled)
{
next.init();
SectionQuery->First();
while(next)
{
SectionQuery->Edit();
next().place(SectionQuery);
SectionQuery->Post();
SectionQuery->Next();
next++;
}
// delete the prof from the table
ProfTable->First();
ProfTable->MoveBy(whichProf);
ProfTable->Delete();
ProfListBox1->Items->Text = _professors.getDescription();
SectionWindow->Repaint(); // show changes in the section window
}
}
}
}
To get complete code for this problem click here .
You will often need to retrieve data from more than one table in a database. If for example you wanted a list of all the sections of each course ordered by course and section number as shown here.
The problem is that the information is stored in more than one table and all of your SQL has retrieved data from only one table. How can you retrieve data with specific properties from more than one table. SQL allows you to join two tables based on a condition. In this case, entries in the Courses table are related to the entries in the Sections table by the courseId. Your query in this case will effectively create a temporary new table with data from two tables. This new table will have the selected fields (in the example below the sectionNumber from Sections and the courseNumber and courseName from the Courses table. Entries are created for pairs of items in the two tables with equal courseId entry. Sorting by the courseNumber first, groups the courses.
SELECT Sections.sectionNumber, Courses.courseNumber, Courses.courseName FROM Courses INNER JOIN Sections ON Courses.courseId=Sections.courseId ORDER BY Courses.courseNumber,Sections.sectionNumber;
You can facilitate reading and displaying the query information by creating a class to store query information. In this case the class is AllSectionsQuery, and it is built much the same way you build the classes corresponding to objects in tables.
class AllSectionsQuery
{
public:
void retrieve(TDataSet *source);
String print()const;
private :
String _sectionNumber;
String _courseNumber;
String _courseName;
};
In this case, you will not find a list, but in the second example there is also a list of query items.
Suppose you want to retrieve all of the courses taught by one professor. You will not only need to join the Courses and Sections tables, you will need to connect with the Professor table. When you have doubts as to the syntax, you can always construct the query in Access and check the SQL for the query when you have it working. The one thing to remember is that in Access you indicate that the user provides a value by placing a variable in square brackets([]). This must be converted to a parameter in Builder. Parameters are variables that start with a colon(:).
In this case your SQL should join all three tables, but as above you will first join the Courses and Sections table to create the same temporary table that you used in the first problem. Then from this table and the professor table, pick entries with the profId matching in the Professor and Sections table. Finally, you want to find courses for a professor with a given last name (not a very good query, but it will work in this case). This means from second temporary table you want only those entries with the professor lastName equal to the name the user enters.
SELECT Professor.lastName,Sections.sectionNumber,
Courses.courseNumber,Courses.courseName
FROM
Professor
INNER JOIN
(Courses INNER JOIN Sections
ON
Courses.courseId = Sections.courseId)
ON
Professor.profId =Sections.profId
WHERE
Professor.lastName=:last
ORDER BY
Professor.lastName;
The c++ code that retrieves the information and displayes it follows.
void __fastcall TMainForm::GetCourse1Click(TObject *Sender)
{
SectionQuery->Close();
SectionQuery->Prepare();
String profId = InputBox("Professor Name" ,"Find all courses with this professor." ,"" );
if (profId!="" )
{
SectionQuery->Params->Items[0]->AsString =profId; //set the parameter
SectionQuery->Open();
QueryList l;
l.retrieve(SectionQuery);
MainListBox->Items->Text=l.print();
}
}
For a complete copy of this program click here .