The Joel on Software Discussion Group (CLOSED)

A place to discuss Joel on Software. Now closed.

This community works best when people use their real names. Please register for a free account.

Other Groups:
Joel on Software
Business of Software
Design of Software (CLOSED)
.NET Questions (CLOSED)
TechInterview.org
CityDesk
FogBugz
Fog Creek Copilot


The Old Forum


Your hosts:
Albert D. Kallal
Li-Fan Chen
Stephen Jones

Large Text fields in Delphi

If you are trying to put more than 8k in a SQL Server text field and you're using Borland's BDE from Delphi, you may have learned about the problem of right truncation.  Faced with the option of rewriting everything in ADO over several months, I chose this as a workaround instead:

http://www.lazarusid.com/datastreamer.html
Clay Dowling Send private email
Tuesday, June 10, 2008
 
 
I think one would be far better off getting away from the BDE altogether and using some high quality VCL components such as those from Core Labs that provide native database access.
DaveW
Tuesday, June 10, 2008
 
 
Well, you're not wrong, but the several month rewrite is a bit of an issue.  If people were willing to undertake the rewrite, just switching to ADO would have resolved the problem.
Clay Dowling Send private email
Tuesday, June 10, 2008
 
 
Why on earth did you start on a project involving BDE in 2008 anyway?

BDE died 10 years ago.
The Luggage
Tuesday, June 10, 2008
 
 
Legacy project.  If we could ditch BDE, we'd do it.
Clay Dowling Send private email
Tuesday, June 10, 2008
 
 
I just converted a rather large project from BDE to ADO, it was fairly painless.
Yo!
Wednesday, June 11, 2008
 
 
<<<If you are trying to put more than 8k in a SQL Server text field...>>>

What are you doing, writing a book in a single text field?

That is NOT what database fields are for.....you don't use data tables to write books of data in fields......

Geesh....

Keep your field values less than 255 chars per field and all will be well.....I don't know what kind of app you are building but you are going to have to have to accept some constraints on your project because no sane DBA or developer creates databases with pages of information in a single field......it is absolute and utter nonsense.
Brice Richard Send private email
Wednesday, June 11, 2008
 
 
The only solution I can imageine where a page of data would be required in a field within a database app would be a template letter database of some sort written for a law firm that uses boiler plate contract and/or letter paragraphs to construct complex legal correspondences.....and even under this condition, I would most likely use independent text files within a .Net application to achieve such a solution.....I certainly wouldn't use a database app which had 8k of data in a field!
Brice Richard Send private email
Wednesday, June 11, 2008
 
 
> I certainly wouldn't use a database app which had 8k of data in a field!

Why not?

* I'd expect the database Engine to keep the field content in a separate page (e.g. not in the indexes), so that it doesn't affect the performance of the table

* Having everything in the database makes it easier to manage:

  - Backup

  - Assign user access rights

* Having everything in the database makes it easier to get at run time (imagine for example that the 8K is an article, and other database fields are title, subtitle, author, date, keywords, etc.): having the articles' text in the database means that I can make one SQL query to get some and/or all of the data (including the article text) for e.g. a given author.
Christopher Wells Send private email
Wednesday, June 11, 2008
 
 
Hey, I was thinking I should hire him to write the call center scripting application that I'm working on, if he knows a better way.  I'd love to see the solution he comes up with to store and distribute scripts for auto insurance claims in 255 characters.
Clay Dowling Send private email
Wednesday, June 11, 2008
 
 
indeed. brice, i do not want to make your head explode, but take a look at an earlier post, say... this one:

http://discuss.joelonsoftware.com/default.asp?joel.3.568375.67

it contains 4,121 characters. assuming one byte per character, thats over 4k right there.

where do you think JoS posts are stored? I do not think it feasable for each post to have its own flat file, for one thing implementing searching would be a bit of a drag...
arg!
Wednesday, June 11, 2008
 
 
ever use an xml datatype???
Yo!
Wednesday, June 11, 2008
 
 
not all DBs support full text search on xml, some do but not on all attributes, etc.
arg!
Wednesday, June 11, 2008
 
 
>and even under this condition, I would most likely use independent text files within a .Net application to achieve such a solution.....I certainly wouldn't use a database app which had 8k of data in a field!

I would. I absolutely would. There are countless scenarios where it is optimal to store information of that sort in the database (backup atomicity, transactional integrity, ease of management, replication, API consistency, among others), and it would be guided-by-the-70s to avoid it just as some sort of unjustified dogma.
Dennis Forbes Send private email
Wednesday, June 11, 2008
 
 
++Dennis

Our customers don't want to have to mix-and-match filesystem and database backups to be sure a system restore is complete & usable. We store Excel, PDF & XML documents in the database and they easily exceed 8k. 

Since our system handles financial data, there's the normal paranoia plus SOX compliance issues etc.  Having everything in the database is a plus.
a former big-fiver Send private email
Wednesday, June 11, 2008
 
 
I don't understand why you need to store these large pieces of text in a varchar field.  Why are you not using the SQL Server 'Text' datatype, which is the equivalent of a BDE text blob (or memo) field?
Herbert Sitz Send private email
Wednesday, June 11, 2008
 
 
Sorry, maybe I posted to soon.  I'm not clear whether you're using a Sql Server Text (blob in BDE) datatype or a Varchar datatype.  Since BDE can be used to store blob data much larger than 8k, I assume if it is a Text datatype you're converting it to Varchar somewhere in the mix.  I'm sure there are ways around this.

I would think the Delphi support newsgroups would have info on this.  A quick search at fulltextsearch.com revealed a number of posts talking about using text memo fields with sql server.

As an example, here's an ancient entry on Borland's dev support that describes saving blobs greater than 32k (they had been limited to <32k at some point):
http://info.borland.com/devsupport/sqllinks/pub-25.html
Herbert Sitz Send private email
Wednesday, June 11, 2008
 
 
"I'm not clear whether you're using a Sql Server Text (blob in BDE) datatype or a Varchar datatype.  Since BDE can be used to store blob data much larger than 8k, I assume if it is a Text datatype you're converting it to Varchar somewhere in the mix.  I'm sure there are ways around this."

At least in Sybase, one is limited in what one can do with BLOBs in SQL. That is, there are trade-offs with blobs as well.
somebody else
Thursday, June 12, 2008
 
 
Herb,

That's an interesting link you had there.  Unfortunately I can't map that particularly well to practice in the field, and this may be due to changes on SQL Server since that post was written.  In any event, the 8k limit on SQL statements for SQL Server has been documented elsewhere, and streaming mode is the preferred workaround.

The right long-term solution is to get rid of BDE, since it causes a number of support issues.
Clay Dowling Send private email
Monday, June 16, 2008
 
 

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

Other recent topics Other recent topics
 
Powered by FogBugz