The Windows desktop software I write is increasingly web-focused. Most applications now have real-time web-based licensing, many send email messages and an increasing number retrieve information from web server databases. In the past I’ve worked with SOAP, various flavours of RPC and with RESTful APIs that return XML. Private custom interfaces don’t require the complexity of these protocols or justify the overhead, but they still cry out for a framework to simplify the interactions.

Over the last three years I’ve developed an approach that works well for my Delphi Windows applications and PHP\MySQL on the web. If you write your desktop or web code in different languages you may still find this useful reading. I’ll deal with web licensing separately – I have a complete package for that – and restrict this discussion to the two other web\desktop interactions I’m most often called for.

Email messages

The desktop / web interaction started for me with email. Clients wanted a way for software users to be able to send them an email message as simply as possible. Back in the pleistocene era I’d simply invoke the computer’s default email client, but increasingly users aren’t using an email application: they use webmail. Having the application connect to an SMTP server can be problematic in this era of heightened spam protection. The solution was to let the user type a message within the application and send the output to a PHP page on the web where it’s shunted on its way by sendmail. A useful bonus of this approach is that the client’s email address doesn’t have to be exposed. Here’s a very simple PHP script.

function getParam($name) {

    global $missingparam;

    if (isset($_GET[$name])) 

      return stripslashes($_GET[$name]);

    else {

      $missingparam = true;

      return '';

    }

  }



  if (!isset($_GET['ref']) || $_GET['ref'] != 'B941A497-1D5E-4435-8D1C-12AFFE8B1DDC') {

    echo 'false: incorrect data';

    return;

  }

 

  $missingparam = false;

  $recipient = 'office@client.com';

  $subject = getParam('su');

  $body = getParam('bo');

  $sender = getParam('fr')."\r\nX-Mailer: PHP/".phpversion();  



  if ($missingparam == true) {

    echo 'false: missing parameter';

    return;

  }



  echo (mail($emailrecipient, $subject, $body, $from))

    ? 'true'

    : 'false: failed to send';

The client only has to put this script into a directory on their own web site. I could host it myself, but it’s better they’re not dependent on my server.

To send an email using this web page, the Delphi application provides the user with a form where a message can be entered and, optionally, an email address. Most of my desktop applications already know the user’s email address from the licensing process. Then a URL request is encoded and sent and the return value inspected to determine success or failure.

http://www.client.com/utils/domail.php?ref=B941A497-1D5E-4435-8D1C-12AFFE8B1DDC&su=Great%20software!&bo=Thanks.%20I've%20almost%20reverse%20compiled%20it%20now.%20All%20the%20best.&fr=hacker%40warez.com

Note the use of a matching GUID in the desktop application and the server script: it adds a small extra challenge when hacking the interface. You’ll also see that we’re not using any headers or markup in the response from the web script. It’s plain, unadorned ASCII: in this case the desktop software only needs to check whether it receives the word “true” or not.

In a real world implementation we’ll want additional features: what if the web server goes down? We could store the URL string and retry, say, every fifteen minutes in a background thread. We can send additional parameters to the web script too: the recipient might depend on the type of message, in which case the destination email address can be passed from the application or selected by sending an index. It’s flexible because it’s simple.

Data access

Retrieving data from the web isn’t too dissimilar, but here a little more structure is helpful. We can create a generic script that allows us to retrieve single values or multiple rows from the database and use these in our desktop application with minimal effort.

  $refconst = 'C0DC9859-E3BA-48B6-BD47-EA40D653884E';

  if (!isset($_GET['ref']) || $_GET['ref'] != $refconst)

    exit;

      

  // These five lines get replaced with whatever your site uses to serve up a database instance

  require ('../core/db_class.php');

  if (class_exists('DB')) 

    $db = DB::getInstance();

  else 

    die('error: unable to open database');

    

  if (isset($_GET['query']) && isset($_GET['type'])){ 

    $query = stripslashes(urldecode($_GET['query']));

    $type = $_GET['type'];

    if ($type == 'value'){

      $out = $db->queryUniqueValue($query);

      echo $out;

      exit;

    }

    $rowset = $db->query($query);

    if ($db->numRows() == 0) 

      exit;

    while ($row = mysql_fetch_array($rowset)) {

      $out = '';

      foreach($row as $key=>$val) {

        if (!is_int($key))

          $out .= sprintf('"%s=%s",', $key, $val);

      }

      echo substr($out, 0, -1)."\n";      

    }

  }

