Bob,
Here is a sample application that illustrates the technique outlined below ...
Sample Applications: http://www.Razzak.com/SampleApplications/
Application Title..: Converting VARCHAR Data to NOTE
Basically ...
01. DECLARE a CURSOR and then select the Column
VARCHAR value into a Variable VARCHAR, such as vWholeData (VARCHAR).
02. Then, split the vWholeData variable values
into three variables, such as ...
SET VAR vPart1 VARCHAR = (SGET(.vWholeData,4092,1))
SET VAR vPart1 VARCHAR = (SGET(.vWholeData,4092,4093))
SET VAR vPart1 VARCHAR = (SGET(.vWholeData,4092,8185))
OR , as you see fit.
03. Then INSERT the values (vPart1, vPart2,
vPart3) INTO appropriate three NOTE columns, etc.
The sample application along with a sample
database includes a command file (VARCHAR_To_NOTE_Conversion.rmd) a follows:
-- VARCHAR_To_NOTE_Conversion.rmd
-- Author: A. Razzak Memon
-- Date Created: March 2, 2017
-- Last Updated:
CONNECT VARCHAR_To_Note
DELETE ROWS FROM TableB
SET VAR vColumnA INTEGER = NULL
SET VAR vColumnB VARCHAR = NULL
SET VAR vColumnC NOTE = NULL
SET VAR vColumnD NOTE = NULL
SET VAR vColumnE NOTE = NULL
SET ERROR MESSAGE 705 OFF
DROP CURSOR c1
SET ERROR MESSAGE 705 ON
DECLARE c1 CURSOR FOR SELECT ColumnA, ColumnB FROM TableA
OPEN c1
FETCH c1 INTO +
vColumnA INDIC iv1, +
vColumnB INDIC iv
WHILE SQLCODE <> 100 THEN
SET VAR vColumnC = (SGET(.vColumnB,4092,1))
SET VAR vColumnD = (SGET(.vColumnB,4092,4093))
SET VAR vColumnE = (SGET(.vColumnB,4092,8185))
INSERT INTO TableB (ColumnA,ColumnC,ColumnD,ColumnE) +
VALUES +
(.vColumnA,.vColumnC,.vColumnD,.vColumnE)
SET VAR vColumnA = NULL
SET VAR vColumnB = NULL
SET VAR vColumnC = NULL
SET VAR vColumnD = NULL
SET VAR vColumnE = NULL
FETCH c1 INTO +
vColumnA INDIC iv1, +
vColumnB INDIC iv
ENDWHILE
DROP CURSOR c1
CLEAR VARIABLES iv%,vColumn%
RETURN
Hope that helps!
Very Best R:egards,
Razzak
At 12:05 AM 3/2/2017, Bob Taylor wrote:
Thanks Razzak.
That works provided the VARCHAR value can fit into a NOTE.
I have a number of records that are approaching
12,000 characters in length which results in
this error during the execution of the "INSERT INTO SELECT" SQL:
-WARNING- Column ColumnName will be truncated.
Once this happens, R:Base becomes unstable;
either crashing outright or refusing to close
down completely (I must kill the process).
I'm trying to work around the next problem using
the SGET function in the SELECT statement, but
this is causing a lot of crashing in R:Base.
Any other suggestions?
Thanks, Bob
On Monday, February 27, 2017 at 6:29:06 PM UTC-7, A. Razzak Memon wrote:
Bob,
You can easily convert the VARCHAR data to NOTE data!
Try the exercise below to get an idea.
Example:
TableA:
ColumnA INTEGER
ColumnB VARCHAR
TableB:
ColumnA INTEGER
ColumnC NOTE
Routine to convert VARCHAR to NOTE Field:
INSERT INTO TableB (ColumnA, ColumnC) SELECT ColumnA, ColumnB FROM TableA
The resulting ColumnC in TableB will include the converted VARCHAR to NOTE.
That's all there is to it!
In your specific case, you can accomplish the task using TEMPORARY tables.
Once you have the required data as NOTE column, you can use the power of
UNLOAD or GATEWAY EXPORT options to extract data, on demand.
Very Best R:egards,
Razzak.
At 08:09 PM 2/27/2017, Bob Taylor wrote:
>R:BASE X, Enterprise, U.S. Version, Build: 10.0.2.20126
>
>I have this table:
>
>CREATE TABLE `NOTES_TABLE` +
>(`NOTE_KEY` BIGINT NOT NULLÂ  ('Value for
column NOTE_KEY cannot be null') , +
>Â `NOTE_VALUE` LONG VARCHAR NOT NULLÂ  ('Value
>for column NOTE_VALUE cannot be null') )
>
>insert into notes_table(note_key, note_value) values (1, 'Testing');
>
>I then use the File Gateway dialog to export the table:
>
>Select Table/View = NOTES_TABLE
>Format = Comma Separated Values (*.csv)
>File Name = NOTES_TABLE_EXTRACT.CSV
>
>All other options are left with the default state.
>
>When executed, the NOTES_TABLE_EXTRACT.CSV file contains this:
>
>NOTE_KEY,NOTE_VALUE
>2,"<BLOB>"
>
>What do I have to do to get the actual column
>contents ("Testing") to appear in the export?
>
>I've tried some of the other formats (xls, xml),
>but I always end up with some type of BLOB reference in the export.
--
You received this message because you are subscribed to the Google Groups
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
