July 18, 2002 >From the Edge: Understanding and Using VARCHAR Data Type Section: Data Types Chapter: Running R:BASE Your Way! Platform: R:BASE 2000 (version 6.5++/7.0 for Windows) Build: 1.855xRT03 and Higher
VARCHAR data type stores large ASCII files, i.e., files with more data than can be placed in a NOTE data type. Working with VARBIT and VARCHAR data types is different than working with other R:BASE data types. Normally, you think of loading data from one file into many columns and many rows in a table. With VARBIT and VARCHAR data, you are loading a file into one column of one row in a table. Once you have loaded a file into a VARBIT or VARCHAR data type, you can delete the original file from disk. The data file is now stored within the database. There are many ways to use VARCHAR data type within your databases and applications. For example, someone may require storing large ASCII data such as Student Biographies when submitting online college Admission Forms, Patient Notes, Diagnostic Surgical Reports, Investigative Reports, Criminal History, Inmate Profile, Task Management and Contact History. . VARCHAR data type lets you store large ASCII data files (Large OBjects or LOBs). . VARCHAR data type is defined with a length, or as a LONG VARCHAR to be variable length. . VARCHAR column can store a file up to 256MB in size per row. . Using Object Manager/RBDefine in R:BASE 2000 (ver 6.5++) or GUI Data Designer in R:BASE 7.0 for Windows, VARCHAR data type is defined as LONG VARCHAR. . 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 . If you set the maximum length to a value over 32,767 characters, the limit is ignored and defaults to 256 MB. . 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 timestamp and can be stored on a different drive. . 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. . NOTE field is limited to 4092 characters. . You can convert TEXT and NOTE data type column(s) to VARCHAR. . You cannot convert VARCHAR back to TEXT or NOTE. . You can use SELECT .. WHERE ColumnName CONTAINS 'text' command for both NOTE and VARCHAR data types. . You cannot use SELECT (AggregateFunction(VarcharColumn)) command for VARCHAR column. . An expression cannot contain a column of type VARCHAR. . A document file can be stored in a VARBIT data type, but is not readable within R:BASE. As with the VARBIT data type, a VARCHAR data type is defined with a length, or as a LONG VARCHAR of variable length. . To load the VARCHAR column data directly from an ASCII file with either the LOAD or INSERT command, use the special format, ['FileName.ext']. This format tells R:BASE to find the specified file and treat it as BLOB data. Data in a VARCHAR can be replaced from a file using the UPDATE command. . VARCHAR cannot be directly edited. Use ZoomEdit [Shift+F12] option in R:BASE 2000 (version 6.5++) Data Browser/Editor or Forms. In addition, VARCHAR data can be loaded and updated through a form. Press [Shift+F10] from a VARCHAR field located on a form to open the Windows common file dialog box. Select the file to be loaded into the field. You do not need to specify the file name in a special format when loading through a form. Use BLOB Field Viewer/Editor option in R:BASE 7.0 for Windows to achieve the same task. . Using R:BASE 2000 (version 6.5++) the original file can be recreated at any time using the WRITE command to write binary or large ASCII data to a file. The data is read from the table into a variable, then the variable is written to a file. This process recreates the file exactly. You may use ZoomEdit [Shift+F2] option to view/edit VARCHAR data in Forms or Data Browser/Editor. . Using R:BASE 7.0 for Windows, you can either use the WRITE command to write ASCII data to a file, or use the graphical user interface of BLOB Field Viewer/Editor to view, load, update, replace and save VARCHAR/VARBIT data. Using BLOB Field Viewer/Editor in R:BASE 7.0 for Windows, you can also cut and paste ASCII data into the VARCHAR column. . Printing VARCHAR data in R:BASE 2000 (ver 6.5++) for Windows Report is limited to only one page. VARCHAR data for any particular row will be truncated if more than one page. . Printing VARCHAR data in R:BASE 7.0 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. . When using UNLOAD DATA FOR TableName command, VARCHAR data is saved in a OutputFileName.LOB file. Example: OUTP FileName.ASC UNLOAD DATA FOR BriefNotes OUTP SCREEN You will find two files, i.e, FileName.ASC and FileName.LOB. PDF version of this technical document will be available after July 24th at: >From The Edge: http://www.razzak.com/fte Enjoy and make sure to have fun! Very Best Regards, Razzak. P.S. Bill Perry, this one is for you! __________________________________________________ Do You Yahoo!? Yahoo! Autos - Get free new car price quotes http://autos.yahoo.com ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/
