Recent

Author Topic: [SOLVED] firebird - fill table  (Read 10800 times)

scons

  • Full Member
  • ***
  • Posts: 141
[SOLVED] firebird - fill table
« on: June 30, 2017, 12:28:08 am »
Hi all

I went on to my next step in "learning how to program": make use of a database.

Right now I am playing with Firebird 3. So far, I managed to create a database and tables by code on a server. But now I need to fill a table with data to use this data.
The data is contained in a csv file. So far I had no luck with finding a solution that does this by code.

I have found things like XMLwizard, which works fine, but I want this step to be done by the application, not by the program user.

Is this possible ? Load a csv by code ?

Thanks
« Last Edit: July 12, 2017, 04:11:40 pm by scons »
Windows 10-64bit Lazarus 2.0.12 + FPC 3.2.0

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: firebird - fill table
« Reply #1 on: June 30, 2017, 02:18:56 am »
Start understanding options: http://wiki.freepascal.org/CSV and http://wiki.lazarus.freepascal.org/FPSpreadsheet.

There are many solutions. IMHO, one of the easiest is to open the CSV on a dataset and the Firebird table (any RDBMS as a matter of fact) on another. Then, read the CSV records and insert that information one by one on the table.

Another easy solution is to open the CSV using FPSpreadsheet and the Firebird table on a dataset. Then, read the CSV lines and insert that information one by one on the table.

Mando

  • Full Member
  • ***
  • Posts: 181
Re: firebird - fill table
« Reply #2 on: June 30, 2017, 10:25:33 am »
Hi.

If you have many records in csv, (>2000), insert in FB record by record it may take long time.

There is a best solution: using external tables in FB. But you must transform your data.

