Executing SQL queries is accomplished through the ExecSQL and Open methods of the TEDBQuery component, or by setting the Active property to True. Before executing a query you must first specify the source database for the query. The source database is specified via the DatabaseName property of the TEDBQuery component. The actual SQL for the query is specified in the SQL property. You may select whether you want a sensitive or insensitive query result cursor set via the RequestSensitive property. Please see the Result Set Cursor Sensitivity topic for more information.

Setting the DatabaseName Property

---

You may specify the DatabaseName property using two different methods:

1) The first method is to set the DatabaseName property of the TEDBQuery component to the DatabaseName property of an existing TEDBDatabase component within the application. In this case the actual source database being used will come from the Database property. The following example shows how to use the DatabaseName property to point to an existing TEDBDatabase component for the source database:

{
   MyDatabase->DatabaseName="AccountingDB";
   MyDatabase->Database="Accounting";
   MyDatabase->Connected=true;
   MyQuery->DatabaseName="AccountingDB";
   MyQuery->SQL->Clear();
   MyQuery->SQL->Add("SELECT * FROM ledger");
   MyQuery->Active=true;
}

Another useful feature is using the BeforeConnect event of the TEDBDatabase component to dynamically set the Database property before the TEDBDatabase component attempts to connect to the database. This is especially important when you have the Connected property for the TEDBDatabase component set to True at design-time during application development and wish to change the Database property before the connection is attempted when the application is run.

2) The second method is to enter the name of an existing database directly into the DatabaseName property. In this case a temporary database component will be automatically created, if needed, for the database specified and automatically destroyed when no longer needed. The following example shows how to use the DatabaseName property to point directly to the desired database without referring to a TEDBDatabase component:

{
   MySession->SessionName="Remote";
   MySession->SessionType=stRemote;
   MySession->RemoteAddress="192.168.0.2";
   MySession->Active=true;           
   MyQuery->SessionName="Remote";
   MyQuery->DatabaseName="Accounting";
   MyQuery->SQL->Clear();
   MyQuery->SQL->Add("SELECT * FROM ledger");
   MyQuery->Active=true;
}

Setting the SQL Property

---

The SQL statement is specified via the SQL property of the TEDBQuery component. The SQL property is a TEDBStrings object. You may enter an SQL statement by using the Add method of the SQL property to specify the SQL statement line-by-line. You can also assign the entire SQL to the Text property of the SQL property.

When dynamically building SQL statements that contain literal string constants, you can use the TEDBEngine QuotedSQLStr method to properly format and escape any embedded single quotes in the string. For example, suppose you have a TEdit component that contains the following string:

Pete's Garage

The string contains an embedded single quote, so it cannot be specified directly without causing an error in the SQL statement.

To build an SQL INSERT statement that inserts the above string into a VARCHAR column, you should use the following code:

MyEDBQuery->SQL->Text="INSERT INTO MyTable "+
   "(MyVarCharColumn) VALUES ("+
   Engine()->QuotedSQLStr(MyEdit.Text)+")";

Preparing the Query

---

By default ElevateDB will automatically prepare a query before it is executed. However, you may also manually prepare a query using the TEDBQuery Prepare method. Once a query has been prepared, the Prepared property will be True. Preparing a query parses the SQL, opens all referenced tables, and prepares all internal structures for the execution of the query. You should only need to manually prepare a query when executing a parameterized query. Please see the Parameterized Queries topic for more information.

Executing the Query

---

To execute the query you should call the TEDBQuery ExecSQL or Open methods, or you should set the Active property to True. Setting the Active property to True is the same as calling the Open method. The difference between using the ExecSQL and Open methods is as follows:



The following example shows how to use the ExecSQL method to execute an UPDATE statement:

{
   MyDatabase->DatabaseName="AccountingDB";
   MyDatabase->Database="Accounting";
   MyDatabase->Connected=true;
   MyQuery->DatabaseName="AccountingDB";
   MyQuery->SQL->Clear();
   MyQuery->SQL->Add("UPDATE ledger SET AccountNo=100");
   MyQuery->SQL->Add("WHERE AccountNo=300");
   MyQuery->ExecSQL();
}

Query Execution Plans

---

If you wish to retrieve a query execution plan for the current execution via the Plan property, then set the RequestPlan property to True before executing the query.

Sensitive Result Set Cursors

---

If you wish to have a sensitive result set generated from the executed query, then set the RequestSensitive property to True before executing the query. This only requests a sensitive result set cursor, and the query may still generate an insensitive result set cursor based upon the query being executed. Please see the Result Set Cursor Sensitivity topic for more information.

Retrieving Query Information

---

You can retrieve information about a query both after the query has been prepared and after the query has been executed. The following properties can be interrogated after a query has been prepared or executed:


The following properties can only be interrogated after a query has been executed:


The following example shows how to use the ExecSQL method to execute an UPDATE SQL statement and report the number of rows affected as well as how long it took to execute the statement:

{
   MyDatabase->DatabaseName="AccountingDB";
   MyDatabase->Database="Accounting";
   MyDatabase->Connected=true;
   MyQuery->DatabaseName="AccountingDB";
   MyQuery->SQL->Clear();
   MyQuery->SQL->Add("UPDATE ledger SET AccountNo=100");
   MyQuery->SQL->Add("WHERE AccountNo=300");
   MyQuery->ExecSQL();
   ShowMessage(IntToStr(MyQuery->RowsAffected)+
              " rows updated in "+
              FloatToStr(MyQuery->ExecutionTime)+" seconds");
}

Tracking the Progress of a Query

---

To take care of tracking the progress of the query execution, we have provided the TEDBQuery OnProgress event. You may set the Continue parameter of this event to False in your event handler to indicate to ElevateDB that you wish to abort the execution of the current SQL statement.