Help with database data import to an array

Forum > Database

(1/1)

teochris:
Hi, I need your help - I have a firebird database (I guess it does not matter that it is a firebird database or another type of database) and I want to import some data of the database during program execution to an array.

at the moment i have followed the example in this lazarus tutorial http://wiki.freepascal.org/SQLdb_Tutorial1

and in my app I am using this code to extract data from database

--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} --- FireBirdDataBase.SQLQuery1.Close; FireBirdDataBase.SQLQuery1.SQL.Text := 'select Protos from results where AA=1'; FireBirdDataBase.DBConnection.Connected := True; FireBirdDataBase.SQLTransaction1.Active := True; FireBirdDataBase.SQLQuery1.Open; Testprint_results.caption := FireBirdDataBase.SQLQuery1.FieldByName('Protos').AsString; FireBirdDataBase.SQLQuery1.Close; FireBirdDataBase.SQLTransaction1.Active := False; FireBirdDataBase.DBConnection.Connected := False;
By this way that I use, I copy only 1 database cell at a time and I believe this takes much longer than to read more database cells at once and send them in an array in my program for further calculations. I have very small database and lazarus knowledge and i can not find it out myself.

My database data looks like this

(table is called results and some Fields are)
AA       Date     Protos Defteros Tritos Tetartos     Pemptos     Bonus
(some example data can be seen below)
1     16.11.1997     32     18     12     5     20     2
2     21.11.1997     13     21     9     17     45     6

Can you please give me an example according above, how can I read data from many database cells at once ( Protos, Defteros, Tritos, Tetartos, Pemptos) lets say where AA=1 and AA=2 and copy them to an array.
I mean i need in this example array

var
pkas : array[1..3000,1..5] of 1..45;

I mean I need my array to have these values after reading the database

pkas[1,32] pkas[1,18] pkas[1,12] pkas[1,5] pkas[1,20]
pkas[2,13] pkas[2,21] pkas[2,9] pkas[2,17] pkas[2,45]

teochris:
My lazarus version is 1.6
fpc 3.0.0

taazz:

--- Quote from: teochris on February 20, 2016, 11:29:25 am ---Hi, I need your help - I have a firebird database (I guess it does not matter that it is a firebird database or another type of database) and I want to import some data of the database during program execution to an array.

at the moment i have followed the example in this lazarus tutorial http://wiki.freepascal.org/SQLdb_Tutorial1

and in my app I am using this code to extract data from database

--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} --- FireBirdDataBase.SQLQuery1.Close; FireBirdDataBase.SQLQuery1.SQL.Text := 'select Protos from results where AA=1'; FireBirdDataBase.DBConnection.Connected := True; FireBirdDataBase.SQLTransaction1.Active := True; FireBirdDataBase.SQLQuery1.Open; Testprint_results.caption := FireBirdDataBase.SQLQuery1.FieldByName('Protos').AsString; FireBirdDataBase.SQLQuery1.Close; FireBirdDataBase.SQLTransaction1.Active := False; FireBirdDataBase.DBConnection.Connected := False;
By this way that I use, I copy only 1 database cell at a time and I believe this takes much longer than to read more database cells at once and send them in an array in my program for further calculations. I have very small database and lazarus knowledge and i can not find it out myself.

My database data looks like this

(table is called results and some Fields are)
AA Date Protos Defteros Tritos Tetartos Pemptos Bonus
(some example data can be seen below)
1 16.11.1997 32 18 12 5 20 2
2 21.11.1997 13 21 9 17 45 6

Can you please give me an example according above, how can I read data from many database cells at once ( Protos, Defteros, Tritos, Tetartos, Pemptos) lets say where AA=1 and AA=2 and copy them to an array.
I mean i need in this example array

var
pkas : array[1..3000,1..5] of 1..45;

I mean I need my array to have these values after reading the database

pkas[1,32] pkas[1,18] pkas[1,12] pkas[1,5] pkas[1,20]
pkas[2,13] pkas[2,21] pkas[2,9] pkas[2,17] pkas[2,45]

--- End quote ---
You do not need any array, sqlquery manages the data internally you simple run a query to return what ever you need. Extending your example.

--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} --- FireBirdDataBase.SQLQuery1.Close; FireBirdDataBase.SQLQuery1.SQL.Text := 'select AA, Date, Protos, Defteros, Tritos, Tetartos, Pemptos, Bonus from results where AA=1'; FireBirdDataBase.DBConnection.Connected := True; FireBirdDataBase.SQLTransaction1.Active := True; FireBirdDataBase.SQLQuery1.Open; FireBirdDataBase.SQLQuery1.first; repeat Testprint_results.Protos := FireBirdDataBase.SQLQuery1.FieldByName('Protos').AsString; // you can also use FireBirdDataBase.SQLQuery1.Fields[2].AsString; Testprint_results.Defteros := FireBirdDataBase.SQLQuery1.FieldByName('Defteros').AsString; // you can also use FireBirdDataBase.SQLQuery1.Fields[3].AsString; Testprint_results.Tritos := FireBirdDataBase.SQLQuery1.FieldByName('Tritos').AsString; // you can also use FireBirdDataBase.SQLQuery1.Fields[4].AsString; Testprint_results.Tetartos := FireBirdDataBase.SQLQuery1.FieldByName('Tetartos').AsString; // you can also use FireBirdDataBase.SQLQuery1.Fields[5].AsString; Testprint_results.Pemptos := FireBirdDataBase.SQLQuery1.FieldByName('Pemptos').AsString; // you can also use FireBirdDataBase.SQLQuery1.Fields[6].AsString; Testprint_results.AA := FireBirdDataBase.SQLQuery1.FieldByName('AA').AsString; // you can also use FireBirdDataBase.SQLQuery1.Fields[0].AsString; Testprint_results.Date := FireBirdDataBase.SQLQuery1.FieldByName('Date').AsString; // you can also use FireBirdDataBase.SQLQuery1.Fields[1].AsString; FireBirdDataBase.SQLQuery1.Next; //<-More records to read? until FireBirdDataBase.SQLQuery1.EOF; FireBirdDataBase.SQLQuery1.Close; FireBirdDataBase.SQLTransaction1.Active := False; FireBirdDataBase.DBConnection.Connected := False;

teochris:

--- Quote from: taazz on March 10, 2016, 12:51:48 am ---You do not need any array, sqlquery manages the data internally you simple run a query to return what ever you need. Extending your example.

--- End quote ---

Thanks for the example!

But I noticed that whenever I need thousands of comparisons between database data it takes much longer than when i do these comparisons with arrays!

I am thinking of placing the database file in my webserver so I will not have to send to all users of my app the newest database file all the time and I guess this will cause extra delays while program execution.

Navigation

[0] Message Index