Delphi ActiveX/Midas Development Hints

Problem/Question/Abstract:

Delphi ActiveX/Midas Development Hints

Answer:

Introduction

This document provides a basis for developing multi-tier database applications that have zero client configuration administration. This architecture was a requirement of the National Department of Agriculture brought on by a shortage of support personnel and the wide spread dispersion of the user-base throughout South Africa. Delphi was chosen as the development platform because it implemented the technologies required, and has a proven track record.

The requirement made was to have a user with limited computer experience download the program automatically and run it, without manually installing anything on his side. Also if a newer version of the program was released, it should automatically update the application on the client. The technology chosen for accomplishing this was to run an ActiveX application within a browser using Microsoft's DCOM technologies to access the data. Also Delphi's Midas technologies have features that make it easy to work with DCOM through a firewall and over the Internet. There is also support for MTS.

The first Server DCOM Application

Preparing the Server: The first thing to do before you can write a DCOM Server Application is to set up the server first, this is the only machine where you need to set up the connection to the database. This can be the Web-Server machine, but doesn't have to be. First set up the ODBC driver (system) to the appropriate Server and Database, then test the connection. Then you must install Delphi's BDE (preferably their latest one), or with Delphi 5, you can chose to use ADO instead, in this case you don't have to install the BDE on the NT machine (ADO drivers come with NT). After installing make sure you have DBCLIENT.DLL and STDVCLxx.DLL in the System32 directory (for Delphi 4 use STDVCL40.DLL), if not, copy them from your Delphi Development machine to the NT Server. Also copy the scktcrvr.exe file over to a directory on the server and put it in the Server startup group (this is part of Midas, and will be explained later).
Writing the Server Application: On your development machine, install the odbc driver exactly the same way you installed it on the Server, with the same name. In Delphi, open a new Project, a blank form will appear, you do not need this form, but it is good practice to put a label on it describing the role of the Server app. Add a new module to your project, chose the Remote Data Module form from the multitier group, give it a name and leave the defaults, this is where you place all your data-access tables that will be provided remotely to the client. Add the Database component from the Data Access tab to the new form, set the following properties:

DatabaseName:        odbc_name_that_you_defined
LoginPrompt:                false
Params:                                USER NAME=username_of_odbc_database_server
                                                        PASSWORD=password_of_username
Connected:                        true

If the connected property does not want to set to true, then there is a connection problem, make sure everything is set up correctly and that your odbc driver on the development machine is working, then try again. After this works, you can drop the Query (or Table) component onto the new form, set the following properties:

CachedUpdates:        false (this is true for editable tables, but our first server will be read-only)
DatabaseName:        odbc_name_that_you_defined
SQL:                                        Select * from your_table_in_the_database
Active:                                false (NB this is compulsory)

You can test your connection by setting the Active property to true, but under no circumstances deploy this application with the Active property set to true, doing so will disable remote refreshing of the table, rather let your client control this property. When you have completed the above, you can right-click on the Query (or Table) component, one of the Items appearing in the pop-up menu is called 'Export Query1 from Data Module', select this. You will notice that after this operation the item does not appear again in the pop-up menu. Now save your project and compile it. Your server application is now finished. To deploy this Server Application to the Server just copy it across to a directory on the server, then on the Server console run it once, this will automatically register it in the registry (make sure the scktsrvr.exe program is also running, if not, run it). Now your DCOM Server is ready to process any requests.

If you need need to replace the Server App with a modified version, do not copy the new one over the old one, first unregister the old one by going to the dos-prompt to the Server directory and typing: Your_Server_App_Name /unregserver. When this executes silently, you can copy the new one over the old one and manually execute it to register it.

The first ActiveX Client Application

Open a New ActiveForm application under the ActiveX tab of the 'New…' menu item and provide a name (leave the rest default), if you had a previous project open it will display a warning message that the ActiveForm cannot be added to the current project and needs to close the project, click to accept this. Add somewhere on the form a SocketConnection component from the 'Midas' tab, set the following properties in the order provided:

Address:                        Physical_IP_address_of_Server (e.g. 155.240.96.100)
ServerName:        Select_your_server_from_list
Connected:                true

