Introduction

The database components of Borland C++Builder were created with SQL and client/server development in mind. Because of this, you will find extensive support to let you leverage the features of a remote server. C++Builder has implemented this support through two methods. First, the native commands of Borland C++Builder allow the developer to navigate tables, set ranges, delete and insert records, and modify existing records. Second, via pass-through SQL, a string can be passed to a server to be parsed, optimized, and executed, with results returned as appropriate.

This paper focuses on the second method of database access, pass-through SQL. While, it is not intended to be a course on SQL syntax or usage, this paper provides many examples of how to use the TQuery and the TStoredProc components. In doing so, many SQL concepts are presented such as selects, inserts, updates, views, joins, and stored procedures. The paper also introduces transaction control and connecting to a database. To begin, let's create a simple SELECT query and display the results.

2. TQuery Component

The TQuery and TTable components are both descended from TDataset which provides much of their database access functionality. TDatasource is used to prepare the data for the visual components. Because both the TTable and TQuery components rely on TDatasource, these components have many common traits. For instance, both provide a DatabaseName property through which an alias can be used to specify what server and database a query will access.

SQL Property

The unique functionality of TQuery includes a property titled 'SQL.' The SQL property is used to store a SQL statement. The steps below describe how to use a SQL statement to query for all employees who have a salary greater than a specified amount.

1. Drag and drop a TQuery object onto a form.
   

