Author: Larry Nezar
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:
1 function ShowForm: TmodalResult;
2 var
3 AXForm: TAXForm; // the variable of the form here
4 begin
5 AXForm := TAXForm.Create(Application);
6 ShowForm := AXForm.ShowModal;
7 AXForm.Free;
8 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:
9 if UpdateKind = ukDelete then
10 begin
11 Query1.SQL.Text := 'Update CUSTOMER set STATUS="DEL" where ID=:ID';
12 Query1.Params[0].Value := SourceDS.FieldByName('ID').Value;
13 Query1.ExecuteSQL;
14 Applied := true;
15 // restore the SQL here
16 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:
17 Properties := VarArrayCreate([0, 0], varVariant);
18 Properties[0] := VarArrayOf(['TABLE_NAME', table_name_you_want_updated,
19 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.
|
