Icon Executing Scripts

Executing scripts is accomplished through the ExecScript and Open methods of the TEDBScript component, or by setting the Active property to True. Before executing a script you must first specify the source database for the script. The source database is specified via the DatabaseName property of the TEDBScript component. The actual script is specified in the SQL property.

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 TEDBScript 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;
   MyScript->DatabaseName="AccountingDB";
   MyScript->SQL->LoadFromFile("c:\\scripts\\GetLedgerEntries.sql");
   MyScript->Active=true;
}

Information The above example does not assign a value to the SessionName property of either the TEDBDatabase or TEDBScript 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;           
   MyScript->SessionName="Remote";
   MyScript->DatabaseName="Accounting";
   MyScript->SQL->Clear();
   MyScript->SQL->Add("SCRIPT ()");
   MyScript->SQL->Add("BEGIN");
   MyScript->SQL->Add("   EXECUTE IMMEDIATE 'BACKUP DATABASE Test ");
   MyScript->SQL->Add("       AS TestBackup TO STORE \"Backups\" ");
   MyScript->SQL->Add("       INCLUDE CATALOG';");
   MyScript->SQL->Add("END");
   MyScript->ExecScript();
}

Setting the SQL Property
The script is specified via the SQL property of the TEDBScript component. You can use the ConvertSQL method to convert a script that consists of a series of SQL statements (INSERT, UPDATE, DELETE, or SELECT) separated by semicolons (;) into a proper ElevateDB script that can be executed by the TEDBScript component.

Preparing the script
By default ElevateDB will automatically prepare a script before it is executed. However, you may also manually prepare a script using the TEDBScript Prepare method. Once a script has been prepared, the Prepared property will be True. Preparing a script compiles the script, opens all referenced tables, and prepares all internal structures for the execution of the script. You should only need to manually prepare a script when executing a script that requires parameters.

Executing the Script
To execute the script you should call the TEDBScript ExecScript 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 ExecScript and Open methods is as follows:

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

The following example shows how to use the ExecScript method to execute a script:

{
   MyDatabase->DatabaseName="AccountingDB";
   MyDatabase->Database="Accounting";
   MyDatabase->Connected=true;
   MyScript->DatabaseName="AccountingDB";
   MyScript->SQL->LoadFromFile("UpdateLedgerEntries.SQL");
   MyScript->Prepare();
   MyScript->ParamByName("AccountNo").AsString="00100";
   MyScript->ExecScript();
}

Tracking the Progress of a Script
To take care of tracking the progress of the script execution, we have provided the TEDBScript OnProgress event. This event will only be fired if the script contains manual progress update calls specifically included by the script creator.
Image