Import & export using IBExpert (1)

<< | Database technology articles | Import and export using IBExpert (2) >>


Import & export using IBExpert (1)


Basics for this tutorial

We read a lot about people searching in the online forums for faster Firebird import or export operations. So we have decided to show the reader ways to improve the speed using some perhaps not so well-known technologies.

All files are included in the attached zip file, so you do not need to copy all objects from this text. The demo mentions some IBExpert tools and technologies, but it can also be used with other tools like isql; however they are - in most cases - not as comfortable.

We have used IBExpert 2011.04.03 Developer Studio in this article, available here: order online and Firebird 2.5, available from www.firebirdsql.org. The attached database file db1.fdb can be registered in your IBExpert IDE after installing Firebird and IBExpert.

1. Prepare firebird.conf

Before we can use the external file technology provided by Firebird, we need to change the firebird.conf file, which is located in the firebird main directory. Open the file with a text editor and change the line with ExternalFileAcess to use the directory, where you wish to have the tutorial files. Important: Remove the comment sign # at the beginning; otherwise it will still be ignored.

 --------------------------------------------------------------------------

 # External File Paths/Directories
 #
 # ExternalFileAccess may be None, Full or Restrict. If you choose
 # Restrict, provide ';'-separated trees list, where external files
 # are stored. Relative paths are treated relative to RootDirectory entry
 # (see above). Default value 'None' disables any use of external files
 # on your site. To specify access to specific trees, enum all required
 # paths (for Win32 this may be something like 'C:\ExternalTables',
 # for unix - '/db/extern;/mnt/extern').
 #
 # NOTE: THE EXTERNAL TABLE ENGINE FEATURE COULD BE USED TO COMPROMISE
 # THE SERVER/HOST AS WELL AS DATABASE SECURITY!!
 #
 # IT IS STRONGLY RECOMMENDED THAT THIS SETTING BE USED TO LIMIT
 # EXTERNAL TABLE LOCATIONS!
 #
 # Type: string (special format)
 #
 ExternalFileAccess = D:\IBEImportExport_Tutorial

 --------------------------------------------------------------------------

back to top of page

2. Restart the Firebird service

Restart Firebird service to be sure that the firebird.conf file is now used with the changed parameter.

3. Register and open the database in IBExpert

Register and open the database in IBExpert and have a look at the target table:

 CREATE TABLE PRODUCT (
     ID           NUMERIC(18,0) NOT NULL,
     CATEGORY_ID  NUMERIC(18,0) NOT NULL,
     TITLE        VARCHAR(50) NOT NULL,
     ACTOR        VARCHAR(50) NOT NULL,
     PRICE        NUMERIC(12,2) NOT NULL,
     SPECIAL      SMALLINT
 );

This is a very basic structure purely for the purposes of this tutorial.

back to top of page

4. Create an import table

Create an import table that has the same structure, but replace all binary columns by char columns, to be able to import readable text files. If you have Windows Fileformat, reserve two bytes for Carriage Return Line Feed CRLF. On Linux it is only one byte.

In the demo database the import table has already been created. If you need it on a different path, first delete the procedure sp_import_product and then delete the table import_product.

All steps necessary to recreate the objects again are taken within the next lines.

Here is the source code to create the import table, this can be executed in the IBExpert SQL Editor in the Tools menu:

 CREATE TABLE IMPORT_PRODUCT external file 'D:\IBEImportExport_Tutorial\FixedFormatData.txt' (
     ID           CHAR(18),
     CATEGORY_ID  CHAR(18),
     TITLE        CHAR(50),
     ACTOR        CHAR(50),
     PRICE        CHAR(12),
     SPECIAL      CHAR(5),
     CRLF         CHAR(2)
 );

The external file part is followed by a file name, which is used to store the table data outside the database file. An external table can be used for insert and select statements, delete and update operations are not supported.

back to top of page

5. Data to be imported