2. Set the DatabaseName property to an alias. (This example uses IBLOCAL which points to the sample database EMPLOYEE.GDB.
   

3. Select the SQL property and click the 'detail' button labeled with an ellipsis ("…"). The String List Editor dialog will appear.
   

4. Enter Select * from EMPLOYEE where SALARY>50000. Click OK.
   

5. Select the Active property and set it to "TRUE."
   

6. Drag and drop a TDatasource object onto the form.
   

7. Set the Dataset property of the TDatasource to "Query1."
   

8. Drag and drop a TDBGrid onto the form.
   

9. Set the Datasource property of the TDBGrid to "Datasource1".
   

Listing 1 displays the code in the Do It button.

Listing 1

void __fastcall TForm1::Button1Click(TObject *Sender)

{

// Check for a blank entry.

if (strcmp(Memo1->Lines->Strings[0].c_str(), "") == 0)

{

MessageBox(0, "No SQL Statement Entered", "Error", MB_OK);

return;

}

else

{

// Deactivate the query as a precaution.

Query1->Close();

// Empty any previous query

Query1->SQL->Clear();

// Assign the SQL property the memo's text.

Query1->SQL->Add(Memo1->Lines->Strings[0].c_str());

}

try

{

// Execute the statement and open the dataset

Query1->Open();

}

catch(EDBEngineError* dbError)

{

for (int i = 0; i < dbError->ErrorCount; i++)

{

MessageBox(0, dbError[i].Message.c_str(), "SQL Error", MB_OK);

}

}

}

Params Property

This would suffice for a user who knows SQL. Most users, however, do not. So your job as the developer is to provide an interface and construct the SQL statement. In Borland C++Builder, you can use a dynamic query to construct the SQL statement on the fly. Dynamic queries allow the usage of parameters. To specify a parameter in a query, use a colon (":") preceding a parameter name. Below is an example SQL statement using a dynamic parameter:

Select * from EMPLOYEE

where DEPT_NO = :Dept_no

When you want to test or set a default value for the parameter, select the Params property of Query1. Click the '...' button. This presents the parameters dialog. Select the parameter DeptNo. Then select Integer from the data type drop-down list. To set a default, make an entry in the Value edit box.

Bind parameters provide run-time access to modify a SQL statement. The parameters can be changed and the query re-executed to update the data. To directly modify the parameter value, use either the Params property or ParamByName method. The Params property is a pointer to a TParams object. So to access a parameter, you need to access the Item property of the TParams object specifying the index of the Items property. For example,

Query1->Params->Items[0]->AsInteger = 900;

or,

Query1->Params->Items[0]->AsInteger = atoi(Edit1->Text.c_str());

The AsInteger property reads the data as an Integer (nicely self documenting). This does not necessarily mean that the field type is an integer. If the field type is ANSIString, C++Builder will do the data conversion. So the above example could have been written as:

Query1->Params->Items[0]->AsString = "900";

or,

Query1->Params->Items[0]->AsString = Edit1->Text;

When you would rather use the parameter name instead of the index number, use the ParamByName method. This method will return the TParam object with the specified name. For example,

Query1->ParamByName("DEPT_NO")->asInteger = 900;

Listing 2 shows a complete example.

Listing 2

void __fastcall TForm1::Button1Click(TObject *Sender)

{

// Deactivate the query.

Query1->Close();

if (! Query1->Prepared)

{

// Make sure the query is prepared;

Query1->Prepare();

// Take the value entered by the user and replace the parameter

// with the value.

if (strcmp(Edit1->Text.c_str(),"") == 0)

{

Query1->ParamByName("DEPT_NO")->AsInteger = 0;

Edit1->Text = 0;

}

else

{

Query1->ParamByName("DEPT_NO")->AsString = Edit1->Text.c_str();

}

// Trap for errors.

try

{

// Execute the statement, and open the dataset.

Query1->Open();

}

catch(EDBEngineError* dbError)

{

for (int i = 0; i < dbError->ErrorCount; i++)

{

MessageBox(0, dbError[i].Message.c_str(), "SQL Error", MB_OK);

}

}

}

}

Notice the procedure first determines if the query is prepared. When the prepare method is called, Borland C++Builder sends the SQL statement to the remote server. The server will then parse and optimize the query. The benefit of preparing a query is to allow the server to parse and optimize it once. The alternative would be to have the server prepare it each time the query is executed. Once the query is prepared, all that is necessary is to supply new parameter values and execute the query.

Data Source

In the previous example, the user could enter a department number and when the query is executed, a list of employees in that department is displayed. What about using the DEPARTMENT table to help the user scroll through the employees and departments.

Note: The next example has a TTable called Table1. Table1's Databasename is IBLOCAL and the Tablename is DEPARTMENT. DataSource2 is the TDatasource bound to Table1. The table is also active and displaying records in a TDBGrid.

The way to connect the TQuery to the TTable is through the TDatasource. There are two main techniques to do this. First, place code in the TDatasource's OnDataChange event. For example, Listing 3 demonstrates this technique.

Listing 3 - Using the OnDataChange event to view child records

void __fastcall TForm1::DataSource2DataChange(TObject *Sender,

TField *Field)

{

Query1->Close();

if (!Query1->Prepared)

{

Query1->Prepare();

Query1->ParamByName("Dept_no")->AsInteger = Table1->Fields[0]->AsInteger;

try

{

Query1->Open();

}

catch(EDBEngineError* dbError)

{

for (int i = 0; i < dbError->ErrorCount; i++)

{

MessageBox(0, dbError[i].Message.c_str(), "SQL Error", MB_OK);

}

}

}

}

While the technique of using OnDataChange is very flexible, there is an easier way to connect a query to a table. The TQuery component has a Datasource property. By specifying a TDatasource for the Datasource property, the TQuery object will compare the parameter names in the SQL statement to the field names in the TDatasource. Where there are common names, those parameters will be filled in automatically. This would release the developer from having to perform the code in Listing 3 above.

In fact, the technique of using the Datasource requires no additional code at all. Perform the steps in listing 4 to connect the query to the table by DEPT_NO.

Listing 4 - Binding a TQuery to a TTable via the Datasource Property With the Query1, select the SQL property and enter:

select * from EMPLOYEE

where DEPT_NO = :DEPT_NO

Select the Datasource property and choose the datasource bound to Table1 (Datasource2 in the sample). Select the Active property and choose TRUE. That's all you need to do for this type of relation. There are some limitations on parameterized queries, however. Parameters are limited to values. You cannot use a parameter for a Column name or Table name for example. To create a query that dynamically modifies the table name, one technique could be to use string concatenation. Another technique is the use the Format command.

sprintf Function (from the C++ RTL)

The sprintf function will replace the format parameter (%s, %d, %n, etc.) with a value passed. For example,

sprintf(sqlStr, "select * from %s", "EMPLOYEE");

The result of the above command would be "Select * from EMPLOYEE". The function will do a literal replacement of the format parameter with the arguments . When using multiple format parameters,

replacement is done from left to right. For example,

tblName = "EMPLOYEE";

fldName = "EMP_ID";

fldValue = 3;

sprintf("select * from %s where %s = %d", tblName, fldName, fldValue)

The result of this sprintf function is "Select * from EMPLOYEE where EMP_ID = 3". This functionality provides an extremely flexible approach to dynamic query execution. The example in Listing 5 below lets the user to show the salary field in the result. The user also can enter a criteria for the salary field.

Listing 5 - Using sprintf to create a SQL statement procedure

void __fastcall TForm1::Button1Click(TObject *Sender)

{

// this will be used to hold the SQL Statement.

//

char* sqlStr = new char[250];

// These will be used to pass values to sprintf.

//

char* fmtStr1 = new char[50];

char* fmtStr2 = new char[50];

// If the Salary check box has a check.

//

if (showSalaryChkBox->Checked)

{

strcpy(fmtStr1, ", SALARY");

}

else

{

strcpy(fmtStr1, "");

}

// If the Salary Edit box is not empty

//

if (!(strcmp(salaryEdit->Text.c_str(),"") == 0))

{

strcpy(fmtStr2, salaryEdit->Text.c_str());

}

else

{

strcpy(fmtStr2, "> 0");

}

// Deactivate the query as a precaution.

//

Query1->Close();

// Erase the previous query.

//

Query1->SQL->Clear();

// Build the SQL statement using the sprintf() function.

//

sprintf(sqlStr, "Select EMP_NO %s from EMPLOYEE where SALARY %s", fmtStr1,

fmtStr2);

Query1->SQL->Add(sqlStr);

try

{

Query1->Open();

}

catch(EDBEngineError* dbError)

{

for (int i = 0; i < dbError->ErrorCount; i++)

{

MessageBox(0, dbError[i].Message.c_str(), "SQL Error", MB_OK);

}

}

}

In this example, we are using the Clear and Add methods of the SQL property. Because a prepared query uses resources on the server and there is no guarantee that the new query will use the same tables and columns, Borland C++Builder will unprepare the query any time the SQL property is changed. When a TQuery has not been prepared (i.e. the Prepared property is False), C++Builder will automatically prepare it each time it is executed

Open vs. ExecSQL

In the previous examples, the TQueries performed a Select statement. Borland C++Builder treats the result of the Select query as a Dataset, like a table would. This is just one class of SQL statements that are permissible. For instance, the Update command updates the contents of a record, but does not return records or even a value. When you want to use a query that does not return a dataset, use ExecSQL instead of Open. ExecSQL will pass the statement to the server to be executed. In general, if you expect to get data back from a query, then use Open. Otherwise, it is always permissible to use ExecSQL, although using it with a Select would not be constructive. Listing 6 provides an excerpt from an example.

Listing 6

void __fastcall TForm1::Button1Click(TObject *Sender)

{

// deactivate the query, and clear the current SQL statement.

//

Query1->Close();

Query1->SQL->Clear();

// change the SQL statement to perform an update (which returns

// no dataset).

//

Query1->SQL->Add("update EMPLOYEE set SALARY = (SALARY * (1 + :raise)) where (SALARY < :salary)");

Query1->ParamByName("salary")->AsString = Edit1->Text.c_str();

Query1->ParamByName("raise")->AsString = Edit2->Text.c_str();

// execute the new SQL statement.

//

try

{

Query1->ExecSQL();

}

catch(EDBEngineError* dbError)

{

for (int i = 0; i < dbError->ErrorCount; i++)

{

MessageBox(0, dbError[i].Message.c_str(), "SQL Error", MB_OK);

}

}

// deactivate the query, and set the SQL statement back to the

// original SQL statement.

//

Query1->Close();

Query1->SQL->Clear();

Query1->SQL->Add("select * from EMPLOYEE");

// open the Query.

//

try

{

Query1->Open();

}

catch(EDBEngineError* dbError)

{

for (int i = 0; i < dbError->ErrorCount; i++)

{

MessageBox(0, dbError[i].Message.c_str(), "SQL Error", MB_OK);

}

}

}

The examples provided here only introduce the subject of using queries in your application. They should give you a good foundation to begin using TQueries in your applications. SQL servers offer additional features including stored procedures and transactions. The next two sections briefly introduce some of these features.