At 04:30 PM 1/19/2009, Lawrence Lustig wrote:
So far I've tried GATEWAY (produces "<BLOB>" instead of the text
in the columns), SELECT AS CSV (produces "Cannot unload VARCHAR
as ASCII", BROWSE and then Export from the menu (same <BLOB>
problem), and BROWSEing a computed version of the column (BROWSE
SGET(ProbVarChar, 50, 1)) which crashes R:Base (but bigtime).
Please point me in the right direction.
That is the result of VARCHAR data being stored as BLOB in file 4.
However, there _is_ more than one way to skin a cat!
01. Define a temporary table on the fly with one exception to
use a different column name to hold NOTE data type.
Example:
Original Table (TableA) with following columns:
. CallID INTEGER
. CallDate DATE
. CallNotes LONG VARCHAR
02. CREATE a dynamic TEMPORARY TABLE (TableB) before using the
GATEWAY EXPORT routine as follows:
Example:
SET ERROR MESSAGE 2038 OFF
DROP TABLE `TableB`
SET ERROR MESSAGE 2038 ON
CREATE TEMPORARY TABLE `TableB` +
(`CallID` INTEGER, +
`CallDate` DATE, +
`CallNotesAsNotes` NOTE)
COMMENT ON TABLE TableB IS 'Temporary Table for Gateway Routines'
03. INSERT rows from original TableA to temporary TableB.
Example:
INSERT INTO TableB +
(CallID, CallDate, CallNotesAsNotes) +
SELECT +
CallID, CallDate, CallNotes +
FROM TableA
04. Putting It All Together
-- Define temporary table
SET ERROR MESSAGE 2038 OFF
DROP TABLE `TableB`
SET ERROR MESSAGE 2038 ON
CREATE TEMPORARY TABLE `TableB` +
(`CallID` INTEGER, +
`CallDate` DATE, +
`CallNotesAsNotes` NOTE)
COMMENT ON TABLE TableB IS 'Temporary Table for Gateway Routines'
-- Insert data
INSERT INTO TableB +
(CallID, CallDate, CallNotesAsNotes) +
SELECT +
CallID, CallDate, CallNotes +
FROM TableA
-- Create file using the UNLOAD command:
OUTPUT TableBData.csv
UNLOAD DATA FOR TableB AS CSV
OUTPUT SCREEN
-- Create file using the GATEWAY Export command:
GATEWAY EXPORT CSV C:\Temp\TableB.CSV +
SELECT * FROM TableB +
OPTION COL_NAMES ON +
|QUALIFIER " +
|SEPARATOR , +
|REC_SEP CRLF +
|SHOW_PROGRESS ON +
|ACTION OPENVIEW
RETURN
I hope this has been some help to point you in the right direction!
Very Best R:egards,
Razzak.
