Fragmented rows in firebird embedded mode

firebird java sql sqlbulkcopy

Question

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.

 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));
                }

And now, all of a sudden the rows on my table look like this:

::c@gmail.com

::abc2@gmail.

::m
abc3@gma

::.com
abc4@

::ail.com
ab

::@gmail.com


::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:

abc@gmail.com
abc2@gmail.com
abc3@gmail.com
abc4@gmail.com
abc5@gmail.com
abc6@gmail.com
abc7@gmail.com
abc8@gmail.com
abc9@gmail.com
abc10@gmail.com
sneciosup@hotmail.com

¿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.

Any help, will be appreciated.

Accepted Answer

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:

  1. 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
  2. you don't specify a separate column for your linebreaks, so your linebreaks will simply be part of the data which is read
  3. 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.


Popular Answer

could you provide also the example file ? seems that the way you prepare the query is wrong

maybe you can split the steps and check after each one , first the file reading , file insertion and then select from database

The main support lists if you want more help are Firebird Support and Firebird-Java

Firebird support lists



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why