EXTERNAL TABLES IN Firebird

Hi,
After i knew there is not Bulk insert methods in FB.
I tried Prepared statements and also partial committing of transaction.
This saved me a lot of time but now even it is slow..
Then i heard that dumping the data into simple table and then creating ENTERNAL TABLE from it and then finally adding the data into database will be even more faster.
So i stared, and got stuck ..
I did it like this...

I'm developing an application in C#.net

TextWriter trwiter = new StreamWriter(@"C:\ExternalTables\Ext.ext", false, Encoding.ASCII);
for (int i = 0; i < 11; i++)
trwiter.WriteLine(i);
trwiter.Close();

Like this i dumped my stuffs in simple file named Ext.ext.
Then i created a database in FB and created an EXTERNAL TABLE like this:

command.CommandText = @"CREATE TABLE TEMP1 EXTERNAL FILE 'c:\ExternalTables\Ext.ext'(ID INTEGER,CRLF CHAR(2));";
int j=command.ExecuteNonQuery();

After doing this, when i fetched data from this table like this:
command.CommandText = "SELECT ID FROM TEMP1;";
FbDataReader reader= command.ExecuteReader();
while (reader.Read())
{
int iii = reader.Getint32(0);
}
And the outputs were:
822742320
856296754
889851188
923405622
...
..
and so on..
I dont know what am i missing..
Please help me...
I am in real need of this..

What does Ext.ext look like in a text editor and in a Hex editor?

In wordpad,
0
1
2
3
4
5
6
7
8
9
10

In Hex Editor
300D0A310D0A320D0A330D0A340D0A350D0A360D0A370D0A380D0A390D0A31300D0A

Any Clues...
Please Help

OK. So now we know you are creating the file as you intend.

Now what happens if you simply issue the SQL against the table in isql or some other database GUI like ibExpert?

If you get garbage then there is something wrong with the way you
are defining the external table in C#.

If it is not garbage, then there is something wrong with the way
you are reading the data in C#.

I am not a C# programmer, so I can only suggest ways you narrow your focus to the actual issue.

Clive.

Hi there,
I found where i was wrong..
It was all due to UNICODE..( damn, i should have thought of that previously)
Now i converted from UNICODE to ASCII char array, and dumped in the file. Then i fetched from file type casted and inserted it on my real table.
It worked but i still have to explore the performance.
Thanks for all the suggestions..
I will post the preformance crapps later.
Take Care
Bye

Okai, Now i'm really in trouble.
This insertion process is getting over my head now.

First i tried simple insertion, it was obviously too too slow.
Then i used prepared statements, things started getting little bit faster.
Then again i used prepared statements with Transaction.CommitRetaining() and it was beneficial too..

Than i heard about EXTERNAL TABLE and EXTERNAL Files.
Searched whole day about it, made it work and implemented it.
First i dumped my stuffs in a text file. Then i made table using this file as EXTERNAL File.
Then i dumped it from that table to my desired table (i removed index from table while dumping it and then recreated index.)
Now the problems were:
1. When i dump my things into text files. It requires hugh space. Nevermind i can manage space. But since all the data type have to be converted to CHAR types to store into EXTERNAL TABLE so i have to convert all my types into fixed length CHARs and then attach to external tables.
Now after i created the external table,
select * from ext_table
while(reader.read())
{
(a) getvalues
(b) typecast it
(c) insert into my real table.
}
This whole process is taking much time than expected.
Dumping to file happens to be fast. But dumping from external table to my database table is very slow.
2. Also deleting and recreating index consumes time.

Can anyone please suggest me any thing...
I've heard "EXTERNAL TABLES are the fastest way you can insert" but its behaving slow.
I must have missed something..
I am in real need of some advice from you people...
Thanks in advance...

Okai, Finally i fgured out how to do the thing but got stuck in another error..

I did it like this:

1. Create EXTERNAL TABLE (TEMP1) with the file (dump.txt) i have dumped.
2. Then i inserted values in my original table (tb1) like
INSERT INTO tb1 SELECT col1,col2 .. from TEMP1;
But while executing this query i got following problem after few minutes....
I/O error for file fseek "D:\TEMP\dump.txt" Error while trying to open file.

Info: My dump.txt file is almost 3 GB.

Is it memory issue i mean.. due to low memory.
Or is it something else..

Hehe i found the solution that there is no solution..
The maximum external file size supported by FB is 2GB
Thanks anyways to all..

Is there no way you can split the data into multiple files of less than 2GB each?

Clive

Yes i can split it.. and i am doing it right now..
But guess it will take some additional steps.. but its acceptable..
I have deployed it successfully, and testing its performance..
Lets see that will happen..
Anyways, thanks for all the concern.
Quite Active and Excellent Forum,this is, i must admit
Take Care

Hi

How did you split the external table in several files? I'm using a external table but it is almot 2G and I need to add more records.

I'm triying but I don't find the command to do it. I used a commnand similar to the one I use to split database in several files.

Thanks