Questions about database programming with Delphi XE2

Question

I know and I've heard about there are many ways to connect to a dabatase server in Delphi (XE2): sockets, datasnap, webservices, remote data module (¿?), dbExpress, and other thrid-party like UniDAC. I generally use UniDAC, but I'd wanna know When and How to properly use them. Apparently DataSnap is the flagship in Delphi. I haven't found useful tutorials answering those questions. So for a Win32 app, which would be the recommended option to develop a client app considering: security, flexibility, and scalability?

Is there an any way to do transparent for client Win32 apps respect to a change in database access? That is, If I had 100 client apps distributed and I wanna change the password for database access, it would be a problem to have to update every client app.

Is it recommendable to have a sole connection to a database for client apps?

I've tried to test DBExpress (bundled with Delphi XE2) and AnyDAC to connect to my remote MySQL database but I have problems with VendorLibs. Is there a handy way to resolve this without to have to install every RDBMS that I wanna test?

Is DBExpress free or I must pay for more functionality?

Anyone may recommend very good books to learn about DataSnap, middleware apps, and so on?

Thanks in advance.

Answer 1

If you have multiple distributed clients, you would want to use a Multi-tier solution (like DataSnap or DataAbstract) where there is a single persisted data connection running on the server-side, and each client simply connects to that server application in order to request data.

DataSnap provides multiple different client/server connectiviy solutions: Socketed, REST etc. so it's up to you to determine what's best for your system(s).

DataAbstract appears to be one of (if not THE) most popular multi-tier data application solutions on the market, and I happen to know many developers swear by it for various reasons (security being one of them, ease of use being another).

I've always maintained that if your client application requires a direct connection to the database, you're doing it wrong!

If you're looking for more information on DataSnap, there were some interesting sessions on it at the recent CodeRage event. You can download/watch any of those sessions (freely) at Embarcadero's CodeCentral subsite.

As for Books, well your best bet will always be Marco Cantú's Delphi Handbooks (specifically the XE book, which still largely applies in terms of DataSnap for XE2).

It's important to keep in mind that DataSnap only ships with the Enterprise, Ultimate and Architect editions of Delphi... it is not available in Pro or Starter!

Still, you can always use ODBC on your own server tier to connect to any ODBC database (which includes MySQL, MSSQL, Oracle... even MS Access if you feel like enjoying a "blast from the past"), then use any networking socket solution you like (such as Indy which ships with Delphi [all editions] as standard) to link your Client applications to the Server tier! Look at TADOConnection, TADOQuery etc.

Hope you find this info useful!

Answer 2

Depending on the actual applications needs, you have to choose between a 2-tier (database and fat client) or n-tier (database, application server, thin client) solution. A 2-tier solution is simpler to develop, and n-tier is more complex.

dbExpress, UniDAC, ecc. are db access libraries. Datasnap, web services, etc. are RPC (remote procedure call) frameworks that can be used to access remote data also (using a db access library on the server side). Sockets are just very low level communication devices.

Thereby you have to use a stack of those components to build a database access layer. For example you can use dbExpress to access the DB and Datasnap as the middle layer.

From a security point of view, Datasnap is very unsecure but the DCOM implementation because of algortithms used and password/certificate management. If you're storing database passwords in your clients you have a big security issue anyway. There are ways like operating system autentication and the like that allows for connecting to a database using the user account security. Using a single "connection" to the database may mean to lose some (or much) security, because database access controls will be limited to the permissions of that connection user. Also, auditing could be lost.

When introducing a new layer, scalability must be designed and well coded into that layer. Database servers are designed and coded to be scalable, a bad middle layer can become a bottleneck.

MySQL is usually picky about what of his lib you use to connect, other database are less.