TSQLRest.OneFieldValue with 'AND'

tomek · 2020-08-19 13:47:17

Hi
How to properly use this method with 'AND'? Example below returns nothing (there definitely exists a record that meets the conditions, example code with 1 condition works ok).

  sRes := Self.OneFieldValue(TSQLMyRec,
                             'RowID',
                             'FieldA=? AND FieldB=?',
                             [sValueA, sValueB]);

Regards, Tomek

ab · 2020-08-19 14:01:50

With which DB engine?
What is the SQL generated by the ORM?

tomek · 2020-08-19 14:12:21

DB is Firebird, but this query is not related with DB engine, it's TMyRest = class(TSQLRestServerFullMemory).
SQL executed internally by ExecuteList looks like this:

'SELECT RowID FROM MyRec WHERE FieldA=:(''some_value_a''): AND FieldB=:(''some_value_b''): LIMIT 1'

ab · 2020-08-19 14:33:50

TSQLRestServerFullMemory has limited SQL support.
It doesn't handle the AND operator IIRC - please check the documentation.

edwinsn · 2020-08-19 14:46:27

Use in-memory sqlite db instead - use :memory: as the db file name.

tomek · 2020-08-20 08:11:07

I finally used TSQLRest.OneFieldValues() with 1 condition (given, that my table contains small amount of records, and first condition cuts it to few).

ab · 2020-08-20 09:51:39

If performance really matters, you could access the TSQLRestServerFullMemory values directly via a loop.

tomek · 2020-09-09 11:38:14

Now I've problem with TSQLRest.OneFieldValues() in Lazarus, it returns corrupted result (in Delphi result is ok).
My tab (server FullMemory) has 2 rows:

writeln(aRestServer.RetrieveListJSON(TSQLMyRec, '')):

[
{"RowID":1,"GroupId":"TA_FK","Prop":"SomeValue"},
{"RowID":2,"GroupId":"TA_SPEDYTOR","Prop":"SomeValue"}
]

sRes := aRestServer.OneFieldValues(TSQLMyRec,'GroupId', FormatUTF8('Prop=:(%):', ['SomeValue'])):

Delphi writeln(sRes):

TA_FK,TA_SPEDYTOR

Lazarus writeln(sRes):

TA_FK  ,TA_SP

Debbuger shows only 'TA_FK'. Lazarus Console shows unprintable characters between 'TA_FK' and ','

Last edited by tomek (2020-09-09 11:42:02)

ab · 2020-09-10 08:04:51

To which OS do you compile?
With which FPC revision?

I added some explicit test, and was not able to reproduce with FPC 3.2 on Windows and Linux.
Check https://synopse.info/fossil/info/9ebe9fda2e

Try to debug the method, and see what is wrong on your computer.

tomek · 2020-09-10 21:30:35

I've tried both Win and Linux.
FPC 3.2.0

Below simple program to reproduce:

program Test;

{$APPTYPE CONSOLE}

uses
  {$I SynDprUses.inc}  // use FastMM4 on older Delphi, or set FPC threads
  SynCommons,
  mORMot;

type
  TSQLMyRec = class(TSQLRecord)
  private
    fGroupId: string;
    fProp: string;
  published
    property GroupId: string read fGroupId write fGroupId;
    property Prop: string read fProp write fProp;
  end;

var
  aModel: TSQLModel;
  aRestServer: TSQLRestServer;
  sTemp: RawUTF8;

begin

  aModel := TSQLModel.Create([TSQLMyRec]);
  try
    aRestServer := TSQLRestServerFullMemory.Create(aModel);
    try
      aRestServer.AddSimple(TSQLMyRec, ['TA_FK', 'SomeValue']);
      aRestServer.AddSimple(TSQLMyRec, ['TA_SPEDYTOR', 'SomeValue']);

      writeln(aRestServer.RetrieveListJSON(TSQLMyRec, ''));
      sTemp := aRestServer.OneFieldValues(TSQLMyRec, 'GroupId', FormatUTF8('Prop=:(%):', ['SomeValue']));
      writeln(sTemp);
      writeln;

      writeln('Press [Enter] to close.');
      readln;
    finally
      aRestServer.Free;
    end;
  finally
    aModel.Free;
  end;

end.

and output:

[
{"RowID":1,"GroupId":"TA_FK","Prop":"SomeValue"},
{"RowID":2,"GroupId":"TA_SPEDYTOR","Prop":"SomeValue"}]

TA_FK  ,TA_SP

Press [Enter] to close.

ab · 2020-09-11 06:03:35

On my computer with FPC 3.2 and Linux i386, x86_64 and Win32:

[
{"RowID":1,"GroupId":"TA_FK","Prop":"SomeValue"},
{"RowID":2,"GroupId":"TA_SPEDYTOR","Prop":"SomeValue"}]

TA_FK,TA_SPEDYTOR

Press [Enter] to close.

My FPC is compiled with FPCUpdDeluxe and shows:

Verbose: Free Pascal Compiler version 3.2.0-r45643 [2020/07/16] for x86_64

Are you sure you are using latest mORMOt 1.18.6132?

Do the TestSQL3.dpr regression tests pass on your PC?

So I guess you need to debug on your computer, to see what is wrong.
Perhaps try to change the "string" type into "RawUTF8" in your TSQLMyRecord definition.

tomek · 2020-09-11 11:35:56

Latest mORMot with updated TSQLRest.OneFieldValues() solved the problem, Thx!

TestSQL3 passed 100%.

Regards

P.S. I've tried debug, but I don't get Lazarus debugger, inspected values are unclear and don't match final result (and lot of "GDB has encountered an internal error").

Last edited by tomek (2020-09-11 11:38:24)

mpv · 2020-09-11 16:02:57

GDB + Lazarus on Windows is a PITA. Under Linux debugging works well, except inspecting a properties values (at last with gdb 9+), it's even jump to the C implementation, so external library can be debugged in the Lazarus IDE

mORMot Open Source

#1 2020-08-19 13:47:17

TSQLRest.OneFieldValue with 'AND'

#2 2020-08-19 14:01:50

Re: TSQLRest.OneFieldValue with 'AND'

#3 2020-08-19 14:12:21

Re: TSQLRest.OneFieldValue with 'AND'

#4 2020-08-19 14:33:50

Re: TSQLRest.OneFieldValue with 'AND'

#5 2020-08-19 14:46:27

Re: TSQLRest.OneFieldValue with 'AND'

#6 2020-08-20 08:11:07

Re: TSQLRest.OneFieldValue with 'AND'

#7 2020-08-20 09:51:39

Re: TSQLRest.OneFieldValue with 'AND'

#8 2020-09-09 11:38:14

Re: TSQLRest.OneFieldValue with 'AND'

#9 2020-09-10 08:04:51

Re: TSQLRest.OneFieldValue with 'AND'

#10 2020-09-10 21:30:35

Re: TSQLRest.OneFieldValue with 'AND'

#11 2020-09-11 06:03:35

Re: TSQLRest.OneFieldValue with 'AND'

#12 2020-09-11 11:35:56

Re: TSQLRest.OneFieldValue with 'AND'

#13 2020-09-11 16:02:57

Re: TSQLRest.OneFieldValue with 'AND'

Board footer