Introduction

---

Executing SQL queries is accomplished through the ExecSQL and Open methods of the TDBISAMQuery component, or by setting the Active property to True. Before executing a query you must first specify the location of the table(s) referenced in the query. The location of the table(s) is specified in the DatabaseName property of the TDBISAMQuery component. The actual SQL for the query is specified in the SQL property. Please see the Overview topic in the SQL Reference for more information. You may select whether you want a live or canned query via the RequestLive property. Please see the Live Queries and Canned Queries 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 TDBISAMQuery component to the DatabaseName property of an existing TDBISAMDatabase component within the application. In this case the database location will come from either the Directory property or the RemoteDatabase property depending upon whether the TDBISAMDatabase has its SessionName property set to a local or remote session. Please see the Starting Sessions and Opening Databases topics for more information. The following example shows how to use the DatabaseName property to point to an existing TDBISAMDatabase component for the database location:

begin
   with MyDatabase do
      begin
      DatabaseName:='AccountingDB';
      Directory:='c:\acctdata';
      Connected:=True;
      end;
   with MyQuery do
      begin
      DatabaseName:='AccountingDB';
      SQL.Clear;
      SQL.Add('SELECT * FROM ledger');
      Active:=True;
      end;
end;

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

2) The second method is to enter the name of a local directory, if the TDBISAMQuery component's SessionName property is set to a local session, or remote database, if the TDBISAMQuery component's SessionName property is set to a remote session, 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 location without referring to a TDBISAMDatabase component:

begin
   with MySession do
      begin
      SessionName:='Remote';
      SessionType:=stRemote;
      RemoteAddress:='192.168.0.2';
      Active:=True;           
      end;
   with MyQuery do
      begin
      SessionName:='Remote';
      DatabaseName:='AccountingDB';
      SQL.Clear;
      SQL.Add('SELECT * FROM ledger');
      Active:=True;
      end;
end;

Setting the SQL Property

---

The SQL statement or statements are specified via the SQL property of the TDBISAMQuery component. The SQL property is a TStrings object. You may enter one SQL statement or multiple SQL statements by using the Add method of the SQL property to specify the SQL statements line-by-line. You can also assign the entire SQL to the Text property of the SQL property. If specifying multiple SQL statements, be sure to separate each SQL statement with a semicolon (;). Multiple SQL statements in one execution is referred to as a script. There is no limit to the number of SQL statements that can be specified in the SQL property aside from memory constraints.

Whenever the SQL property is modified, any event handler assigned to the TDBISAMQuery OnSQLChanged property will be executed.

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

This is a
test

The string contains an embedded carriage-return and line feed, 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 memo field, you should use the following code:

MyDBISAMQuery.SQL.Text:='INSERT INTO MyTable '+
   '(MyMemoField) VALUES ('+
   Engine.QuotedSQLStr(MyMemo.Lines.Text)+')';

Preparing the Query

---

By default DBISAM will automatically prepare a query before it is executed. However, you may also manually prepare a query using the TDBISAMQuery 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 TDBISAMQuery 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 SQL statement:

begin
   with MyDatabase do
      begin
      DatabaseName:='AccountingDB';
      Directory:='c:\acctdata';
      Connected:=True;
      end;
   with MyQuery do
      begin
      DatabaseName:='AccountingDB';
      SQL.Clear;
      SQL.Add('UPDATE ledger SET AccountNo=100');
      SQL.Add('WHERE AccountNo=300');
      ExecSQL;
      end;
end;

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:

begin
   with MyDatabase do
      begin
      DatabaseName:='AccountingDB';
      Directory:='c:\acctdata';
      Connected:=True;
      end;
   with MyQuery do
      begin
      DatabaseName:='AccountingDB';
      SQL.Clear;
      SQL.Add('UPDATE ledger SET AccountNo=100');
      SQL.Add('WHERE AccountNo=300');
      ExecSQL;
      ShowMessage(IntToStr(RowsAffected)+
                  ' rows updated in '+
                  FloatToStr(ExecutionTime)+' seconds');
      end;
end;

Trapping for Errors

---

To take care of trapping for errors during the preparation or execution of queries we have provided the OnQueryError event. Whenever an exception is encountered by DBISAM during the preparation or execution of a query, the exception is passed to the event handler assigned to this event. If there is no event handler assigned to this event, DBISAM will go ahead and raise the exception. You may set the Action parameter of this event to aaAbort in your event handler to indicate to DBISAM that you want to abort the preparation or execution of the entire query, not just the current SQL statement being prepared or executed. You may set the Action parameter of this event to aaContinue to indicate to DBISAM that you want to skip the current SQL statement and continue on with the next SQL statement, if present. This is especially useful for scripts because it gives you the ability to continue on with a script even though one or more of the SQL statements in the script may have encountered an error. Finally, you may set the Action parameter of this event to aaRetry to indicate to DBISAM that you want to retry the current SQL statement. This is especially useful in situations where the application encounters a record lock error during an SQL UPDATE or DELETE statement.


1) An OnQueryError event handler is not assigned to the TDBISAMQuery component being used

OR

2) The OnQueryError event handler sets the Action parameter to aaAbort, indicating that the script should immediately terminate.

Tracking the Progress of a Query

---

To take care of tracking the progress of a query execution we have provided the TDBISAMQuery OnQueryrogress event. You may set the Abort parameter of this event to True in your event handler to indicate to DBISAM that you wish to abort the execution of the current SQL statement.


SQL-Specific Events

---

There are certain events that will be triggered when specific SQL statements are executed using the TDBISAMQuery component. These are as follows: