community.borland.com

Article #25401: Embedding a carriage return into a column

Problem:
Exporting an internal table to a text file requires that 
a 'newline' column contain a carriage return.

Solution:
The information in this article applies to:

* InterBase 4.x
* InterBase 5.x

You could use a C/Delphi program to pass the ASCII 
character value (as a string) to the UPDATE
statement, but here is a way using standard SQL.

Assume that the table you wish to export (TABLE1)
has two fields (ID INTEGER, NAME VARCHAR(10)) and
some existing data:

ID                       NAME
===                    ==========
101	        Name1
102                    Name2
103                    Name3	

The first step is to add a column to hold a NEWLINE 
character (2 bytes):

ALTER TABLE TABLE1 ADD NEWLINE CHAR(2);

The next step is to create an external table that will 
hold the final exported data. The fields must correspond 
to those of the table to be exported (including the newly 
added NEWLINE field), be of type CHAR and wide enough 
to hold the the existing data: 

CREATE TABLE T1_EXT EXTERNAL FILE "C:T1_EXT.TXT"
(ID CHAR(3), NAME CHAR(10), NEWLINE CHAR(2));

Next create a textfile (with an ASCII text editor such
as Notepad) that contains a single carraige return. In 
this example assume that I have named this file 
"C:NL_EXT.TXT". The following CREATE statement will 
add the appropriate information to the system tables 
for later access:

CREATE TABLE NL_EXT EXTERNAL FILE "C:NL_EXT.TXT" 
(NEWLINE CHAR(2));


INSERTING A CARRIAGE RETURN

First, take a look at what table TABLE1 consists of:

select * from table1

         ID             NAME              NEWLINE 
=========== ========== ======= 

                   101 name1            
                   102 name2            
                   103 name3            

Then you issue the following SQL statement that takes 
the carriage return value in the newline external file 
(NL_EXT) and updates the NULL newline column in the 
internal table (TABLE1) with it.

update TABLE1 set newline = (select newline from NL_EXT)

Now TABLE1 has:

select * from TABLE1

         ID             NAME             NEWLINE 
=========== ========== ======= 

                   101 name1      
      
                   102 name2      
      
                   103 name3      

Notice that the NULL is missing from the NEWLINE column.  
That is because it contains a carriage return.  Also, the 
carriage return value takes two characters on Windows 
platforms, one on UNIX.


EXPORTING TO AN EXTERNAL TABLE/FILE

Now, a simple insert statement to move the data from the 
internal table to the external table/file:

insert into T1_EXT (ID, name, newline)  select CAST(ID as char(4)),
cast(NAME as char(10)), NEWLINE from TABLE1


Note that the integer column in T1_EXT was a char and you 
had to use the CAST operator to move the numeric value.  
Now, did the data go over?

select * from T1_EXT

ID           NAME             NEWLINE 
====== ========== ======= 

       101    name1      
      
       102    name2      
      
       103    name3      

Last Modified: 29-SEP-00