Accessing Databases (Server side)

The Server.

This demo will show how really simple is to create a server that can send the content of tables in a database to a client.

Steps.

  1. Create a new project in Delphi 7
  2. Create a DataModule to connect to Database and send responses back to the client
  3. Create a Form to control the Server.
  4. Compile and run the Server.

1. Create a DataModule to connect to the Database and send responses back to the client.

We create a new DataModule to hold all the data interaction in the Server.

The database as we said before is a SQLite DB, to connect to this database we are using the ZeosDB  components, they’re free and simple to use. We use:

  • A TZConnection (cnData) and
  • A TZQuery (qyProducts)

Now, to send data to the requesting client, we are using:

  • An RtcDataServerLink component  to link with the RtcHTTPServer component in the main form (Step 2)
  • An RtcServerModule to control the response
  • An RtcFunctionGroup to hold our function and of course,
  • An RtcFunction to send response back to the client.

If you don’t know what Remote Functions are for, you can check the RTC Functions Demo Article here, it gives a good starting point to understand how Remote Functions work.

We create the DataModule and add the needed controls:

Data Module

Data Module for Database Access

The controls are configured this way:

  • cnData (TZConnection):
    • Set ClientCodepage: UTF-8
  • qyProducts (TZQuery):
    • Set Connection: cnData
    • Set SQL: SELECT * FROM tbl_products
  • rtcDataServerLink (TRtcDataServerLink):
    • We are defining the Server to link at runtime (Step 2) so we don’t define anything in here.
  • rtcSvrModule (TRtcServerModule):
    • Set ModuleFileName: /QUERY
  • rtcFunctionGroup (TRtcFunctionGroup): We don’t need to define anything in here.
  • rtcFunction (TRtcFunction):
    • Set FunctionName: GetProductsData
    • Set Group: rtcFunctionGroup

For the DataModule’s properties:

  • Set Name property to dmData

The code for this unit has a function to connect to the Database:


function TdmData.ConnectDB(psDBFile: string): boolean;
begin
  //Connect to the Database
  Result := False;

  if cnData.Connected = True then
    cnData.Connected := False;

  //Check that we have the library in the same folder as the server
  if FileExists(ExtractFilePath(ParamStr(0)) + 'sqlite3.dll') then
    cnData.LibraryLocation := ExtractFilePath(ParamStr(0)) + 'sqlite3.dll'
  else
    Result := False;

  //Check that the Database File requested exists
  if FileExists(psDBFile) then
    try
      cnData.Database := psDBFile;
      cnData.Connected := True;
      Result := True;
    except
    on E:EDatabaseError do
      begin
        // Something went wrong.
        Result := False;
      end;
    end;
end;

We are checking that:

  1. The Library (sqlite3.dll file included in the zip file for the source code of this demo) exists in the same path as the one where the server application is being executed. To be able to connect to the SQLite Database, all we need besides the components in the application is the dll that includes all the functionality and a good practice to avoid problems with different versions is to ship the one that you used  in the development along with your application’s exe file.
  2. The file passed in the psDBFile parameter does exists. Once we’ve validated that it exists we make the connection and send back True as result to the calling method.

Also, we need to define what happens when our rtcFunction is executed:


procedure TdmData.rtcFunctionExecute(Sender: TRtcConnection;
Param: TRtcFunctionInfo; Result: TRtcValue);
var
rtcDS: TRtcDataSet;
begin
  //Create a TRtcDataSet object to store the data from our dataset
  rtcDS := TRtcDataSet.Create;
  try
    //Activate the dataset
    qyProducts.Active := True;
    //Store the result in the rtcDS object we already created
    DelphiDataSetToRtc(qyProducts, rtcDS);
    //Send back the result
    Result.asObject := rtcDS;
  except on E:EDatabaseError do
    //If something went wrong we free the created TRtcDataSet object
    rtcDS.Free;
  end;
end;

We are using an object of TRtcDataSet type to store the content of the requested table, then we try to activate the qyProducts (TZQuery) dataset to get the data from the table in the SQLite database. After this, we use a method included in the rtcDB unit, DelphiDataSetToRtc will allow use to convert our plain dataset into an rtcDataSet to send it back to the client. Once we have converted our dataset into an TRtcDataset object, we send it back to the client. This is a basic demo of this functionality, although in a more elaborated scenario you’ll have to be careful with the memory consumption with lots of records.

This is all that has to be done in the DataModule, now we are going to create the Form to control the Server State.

