mORMot Open Source

dansot

Member

Registered: 2012-11-12

Posts: 25

result sets

My brain just isn't translating this well even with searches, examples and documentation.

This is where I am coming from:

$result = mysql_query($query);
if ($result) {
   $nr = mysql_num_rows($result);
   if ($nr > 0) {
       while($row = mysql_fetch_array($result, MYSQL_NUM)) {
        //do stuff
       }     
  }

how do I translate that function to ORM?  Perform a query and then enumerate the result set?

Last edited by dansot (2013-02-02 09:22:10)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: result sets

Download the latest version of the mORMot 1.18 documentation from http://synopse.info/fossil/wiki?name=Do … umentation
Direct link: http://synopse.info/files/pdf/Synopse%2 … 201.18.pdf

Read the whole "ORM" chapter, and for your question, the one named "Queries".
Extract:

Return a list of objects

You can query your table with the FillPrepare or CreateAndFillPrepare methods, for instance all babies with balls and a name starting with the letter 'A':

aMale := TSQLBaby.CreateAndFillPrepare(Client,
  'Name LIKE ? AND Sex = ?',['A%',ord(sMale)]);
try
  while aMale.FillOne do
    DoSomethingWith(aMale);
finally
  aMale.Free;
end;

This request loops through all matching records, accessing each row content via a TSQLBaby instance.

The mORMot engine will create a SQL statement with the appropriate SELECT query, retrieve all data as JSON, transmit it between the Client and the Server (if any), then convert the values into properties of our TSQLBaby object instance. Internally, the *FillPrepare / FillOne methods use a list of records, retrieved as JSON from the Server, and parsed in memory one row a time (using an internal TSQLTableJSON instance).

dansot

Member

Registered: 2012-11-12

Posts: 25

Re: result sets

thanks!  Something isn't right here that maybe you can see:

Idea is to take a full list of files, extract a sub list of files with the same file sizes.  Then do some stuff with that sub list, remove them from the full list and repeat.  The error I am getting is one the second "CreateAndFillPrepare" that says
"There is no overloaded version of 'CreateAndFillPrepare' that can be called with these arguments"

tfilelist = class(TSQLRecord)
  private
    ftype,fpform,fname,ffilename,fsig: RawUTF8;
    fsize: int64;
    foffsets: RawUTF8;
  published
    property _type: RawUTF8 read ftype write ftype;
    property pform: RawUTF8 read fpform write fpform;
    property name: RawUTF8 read fname write fname;
    property filename: RawUTF8 read ffilename write ffilename;
    property size: int64 read fsize write fsize;
    property offsets: RawUTF8 read foffsets write foffsets;
    property sig: rawutf8 read fsig write fsig;
  end;

var
    full_list, sub_list: tfilelist;
begin
  full_list:=tfilelist.CreateAndFillPrepare(filelist, '*');
  while full_list.FillOne do
    begin
      sub_list:=tfilelist.CreateAndFillPrepare(full_list,'_type = ? AND pform = ? AND name = ? AND size = ?', [full_list._type, full_list.pform, full_list.name, full_list.size]);
      if (sub_list.FillTable.RowCount > 6) then
        begin
          aprogress.Lines.Insert(0, format('Processing %s.%s.%s',[sub_list._type,sub_list.pform,sub_list.name]));
          while sub_list.FillOne do
            begin
              //do some stuff
              filelist.Delete(full_list, sub_list.ID);
            end;
          sub_list.Free;
        end;
    end;
  full_list.Free;
end;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: result sets

Perhaps

sub_list:=tfilelist.CreateAndFillPrepare(filelist, ... 

sub_list.Free shall be called in all cases: your current code is leaking memory.

But you have two nested queries from the same data.
Not efficient, I guess.

Just one loop could make the trick, with manual search using a "for..." loop.

You can use the new TSQLRest.RetrieveList() method instead of CreateAndFillPrepare() to retrieve a TObjectList in memory.
Something list this:

var List: TObjectList;

List := filelist.RetrieveList(tfilelist,'',[]);
if List<>nil then
try
  for i := 0 to List.Count-1 do
   // then use tfilelist(List[i]) - in 2 loops

finally
  List.Free;
end;