Tuesday, February 07, 2006

BDS2006, BDP and memo fields

Lately I encountered some strange problems using the Borland Data Provider (BDP) in a ASP.NET project. At first I thought I had solved it, but eventually (mostly because of project scope changes) it made me change from BDP to OleDB.

The project is a database-driven website for which I use BDP to connect to the database.

Why Borland DataProvider?

Borland DataProvider has some benefits compared to the standard .NET providers (SQL/OleDB):

  • BDP is database independent (Same components for SQL and local databases)
  • Connection pooling
  • Easy data remoting components

The database is used to provide the content of the website. At first I used a SQL Server database and found out that having two text fields (memo fields) in the SELECT clause causes the text fields to be truncated to 1024 chars. This is inconvient!
[update: Problem solved, see below]

Having only one text field in the SELECT works just fine! That is strange, isn't it? [No]

In SQL Server however I could of course change one of the Text fields to a varchar with a (max) length of 4000. Not the most elegant way, I know, but it is a rather small database only holding the website content. Problem solved.
Now, for other reasons, my customer wants to use an Access database instead. No problem! As said BDP components are database vendor independent!
Except, of course, that varchar fields in Access are limited to 255 chars, so I ended up with two truncated memo fields. :-(

The only solution left was to change from BDP to OleDB. This switch was easy because the BDP components are similar to the standard .NET providers.
My project now contains two database technologies, BDP and OleDB. All works fine but I can not call it elegant. (I keep the BDP, because there might be a switch back to SQL Server in the future.)

I only can imagine that this is a bug in the Borland Data Provider so I added a report to quality central. You can find it under QC number #24314

Update 8-2-2006:
Well I imagined it wrong. It seemed to be a property of the BdpConnection called BlobSize, which is default set to 1024! Well QC report withdrawn!
Wow this one was really hard to find! :-)

No comments: