Borland/Inprise  
. Shop  |  Products  |  Downloads  |  Programs  |  Worldwide  |  Newsgroups  |  Contact  |  Support  |  Search  |  Community .
.
 
C++ Builder Developer Support


Technical Information Database

TI2654C.txt   SQL Programming in C++Builder
Category   :General
Platform    :All
Product    :C++Builder  All

Description:
SQL access in C++Builder

- Introduction

- TQuery Component

- SQL Property

- Params Property

- DataSource

- Format Command

- Open vs. ExecSQL

- TStoredProc Component

- TDatabase

- Conclusion

1. Introduction

The database components of C++Builder were created with SQL and

client/server in mind. As such, you will find extensive support to

leverage the features of a remote server. C++Builder has implemented

this support through two methods. First, the native commands of

C++Builder allow the developer to navigate tables, set ranges, delete

and insert records, and modify existing records. The second method, is

via pass-through SQL, where a string is passed to the server to be

parsed, optimized, executed, and return the results.

This paper will focus on the second method of database access,

pass-through SQL. While, it is not intended to be a course on SQL syntax

or usage, it will provide many examples on how to make use of the TQuery

and the TStoredProc components. In doing so, many SQL concepts will be

presented such as selects, inserts, updates, views, joins, and stored

procedures. The paper will also introduce transaction control and

connecting to the database, but will not spend much time with these

subjects. So to get started, let's develop a simple SELECT query and

display the results.

2. TQuery Component

If you are going to use SQL in your applications, you will become very

familiar with the TQuery component. The TQuery and TTable components are

descended from TDataset. TDataset provides much of the functionality for

database access. As such, TQuery and TTable components have many common

traits. Therefore, a TDatasource is still used to prepare the data for

the visual components. Also, to specify what server and database the

query will access, an alias name needs to be specified. This would be

accomplished by setting the DatabaseName property of the TQuery object.

SQL Property

The TQuery has some unique functionality though. For instance, TQuery

has a property called SQL. The SQL property is used to store the SQL

statement. The directions below describe the basic steps to query out

all employees who have a salary greater than $50,000. 

      1.  Create a TQuery object

      2.  Set the DatabaseName property to an alias. (This               

   

         example uses IBLOCAL which is defined the sample        

          database EMPLOYEE.GDB.

      3.  Select the SQL property and click the button with           

          the text-'...'. The String List Editor dialog               

          should appear.

      4.  Enter: Select * from EMPLOYEE where SALARY>50000.           

          Then press OK.

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

      6.  Drop down a TDatasource object.

      7.  Set the Dataset property of the TDatasource to              

          Query1.

      8.  Drop down a TDBGrid.

      9.  Set the Datasource property to Datasource1.

The SQL property is of type TStrings. A TStrings object is a list of

strings, similar to an array. The TStrings data type offers commands to

add lines, load from a text file, and exchange data with another

Tstrings object. Another component that uses TStrings is a TMemo. In 

the following listing the user would enter the SQL Statement in the

Memo1 TMemo control and push  the Do It button. The results of the query

are displayed in the grid.  

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]);

      }

      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 don't however.

So your job as the developer is to provide an interface and construct

the SQL statement. In 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 either 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 Dept_no. 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, 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 of the need to perform the code in Listing 3 (***

the listing 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 there is to it for this type of relation.  There are some

limitations to 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 below in listing 5 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, 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. So in this case, even if the prepare method was called,

there would be no benefit to the application.

Open vs. ExecSQL

In the previous examples, the TQueries were performing a Select

statement. 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);

         }

      }

   }

All the examples provided here only tap the surface of using queries in

your application. They should give you a solid foundation to begin using

TQueries in your applications. This is not the end of using SQL in your

applications though. Typical servers offer other features including

stored procedures and  transactions. In the next two sections, a brief

overview will be covered.

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 similarities 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. For example, 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, then use

the Params property to input values.  

4. TDatabase

The TDatabase component provides functionality that a TQuery or

TStoredProc cannot. 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 have transaction control.

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 is 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 into the EditOldLocation TEdit control, to the

location entered into 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 thing to be covered 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, User Name and others.  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 those

parameters at runtime.

The TDatabase has a property, Params, that stores connection

information. Each row in the Params 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("SERVERNAME=                             

                d:\\ebony\\IntrBase\\EXAMPLES\\EMPLOYEE.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 having an alias

created. 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, rather you just need to specify those that

are not set with the driver in the BDE Configuration for that database

driver. 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 driver (in BDE Config). The example above also introduces a new

concept of session and the GetTableNames method. This is beyond the

scope

of this paper but suffice it to say, the session variable

is a handle to the database engine. It is only added to enhance the

example.

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

5. 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. 



Reference:


7/2/98 10:41:07 AM

Top of Page Link
 

Borland Home
© 1999 Inprise Corp.
Shop | Search | Contact | Products | Services & Support | Downloads | Programs | Worldwide | Newsgroups | Community
Last Modified Monday, 15-Nov-99 15:16:22 PST.
 

1