Read how do it here (https://firebird21.wordpress.com/2013/03/03/insertando-una-gran-cantidad-de-filas/)

The post is in spanish. Now, I have not time now, but later or perhaps tomorrow, I can publish a translation and other details that I have found when using this method by myself.

regards.

scons

  • Full Member
  • ***
  • Posts: 141
Re: firebird - fill table
« Reply #3 on: June 30, 2017, 11:32:52 am »
Thanks for your answers, reading to your suggestions as we speak.
Windows 10-64bit Lazarus 2.0.12 + FPC 3.2.0

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: firebird - fill table
« Reply #4 on: July 01, 2017, 04:18:26 am »
Another new option for you to try:
NoSQL created by Free Pascal
http://forum.lazarus.freepascal.org/index.php/topic,37391

kapibara

  • Hero Member
  • *****
  • Posts: 610
Re: firebird - fill table
« Reply #5 on: July 01, 2017, 05:00:56 am »
If you have many records in csv, (>2000), insert in FB record by record it may take long time.

I have not used Firebird, but with PostGres and SQLite something like 2000 records are inserted very fast. Provided that you don't commit the data until all records are posted. Why would FB be so much slower?
Lazarus trunk / fpc 3.2.2 / Kubuntu 22.04 - 64 bit

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: firebird - fill table
« Reply #6 on: July 01, 2017, 05:02:57 am »
If you have many records in csv ... using external tables in FB. But you must transform your data.

@Mando.
Using external tables in Firebird is an advanced feature used when you have DBA knowledge and DBA rights over the Firebird server.
Technically its files use a binary format, not a text format.
Usually, to worth its use, you should be importing/exporting millions, at least hundreds of thousands, of lines.

"External File Format
The 'row' format of the external table is fixed length. There are no field delimiters: both field and row boundaries are determined by maximum sizes, in bytes, of the field definitions. It is important to keep this in mind, both when defining the structure of the external table and when designing an input file for an external table that is to import data from another application. The ubiquitous “.csv” format, for example, is of no use as an input file and cannot be generated directly into an external file."
https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-ddl-tbl.html#fblangref25-ddl-tbl-external

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Re: firebird - fill table
« Reply #7 on: July 01, 2017, 05:08:47 am »
If you have many records in csv, (>2000), insert in FB record by record it may take long time.

I have not used Firebird, but with PostGres and SQLite something like 2000 records are inserted very fast. Provided that you don't commit the data until all records are posted. Why would FB be so much slower?

@kapibara
Firebird itself is not slow at all, but depending on your hardware, network and programming skills, things can get very frustrating.
« Last Edit: July 01, 2017, 05:11:03 am by valdir.marcos »

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: firebird - fill table
« Reply #8 on: July 02, 2017, 12:18:28 pm »
Quote from: mando
If you have many records in csv, (>2000), insert in FB record by record it may take long time.
There is a best solution: using external tables in FB. But you must transform your data.
What use is to load your csv without your application.
Valdir.marcos is right. To build a good parser it wil take seconds to load your data. Don't use memo and other visual things. This delays your import. 
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

Almir.Bispo

  • Jr. Member
  • **
  • Posts: 91
  • CSV Comp DB is the Best NoSQL
    • CSV Comp DB (NoSQL)
Re: firebird - fill table
« Reply #9 on: July 02, 2017, 06:38:30 pm »
With CSV Comp DB I dispense other databases (even Firebird). I see that any application, be it:
-WEB
-Desktop
-Internet of Things
Even using client / server architecture, we can create and manipulate data in a consistent way.
I will show an example of a search in a database where you use the CQL function built into Pascal.
The @PESQUISAR function will be used

Be the SALES table with millions of records (or documents):

ID; PRODUCT; SALES; SUM
0001;RICE;102;12000
0002;BEAN;20;9000

Code: Pascal  [Select][+][-]
  1. uses shellapi; //to run the Database engine
  2. procedure SelectByID(ID_data:string);
  3. var MyCQL:Tstringlist;
  4. begin
  5. MyCQL:=Tstringlist.create;
  6. //create statement (CQL embedded). "query=0" because ID FIELD is the "0" index of table header
  7. MyCQL.add( '{...\SALES ;@pesquisar;FIELD_ID;'+ ID_data + ';0;0;query=0;destino=0 }' );
  8. //save statement to input and execute database trigger (destop version)
  9. MyCQL.savetofile('...\inpout_comp.exe');
  10. shellexecute(handle,pchar('open'),pchar('...\CSV_PARSER.exe'),nil,nil,0);
  11. //wait transaction
  12. while not fileexists('...\lock.inf') begin end; //wait start transaction
  13. while  fileexists('...\lock.inf') begin end;   //wait end transaction,because "lock.inf" control the multi competition
  14. //clear and load results from *.que result tables
  15. MyCQL.clear;
  16. MyCQL.loadfromfile('....\SALES.que');
  17. Showmessage(MyCQL.text);
  18. MyCQL.free;
  19. end;
  20.  

So you can use it like this On Button click:
Code: Pascal  [Select][+][-]
  1. begin
  2. SelectByID(Edit1.text);
  3. end;
  4.  
Of course we can display in dataset and much more ...Are 53 functions
To know more:http://adltecnologia.blogspot.com
CSV Comp DB Developer {Pascal Lover}

scons

  • Full Member
  • ***
  • Posts: 141
Re: [SOLVED] firebird - fill table
« Reply #10 on: July 12, 2017, 04:23:42 pm »
Thanks for all the suggestions, really helpfull.

@ Almir.Bispo : interesting to see that there are more usefull (mostly unknown) features available in FPC, I might have a look at this later.

This is how I did it right now, without too much hassle (might be not the most optimal solution).

It turned out that the csv will always have the same format (same columns), only the content can be more or less (rows with data), and after some searching and thinking I came up with this strategy:

  • create the DB
  • create the tables
  • load csv in stringlist
  • parse stringlist
  • insert into table

Thanks again for everyone's input. It goes pretty fast and all is done in code now without the use any external program.
Windows 10-64bit Lazarus 2.0.12 + FPC 3.2.0

avra

  • Hero Member
  • *****
  • Posts: 2514
    • Additional info
Re: [SOLVED] firebird - fill table
« Reply #11 on: July 13, 2017, 09:10:10 am »
  • load csv in stringlist
  • parse stringlist
You are doing these steps manually. You can automate them and avoid stringlist and parsing by using TSdfDataset. Usage example can be found at https://bitbucket.org/avra/ct2laz/src in "ReplaceInFile" method of "mainform.pas".
ct2laz - Conversion between Lazarus and CodeTyphon
bithelpers - Bit manipulation for standard types
pasettimino - Siemens S7 PLC lib

Devstructor

  • New Member
  • *
  • Posts: 27
    • Devstructor.com - Lazarus Tutorials and more
Re: firebird - fill table
« Reply #12 on: July 13, 2017, 09:53:41 am »
If you have many records in csv, (>2000), insert in FB record by record it may take long time.

Btw, 2.000 records is nearly nothing. Like kapibara mentioned, you just need to import all records in one or a few transactions. Firebird 3.0 is really fast too. On my laptop, I can easily import more than 10.000 records per second using a csv parser.
http://www.devstructor.com    Devstructor.com - Lazarus Tutorials and more

scons

  • Full Member
  • ***
  • Posts: 141
Re: [SOLVED] firebird - fill table
« Reply #13 on: July 13, 2017, 01:56:24 pm »
You are doing these steps manually. You can automate them and avoid stringlist and parsing by using TSdfDataset. Usage example can be found at https://bitbucket.org/avra/ct2laz/src in "ReplaceInFile" method of "mainform.pas".

Thanks for this tip.

Unfortunately my knowledge of using the SDFdataset at his fullest power is not that far yet. Although I started with this dataset, I stopped beacuse there is not very much to find on how to use it for particular functions. But I think, personally, that it is probably is a very good, and fast dataset that could be used for a vast amount of "simple" tasks.
Windows 10-64bit Lazarus 2.0.12 + FPC 3.2.0

avra

  • Hero Member
  • *****
  • Posts: 2514
    • Additional info
Re: [SOLVED] firebird - fill table
« Reply #14 on: July 14, 2017, 10:17:06 am »
Unfortunately my knowledge of using the SDFdataset at his fullest power is not that far yet. Although I started with this dataset, I stopped beacuse there is not very much to find on how to use it for particular functions.
http://wiki.lazarus.freepascal.org/CSV#SDFDataset
http://wiki.lazarus.freepascal.org/TSdfDataSet
ct2laz - Conversion between Lazarus and CodeTyphon
bithelpers - Bit manipulation for standard types
pasettimino - Siemens S7 PLC lib

 

TinyPortal © 2005-2018