Razzak, thank you for this explanation. I have a couple of questions about
the length of the VARCHAR data type:
A week or two ago I decided to convert the way I do something to use RUN
SELECT so I added, via the Data Designer, a VARCHAR column to a table. When
I came to set the length I was not able to do so as any figure that I
entered returned to null. I saved the changes and updated the data anyway.
As everything seemed to work and my program was hugely simplified I thought
no more of it. However, I have just checked the definition by unloading and
it shows as "LONG VARCHAR"
My questions are firstly, does the use LONG VARCHAR have any effect on the
size or performance of the database compared to a (short) VARCHAR? And,
secondly, am I right in thinking that I can convert this column to VARCHAR
simply by removing the word LONG and adding the required length in brackets
to my unload file (and reload it)?
Thanks in advance for any clarification,
Regards,
Alastair.
----- Original Message -----
From: "A. Razzak Memon" <[EMAIL PROTECTED]>
To: "RBASE-L Mailing List" <[email protected]>
Sent: Tuesday, January 15, 2008 1:39 AM
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.
--
No virus found in this incoming message.
Checked by AVG Free Edition. Version: 7.5.516 / Virus Database:
269.19.2/1224 - Release Date: 14/01/2008 17:39
--- RBASE-L
================================================
TO POST A MESSAGE TO ALL MEMBERS:
Send a plain text email to [email protected]
(Don't use any of these words as your Subject:
INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH,
REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP)
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: INTRO
================================================
TO UNSUBSCRIBE:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: UNSUBSCRIBE
================================================
TO SEARCH ARCHIVES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: SEARCH-n
(where n is the number of days). In the message body,
place any
text to search for.
================================================
