Razzak, Thank you for this great explanation. Between MikeB and you it is clear that I need to change many of my uses of varchar and I understand why.
Tom Frederick Elm City Center 1314 W Walnut Jacksonville, IL 62650 Off - 217-245-9504 Fax - 217-245-2350 Email - [EMAIL PROTECTED] Web - www.elmcity.org -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of A. Razzak Memon Sent: Monday, January 14, 2008 7:39 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Data Types and File Gateway At 04:32 PM 1/14/2008, Tom Frederick wrote: >I have been using varchar for pictures and many text inputs >that are often 4+ lines long. Tom, FWIW, the VARBIT data type is the most useful data type to store and manage BLOBs, such as pictures. Technically, the VARCHAR data type is ideal for storing VARiable length CHARacter data, such as text and rich text format (.rtf) files. >Everything has gone very well until I used Gateway to export a >table to Excel. When I looked in the Excel file, the varchar >columns were simply statements that read "[Blob]". The pictures >and data were not there. I realize varchars are kept in a >separate file from the other data so I assumed Gateway does not >retrieve this separate data during an export. So two questions: >1. Does varchar work with Gateway at all or can this only be done > through Load/Unload? AFAIK, MS Excel cannot read VARCHAR, BITNOTE, or VARBIT data types. You should have no problem using the UNLOAD or LOAD command to UNLOAD or LOAD any BLOB data in R:BASE. >2. More importantly, is there a basic rationale on when to use > text, note, or varchar in various settings. Something like > (I am making this up) text = editable/less than 250 characters, > note = some editable/250-2000 characters, and varchar = not > easily editable/over 2000 characters/pictures. Here are a few guidelines to use TEXT, NOTE and VARCHAR data types: . Use TEXT data type if you know that the length is going to be between 1 and 1500 characters. . Use NOTE data type if you know that the length is going to be between 1 and 4092 characters. You may also limit the length, if you wish. . Use VARCHAR data type if the need exceeds 4092 characters with added feature of managing Rich Text Format files. ***** Added Features of VARCHAR Data Type (R:BASE 7.6 and Turbo V-8): 01. Use VARCHAR data type to manage Rich Text Format (.RTF) files. 02. VARCHAR data type lets you store large ASCII data files (Large OBjects or LOBs). 03. VARCHAR data type is defined with a length, or as a LONG VARCHAR to be variable length. 04. VARCHAR column can store a file up to 256MB in size per row. 05. Using Object Manager/RBDefine in R:BASE 7.x for Windows, VARCHAR data type is defined as LONG VARCHAR. 06. Using CREATE TABLE ... command you can either define LONG VARCHAR or VARCHAR (xxx) data type with a fixed length. -- Example 01: SET ERROR MESSAGE 2038 OFF DROP TABLE InvestigationNotes CREATE TABLE `InvestigationNotes` + (`CID` INTEGER, `CNotes` LONG VARCHAR) SET ERROR MESSAGE 2038 ON RETURN -- Example 02: SET ERROR MESSAGE 2038 OFF DROP TABLE BriefNotes CREATE TABLE `BriefNotes` + (`CID` INTEGER, `BNotes` VARCHAR (7000)) SET ERROR MESSAGE 2038 ON RETURN 07. If you set the maximum length to a value over 32,767 characters, the limit is ignored and defaults to 256MB. 08. The data for VARBIT and VARCHAR data type is stored in the R:BASE dbname.rb4 file. As with the other three database files, dbname.rb4 is linked with a time stamp and can be stored on a different drive. 09. A document file from a word processing program such as Word or WordPerfect is a binary file, not an ASCII file. The document file contains formatting characters and must be saved as a text or ASCII file to be stored in R:BASE as a VARCHAR data type. 10. You can convert TEXT and NOTE data type column(s) to VARCHAR. 11. You cannot convert VARCHAR column back to TEXT or NOTE. Use INSERT command to convert VARCHAR data into TEXT or NOTE data. 12. You can use SELECT .. WHERE ColumnName CONTAINS 'text' command for VARCHAR data types. 13. An expression (TEXT, NOTE, VARCHAR) may contain a column of type VARCHAR. 14. VARCHAR data can easily be managed by using the enhanced Note/BLOB Field Viewer/Editor in Data Browser/Editor or Form Controls such as DB Memo, DB Rich Edit, Variable Memo, or Variable Rich Edit. 15. Using enhanced Note/BLOB Field Viewer/Editor in Data Browser/Editor or Form Controls, you may also save VARCHAR data as external file. 16. Printing VARCHAR data in R:BASE 7.x or Turbo V-8 for Windows Report is unlimited. Use DBMemo component to locate VARCHAR column in Report Designer. Use Stretch property to allow VARCHAR data to fit inside the component. When Stretch is set to true, VARCHAR data is scaled proportionally across the pages maintaining the original paragraphs, indentation, line spacing, etc. Stretch property default is set to false. From the Report Designer you can access the Stretch property by positioning the mouse cursor over the component and clicking the right mouse button. A speed menu containing the key properties for the component will be displayed. If the Stretch property is checked, the VARCHAR data will be scaled accordingly. Selecting the Stretch option will toggle its value. 17. When using UNLOAD DATA FOR TableName command, the VARCHAR data is saved in a OutputFileName.LOB file. 18. Now you may also use the following string manipulation functions for VARCHAR data type: . (SLEN(text)) . (SGET(text,nchar,pos)) . (SRPL(sourcestring,searchstring,replacestring,[0|1])) . (SKEEP(source, chars)) . (SKEEPI(source, chars)) . (SSTRIP(source, chars)) . (SSTRIPI(source, chars)) . (SKEEP(source, chars)) I hope that gives you enough information to manage your R:BASE 7.6 and Turbo V-8 for Windows applications. Very Best R:egards, Razzak.
