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 --------------------------------------------------------------------------
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.
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.
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.
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:
---------------------------------------------------------------------------------
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.
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:
------------------------------------------------------------------------
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.
8. How to execute the procedure from the script that converts the file
Just add the following lines at the end of the script:
--------------------------------------------------------------------------
***
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.
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
Appendix x. IBExpert import and export download files
- Full set of files (ZIP file)
- Individual 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) >>
