mORMot Open Source

noobies

Member

Registered: 2011-09-13

Posts: 139

TTimeLog and BETWEEN

dont understand why this  work

...' AND Created >= ' + IntToStr(TimeLogFromDateTime(db)) + ' AND Created <= ' + IntToStr(TimeLogFromDateTime(de));

but this dont work

...' AND Created BEETWEN ' +  IntToStr(TimeLogFromDateTime(db)) + ' AND ' + IntToStr(TimeLogFromDateTime(de));

Last edited by noobies (2015-05-22 06:48:06)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: TTimeLog and BETWEEN

I guess you are using external DB.

Not all SQL is handled by the Internal SQL adaptator.
AND is handled, but BETWEEN is not.
So BETWEEN has to fallback to virtual tables, which may be less efficient, and in this case... perhaps buggy.

noobies

Member

Registered: 2011-09-13

Posts: 139

Re: TTimeLog and BETWEEN

i not using external DB.
server

  with TSQLite3HttpService.Create do
  try
    ServicesRun;
  finally
    Free;
  end;

client

  
  Model := CreateModel;
  ReadConfig;
  ClientRest := TSQLHttpClient.Create(ip, port, Model);
  ClientRest.ForceBlobTransfert := True;
  if not ClientRest.ServerTimeStampSynchronize then Result := False;

sql exec

  s :=
    'select ls.TradeNmR, ls.InnR, ls.DosageR, ls.PackQn, ls.EAN, r.Series, r.MnfPrice, r.PrcPrice, ' +
    'r.TotDrugQn, v.VendorNm, z.Name, r.RekvType, r.RekvNum, r.RekvDate ' +
    'from Recs r ' +
    'inner join RZLS ls on r.DrugID = ls.DrugID ' +
    'inner join RZVendor v on r.VendorID = v.VendorID ' +
    'inner join Zakupka z on r.ZakupkaID = z.ID ' +
    'where r.mo = ' + mo_id.ToString +
//    ' AND r.Created BEETWEN ' +  IntToStr(TimeLogFromDateTime(db)) + ' AND ' + IntToStr(TimeLogFromDateTime(de));
    ' AND r.Created >= ' + IntToStr(TimeLogFromDateTime(db)) + ' AND r.Created <= ' + IntToStr(TimeLogFromDateTime(de));
  t := ClientRest.ExecuteList([], s);

BETWEEN not work only in TTimeLog fields, with others work pretty fine

Last edited by noobies (2015-05-22 08:53:01)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: TTimeLog and BETWEEN

TTimeLog fields are stored as Integer SQLite3 values, so they should work.

Does your request work with plain SQlite3 (e.g. SynDBExplorer)?

BTW, you should better use parameters instead of IntToStr() within the SQL itself.

...
' AND (r.Created BEETWEN ? AND ?)');
  t := ClientRest.ExecuteFmt(s,[],[TimeLogFromDateTime(db),TimeLogFromDateTime(de)]);

Also note that I've added a parenthesis around the BETWEEN expression.

noobies

Member

Registered: 2011-09-13

Posts: 139

Re: TTimeLog and BETWEEN

TTimeLog fields are stored as Integer SQLite3 values, so they should work.

it works, sorry gramma misstake BETWEEN not BEETWEN.

' AND (r.Created BETWEEN ? AND ?)');
  t := ClientRest.ExecuteFmt(s,[],[TimeLogFromDateTime(db),TimeLogFromDateTime(de)]);

yes but ExecuteFmt return boolean not table(((.
it would be wonderful to have a execute format returned table

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: TTimeLog and BETWEEN

Try to use TSQLRest.MultiFieldValues

noobies

Member

Registered: 2011-09-13

Posts: 139

Re: TTimeLog and BETWEEN

Try to use TSQLRest.MultiFieldValues

but how create join with 4 tables?

    function MultiFieldValues(Table: TSQLRecordClass; const FieldNames: RawUTF8;
      const WhereClauseFormat: RawUTF8; const BoundsSQLWhere: array of const): TSQLTableJSON; overload;

if put inner join in FieldNames he placed before FROM, if put in WhereClauseFormat he placed after WHERE

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: TTimeLog and BETWEEN

Oups... I forgot about the JOIN.

So in this case, you could use ClientRest.ExecuteList(), but creating the WHERE clause with the overloaded FormatUTF8() allowing both Args and Bounds parameters.

noobies

Member

Registered: 2011-09-13

Posts: 139

Re: TTimeLog and BETWEEN

creating the WHERE clause with the overloaded FormatUTF8() allowing both Args and Bounds parameters.

thanks i change my code

   ...'where ' + FormatUTF8('r.mo = ? AND r.Created BETWEEN ? AND ?', [], [mo_id, TimeLogFromDateTime(db), TimeLogFromDateTime(de)]);