If your program should be deployed outside the NT Domain area (i.e. the Internet or WAN) then it is better to use the Address property than the Host property, that is because the Host can only be resolved locally. If you do not see your Server Application in the drop-down list under ServerName, then there is a problem with either the Server Setup (See above), or the IP Address is wrong (Make sure that both the scktsrvr.exe and your server app is running, if so then you might not have exported the Query component from the Data Module via the pop-up menu). If all works fine you can add a ClientDataset component (also from the 'Midas' tab) to the form and set the following properties in the order provided:

RemoteServer:        Select_Your_SocketConnection_from_list
ProviderName:        Select_Your_Query_component_from_list
Active:                                true

If the above works, which should, you now have a local record set of a remote table, all that you must now do is use it. Add a DataSource component (under the 'Data Access' tab) to the form and select your ClientDataSet component in the DataSet property. Add a DBGrid component to the form (under 'Data Controls') and select the DataSource component in the DataSource property. If you have followed all the steps correctly, you should now see data in the Grid, enlarge it to have a larger view. The simple ActiveX application is now finished, save your work and compile it. To test your form in a browser you must deploy it, to do this you must set a few options in the 'Web Deployment Options…' first. We will deploy your app to a directory on your hard drive as this will speed up the deployment and page-open time. Set the Target directory field and the HTML directory field to the same value being the drive and directory you want to store the htm and ocx file. In the Target URL just enter './', this makes it possible to execute the htm file directly from the directory (this would otherwise point to the URL of where the ocx file would be found). Now you can deploy your app with the 'Web Deploy' menu option, if everything was set up correctly, you should have an htm file and an ocx file in the directory you specified. Browse to that directory with your Windows explorer, and double-click on the htm file… your Internet Browser should open, and after a delay, you should see your program running within.

If you want to deploy to a Web server, it is important that you have 'Deploy additional files' clicked in the Web Deployment Options. After this, go to the 'Additional Files' tab and add the dbclient.dll file found in your /winnt/system32 directory. Not always, but sometimes if the application still gives an error when run, add the stdvcl40.dll file also found in the same directory. You should see an INF file created in your deployment directory when you deploy, including the dll files.

