Subject Re: [Firebird-Java] Fragmented rows in firebird embedded mode
Author Mark Rotteveel
See my response on Stackoverflow. The reason is that your use of
external files is incorrect.

My response on stackoverflow (http://stackoverflow.com/a/11810733/466862 ):
The external table file in Firebird is not just a plaintext file, it is
a fixed width format with special requirements to the content and
layout. See the Interbase 6.0 Data Definition Guide, page 107-111
(available for download from
http://www.firebirdsql.org/en/reference-manuals/ ) or The Firebird Book
by Helen Borrie page 281-287.

The problems I see right now are:

you declare the column in the external table to be VARCHAR(11),
while the shortest emailaddress in your file is 13 characters, the
longest is 21 characters, so Firebird will never be able to read a full
emailaddress from the file
you don't specify a separate column for your linebreaks, so your
linebreaks will simply be part of the data which is read
you have declared the column as VARCHAR, this requires the records
to have a very specific format, where the first two bytes declare the
actual data length followed by a string of that length (and even then it
only reads upto the declared length of the column). Either make sure you
follow the requirements for VARCHAR columns, or simply use the CHAR
datatype and make sure you pad the column with spaces upto the declared
length.

I am not 100% sure, but your default database characterset may also be
involved in how the data is read.


On 4-8-2012 18:11, joecs6 wrote:
> I was doing this code, and something weird has happened. I make a bulk insert from an external file. But the result it's just fragmented, or maybe corrupted.
>
> Code:
>
> cnx=factoryInstace.getConnection();
> pstmt = cnx.prepareStatement("DELETE FROM TEMPCELULAR");
> pstmt.executeUpdate();
> pstmt = cnx.prepareStatement("EXECUTE BLOCK AS BEGIN if (exists(select 1 from rdb$relations where rdb$relation_name = 'EXT_TAB')) then execute statement 'DROP TABLE EXT_TAB;'; END");
> pstmt.executeUpdate();
> pstmt = cnx.prepareStatement("CREATE TABLE EXT_TAB EXTERNAL '"+txtarchivoProcesar.getText()+"'(CELULAR varchar(11))");
> pstmt.executeUpdate();
> pstmt = cnx.prepareStatement("INSERT INTO TEMPCELULAR (CELULAR)SELECT CELULAR FROM EXT_TAB");
> pstmt.executeUpdate();
> pstmt = cnx.prepareStatement("SELECT CELULAR FROM TEMPCELULAR");
> ResultSet rs=pstmt.executeQuery();
> while(rs.next()){
> System.out.println("::"+rs.getString(1));
> }
> End of code.
>
>
> And now, all of a sudden the rows on my table look like this:
>
> ::c@...
>
> ::abc2@gmail.
>
> ::m
> abc3@gma
>
> ::.com
> abc4@
>
> ::ail.com
> ab
>
> ::@...
>
>
> ::bc6@gmail.c
>
> ::abc7@gmai
>
> ::com
> abc8@g
>
> ::il.com
> abc
>
> ::gmail.com
>
> ::c10@gmail.c
>
> ::
>
> The blank spaces between results were not made by me. This is the result as it is.
>
> Source file for external table (Plain .txt):
>
> abc@...
> abc2@...
> abc3@...
> abc4@...
> abc5@...
> abc6@...
> abc7@...
> abc8@...
> abc9@...
> abc10@...
> sneciosup@...
>
> ┬┐What's wrong with my code?
>
> I've haven't seen this wack results in years. The database is created of the users pc on the first run. Hence, while in production every time I run the program.
>
> This was done with Java 7 and Firebird Embedded Edition 2.5
>
> I' ve cheked every query, and it goes wrong in the part where I create the external table: "CREATE TABLE EXT_TAB EXTERNAL '"+txtarchivoProcesar.getText()+"'(CELULAR varchar(11))". The external table output varies according to the data type and it's length, so if you put varchar(300) or varchar(50) then the output will vary a lot. Gee, I mean one hour before my presentation with my boss this was working ok...
>
> Any help, will be appreciated.

--
Mark Rotteveel