4

I am looking for some starting points integrating a Win32 Delphi application's data with a remote database for a web application.

Problem(s) this project intends to solve:

1) The desktop does not perform well over vpns. Users in remote office could use the web app instead.

2) Some companies prefer a web app to the desktop app

3) Mobile devices could hit the web app as a front end.

Issues I've identified:

Web application will run on a Unix based system, probably Linux while the desktop application uses NexusDB while the web application will likely be Postgres. Dissimilar platforms and databases.

Using Delphi it appears the Microsoft Sync Framework is not available for this project.

My first thought was to give the web app your standard REST API and have the desktop app hit the API as though it's a client every n-minutes from the local database server. Tons of issues I see with this already!

Richard Holland
  • 2,663
  • 2
  • 21
  • 35
  • Did you resolve the issue. I'm currently stugguling with the issue of connecting to a DBISAM database from linux? – JorgeO Feb 13 '12 at 22:06

4 Answers4

6

Richard, I have been down this path before and all I can say is DON'T DO IT! I use to work for a company that had a large Delphi Desktop Application (over 250 forms) running on DBISAM (very similar to what you have). Clients wanted a "Web" interface so people could remotely work and then have the web app and desktop app synch changes. Well, a few years later and the application was horrible - data issues and user workflow was terrible because managing the same data in two different places is a nightmare.

I would recommend moving your database to something like MySQL (Delphi and Web Client both hit) and use one database between the two interfaces. The reason the Delphi client is not working well over the VPN is because desktop databases like NexusDB and DBISAM copy way to much data over the pipe when it runs queries (pulls back all the data and then filters/orders, etc)- it not truly client / server like SQL Server or MySQL where all the heavy lifting is being done on the server and only the results come back. Of course, moving the Delphi app to DB like MySQL could eleviate speed issues all together - but you don't solve #2 and #3 with that.

Another option is to move the entire application to the web and only have 1 application to support. Of course, a good UI developer in a tool like Delphi can always make a superior user interface to a web app - especially in data-entry heavy applications - so that may not be an option for you.

I would be very weary of "synching data".

My 2 cents worth. Mike

MDV2000
  • 812
  • 2
  • 9
  • 14
  • 1
    The more I think about it the more correct this seems. Thanks. – Richard Holland Apr 26 '11 at 16:28
  • 1
    Exactly. All replication systems end up with conflicts that have to be manually sorted out. What a pain. – Warren P Apr 26 '11 at 16:42
  • 1
    I would just like to say that for the delphi desktop app, you should consider 3-tier development. Even SQL Servers push a lot of info over the wire making desktop apps slower over narrowband connections. I have used datasnap successfully in the past and I moved on to RemObjects SDK + DataAbstract. The latter has options for data compression. (Disclaimer: just a satisfied customer) – Stuart Apr 27 '11 at 09:52
5

If you use a RESTful based ORM, you could have both for instance AJAX and Client Delphi applications calling the same Delphi server, using JSON as transmission format, HTTP/1.1 as remote connection layer, Delphi and Javascript objects to access the data.

For instance, if you type http://localhost:8080/root/SampleRecord in your browser, you'll receive something like:

[{"ID":1},{"ID":2},{"ID":3},{"ID":4}]

And if you ask for http://localhost:8080/root/SampleRecord/1 you'll get:

{"ID":1,"Time":"2010-02-08T11:07:09","Name":"AB","Question":"To be or not to be"}

This can be consumed by any AJAX application, if you know a bit about JavaScript.

And the same HTTP/1.1 RESTful requests (GET/POST/PUT/DELETE/LOCK/UNLOCK...) are already available in any Client HTTP/1.1 application. The framework implements the server using the very fast kernel-mode http.sys (faster than any other HTTP server on Windows), and fast HTTP API for the client. You can even use HTTPS to handle a secure connection.

IMHO, using such an ORM is better than using only a database connection, because:

  • It will follow more strictly the n-Tier principle: the business rules are written ONCE in the Delphi server, and you consume only services and RESTful operations with business objects;
  • It will use HTTP/1.1 for connection which is faster, more standard across the Internet than any direct database connection, and can be strongly secured via HTTPS;
  • JSON and RESTful over HTTP are de-facto standard for AJAX applications (even Microsoft uses it for WCF);
  • The data will be transmitted using JSON, which is a very nice format for multiple front-end;
  • The Stateless approach makes it very strong, even in unconnected mode;
  • Using a local small replication of the database (we encourage SQLite for this) allow you to have client access in unconnected mode (for Delphi client, or for HTML 5 clients).
Arnaud Bouchez
  • 42,305
  • 3
  • 71
  • 159
  • I'm wanting a solution for an Android app that may only sometimes have access to the internet but when it does, sync data should flow from my (internet) server to the app to update some stuff. I'm using XE7 and I've looked the "FireDAC_SQLite.dproj" example which is great for a fixed local or remote DB. Could I use one of the Synopse solutions for my problem? Is there an example I can try? Thanks. – Brian Frost Sep 21 '14 at 11:35
  • 1
    @BrianFrost Yes, of course. We introduced cross-platform clients, including Android clients, via Delphi/FireMonkey or AJAX/SmartMobileStudio. A BATCH mode is available, and make you able to push modified data to the server, via REST and JSON. – Arnaud Bouchez Sep 23 '14 at 09:37
2

I recommend you have one database, and two front ends (web UI that calls SOAP methods for its back end work, and a SOAP method call based rich client in Delphi, and a SOAP server tier that implements SOAP accessible methods which contains your business logic).

From what you're describing, you think replication will merely speed you up, but what it will do instead, is slow you down and cause you to have replication, coherence, and relational integrity problems that must be sorted out by hand (by you).

Warren P
  • 65,725
  • 40
  • 181
  • 316
  • I was thinking the same thing – Richard Holland Apr 26 '11 at 16:27
  • But a direct database connection will force you to code your business logic twice, for each client, in two diverse languages. This could therefore be also very confusing on the long term. And direct database connection will need tuned VPN over the Internet and such, with no possible AJAX approach (no AJAX application will connect to a database): so you'll have to write a web front-end in PHP or IntraWeb... Not the better solution IMHO in 2011... – Arnaud Bouchez Apr 26 '11 at 18:45
  • I didn't say a direct database connection. maybe it wasn't clear but by SOAP I meant, implies you write server methods, and your business logic tier on the server. I edited my answer to make that more clear. – Warren P Apr 26 '11 at 18:50
  • @Warren OK it does make sense this way! But using SOAP nowadays... – Arnaud Bouchez Apr 26 '11 at 18:51
  • SOAP is still pretty common for people who need Delphi + WebPHP. What would you suggest? – Warren P Apr 26 '11 at 20:58
  • @Warren A RESTful approach using JSON as format. And don't use PHP but an Ajax application for the web site. But of course, SOAP is a common solution for this kind of task since years. Even Embarcadero starts using JSON instead of XML. – Arnaud Bouchez Apr 27 '11 at 05:18
1

Take a look at this

CopyCat is a database replication engine, written as a component set for Embarcadero Delphi. CopyCat has been in production use since 2004, and is very stable. It is relied upon daily by a number of small to large businesses for applications ranging from inter-site synchronization, itinerant work, database backup and more. We are confident that it can fulfill your needs as well. Read on...

Hugues Van Landeghem
  • 6,755
  • 3
  • 34
  • 59