BDE and NVARCHAR . Should I go for ADO or dbExpress ?

Last week , I was working on a task that basically supports the use of SQL Server 2005 instead of Oracle in Delphi.

The following Interesting observations were made during this time .

The Project used a lot of BDE components ( TQuery , TDatabase , TTable ) , which connects to the Oracle through the driver Oracle Driver that comes with the Delphi 7.

I tried Changing the connection string / Alias Name ( Used ODBC ) of the TDatabase component and was successfully able to connect to the SQL Server , but faced an interesting situation that some of the columns were not accessible . This happened , even if i tried using the MSSQL Driver that comes with Delphi or SQL Native Client .

Is BDE and NVARCHAR an issue in Delphi ?

After some R & D , i noticed that fields of type NVARCHAR was the one that was causing the problem in my Delphi application .

When , i tried using the SQL Explorer ( part of BDE ) , and when i query the table to retreive the database , these fields / Column names with NVARCHAR was still not recognised as the column names were empty .

Assuming that since BDE was deprecated longback ( 2000 ) , i tried a sample program that uses ADO Components(TADOTable,TADOConnection,TADOTable) , for which i was able to get the column name very easily .

But when i use the BDE components and bind the resultset to the DBGrid , i was still unable to view the Columns with NVARCHAR type .When i changed the field to VARCHAR , this worked .

I also tried casting the field from NVARCHAR to VARCHAR.

This got the values the of the column But still there are chances that the data might be truncated too.

 Select CAST(ColumnName as VARCHAR ) from Tablename .

Found this functional/delphi-l/how-to-get-nvarchar-field-in-a-select-statement-using-bde-query-component-1699324 , where another developer too faced the similar issue .

The reply seems to be obviously to get rid of the BDE components and move to ADO .

But , now i am wondering what component to use as there are 2 components ( ADO / dbExpress that comes with Delphi ) .

The ADO works based on the Connected Data similar to BDE , but the dbExpress works on the Disconnected Model , which means that it can provide a better performance , but needs extra workaround when binding the Data to DBGrid than ADO components .

Still wondering what would be the right solution …..