Have a look at the data to be imported. In this example we have used a CSV file with 100,000 lines to see the speed of this technology in the file rawdata.csv.

 ID;CATEGORY_ID;TITLE;ACTOR;PRICE;SPECIAL
 1;11;CLOSER TYCOON NATURAL;DORIS DAVIS,  AL WAHLBERG;26.31;0
 2;7;OPERATION ELEMENT HANOVER;JON MOSTEL,  DREW DICAPRIO;28.01;0
 3;9;ELEPHANT RESURRECTION GOODFELLAS;PENELOPE BULLOCK,  CHRIS STREEP;17.73;0
 4;13;PEAK BAREFOOT DUFFEL;RAY SUVARI,  TOM TAUTOU;23.96;0
 5;10;SAINTS TEXAS ENOUGH;SHIRLEY SANDLER,  ELLEN LOREN;25.40;0
 6;14;WARLOCK ANGELS VICTORY;HARRISON WOOD,  RIVER ZELLWEGER;14.82;0
 ..........

This is a CSV format, which cannot directly be used for an external file, since it has no fixed length for each column. Important: you will not be able to open this in Excel, because when Excel opens a CSV file with "ID;" as the first characters, it thinks that this is a different format. OpenOffice or LibreOffice do not have this problem. Also, older Excel versions are unable to open any file with more than 65k lines. So using Excel is definitely a somewhat limited method to check if your data is correct.

back to top of page

6. Convert the CSV to a fixed format

There are several ways to convert a CSV file into the external file format, which can be used in Firebird. As an example you can see here a script based on IBExpert IBEBlock technology, that does exactly this job, with some comments. Important: IBEBlock commands are only supported in the IBExpert Developer Studio full versions, not in the IBExpert Personal Edition.

When you have registered the attached demo database in IBExpert, you can simply open the database, press [F12] to open the SQL Editor and copy the following part into it:

(ConvertCsvToFixed.sql)

 ---------------------------------------------------------------------------------
 execute ibeblock
 as
 --Declare the variables, in ibeblock this is optional, but sometimes useful
 DECLARE VARIABLE ID CHAR(18);
 DECLARE VARIABLE CATEGORY_ID CHAR(18);
 DECLARE VARIABLE TITLE CHAR(50);
 DECLARE VARIABLE ACTOR CHAR(50);
 DECLARE VARIABLE PRICE CHAR(12);
 DECLARE VARIABLE SPECIAL CHAR(5);
 declare variable Line char(153);
 begin
   i = 0;  --just a counter 
   StartTime=ibec_GetTickCount();   --remember the starting time
   --delete the Fixedformat file if it exists
   if (ibec_FileExists('D:\IBEImportExport_Tutorial\FIXEDFORMATDATA.TXT'))
   then ibec_DeleteFile('D:\IBEImportExport_Tutorial\FIXEDFORMATDATA.TXT');  

   --create a filehandle for the input file, in this case the CSV file, that we want to import
   RawData = ibec_fs_OpenFile('D:\IBEImportExport_Tutorial\RawData.csv', __fmOpenRead);
   --create a filehandle for the output file, create an empty file
   FixedData = ibec_fs_OpenFile('D:\IBEImportExport_Tutorial\FIXEDFORMATDATA.TXT', __fmCreate);
   ibec_fs_CloseFile(FixedData); --close and write the empty file
   --and reopen it for write operations
   FixedData = ibec_fs_OpenFile('D:\IBEImportExport_Tutorial\FIXEDFORMATDATA.TXT', __fmOpenWrite);

   --all is fine, now we start the processing
   if (not RawData is null) then
   begin
     s = ibec_fs_Readln(RawData);                        --read the first line to step over the captions
     while (not ibec_fs_Eof(RawData)) do                 --now start the loop for all lines in input
     begin
       s = ibec_fs_Readln(RawData);                      --read next line  
       ValCount =   ibec_ParseCSVLine(Vals, s, '', ';', __csvEmptyStringAsNull);  --copy data to an array
       ID=          ibec_Copy(Vals[0]+'                  ',1,18);                 --we know that we get 6 columns and all must be filled with spaces
       CATEGORY_ID= ibec_Copy(Vals[1]+'                  ',1,18);
       TITLE=       ibec_Copy(Vals[2]+'                                                  ',1,50);
       ACTOR=       ibec_Copy(Vals[3]+'                                                  ',1,50);
       PRICE=       ibec_Copy(Vals[4]+'            ',1,12);
       SPECIAL=     ibec_Copy(Vals[5]+'     ',1,5);
       line=ID+CATEGORY_ID+TITLE+ACTOR+PRICE+SPECIAL;                             --combine the columns with spaces, but without delimiter
       ibec_fs_Writeln(FixedData,line);                                           --write this new line to the output file
       i = i + 1;                                                                 --increment the counter 
       if (ibec_mod(i,1000)=0) then ibec_Progress(I);                             --every 1000 operations send a message to the screen
     end
     ibec_fs_CloseFile(RawData);                         --after loop has ended, close input and output file
     ibec_fs_CloseFile(FixedData);
   end
   EndTime=ibec_GetTickCount();
   TotalTime=EndTime-StartTime;
   ibec_Progress('finished in '||TotalTime||' ms');      --display the required time
   ibec_Pause(1000);                                     --when working in ibexpert, the result stays for 1 second on the line above the sql editor
 end
 ---------------------------------------------------------------------------------

