* * *

Author Topic: Help with database data import to an array  (Read 1830 times)

teochris

  • Jr. Member
  • **
  • Posts: 86
Help with database data import to an array
« 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  [Select]
  1.     FireBirdDataBase.SQLQuery1.Close;
  2.     FireBirdDataBase.SQLQuery1.SQL.Text := 'select Protos from results where AA=1';
  3.     FireBirdDataBase.DBConnection.Connected := True;
  4.     FireBirdDataBase.SQLTransaction1.Active := True;
  5.     FireBirdDataBase.SQLQuery1.Open;
  6.     Testprint_results.caption := FireBirdDataBase.SQLQuery1.FieldByName('Protos').AsString;
  7.     FireBirdDataBase.SQLQuery1.Close;
  8.     FireBirdDataBase.SQLTransaction1.Active := False;
  9.     FireBirdDataBase.DBConnection.Connected := False;
  10.  

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]
« Last Edit: March 09, 2016, 11:47:31 pm by teochris »

teochris

  • Jr. Member
  • **
  • Posts: 86
Re: Help with database data import to an array
« Reply #1 on: March 09, 2016, 06:17:51 pm »
My lazarus version is 1.6
fpc 3.0.0

taazz

  • Hero Member
  • *****
  • Posts: 3711
Re: Help with database data import to an array
« Reply #2 on: March 10, 2016, 12:51:48 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  [Select]
  1.     FireBirdDataBase.SQLQuery1.Close;
  2.     FireBirdDataBase.SQLQuery1.SQL.Text := 'select Protos from results where AA=1';
  3.     FireBirdDataBase.DBConnection.Connected := True;
  4.     FireBirdDataBase.SQLTransaction1.Active := True;
  5.     FireBirdDataBase.SQLQuery1.Open;
  6.     Testprint_results.caption := FireBirdDataBase.SQLQuery1.FieldByName('Protos').AsString;
  7.     FireBirdDataBase.SQLQuery1.Close;
  8.     FireBirdDataBase.SQLTransaction1.Active := False;
  9.     FireBirdDataBase.DBConnection.Connected := False;
  10.  

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]
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  [Select]
  1.     FireBirdDataBase.SQLQuery1.Close;
  2.     FireBirdDataBase.SQLQuery1.SQL.Text := 'select AA, Date, Protos, Defteros, Tritos, Tetartos, Pemptos, Bonus from results where AA=1';
  3.     FireBirdDataBase.DBConnection.Connected := True;
  4.     FireBirdDataBase.SQLTransaction1.Active := True;
  5.     FireBirdDataBase.SQLQuery1.Open;
  6.     FireBirdDataBase.SQLQuery1.first;
  7.     repeat
  8.       Testprint_results.Protos  := FireBirdDataBase.SQLQuery1.FieldByName('Protos').AsString; // you can also use FireBirdDataBase.SQLQuery1.Fields[2].AsString;
  9.       Testprint_results.Defteros := FireBirdDataBase.SQLQuery1.FieldByName('Defteros').AsString; // you can also use FireBirdDataBase.SQLQuery1.Fields[3].AsString;
  10.       Testprint_results.Tritos  := FireBirdDataBase.SQLQuery1.FieldByName('Tritos').AsString; // you can also use FireBirdDataBase.SQLQuery1.Fields[4].AsString;
  11.       Testprint_results.Tetartos := FireBirdDataBase.SQLQuery1.FieldByName('Tetartos').AsString; // you can also use FireBirdDataBase.SQLQuery1.Fields[5].AsString;
  12.       Testprint_results.Pemptos  := FireBirdDataBase.SQLQuery1.FieldByName('Pemptos').AsString;  // you can also use FireBirdDataBase.SQLQuery1.Fields[6].AsString;
  13.       Testprint_results.AA  := FireBirdDataBase.SQLQuery1.FieldByName('AA').AsString; // you can also use FireBirdDataBase.SQLQuery1.Fields[0].AsString;
  14.       Testprint_results.Date  := FireBirdDataBase.SQLQuery1.FieldByName('Date').AsString; // you can also use FireBirdDataBase.SQLQuery1.Fields[1].AsString;
  15.       FireBirdDataBase.SQLQuery1.Next; //<-More records to read?
  16.     until FireBirdDataBase.SQLQuery1.EOF;
  17.     FireBirdDataBase.SQLQuery1.Close;
  18.     FireBirdDataBase.SQLTransaction1.Active := False;
  19.     FireBirdDataBase.DBConnection.Connected := False;
  20.  
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

teochris

  • Jr. Member
  • **
  • Posts: 86
Re: Help with database data import to an array
« Reply #3 on: March 12, 2016, 10:52:35 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.

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.

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus