The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Struggling with Delphi + SQL Server

Is there anybody out there using Delphi with SQL Server?

We are in the process of migrating Paradox based tables to MS SQL Server (SQL Server is a requirement, no alternative databases are allowed by IT), and struggling because using the BDE to SQL Server has a bug that causes interlocks particularly with large DBs and we are using ADO directly with such DBs seems to be so slow, particularly with DB Grid.

Has anybody used Delphi successfully with SQL Server in an application with large DBs, master/detail relationships and DBGrid components?  Which approach to DB access did you use?

We are looking into dumping BDE because it has not been improved since 1999 and after the great efforts trying to workaround the BDE bug, we now consider it a risk to continue to stick with a technology that is not maintained anymore by anyone, to support newer DBs.
Wednesday, July 25, 2007
I think there are many mapping tools available which can convert from Paradox to SQL. Also to use SQL (even for large DBs) in Delphi ADO is the best.

Wednesday, July 25, 2007
Do yourself a favor and ditch the BDE ASAP. There are 3rd party software around for accessing databases directly; I've used Direct Access components with Oracle and PostgreSQL and MySQL databases.

Here is some components for MS-SQL Server as well:
Wednesday, July 25, 2007
+1 for the Core Labs components.  Core Labs provides native VCL components for direct access to a number of popular databases including SQL Server.  I have used them in applications for SQL Server and Oracle access and they work great.  I got away from the BDE several years ago.
Wednesday, July 25, 2007
+1 to the suggestion for using ADO. It's (potentially) not even all that painful making the switch across from the BDE (which REALLY ought to be done ASAP, BTW). I did that kind of conversion for a project myself many moons ago and it was more or less a simple case of literally swapping components (TQuery to TADOQuery, TDatabase to TADOConnection) and then replacing any references to BDE aliases with the equivalent ADO connection strings. It actually turned out not too bad at all :-)
Wednesday, July 25, 2007
You normally can not just do a component replacement to switch from Paradox based database to SQL database. With a file based database, you normally just access the whole table directly. But with SQL database, especially very large tables, you DO NOT want to view the table directly. Doing so involves getting every records of the database from the server to the client.  You need to change the way data is accessed at the client side. You should use TADOQuery to get only the set of data needed to display.
Jerry Wang Send private email
Wednesday, July 25, 2007
Definitely agree, Jerry - the app that I'd mentioned converting previously was already TQuery based, BDE to SQL Server job and didn't have any TTable components at all...

Been that long since I'd seen anyone use TTable that I'd clean forgotten about it - should have guessed that when the OP was talking about speed problems, TTable might be at the root of his problems :-)
Friday, July 27, 2007
Over the past 3 years, I have taken over maintenance a back-office suite written with Delphi 5 that connects to SQL Server (7, 2000, 2005) - and unfortunately for me - but perhaps fortunately for you!, still uses the BDE!

+1 for Corelabs - I can vouch for Corelabs with MySQL, the components are a snap to work with, Mysql5 stored procs, cached updates, transactions no problem - but haven't tried myself with SQL Server.

I would DEFINITELY put BDE removal on your roadmap. With those pesky BDE dlls and registry entries, locale-sensitive settings, deployment can be a real headache.

If that isn't possible at the moment, I have dealt with just about every BDE headache I could imagine from blob cache to problems with field types... ugh, you name it!

If you are stuck with BDE for the moment and have TTables - you need to change these to TQuery - or descendant. TTables just don't work well with SQL Server. An ugly, network killing method is to jam in select * from... to begin. It will be a network monster, but it can get you moving without having to change too much code. That would be a good first step. Then you can move towards cached updates if you don't already have this - either by sproc or by sql statement.

I will make a guess that your grids use results filtering -  local filtering controls. Your next step would be to parameterize your queries and change the local filtering to a "parameter set/close/open" type of mechanism.

I have also used ADO in some applications (SQL Server and MySQL via myodbc) before I went with Corelabs. I can vouch for that as well.

Question for the Corelabs guys using SQL Server - do the components (easily?) support multiple datasets returned from SQL Server stored procs?
I still code in Delphi
Tuesday, July 31, 2007

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz