Help me wrap my head around storing data in databases and arrays.
lazarus / 16-Databases

----------------------------------------------------------------------
tinytim33 · 2015-01-14 18:35

Hi, I have been fairly comfortable on working with Arrays and Arrays
of Records up until now. I have also been learning the Insertion Sort
and Binary Search and am very happy applying these to arrays. BUT, my
own learning in Pascal is taking me to databases and I'm now down a
path wondering, how to do the stuff i did to arrays...but to a
database table?  I believe i get a record/data- set as the result of
an SQL query but am lost as to how i could then apply, say my
Insertion Sort, to a dataset. I mean, dataset isn't even a datatype is
it?  Or, if i wanted to apply my Insertion Sort, do i move the data
from the data/record-set into an array and carry on as normal?   I am
not expecting a low level answer (e.g. the code)...but just a general
overview.

I know there's simpler ways around this, but I am learning the
concepts and following my own tutorials. 

Any response, specific to this example, would be much appreciated.
thanks

----------------------------------------------------------------------
User137 · 2015-01-14 21:01

Have you studied SQL for starters? Taking time on that can answer many
of your questions before trying to adapt the knowledge into pascal.
The difference is that with databases you'll no longer be saving whole
records into file as records, but as each individual variable types.
You can do everything with SQL string commands, but there are helpful
functions in the sql classes that you can do them easier. But i can't
explain these better than the dedicated Lazarus tutorials around.

----------------------------------------------------------------------
jshand2010 · 2015-01-14 23:17

Hi there,

the way got this to work was to create procedures and functions in a
separate file including all variables needed for your application:
please note.  your database has to be set up correctly before this
will work.  you need to add the postgres, sqlquery, sqltrans and
tdatasource modules to your form and enter the settings first.

type
  UserLogin=record
     username, password: string[30];
  end;

var
  ul: array of UserLogin;

  a, d: integer;
 
procedure clearul(r: integer);

procedure loadlogintb(p: TPQConnection; q: TSQLQuery; t:
TSQLTransaction; ds: TDatasource); //for this to work, you must set
your settings in the gui sql modules
procedure savelogintb(p: TPQConnection; q: TSQLQuery; t:
TSQLTransaction; ds: TDatasource);

implementation

procedure cleanul(r: integer);
begin
  SetLength(ul, r);
end;

procedure loadlogintb(p: TPQConnection; q: TSQLQuery; t:
TSQLTransaction; ds: TDatasource); //for this to work, you must set
your settings in the gui sql modules
begin
  a:=0;
  clearul(a);

  try
    if (p.connected=true) then begin
      q.close;
      q.sql.clear;
      q.sql.add('select * from login;');
      q.execsql;
      q.lase;

      a:=q.recordcount; //get the total number of records in database
      clearul(a);  //sets memory up for data
     
      q.first

      if (a>=1) then begin //this is to prevent memory overflow
         for d:=1 to a do begin
             //this is taking the data from the database and send it
straight to memory as a copy
             ul[d-1].username:=q.fields[0].asstring;
             ul[d-1].password:=q.fields[1].asstring;
             q.next; //do not forget this as you will enter into a
endless loop
         end; 

         q.close;
      end;
    end;
  except
  end;
end;

procedure savelogintb(p: TPQConnection; q: TSQLQuery; t:
TSQLTransaction; ds: TDatasource);
begin
   if (a>=1) then begin
      for d:=1 to a do begin
         q.close;
         q.sql.clear;
         q.sql.add('insert into login values (:username,
:password);');
         q.params.parambyname('username').asstring:=ul[d-1].username;
         q.params.parambyname('password').asstring:=ul[d-1].password;
         q.execsql;
      end;
     t.commit;
     q.close;
   end;
end;

i hope this will help

Quote from: tinytim33 on January 14, 2015, 07:35:36 pmHi, I have been
fairly comfortable on working with Arrays and Arrays of Records up
until now. I have also been learning the Insertion Sort and Binary
Search and am very happy applying these to arrays. BUT, my own
learning in Pascal is taking me to databases and I'm now down a path
wondering, how to do the stuff i did to arrays...but to a database
table?  I believe i get a record/data- set as the result of an SQL
query but am lost as to how i could then apply, say my Insertion Sort,
to a dataset. I mean, dataset isn't even a datatype is it?  Or, if i
wanted to apply my Insertion Sort, do i move the data from the
data/record-set into an array and carry on as normal?   I am not
expecting a low level answer (e.g. the code)...but just a general
overview.

I know there's simpler ways around this, but I am learning the
concepts and following my own tutorials. 

Any response, specific to this example, would be much appreciated.
thanks