2. Create a form to control the Server

Create a New Project and add some controls:

New Project

New Project with controls

The Server is really simple. In this main form we are using:

  1. A TLabel (lbPort) and a TEdit (edPort) to configure the port where the Server will be listening.
    • Set lbPort.caption = Port
    • Set edPort.Text = 88 (You may use any port you like, if you use port 80 (default for HTTP servers) make sure that there’s no other service running on that port or you’ll get an error)
  2. A TButton (bnServerListen) to start and stop the server listening for requests.
    • Set bnServerListen.Caption = Start Listening
  3. A TRtcHttpServer (rtcHTTPSvr) component to receive requests from client.
  4. A TLabel (lbConnectionStatus) to show the Server status.
    • Set lbConnectionStatus.Caption = Not Connected

As for the Form’s properties:

  • Set form’s Name property to fmMainServer
  • Set fmMainServer.Caption = DB Server

Now, the coding part.

First, we define our form’s OnCreate event:


procedure TfmServerMain.FormCreate(Sender: TObject);
begin
  if dmData.ConnectDB(ExtractFilePath(ParamStr(0)) +
  'Data\Products.db') = False then
  begin
    Application.MessageBox('The Server hasn''t been able to connect to Database.'
    + #13#10#13#10 +
    'Check that sqlite3.dll exists and that the Data sub-dir exists and that the File Products.db is in that sub-dir.',
    'Cannot Connect', MB_OK + MB_ICONSTOP + MB_TOPMOST);
    Application.Terminate;
  end
  else
    lbConnectionStatus.Caption := 'Server is connected to Database';
  end;

We are calling the ConnectDB function in the dmData DataModule. If the connection fails, then something is missing so we close the application. Once we are connected, the Caption of the lbConnectionStatus control is changed to reflect the server status for the Database connection. This connection will stay open no matter if the Server is Listening or not until the Server application is closed.

Next, we link the Server to the ServerLink in the DataModule at the FormShow event.


procedure TfmServerMain.FormShow(Sender: TObject);
begin
  dmData.rtcDataSvrLink.Server := rtcHTTPSvr;
end;

With this we have our DataModule connected with our TRtcHttpServer component ready to comunicate and send responses to clients.

Next, we define a function to start the listening process:


procedure TfmServerMain.ServerStartsListening(Sender: TRtcConnection);
begin
  try
    // We assign the Port where the server will start listening
    rtcHttpSrv.Port := edPort.Text;
    rtcHTTPSrv.Listen();
    bnServerListen.Caption := 'Stop Server';
  except
    on E:Exception do
    begin
      ShowMessage('Something went wrong with the Start Process');
      Application.Terminate;
    end;
  end;
end;

We simply assign the port in the edPort control to the Port property of the Server and start to listen for new requests. Remember that by default, the rtcHttpServer will listen on all the local interfaces so there’s no need to specify the address of the interface we are listening unless you have serveral interfaces and just want to listen on one of them.

Now, we define the procedure to stop listening:

procedure TfmServerMain.ServerStopsListening(Sender: TRtcConnection);
begin
  try
    rtcHttpSvr.StopListen;
    bnServerListen.Caption := 'Start Listening';
  except
    on E:Exception do
    begin
      ShowMessage('Something went wrong trying to stop the Server.');
      Application.Terminate;
    end;
  end;
end;

Now,we define the OnClick event for the bnServerListen control


procedure TfmServerMain.bnServerListenClick(Sender: TObject);
begin
  if rtcHTTPSvr.isListening = True then
    ServerStopsListening(rtcHTTPSvr)
  else
    ServerStartsListening(rtcHTTPSvr);
end;

We are just checking the Server status and, based on that info we Start or Stop the Server.

Last, when the form gets closed we check that the connection also gets closed:


procedure TfmServerMain.FormClose(Sender: TObject;
var Action: TCloseAction);
begin
  if rtcHTTPSvr.isListening then
    rtcHTTPSvr.StopListen;

  if dmData.cnData.Connected = True then
    dmData.cnData.Connected := False;

  Action := caFree;
end;

4. Compile and run the Server:

Server Running

Server Running

This is the server, now we are ready to send our table’s data to the client once the Server starts listening. Next, we will build the client, as simple as the Server.

Files included in this post:

  1. PDF of this post.
  2. Source Code for the Server.

One thought on “Accessing Databases (Server side)

  1. Pingback: RealThinClient SDK – Accessing Databases (Main Article) | RealThinClassroom

Comments are closed.