Again, note the use of a GUID to restrict access and the lack of markup or headers in the response. The script allows two types of queries to be run against the database. With the type parameter set to value, a single value is the only thing returned. The calling application can cast this to whatever data type is expected. Otherwise the result is a sequence of comma-separated name=value pairs, with each row of the result set written to a new line.

A Delphi class that makes use of the above script could define the following public methods.

constructor Create(sBaseURL, sWebGUID: String);

destructor Destroy; override;

function QueryBoolFmt(sSQL: String; oArgs: array of const): Boolean;

function QueryDoubleFmt(sSQL: String; oArgs: array of const): Double;

function QueryIntegerFmt(sSQL: String; oArgs: array of const): Integer;

procedure QueryListFmt(sSQL: String; oArgs: array of const; oList: TStrings);

function QueryRowFmt(sSQL: String; oArgs: array of const): String;

function QueryStringFmt(sSQL: String; oArgs: array of const): String;

Here are some simple examples of how these methods can be used.

constructor TfrmMain.Create(AOwner: TComponent);

begin

  FDB := TWebDB.Create(

    'http://www.myserver.com/etc/dbaccess.php', 

    'C0DC9859-E3BA-48B6-BD47-EA40D653884E'

  );

end;



function TfrmMain.GetPhoneNumberForClientID(iClientID: Integer): String;

begin

  Result := FDB.QueryStringFmt('SELECT phone FROM clients WHERE id = %d', [iClientID]);

end;



procedure TfrmMain.PopulateOrderDetail(iOrderID: Integer);

var

  oList: TStringlist;

begin

  oList := TStringlist.Create;

  try

    oList.CommaText := FDB.QueryRowFmt('SELECT o.orderdate, o.description,' +

      ' WS_CONCAT(" ", c.namefirst, c.namelast) AS client, d.name AS department' +

      ' FROM orders o, clients c, departments d' +

      ' WHERE c.id = o.clientid AND d.id = o.departmentid AND o.id = %d', [iOrderID]);

    lblClientName.Caption := oList.Values['client']);

    lblDescription.Caption := oList.Values['description']);

    lblDepartment.Caption := oList.Values['department']);

    lblOrderDate.Caption := FormatDateTime('d mmm hh:nn', StrToFloatDef(oList.Values['orderdate'], Now));

  finally

    oList.Free;

  end;

end;



procedure TfrmMain.PopulateClientList(iStatus: Integer);

var

  iPos: Integer;

  oList,

  oResultSet: TStringlist;

begin

  ClientListBox.Clear;

  oResultSet := TStringlist.Create;

  oList := TStringlist.Create;

  try

    FDB.QueryListFmt('SELECT id, WS_CONCAT(" ", namefirst, namelast) AS name' +

      ' FROM clients WHERE status = %d', [iStatus], oResultSet);

    for iPos := 0 to oResultSet.Count - 1 do begin

      oList.CommaText := oResultSet[iPos];

      ClientListBox.AddObject(oList.Values['name'], Pointer(StrToIntDef(oList.Values['id'], 0));

    end;

  finally

    oList.Free;

    oResultSet.Free;

  end; 

  ClientListBox.Sorted := True;

end;



destructor TfrmMain.Destroy;

begin

  FDB.Free;

end;

The beauty of this approach is that client SQL can be as complex as anything you can run on the server (because it is running on the server), but the result set can be managed easily with native Delphi data types.

I’ve written a complete solution for managing the licensing of desktop applications via a web interface. There’ll be a follow-up with full details of that in the future.