Products

Price/Order

Support

Partners

Testimonials

Test Results

About us

Contact
 Fetching DB Queries using the RTC SDK
Bottom
 
Total posts: 3
 Author Fetching DB Queries using the RTC SDK
Glynn Owen [RTC]

24.02.2007 20:58:04
Registered user
There are two D7 projects related to this article. Both are in a zip file that can be downloaded from here -

Download First DB Demo

One project is an HTTP server and the other is a client for that server. The server accepts SQL queries from the client, and returns the result to the client, which then puts those results on display in its grid. The server assumes that its host system has a file with this name and folder -

C:\Program Files\Common Files\Borland Shared\Data\dbdemos.mdb

This file is part of the D7 installation. If you don't have this file, the server will install it the first time it runs. This discussion is based on the Customer table of this database, but you can access any table of the database if you know its name.

To see the demo, unzip into any folder, and both a Client and Server sub-folder will be created and filled with the appropriate project. You can compile both projects, or you can use the existing .exe that is part of the zip file. I sometimes run two instances of D7, and compile one project from one instance and the other project from the other instance. This makes it possible to debug both Client and Server at the same time. Run the Server first, and click on its "Start Listening" button. After the server is running, run the client. Attempting to run the client before the server is listening will cause an exception on the client end, but that is harmless.

The Client uses "localhost" on port 80 by default. You can change either or both of those properties to accomodate the Server. The Server will only listen on port 80 unless you modify it and recompile it.

The contents of the grid, and the list of field names, that you see when you click the "Get Query Dataset" button, is taken from the result of running the query that you see in the editbox at the top of the client screen. Type other queries into that editbox, and then click the "Get Query Dataset" button of the client to see the results. Running invalid queries results in exceptions that are usually pretty meaningful. In any case, an exception should not be a problem. Fieldnames are on display on the right side of the window. You can even use UPDATE commands to modify records, although the query will complain about no result set being returned.

The Client/Server exchange takes place using a Remote Function with this signature -

function GetRemoteQuery(Qry:String):TrtcDataSet;

This function was written using the Remote Functions Wizard I wrote about a few days ago. That tool is available from this link -

Download Remote Function Wizard

Most of the work done by the client is done in this handler for the "Get Query Dataset" button -

procedure TformMain.btnGetQueryClick(Sender: TObject);
begin
  edtCount.Text := '';
  Src.DataSet := NIL; // Disconnect from CDS
  CDS.IndexFieldNames := '';
TRY // Assure the Src dataset is restored
  RtcDataSetToDelphi(DBFunc.GetRemoteQuery(edtQuery.Text),CDS);
  CDS.First;
  edtCount.Text := intToStr(CDS.RecordCount)+' records returned';
  ShowFieldNames;
FINALLY
  Src.DataSet := CDS;
END
end;

Within this handler, almost all of the work is done by this call -

    RtcDataSetToDelphi(DBFunc.GetRemoteQuery(edtQuery.Text),CDS);

This line calls the remote function named "GetRemoteQuery" using the contents of the query editbox as its parameter. A TrtcDataSet, which has been pulled from the server, is returned by that function. That rtcDataset is then passed to RtcDataSetToDelphi along with the ClientDataSet that is part of the main form. RtcDataSetToDelphi is a procedure in the rtcDBTypes unit which has a TrtcDataset for its first parameter, and a TDataset for its second parameter. It transfers the field structure and contents of the TrtcDataset into the TDataset.

The GetRemoteQuery function is implemented in the DBDemoFunc unit, and was created using the Wizard that was uploaded a few days ago. There is a link above for downloading the Wizard .

As neat as this is, it does have some shortcomings. Mostly because RtcDatasetToDelphi recognizes only a limited number of the datatypes that can appear in a field. In particular, it does not support blob fields. That issue will be the basis of next week's RTC DB Demo article.

The client consists of three panes. The top pane shows the current query and the number of records it returned. The bottom-left pane is a DBGrid that shows the results of that query. The bottom-right pane is a list of the fields included in the query. You can double-click any fieldname in that list, and it will be used as the index for the grid.

The server consists of a single button that is used to start and stop the server listening. Behind the scenes, the server consists of a main form, a server datamodule named SrvrDM, and a DB connection module named ADO_DM. The use of a separate connection module makes this demo easily adaptable to most databases, particularly in view of the fact that all access to the DB is through an interface. To use a different database would require that a different datamodule be written which implemented the methods of that interface.

Almost all of the work on the server side is done by this bit of code -

    With Conn do begin
      SetQuery(Param.asString['Qry']);
      DelphiDataSetToRtc(GetDataset,RtcDS);
      End;

First, the conn.SetQuery method is called to set the current query to the value passed in the Qry parameter of the remote function call. Second, the dataset resulting from that query is stored in a TrtcDataset component by a call to DelphiDatasetToRtc, which is another procedure in the RtcDBTypes unit.

Once you have this demo running, use the field list on the right side to type in different queries. Put in some nonsense queries and notice how fault-tolerant the RTC SDK is about returning a sensible error message.

This demonstration shows one way of returning the results of a query from a remote system by using the RTC SDK. It is intended as an illustration, and is by no means a comprehensive solution to the problem. If you have any questions about or problems with this demo, please bring them forward on this forum.

Regards,
Glynn
Glynn Owen [RTC]

25.02.2007 21:43:48
Registered user
I made some minor changes to the code for this article, and changed the .zip file so that it includes the dbdemo.mdb file that is required by the demo, as well as working .exe files for both the Client and Server so anyone can see how it works even if they can't compile it. The full source code of the example is also included, and requires only the RTC SDK to compile.

One user has already created an interface module for NexusDB. Way to go, Malcolm!

Regards,
Glynn
Glynn Owen [RTC]

26.02.2007 19:51:42
Registered user
I surfaced the RemoteAddr and RemotePort properties of the Client. As it was, those properties were set to localhost and 80 and could not be changed without re-compiling the Client.

Glynn