Icon Executing Queries

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;
}

Information The above example does not assign a value to the SessionName property of either the TEDBDatabase or TEDBQuery component because leaving this property blank for both components means that they will use the default session that is automatically created by ElevateDB when the engine is initialized. This session is, by default, a local, not remote, session named "Default" or "". Please see the Connecting Sessions topic for more information.

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)+")";

Information If re-using the same TEDBQuery component for multiple query executions, please be sure to call the SQL property's Clear method to clear the SQL from the previous query before calling the Add method to add more SQL statement lines.

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:

MethodUsage
ExecSQLUse this method when the SQL statement specified in the SQL property may or may not return a result set. The ExecSQL method can handle both situations.
OpenUse this method only when you know that the SQL statement specified in the SQL property will return a result set. Using the Open method with an SQL statement that does not return a result set will result in an EDatabaseError exception being raised with an error message "Error creating table handle".

Information The SQL SELECT statement is the only statement that returns a result set. All other types of SQL statements do not.

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:

PropertyDescription
SQLStatementTypeIndicates the type of SQL statement currently ready for execution.

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

PropertyDescription
PlanContains information about how the current query was executed, including any optimizations performed by ElevateDB. This information is very useful in determining how to optimize a query further or to simply figure out what ElevateDB is doing behind the scenes. The Plan property is automatically cleared before each execution of an SQL statement.

Information Query plans are only generated for SQL SELECT, INSERT, UPDATE, or DELETE statements.
RowsAffectedIndicates the number of rows affected by the current query.
ExecutionTimeIndicates the amount of execution time in seconds consumed by the current query.
ExecutionResultIndicates the Boolean result of the current SQL execution.
SensitiveIndicates the whether the result set cursor for the query is sensitive or insensitive. Please see the Result Set Cursor Sensitivity topic for more information.

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