[SOLVED] firebird - fill table lazarus / 16-Databases ---------------------------------------------------------------------- scons · 2017-06-29 22:28 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 ---------------------------------------------------------------------- valdir.marcos · 2017-06-30 00:18 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 · 2017-06-30 08:25 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 · 2017-06-30 09:32 Thanks for your answers, reading to your suggestions as we speak. ---------------------------------------------------------------------- valdir.marcos · 2017-07-01 02:18 Another new option for you to try: NoSQL created by Free Pascal http://forum.lazarus.freepascal.org/index.php/topic,37391 ---------------------------------------------------------------------- fpcoder · 2017-07-01 03:00 Quote from: Mando on June 30, 2017, 10:25:33 amIf 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? ---------------------------------------------------------------------- valdir.marcos · 2017-07-01 03:02 Quote from: Mando on June 30, 2017, 10:25:33 amIf 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/fblangref 25-en/html/fblangref25-ddl-tbl.html#fblangref25-ddl-tbl-external ---------------------------------------------------------------------- valdir.marcos · 2017-07-01 03:08 Quote from: kapibara on July 01, 2017, 05:00:56 amQuote from: Mando on June 30, 2017, 10:25:33 amIf 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. ---------------------------------------------------------------------- mangakissa · 2017-07-02 10:18 Quote from: mandoIf 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. ---------------------------------------------------------------------- Almir.Bispo · 2017-07-02 16:38 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i uses shellapi; //to run the Database engineprocedure SelectByID(ID_data:string);var MyCQL:Tstringlist;beginMyCQL:=Tstringlist.create;//create statement (CQL embedded). "query=0" because ID FIELD is the "0" index of table headerMyCQL.add( '{...\SALES ;@pesquisar;FIELD_ID;'+ ID_data + ';0;0;query=0;destino=0 }' );//save statement to input and execute database trigger (destop version)MyCQL.savetofile('...\inpout_comp.exe ');shellexecute(handle,pchar('open'),pchar('...\CSV_PARSER.exe'),nil,n il,0);//wait transactionwhile not fileexists('...\lock.inf') begin end; //wait start transactionwhile  fileexists('...\lock.inf') begin end;   //wait end transaction,because "lock.inf" control the multi competition//clear and load results from *.que result tablesMyCQL.clea r;MyCQL.loadfromfile('....\SALES.que');Showmessage(MyCQL.text);MyCQL.f ree;end;  So you can use it like this On Button click: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i beginSelectByID(Edit1.text);end; Of course we can display in dataset and much more ...Are 53 functions To know more:http://adltecnologia.blogspot.com ---------------------------------------------------------------------- scons · 2017-07-12 14:23 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 DBcreate the tablesload csv in stringlistparse stringlistinsert 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. ---------------------------------------------------------------------- avra · 2017-07-13 07:10 Quote from: scons on July 12, 2017, 04:23:42 pmload csv in stringlistparse stringlistYou 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". ---------------------------------------------------------------------- Devstructor · 2017-07-13 07:53 Quote from: Mando on June 30, 2017, 10:25:33 amIf 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. ---------------------------------------------------------------------- scons · 2017-07-13 11:56 Quote from: avra on July 13, 2017, 09:10:10 amYou 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. ---------------------------------------------------------------------- avra · 2017-07-14 08:17 Quote from: scons on July 13, 2017, 01:56:24 pmUnfortunately 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 ---------------------------------------------------------------------- scons · 2017-07-14 08:40 Yes, I should investigate some time for this. Just did a test with a bigger csv file. Load csv file in Firebird 3 (with my silly parsing code, and over wifi network) Code: Text  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i [load csv in DB]...[START] - 10:28:38.537[END]   - 10:32:39.658[LOADED ROWS]- 21435 Load that same file in an sdfdataset Code: Text  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i [load csv]...[START] - 10:36:13.313[END]   - 10:36:13.348[LOADED ROWS]- 21435[show in grid]...[START] - 10:36:13.350[END]   - 10:36:13.363 Of course this is not the same test but is just shows how amazingly fast this SDFDataset is. ---------------------------------------------------------------------- mangakissa · 2017-07-15 06:44 @avra, sdfdataset is nice, but after you read the CSV into it, you still have to make an import function to the database. The way scons handle the CSV is exactly the same way ---------------------------------------------------------------------- avra · 2017-07-15 09:38 Quote from: mangakissa on July 15, 2017, 08:44:04 amsdfdataset is nice, but after you read the CSV into it, you still have to make an import function to the database.Quite true. I didn't say that importing data into Firebird is automatic. QuoteThe way scons handle the CSV is exactly the same wayNot quite. I think that using something like Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i ThirdColumnStrData := MyCSV.Fields[3].AsString;is more readable and less work then code that manually extracts column from a line in StringList. Besides, having a dataset also enables nice preview of data in a grid if needed.