Let me add that I'm aware this isn't the best way, but it is an alternative when a database, or other persistence layer, doesn't support clobs or text/memo datatypes. I started using this technique many years ago before CF and Oracle played well together with xLOBs.
Basically, yes, I do that. I find the x number of characters, cut that from the string and insert it. Then, repeat as needed. When I break it apart, it does not matter if it breaks in the middle of a word or not. When put back together for display, all the parts come back together like they were before splitting them apart. I use this technique to store instructors' syllabus in a database. There is no need to make it searchable. Students can only (get to it/see it) if they are enrolled in the course. I have thought about rewriting this to use clobs, but since most of my intranet is being replaced with a pre-built package, to hell with them. ;^) M!ke -----Original Message----- From: S. Isaac Dealey [mailto:[EMAIL PROTECTED] Sent: Friday, April 29, 2005 11:25 AM To: CF-Talk Subject: RE: longer than a varchar2 > -----Original Message----- > From: Dawson, Michael [mailto:[EMAIL PROTECTED] > Sent: Friday, 29 April 2005 10:26 > To: CF-Talk > Subject: RE: longer than a varchar2 > When I need to store unlimited amounts of text, I usually create a > separate table like this: > Notes > ----- > ID (sequential value) > FK (links to a parent table) > TextValue (varchar 4000) > Then, break up the text into 4000-byte chunks and insert them into the > table. I also store the related foreign key value to keep them > "together". > When I query the data back out, I retrieve all records for a > particular FK value, and sort it by the ID (sequential value). > This method is very portable and doesn't require changing any > datatypes from one rdbms to another. I'll give it portability... although it seems like it would create more work in other areas: re search engines, etc... How do you determine where to split the text? Left(4000) or do you search for a space? Even still, if someone is searching for "if someone is searching for" in your long-text and the split happens to occur between "if someone is" and "searching for" any simple sql query I know of won't be able to return that record. Or does your application not search on the long-text data? (Hoping ID and FK are only hypothetical and not literal column names.) s. isaac dealey 954.522.6080 new epoch : isn't it time for a change? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:205103 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
