community.borland.com

Article #25425: Problem with External Files and InterBase version 5.x

Problem:
At some point in time my external files got messed up.  When 
I select data from them it comes back all jarbled and mis-aligned.


Solution:
Note: This issue is fixed in v5.5.

The problem is not because of but, manifest through the GBAK and RESTORE process.

If you are not interested in the how's and why's your work around options are as follows:

Work Around Options:
--------------------------------
1) Don't GBAK and RESTORE you databases if you have external tables.

  An Unacceptable Solution

2) Drop your external table (not the actual file on disk) definition from the
    database before you GBAK.  Then RESTORE database and re-define
    the external table definition

Why it happens:

Example:

---------8< cut script 8<----------------------------
connect 18284.gdb user "sysdba" password "masterkey";
commit;

drop database;
commit;

create database "18284.gdb" user "sysdba" password "masterkey";
commit;

create table intern_tbl (
  a_num integer,
  a_char char(10),
  a_vchar varchar(20));
commit;

create table extern_tbl external file "d:temp18284_ext.txt" (
  ext_num char(10),
  ext_char char(10),
  ext_vchar char(20));
commit;

/*
 * insert 5 records into the internal table 
 */

insert into intern_tbl (a_num, a_char, a_vchar)
  values (1, "first", "the first record");

insert into intern_tbl (a_num, a_char, a_vchar)
  values (2, "second", "the second record");

insert into intern_tbl (a_num, a_char, a_vchar)
  values (3, "third", "the third record");

insert into intern_tbl (a_num, a_char, a_vchar)
  values (4, "fourth", "the fourth record");

insert into intern_tbl (a_num, a_char, a_vchar)
  values (5, "fifth", "the fifth record");
commit;

select * from intern_tbl;
commit;

select * from extern_tbl;
commit;

insert into extern_tbl (
  ext_num, 
  ext_char, 
  ext_vchar) 
select 
  cast(a_num as char(10)),
  cast(a_char as char(10)),
  cast(a_vchar as char(20))
from intern_tbl;
commit;

select * from extern_tbl;
commit;
---------8< cut script 8<----------------------------

When you run the script you get the following results.

Database:  18284.gdb, User: sysdba
Records affected: 1
Records affected: 1
Records affected: 1
Records affected: 1
Records affected: 1

          1	first      	the first record     
          2	second     the second record    
          3	third      	the third record     
          4	fourth     	the fourth record    
          5	fifth      	the fifth record     

Records affected: 5
Records affected: 0
Records affected: 5

1          first      	the first record     
2          second     	the second record    
3          third      	the third record     
4          fourth     	the fourth record    
5          fifth      	the fifth record     

Records affected: 5

GBAK and RESTORE and SELECT you get:

select * from intern_tbl;

          1 first      	the first record     
          2 second     	the second record    
          3 third      	the third record     
          4 fourth     	the fourth record    
          5 fifth      	the fifth record     

Records affected: 5

select * from extern_tbl;

record     the first  		1         first      
 record    the second		2         second     
record     the third  		3         third      
 record    the fourth 		4         fourth     
record     the fifth  		5         fifth      

Records affected: 5

OOOOOOOOOOOOOOOPs!!!

While it appears as though GBAK is what breaks the external file usage,
it is actually the server itself.  There is a system table named: 
[RDB$RELATION_FIELDS] which contains three fields that pertain 
to this issue.

  RDB$FIELD_NAME
  RDB$RELATION_NAME
  RDB$FIELD_POSITION

Here is a query you can run against your database.  If you run it against 
the db before gbak and after restore you will find the following
results:

select cast(rdb$relation_name as char(20)), 
  cast(rdb$field_name as char(20)), 
  cast(rdb$field_position as char(20)) 
from rdb$relation_fields 
where rdb$relation_name not starting with "RDB$";

Output:
=======

original database system table stuff 

INTERN_TBL           A_NUM                	0                    
INTERN_TBL           A_CHAR              	1                    
INTERN_TBL           A_VCHAR            	2                    
EXTERN_TBL           EXT_NUM              	0                    
EXTERN_TBL           EXT_CHAR             	1                    
EXTERN_TBL           EXT_VCHAR            	2                    

database system table stuff after gbak and restore 
                                                               
INTERN_TBL           A_NUM                	0                    
INTERN_TBL           A_VCHAR              	2                    
INTERN_TBL           A_CHAR               	1                    
EXTERN_TBL           EXT_VCHAR            	2                    
EXTERN_TBL           EXT_CHAR             	1                    
EXTERN_TBL           EXT_NUM              	0                    

RDBMS are built on the princple of relational algebra which
does not apply ordering to sets.  When the gbak -restore process
writes the records to the table, the rows are not guaranteed to 
be ordered as they were prior to the database being backed up.

When we obtain table 'field' data from the system tables for 
external tables we _do_not_ sort by field position, like we do 
with internal tables. The data stored in the external file is very
much reliant on the ordering of the columns.  Because the 
columns are NOT written back to the system table in the same 
order, your data becomes misaligned.

Ref Bug#8883

Last Modified: 27-OCT-00