Recent

Author Topic: Getting array of results from database query  (Read 5965 times)

jpandaconnor

  • New member
  • *
  • Posts: 7
Getting array of results from database query
« on: January 11, 2018, 10:17:01 pm »
Hi,

I was wondering if anyone has managed to figure out how to pass data back from a database query as some sort of object.

At the moment I can loop through the results like this

Code: Pascal  [Select][+][-]
  1. procedure Query_DoesInstallationExist();
  2. begin
  3.   Conn := TSQLConnector.Create(nil);
  4.   with Conn do
  5.   begin
  6.     ConnectorType := 'mysql 5.5';
  7.     HostName := 'HOST';
  8.     DatabaseName := 'DB';
  9.     UserName := 'USER';
  10.     Password := 'PASSWORD';
  11.     Transaction := TSQLTransaction.Create(Conn);
  12.  
  13.   end;
  14.  
  15.   Query := TSQLQuery.Create(nil);
  16.   Query.DataBase := Conn;
  17.  
  18.   Query.SQL.Text := 'SELECT * FROM awms_details';
  19.   Query.Open;
  20.  
  21.   while not Query.EOF do
  22.   begin
  23.        MessageDlg(IntToStr(Query.FieldByName('string').AsString), mtWarning, [],0);
  24.        Query.Next;
  25.   end;
  26.   // Query.Close;
  27.   // Query.Free;
  28.   // Conn.Free;
  29. end;                

Is there any way to return what I've queried here in an object or array, similar to the MVC layout where all the queries are in one place.

Thanks!

Leledumbo

  • Hero Member
  • *****
  • Posts: 8744
  • Programming + Glam Metal + Tae Kwon Do = Me
Re: Getting array of results from database query
« Reply #1 on: January 11, 2018, 11:42:23 pm »
Is there any way to return what I've queried here in an object or array, similar to the MVC layout where all the queries are in one place.
Use proper ORM/OPF, TDataset (as TSQLQuery ancestor) paradigm is that you can move around query result, but only one active record at a time.

jpandaconnor

  • New member
  • *
  • Posts: 7
Re: Getting array of results from database query
« Reply #2 on: January 15, 2018, 11:43:06 am »
Apologies I've just found your other post on here: https://forum.lazarus.freepascal.org/index.php?topic=38967.0 It has a few fantastic examples for those who have the same question as me.

I'll reply back the results

jpandaconnor

  • New member
  • *
  • Posts: 7
Re: Getting array of results from database query
« Reply #3 on: January 15, 2018, 12:01:15 pm »
I've looked into datasets but cannot understand them at all. Are they basically an object that will store the data from a SQL Query?

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: Getting array of results from database query
« Reply #4 on: January 15, 2018, 04:50:21 pm »
A dataset is an object. But that isn't what you want. Just like leledumbo said; look at a soluton like mORMot or tiOPF.
If you want to use MVC, the class must be created manually for using it.
There's no other way.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

Leledumbo

  • Hero Member
  • *****
  • Posts: 8744
  • Programming + Glam Metal + Tae Kwon Do = Me
Re: Getting array of results from database query
« Reply #5 on: January 15, 2018, 11:25:33 pm »
I've looked into datasets but cannot understand them at all. Are they basically an object that will store the data from a SQL Query?
No, you misunderstood my answer. I said "TDataset (as TSQLQuery ancestor)", hence I was pointing at what you were doing. You need to use proper ORM/OPF to get query results as plain objects instead. There are some existing solutions already, apart from mORMot (the ORM part, because it actually is a hell lot more complex than just an ORM) and tiOPF which are already mentioned by mangakissa, here are some others:

taazz

  • Hero Member
  • *****
  • Posts: 5368
Re: Getting array of results from database query
« Reply #6 on: January 16, 2018, 12:16:01 am »
I've looked into datasets but cannot understand them at all. Are they basically an object that will store the data from a SQL Query?
You can think of the dataset as an in memory database. Calling open will retreive all the records (sql defined) from a server and cache them locally after that you need to use the method first, next, previous, last, locate, find, filter etc to move the local cursor between records  when you want to change the data you call edit, you change the data and then call post, that saves the changes in the local cache after you have altered multiple records (or after the post is called) you can call ApplyUpdates to have the dataset create update sql commands for each record changed and execute them on the server making them permanent. You can only edit one record at a time and before the cursor is moved to a new record you must either post or cancel the current changes.
Each dataset has a list of fields that is filled dynamicaly when the query is "opened"or statically by you during the design of the application that describes  each field in the record and allow you to set some properties of that field like display label, max/min length/size, the data type etc, that centralizes the management of the information in a single place instead having it all over your forms/ dialogs. that is the fast and almost complete description of the dataset. It might fill a bit weird to use but once you have multiple "views" attached to a single dataset and see them change in real time as the data in the dataset change you might find it worthwhile to learn.
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

madref

  • Hero Member
  • *****
  • Posts: 949
  • ..... A day not Laughed is a day wasted !!
    • Nursing With Humour
Re: Getting array of results from database query
« Reply #7 on: January 16, 2018, 12:39:02 am »
Look alt this example from my post and see what happens and how its done.
https://forum.lazarus.freepascal.org/index.php/topic,39288.15.html
the question i asked is irrelevant.


Hope it helps you
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 3.99 (rev main_3_99-649-ge13451a5ab) FPC 3.3.1 x86_64-darwin-cocoa
Mac OS X Monterey

 

TinyPortal © 2005-2018