After the script is loaded in the SQL Editor, execute it by pressing [F9].

And that's it. On my test machine this conversion took about 8 seconds.

What happens if you do not want to execute this in interactive mode, but in a batch mode? No problem, simply save the above script to file, for example as D:\IBEImportExport_Tutorial\ConvertCsvToFixed.sql and start it from a batch file with ibescript.exe, which can be found in the IBExpert main directory:

 C:\program Files\HK-Software\IBExpert\ibescript.exe D:\IBEImportExport_Tutorial\ConvertCsvToFixed.sql

IBExpert allows also execution of the same functionality using the ibescript.dll library directly from your own application, when you have IBExpert Distribution software].

The method using IBEBlock is only one of the solutions to convert the data from CSV to a fixed format; you can use any other programming tools to do the same job, more or less comfortable and fast.

When you want to learn more about the ibec_* functions, just go with the cursor in the IBExpert SQL Editor on any keyword and press [F1], the documentation will open automatically for this keyword.

back to top of page

7. Next steps

We now have the data prepared for use inside an external table. In IBExpert we can now open the Data page in the table IMPORT_PRODUCT. We see the data from the external file. So now we can think about what we have to do with the data. A stored procedure is a good choice. Here is an example, again with some comments:

(sp_import_product.sql)

 ------------------------------------------------------------------------

 create or alter procedure SP_IMPORT_PRODUCT
 as
 --declare the required variables
 declare variable ID char(18);   
 declare variable CATEGORY_ID char(18);
 declare variable TITLE char(50);
 declare variable ACTOR char(50);
 declare variable PRICE char(12);
 declare variable SPECIAL char(5);
 declare variable CNT integer;
 begin
   --start a for select  loop over all records in the import table and put the data into variables
   for
     select ID, CATEGORY_ID, TITLE, ACTOR, PRICE, SPECIAL
     from IMPORT_PRODUCT
     into :ID, :CATEGORY_ID, :TITLE, :ACTOR, :PRICE, :SPECIAL
   do
   begin
     --is this record already there? 
     select count(*) from product where product.id=:id into :cnt;
     if (cnt=0) then
     begin
       --no, so do an insert
       insert into PRODUCT (ID, CATEGORY_ID, TITLE, ACTOR, PRICE, SPECIAL)
       values (:ID, :CATEGORY_ID, :TITLE, :ACTOR, :PRICE, :SPECIAL);
     end
     else
     begin
       --yes, so update the record 
       update PRODUCT
       set CATEGORY_ID = :CATEGORY_ID,
           TITLE = :TITLE,
           ACTOR = :ACTOR,
           PRICE = :PRICE,
           SPECIAL = :SPECIAL
       where (ID = :ID);
     end
   end
   --thats all
 end

 ------------------------------------------------------------------------

