C++Builder, 1, Borland C++Builder White Paper: SQL Access in C++Builder

By: Borland Staff

Abstract: 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.

SQL Access in C++Builder
Written by Mike Destein. Revised by Charles Gallant.

3. TStoredProc Component

A stored procedure is a listing of SQL, or server-specific, commands stored and executed on the server. Stored procedures are not much different in concept than other kinds of procedures. The TStoredProc is descended from TDataset so it will share many traits with the TTable and TQuery. The similarity to a TQuery is especially noticeable. Since stored procedures aren't required to return a value, the same rules apply for the ExecProc and Open methods. Each server will implement stored procedure usage a little differently. For example, if you are using Interbase as the server, stored procedures are executed via Select statements. To view the result of the stored procedure, ORG_CHART, in the example EMPLOYEE database use the following SQL statement:

Select * from

ORG_CHART

With other servers such as Sybase, you can use the TStoredProc component. This component has properties for Databasename and the name of the stored procedure. If the procedure takes any parameters, use the Params property to input values.

Image 2


4. TDatabase

The TDatabase component provides functionality in addition to that of the TQuery and TStoredProc component. Specifically, a TDatabase allows the application to create a BDE alias local to the application thus not requiring an alias to be present in the BDE Configuration file. This local alias can be used by all TTables, TQueries, and TStoredProcs in the application. The TDatabase also allows the developer to customize the login process by suppressing the login prompt or filling in parameters as necessary. Lastly and most importantly, a TDatabase can keep a single connection to a database funneling all database operations through one component. This allows the database controls to support transactions.

A transaction can be thought of as a unit of work. The classic example of a transaction is a bank account transfer. The transaction would consist of adding the transfer amount to the new account and deleting that amount from the original account. If either one of those steps fails, the entire transfer is incomplete. SQL servers allow you to rollback commands if an error occurs, never making the changes to the database. Transaction control is a function of the TDatabase component. Since a transaction usually consists of more that one statement, you have to mark the beginning of a transaction and the end. To mark the beginning of a transaction, use TDatabase->StartTransaction() . Once a transaction has begun, all commands executed are in a temporary state until either TDatabase->Commit() or TDatabase->Rollback() is called. If Commit is called, any changes made to the data are posted. If Rollback is called, any changes are discarded. The example in Listing 7 below uses the PHONE_LIST table. The function shown will change the location of the office that was entered in the EditOldLocation TEdit control to the location entered in the EditNewLocation TEdit control.

Listing 7

void __fastcall TForm1::Button1Click(TObject *Sender)

{

try

{

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

//

char sqlStr[250];

Database1->StartTransaction();

Query1->SQL->Clear();

// change Location of the office that was entered

// into EditOldLocation to the value entered into

// EditNewLocation.

//

sprintf(sqlStr, "update PHONE_LIST set LOCATION = "%s" where (LOCATION = "%s")", EditNewLocation->Text.c_str(), EditOldLocation->Text.c_str());

Query1->SQL->Add(sqlStr);

Query1->ExecSQL();

Query1->SQL->Clear();

// change Location of the office that was entered

// into EditNewLocation to the value entered into

// EditOldLocation.

//

sprintf(sqlStr, "update PHONE_LIST set LOCATION = "%s" where (LOCATION = "%s")", EditOldLocation->Text.c_str(), EditNewLocation->Text.c_str());

Query1->ExecSQL();

// commit all changes made to this point.

//

DataBase1->Commit();

Table1->Refresh();

Table2->Refresh();

}

catch(EDBEngineError* dbError)

{

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

{

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

}

Database1->Rollback();

return;

}

catch (Exception* exception)

{

MessageBox(0, exception->Message.c_str(), "Error", MB_OK);

Database1->Rollback();

return;

}

}

The last step necessary is connecting to the database. In the example above, a TDatabase was used to provide the single conduit to the database, thus allowing a single transaction. To accomplish this, an Aliasname was specified. The alias holds the connection information such as Driver Type, Server Name, and User Name. This information is used to create a connect string. To create an alias, you can use the BDE Config utility or, as the next example shows, you can fill in parameters at runtime.

The TDatabase component provides a Params property that stores connection information. Each row in the Params property is a separate parameter. In the example below, the user puts their User Name in Edit1 and the Password in Edit2. To connect to the database, the code in Listing 8 is executed:

Listing 8

void __fastcall TForm1::Button1Click(TObject *Sender)

{

try

{

// create two buffers, one for user name,

// and the other for password entries.

//

char* nameStr = new char[20];

char* passStr = new char[20];

//Close the Database, and set the params.

//

Database1->Close();

Database1->DriverName = "INTRBASE";

Database1->KeepConnection = true;

Database1->LoginPrompt = false;

Database1->Params->Add("SERVER NAME=d:ebonyIntrBaseEXAMPLESEMPLOYEE.GDB");

Database1->Params->Add("SCHEMA CACHE=8");

Database1->Params->Add("OPEN MODE=READ/WRITE");

Database1->Params->Add("SQLPASSTHRU MODE=SHARED NOAUTOCOMMIT");

sprintf(nameStr, "USER NAME=%s", Edit1->Text.c_str());

Database1->Params->Add(nameStr);

sprintf(passStr, "PASSWORD=%s", Edit2->Text.c_str());

Database1->Params->Add(passStr);

// Re-Open the Database, and re-open the Table

//

Database1->Open();

Table1->Open();

// Fill a ComboBox with the names of the tables in the

// Database.

//

Database1->Session->GetTableNames(Database1->DatabaseName, "*",

true,

true,

ComboBox1->Items);

}

catch(EDBEngineError* dbError)

{

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

{

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

}

}

}

This example shows how to connect to a server without creating an alias. The key points are to specify a DriverName and fill out the Params with the necessary information to connect. You don't need to specify all the parameters, you just need to specify those that are not set with the driver in the BDE Configuration. Making an entry in the Params property will override any settings in BDE Configuration. By omitting a parameter, C++Builder will fill in the rest of the parameters with the settings in the BDE configuration. The example above also introduces the new concepts of sessions and the GetTableNames method. The session variable is a handle to the database engine and is discussed further in your Borland C++Builder documentation.

One other point to be made is the use of SQLPASSTHRU MODE. This parameter of a database determines how native database commands such as TTable.Append and TTable.Insert will interact with TQueries connected to the same database. There are three possible values: NOT SHARED, SHARED NOAUTOCOMMIT, and SHARED AUTOCOMMIT. NOT SHARED means that native commands are using one connection to the server and the queries are using another connection. The server sees this as two different users effectively. Any time that a transaction is active, native commands are not committed until that transaction is posted. If a TQuery is executed, any changes it might make are posted to the database, separate from the transaction.

The other two modes SHARED NOAUTOCOMMIT and SHARED AUTOCOMMIT. AUTOCOMMIT will share the same connection to the server with the native commands and queries. The difference between the two is whether native commands are automatically committed upon execution. If SHARED AUTOCOMMIT is the selected mode, then it would be pointless to begin a transaction that uses native commands to delete a record and then try to issue Rollback. The record would be deleted and the change would be committed prior to the Rollback command. If you need to issue native commands within a transaction and have those commands included with the transaction, make sure the SQLPASSTHRU MODE is set to SHARED NOAUTOCOMMIT or NOT SHARED.

4. Conclusion

C++Builder provides many services to use the SQL language with your database servers. At this point, you should have a good framework to begin using SQL in your C++Builder applications.


Server Response from: ETNASC03