ODBC vs. newer methods for database management over the internet...

Question

I am taking on a legacy project in which database management was handled over the internet using an ODBC connection. The legacy program has recently been rewritten in C#. We are currently discussing how to improve the program and I am a bit uncomfortable with using ODBC to connect to the database. I have written routines to connect to a server using sockets and POST, PUT, and GET commands combined with cgi or php scripts and have read extensively about the AJAX paradigm which I see as the way forward. My colleague insists on using ODBC. What are the pros and cons of using an ODBC connection vs. a more modern approach?

Answer 1

Database-to-application protocols were never designed to be used over the internet. They are too chatty and difficult to secure. If you have the opportunity to do so, then you should consider encapsulating the database behind a properly-secured web service.

Answer 2

How about using ODBC with a modern approach, web services. There are many advantages to this approach. For example:

• Multiple client programs can use a single instance of the web service to access data. There is no need to write database related code in each individual application.
  • Users need to install ODBC drivers and configure ODBC data sources only on the server machine that hosts the web service. Client programs can be running on other machines over the network.
  • Client programs are not limited to .NET or Windows platform. All they have to do to access database is call a web service.
  • Database connections can be shared among different client programs.
  • Access to databases can be controlled and monitored from a central location (the web service).

Of course, there are some security issues and limitations to the complexity of your queries.

Answer 3

those who don't know networking are doomed to reinvent it on port 80

there's nothing 'modern' about HTTP over ODBC. just be sure to wrap it in SSL and/or a VPN and use sensible access controls.

it will be a lot more efficient than HTTP, which wasn't designed for this. at the very least, HTTP commands add a lot of overhead for each operation. ODBC will get you far better latency (which is critical in client-driven DB designs)

Answer 4

I had something similar in my office. They had lots of machines with VB.NET apps hitting the local database (regularly got it stuck with too many unused connections) and some web services that contacted an external database through an SSH/SSL tunnel.

We didn't really have a lot of problems with the external database unless the tunnel went down which was rare. You can probably also set up a VPN.

If you are interested in using AJAX/JSON/REST technologies to communicate with a database, you can use an abstraction layer like DBSlayer.

Answer 5

Using a TypeIV "direct" database driver like the System.Data.SqlClient namespace for C# ,or a JDBC driver for Java, is 2-3 times more efficient (better performance) than going through the ODBC layer.

I would avoid ODBC because its slower and I think its not any easier.