Need Advise on Web Database/Client Application


I'm looking for some direction from you who would have some better viewpoint and experience as to the best way to accomplish my project.

What I would like to accomplish is to have a client/field office update/add/edit database records located on a server, ala, via the internet.  I was thinking that setting a secure vpn would be more difficult in the end, but perhaps not.  A simple app which as far as it know is accessing a table on a mapped drive.  But then I thought I would investigate the other alternative (web app/service) before getting too committed in one direction.  Any comments would be very welcome.  Thanks

Who is Participating?
Wim ten BrinkConnect With a Mentor Self-employed developerCommented:
The advantage of client/server development is that you can have more users working on the same database with less problems. If a database is accessed from 10 different computers and every computer is modifying data, then every computer will also have to keep an eye on modifications made by all other systems that are active at that moment. On a single desktop you don't have this problem since it's only one user connecting to the system and the system doesn't need to communicate with other computers to see who is doing what.

With a client/server system, you're using the same approach as a single desktop system. The server is the only one communicating with the database so it doesn't have to keep an eye on other systems trying to access the same data. This improves the data integrity of the system. The drawback is of course that you have to build server components. You need something on the server that keeps track of all connections.
A few real databases (SQL Server, Oracle, InterBase) have already enough locig build in to allow multiple PC's connecting to the database. Basically, these databases already have some client/server support built-in. But you can still improve their functionality by using client/server techniques because these techniques often tend to allow even more users accessing the database than normal desktop solutions. Even better, the C/S solution don't need the database client software to be installed on the client systems. Basically, you would get a 3-tier solution. The client system with your client application. The middle-tier with your server application and the database client software and the database system with the database (server) software.

Current developments have even made it possible to make things even more complicated. You could add even more tiers, like this:
Client application/Web client - Web server - Server application/database client - database server.
In this situation the client is extracting data through a webpage from the server. SOAP/XML is the most popular solution for this. The web server would be something you create to connect to your whenever a web request is made. Your server then asks the database to execute whatever command you gave and then returns the result, which the webserver converts to XML and the webclient will convert it back to data again. With a single user, this is a slow solution. But this setup can easily handle hundreds of users at the same time.

What you are thinking about is called SOAP. Delphi supports SOAP and XML but it's implementation is a bit flawed. Especially date/time fields and booleans might cause problems, especially if combined with other development languages. An upgrade to Delphi 8 might be useful, so you can create a Delphi/ASP.NET webservice. In Delphi 8 you won't have problems with SOAP anymore, since .NET has a better implementation for SOAP.

But in the simplest form, to send over data over the Internet would be by creating a simple Delphi ISAPI module that will return results as plain comma-separated textfiles. This would be the easiest way. A bit more complex would be by converting it to XML by using the MSXML type library (which doesn't have the Delphi flaws) and then send over the XML from server to client. But in that case you're already half-way to a real SOAP solution.

The best thing to do is to experiment for about two weeks with the different techniques before deciding what would be the best solution. Maybe you'd like COM+ even more, for which you can create SOAP wrappers on W2K+3 and XP. Then all you need to do is create COM+ server components and a SOAP-client.
ceoworksConnect With a Mentor Commented:
Hi Tony,

I would suggest you to use PHP+MySQL+Delphi combination. I already have an application which is using the same technique. Data packets are transferring with plain XML files via HTTP.

  <thing name = "xxx 1">
    <thing name = "xxx 2">

This is a good way if there won't be a lots of heavy load data transfers. When you need to reveive some records from the webapp(php side), you just need to send a request and get what it turns by xml. And when you need to upload some data to the remote db, you can use form POST method. Then in the PHP side, you'll receive the fields like :

      $prop1 = $_POST['thing_prop1'];
      $prop1 = $_POST['thing_prop1'];
      $prop1 = $_POST['thing_prop1'];

Of course this is not an automated way to develop that kinds of applications. I mean this is not SOAP or something else so you'll need to do something by yourself. You need to make some design and all these things will not be a real RAD way. But i'm using this way and it works pretty fine. POST and parse the returning results.. That's the only thing you need to design.

Oktay Sancak
Wim ten BrinkSelf-employed developerCommented:
@ceoworks, PHP, ASP, Perl, ColdFusion... There are many web solutions available for developers. But as nice as many of these solutions are, most of them will include a lot of studying. However, I would discourage the use of MySQL and think it's better to use a more professional database if it's affordable. I'm not saying MySQL is a bad solution, just that it doesn't perform as well as the bigger databases. InterBase would be a good alternative since it comes with Delphi and there's a free, related database to it called Firebird that's completely open-source.
PHP is a good option though, although it's a bit better when used on a Unix system than on Windows. On unix, the combination PHP+MySQL has a very good performance but on Windows there are quite a few alternatives. Actually, on Windows you actually have too many choices, not being able to see a tree because of the forest...
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Comment Only

I note that Tony mentions VPN between Client and Field Office.

Workshop Alex goes into Client Server.  

An easy way to go is Client Server over a VPN.  This gets around the clunkiness of ASP or Perl (or whatever) which at the end of the day are basically scripted languages for which you need a host out on the Internet (you could of course run this over your VPN - but why go to the trouble?.  With VPN all you need is any old clunker of an NT Workstation running MYSQL or MSDN (which is free if you have Office Pro) and a couple of VPN enabled Routers with built in firewall at around $150 a piece.

The VPN setup comes with the routers or you could run any Windows 98 or later with modems as you can install Microsoft VPN (I havent done it myself with just modems).

Once you have VPN Setup you just open the Server by using the IP address.  It should work really good.  I have run SQLServer over ISDN 64kDialups and it works fine even with more than one client on the same dialup!!

You will of course need ADSL at both ends.  You could even consider good old fashioned Dialup Networking over 56k dialup - this also works in a reasonable way.


"InterBase would be a good alternative since it comes with Delphi and there's a free, related database to it called Firebird that's completely open-source."

I agree but if your webserver application will not run on your own webserver, this would be a problem. Because if you don't have a dedicated webserver, hosting companies wouldn't let you to install the FireBird :) So in these situations, PHP+MySQL+Delphi may be a good choice.

You could of course host your own webserver on the end of an adsl line with fixed IP address.

In which case you could run whatever client server database you want.

You could also run NT4 workstation with IIS (microsoft Information Server) which is a free download (its in the option pack).  IIS tightly integrates with MSAccess and serves pages up (no need to drag recordsets onto the client) - it supports apparently 250 seats in this configuration.

Remember that if you want to run SQLServer it costs around $10,000 on a Webserver - wow!!

In a commercial application MySQL does have a cost of $200 or something.

I dont know about Firebird which is Interbase - Borland couldnt sell it so they gave it away and then changed there minds or something - thats how good it is.....

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.