If you click CAB file compression in the Deployment Options, try to compress each file added separately (options available in the 'Additional Files' tab. This will ensure that no unnecessary downloads take place when one of the components (ocx or dll) is updated.

Persistent verses Dynamic Fields

In the above example, you used dynamic field allocation, you did not have to tell the DBGrid component what fields are available in the table, it deduced that from itself by examining the field results from the Select * statement. The nice thing with this is that if your table structure changed in the table you used, you would not have to modify the program, the new structure will be available dynamically in the DBGrid. You can even edit using the DBGrid. Even when you use separate edit fields (DBEdit, DBMemo, DBImage, etc), you can get the field names from a dynamic field list. There are two situation where you might consider using persistent fields (field names that you define during design time), the first is if you want to manipulate field values programmatically, the second would be when you want to use field values as parameters in your own SQL statements. To make fields persistent, you just right-click on the Table or Query component and select 'Fields editor…' from the drop-down menu. In the Field Editor you just add the fields you need, a separate field type is created for each field that can be referenced in code. (e.g. a field called name in the Query1 component can be referenced as Query1name.value).

You will notice in the fields editor that you can add new fields (user defined) that you can assign yourself or automatically (e.g. Lookup fields, Calculated fields etc). For lookup fields you just define the lookup field and key values in a foreign table, with a calculated field you use the OnCalculateFields event to add code to calculate the field value for each record.

You will also notice in the persistent field properties in the field editor that each field has a list of it's own properties, one of them is called 'Displayed Name', this property is used to enter a formatted description that appears in the header part of the DBGrid, change this if you want to see a field description instead of the field name on the field headers.

You can separately configure what fields to display in a DBGrid by right-clicking on the DBGrid component and selecting the fields to display in it's own field editor.

Using filters on a Table or Query

In certain cases you may want to shrink the size of the result query for search purposes by using certain search criteria. One way to do this is to modify the SQL in such a way as to return only a smaller sub-set of the query using a where clause. This is however inefficient as the query requires the SQL to be executed on the server, the new dataset returned to the client, and when the client is finished with the dataset and needs to be returned to it's previous state, the old SQL has to be executed and the result returned. This, even with a thin client takes time and wastes bandwidth. Delphi provides a means to filter the current dataset without re-issuing any SQL, each dataset (Query or Table) has a filter and filtered property to enable this. In the filter property you can add a string such as 'surname='Smith'' (you can set this programmatically), the filter won't engage until you set the filtered property to true. Setting the filtered property to false disables the filter again and restores your viewed recordset. The nice thing about this is that all processing gets done on the workstation, and happens instantly, as opposed to re-issuing an SQL command.

Opening a dialog form from an ActiveForm

The one thing you might want your program to have is a load of custom dialogs, however, if you add a standard form to your ActiveForm application, you'll notice that the form opens within the region of your ActiveForm app. You would ideally like to open a form external to your ActiveForm app/browser. You can do this by instantiating the form within the unit of the form instead of instantiating (Showmodal) it from within the unit of the ActiveForm. To do this follow these steps:

Add a new form to the project, make sure the prj file does not instantiate the form, if so, remove the reference.
Remove the variable of the form type.
In the unit of the form add a function called ShowForm, with a return result of TmodalResult. Add var parameters you'd like returned. An example of the implementation code should look like the following:

function ShowForm: TmodalResult;
var
  AXForm: TAXForm; // the variable of the form here
begin
  AXForm := TAXForm.Create(Application);
  ShowForm := AXForm.ShowModal;
  AXForm.Free;
end;

Now add this unit to the uses clause of the ActiveForm. To call the form, just call the ShowForm function of the dialog. (you can even have menus on these forms).

On the dialog box you normally add an OK and CANCEL button to close the form, however, you would normally like to know which of the buttons were pressed, this is where ModalResult comes in. When you add a button to the dialog, you'll notice that among the button's properties is a modalresult property, selecting the button type from the drop-down list changes the function of the button. The ShowModal result returns the value you selected as the modalresult of the button (e.g. mrOK or mrCancel), and closes the form automatically without you having to enter code to close the form. You can then react on the result returned.

Handling database errors

Sometimes when you update a table and the update is unsuccessful, you'd like to know exactly what the error was instead of trying to figure out what went wrong. One of the most common type of errors that occur is when someone modifies a record that you are currently modifying, this is an example of a typical reconcile error. Fortunately Delphi makes it simple to capture the exact error and display it with the standard ReconcileError dialog form. Just add the form to the project, make sure the prj file does not instantiate the form, if so, remove the reference. Add the unit name of the dialog to the uses clause of all the forms that have clientdatasets you want monitored. Double-click the OnReconcileError property of the Table/Query you want monitored and type the following code in the handler: Action := HandleReconcileError(DataSet, UpdateKind, E);
Now when you receive an error, the dialog will pop-up with the appropriate error, and also give a list of the fields involved and their data. The dialog also allows you to take certain actions (e.g. skip, Cancel, etc ).

Updating data

When working with data on a local Query component, you can add an UpdateSQL component to the form and connect it to the UpdateObject property of the Query component. However, if working with a Query component in a DCOM remote data module, this step is not necessary, as the appropriate SQL is automatically generated for delete, insert and modify. If however you do need to use parameters, you can use the Provider.BeforeUpdateRecord event to execute your SQL (The UpdateSQL component is not supported here). Code within this event will look something like:

if UpdateKind = ukDelete then
begin
  Query1.SQL.Text := 'Update CUSTOMER set STATUS="DEL" where ID=:ID';
  Query1.Params[0].Value := SourceDS.FieldByName('ID').Value;
  Query1.ExecuteSQL;
  Applied := true;
  // restore the SQL here
end;

If you have a Join select statement in a Query, the Query component needs to know which one of the tables used in the statement need to be updated, and what fields are involved in the update to that table, otherwise you get an 'Unable to resolve record, Table name not found' error. Using a separate Provider component, do the following:
In the Provider.OnGetDatasetProperties event, add the code:

Properties := VarArrayCreate([0, 0], varVariant);
Properties[0] := VarArrayOf(['TABLE_NAME', table_name_you_want_updated, true]);

Add persistent fields to the remote data module for the join query.
Select the non-involved TFields in the fields-editor and set all of the ProviderFlags elements to false. (i.e. set pfInUpdate and pfInWhere to false).

Now the Query component will be able to correctly build up the update SQL statements.

If you use a separate form to modify data and you need to refresh the root-view so as to reflect any changes made, use the Query.refresh method of the root-view form. Also remember that if you have CachedUpdates set to true, you must apply those updates with the Query.ApplyUpdates(-1) method.