How to create this procedure? Simply copy it again to IBExpert´s SQL Editor, execute it with [F9], commit your operation and you will see the procedure on the left side in the Database Explorer.

In the SQL Editor we executed the definition of the procedure, but the procedure itself was not executed. If you double-click on the entry in the Database Explorer, the procedure is opened in the Procedure Editor. When pressing [F9] here, it is executed. Don´t forget to commit your operation.

If it does not work as expected, click on the Debugger tool button or [F8] in the Stored Procedure editor and you can debug it using all important functionalities such as breakpoints, step in, step over, etc. If you have data, for example with the wrong format such as a wrong decimal, the best way is to check it in the first conversion step. However if you prefer it in the stored procedure, there are also several possibilities to avoid import errors.

I know that Firebird also has powerful commands like UPDATE OR INSERT, but this tutorial is only intended to give you some hints for the initial steps and approach, rather than a 1,000-page reference.

back to top of page

8. How to execute the procedure from the script that converts the file

Just add the following lines at the end of the script:

(ConvertCsvToFixed2.sql)

 --------------------------------------------------------------------------

     ***    
     end
     ibec_fs_CloseFile(RawData);                         --after loop has ended, close input and output file
     ibec_fs_CloseFile(FixedData);
   end 

   --this part is new
   ibec_Progress('Execute procedure sp_import_product');  --simply a hint on the screen
   DB = ibec_CreateConnection(__ctInterBase,              --define a connection to the database
                              'DBName="LOCALHOST:D:\IBEImportExport_Tutorial\DB1.FDB";
                              ClientLib=gds32.dll;
                              User=SYSDBA; Password=masterke; Names=NONE; SqlDialect=3;');
   use db;                                                --and open the connection
   execute procedure sp_import_product;                   --execute the procedure
   commit;                                                --commit the transaction
   ibec_CloseConnection(db);                              --close the connection
   --this part was new 

   EndTime=ibec_GetTickCount();
   TotalTime=EndTime-StartTime;
   ibec_Progress('finished in '||TotalTime||' ms');      --display the required time
   ibec_Pause(1000);                                     --when working in ibexpert, the result stays for 1 second on the line above the sql editor
 end

On my machine, the import took 21 seconds for processing 100,000 records, I used Firebird 2.5x64 on Windows 7x64.

back to top of page

9. Conclusion

You can also use several other methods to import data into a Firebird database, but try to avoid TTable or TDataset append methods when working with Delphi or CPPB to avoid wasting time. When doing an import from a client programming environment, for example Delphi, CPPB, VS, Java or any other, try to use active transaction control and prepared queries, otherwise it will be slow. Do NOT deactivate forced writes to improve the speed, because this can result in corrupt databases, when there is, for example, an endless loop in your import process and you have to restart the Firebird server. When using active transaction control, the speed should make much difference whether forced writes is on or off.

This example can also be easily altered to perform a similar export of internal data to external CSV format. In general, an import or export with more than 5,000 records per second are standard if you use the right technology. When you do not need to convert the data from another format to a fixed format, you can also copy between 20,000 and 50,000 records per second, depending on the structure etc.

© The author of this article is Holger Klemt, IBExpert Ltd, www.ibexpert.com, first published in April 2011

back to top of page

Appendix x. IBExpert import and export download files

Further reading:
Import & export using IBExpert (2)
Import data
Data export from the IBExpert Table Editor
Exporting query results
Creating CSV files
ODBC Viewer
Extract metadata
IBEBlock
ibec_ImportData
IBEBlock examples including data import and export
Firebird administration: import & export

back to top of page
<< | Database technology articles | Import and export using IBExpert (2) >>