mORMot Open Source

clive_g

Member

Registered: 2014-04-05

Posts: 10

Retrieve multiple records based on a field value CreateAndFillPrepare

I am attempting to retrieve multiple records based on a field value. When I execute the code below the sql appears well formed but returns no results.
I have the latest source (mORMot_and_Open_Source_friends_2015-04-14_143518_a47d1b738e). Any advise greatly appreciated.

procedure TForm1.btnFilterClick(Sender: TObject);
var
 person: tperson;
 aObject: TSQLRecord;
 aVal1: RawUTF8;
 aVal2: RawUTF8;
begin
  lst1.Items.BeginUpdate();
  try
    aVal1 := 'Ulysses Price');
    aVal2 := 'Jose Williams');
    person := tperson.CreateAndFillPrepare (HTTPClientController.HTTPClient,
                                            'fullname=? OR fullname=? order by fullname',
                                            [aVal1, aVal2]);
    try
      try
        while person.FillOne do begin
            aObject := person.CreateCopy;
            lst1.Items.AddObject (UTF8ToString(tperson (aObject).fullname), person.CreateCopy);
        end;
      finally
        person.FillClose;
      end;
    finally
      FreeAndNil(person);
    end;
  finally
    lst1.Items.EndUpdate();
  end;
end;

Last edited by clive_g (2015-04-16 14:19:23)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,666

Website

Re: Retrieve multiple records based on a field value CreateAndFillPrepare

Code sounds correct to me...

Try to debug it on the server side, or at least run the SQL in SynDBExplorer over the database.

itSDS

Member

From: Germany

Registered: 2014-04-24

Posts: 516

Re: Retrieve multiple records based on a field value CreateAndFillPrepare

what about using

fullname in (?, ?) order by fullname

---

Rad Studio 12.1 Santorini

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,666

Website

Re: Retrieve multiple records based on a field value CreateAndFillPrepare

Perhaps a collation issue, on your database.

What is the version of SQLite3 linked as .obj?
Ensure you got the latest 3.8.9, and the latest revision of the framework source code.

clive_g

Member

Registered: 2014-04-05

Posts: 10

Re: Retrieve multiple records based on a field value CreateAndFillPrepare

Many thanks for all the quick responses.  I think I need to add more information.

I am linking through to a PostGres DB. My implementation is as follows:

type
  THTTPController = class (TObject)
  private
    fModel: TSQLModel;
    fProps: TSQLDBZEOSConnectionProperties;
    fDB: TSQLRestServerDB;
    fHTTPClient: TSQLHttpClient;
    fHTTPServer: TSQLHttpServer;
    fConnection: TSQLDBZEOSConnection;
    fIsConnected: Boolean;
  published
    property HTTPModel: TSQLModel read fModel;
    property HTTPProps: TSQLDBZEOSConnectionProperties read fProps;
    property HTTPDB: TSQLRestServerDB read fDB;
    property HTTPClient: TSQLHttpClient read fHTTPClient;
    property HTTPServer: TSQLHttpServer read fHTTPServer;
    property HTTPConnection: TSQLDBZEOSConnection read fConnection;
    property HTTPIsConnected: Boolean read fIsConnected;
  public
    constructor Create; virtual;
    destructor Destroy; override;
  end;

  TServerController = class (THTTPController)
    public
    constructor Create; override;
    destructor Destroy; override;
  end;

  TClientController = class (THTTPController)
    public
    constructor Create (aNotifyPrcocedure: TOnRecordUpdate; aNotifyTableModified: TOnTableUpdate);
    destructor Destroy; override;
  end;

Registration of tables

 function DataModel (): TSQLModel;
 var
   thisRecord: TEnumTable;
 begin
   result := nil;
   for thisRecord := low (tRecordDetails) to high (tRecordDetails) do begin
     if (Result = nil) then begin
       result := TSQLModel.Create( [tRecordDetails [thisRecord].riTableClass], SERVER_ROOT);
     end else begin
       result.AddTable (tRecordDetails [thisRecord].riTableClass);
     end;
   end;
 end;


 const
   SERVER_ROOT = 'root';
   SERVER_PORT = '888';
   IP_ADDRESS = '127.0.0.1';


 function DataModel (fProps: TSQLDBZEOSConnectionProperties): TSQLModel;
 var
   thisRecord: TEnumTable;
 begin
   result := nil;
   for thisRecord := low (tRecordDetails) to high (tRecordDetails) do begin
     if (Result = nil) then begin
       result := TSQLModel.Create( [tRecordDetails [thisRecord].riTableClass], SERVER_ROOT);
     end else begin
       result.AddTable (tRecordDetails [thisRecord].riTableClass);
     end;
     VirtualTableExternalRegister (result,
                                   tRecordDetails [thisRecord].riTableClass,
                                   fProps,
                                   tRecordDetails [thisRecord].riName);
   end;
 end;

Connections are:

constructor TServerController.Create;	
	
   inherited Create;	
   fProps := TSQLDBZEOSConnectionProperties.Create(
	TSQLDBZEOSConnectionProperties.URI(dPostgreSQL,
		 IP_ADDRESS,
		 '',
		 False),                                                              
		 DatabaseName, { name of databse to connect to {}
		 UserName, { username i.e. postgres {}
		 Password); { password of user postgres {}	

    fProps.ZeosURL.Properties.Values['createNewDatabase'] := '';
    fConnection := TSQLDBZEOSConnection.Create(fProps);
    fModel := DataModel (fProps);
    // add the tables in the model class
    // create main mORMot server
    fDB := TSQLRestServerDB.Create(fModel,fProps.DBMSName,false);
    fDB.CreateMissingTables; // create tables or fields if missing
    // add some indexes
    fDB.CreateSQLIndex (tperson, 'id', True, 'tperson_id');
    fDB.CreateSQLIndex (tperson, 'fullname', False, 'tperson_fullname');    
    fHTTPServer := TSQLHttpServer.Create(SERVER_PORT,[fDB]);
	// if we get this far we should be connected
    fIsConnected := True;																   
	
	
constructor TClientController.Create (aNotifyPrcocedure: TOnRecordUpdate; aNotifyTableModified: TOnTableUpdate);

  inherited Create;
  fModel := DataModel;
  fHTTPClient := TSQLHttpClientWinHTTP.Create (IP_ADDRESS),
                                               SERVER_PORT),
                                               HTTPModel);
  fHTTPClient.OnRecordUpdate := aNotifyPrcocedure;
  fHTTPClient.OnTableUpdate := aNotifyTableModified;
  fIsConnected := HTTPClient.ServerTimeStampSynchronize;	

The following SQL statements work as expected

• fullname>=? AND fullname<=? order by fullname
  fullname<>? AND fullname<>? order by fullname
  ID>=? AND ID<=?

The following do not

• fullname=? AND fullname=? order by fullname
  not(fullname<>? AND fullname<>?) order by fullname
  ID=? OR ID=?
  fullname in (?, ?) order by fullname

I have tested each of the above in PG Admin just to rule out any typos and that the query is correct.
It is not imperative that I find a solution immediately as there are always alternative solutions, however I would (for sanity sake , like to understand the reason why the second parameter is ignored.  I will update this post with my results.

Once again many thanks for your feedback

Last edited by clive_g (2015-04-17 12:52:43)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,666

Website

Re: Retrieve multiple records based on a field value CreateAndFillPrepare

I do not understand why  fullname=? AND fullname=? order by fullname  does not work.
The SQL should be adapted by the ORM, and therefore sent directly to the external DB.

For not(fullname<>? AND fullname<>?) order by fullname  I suppose this is because the parenthesis are not handled by the ORM adaptor yet, so it would fallback to use virtual tables.

What is the SQL statement generated and sent to PostgreSQL?
Please enable the logs and see what's up.

clive_g

Member

Registered: 2014-04-05

Posts: 10

Re: Retrieve multiple records based on a field value CreateAndFillPrepare

Hi

The query I really want to execute is:

SELECT ID,fullname,street,town FROM tperson WHERE fullname='Jose' OR fullname='Ulysses' order by fullname

so my thoughts are that this should produce the same results

person := tperson.CreateAndFillPrepare (HTTPClientController.HTTPClient, 'fullname=? OR fullname=? order by fullname', [aVal1, aVal2]);

this is the sql that is generated at MultiFieldValues when calling CreateAndFillPrepare

'SELECT ID,fullname,street,town FROM tperson WHERE fullname=:('Jose'): OR fullname=:('Ulysses'): order by fullname;'

function TSQLRest.MultiFieldValues(Table: TSQLRecordClass;
  const FieldNames, WhereClause: RawUTF8): TSQLTableJSON;
var sql: RawUTF8;
begin
  sql := SQLComputeForSelect(Table,FieldNames,WhereClause);
  if sql='' then
    result := nil else
    result := ExecuteList([Table],sql);
end;

I don't see any other mention on the Forum so was hoping it was as simple as something I had failed to include or my implementation.

I will enable the logs and follow the code path and add any results. I'll also I'll start a fresh and get the latest ZEOS branch and Mormot branch and retry. thanks.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,666

Website

Re: Retrieve multiple records based on a field value CreateAndFillPrepare

The query computed at client side is correct.
It includes '...'): to mark the parameter values.
Then those values would be converted as the expected SQL on the server side.
See http://synopse.info/files/html/Synopse% … ml#TITL_61

Please check on server side what is the executed SQL, at SynDBZeos.pas level, just before sending to ZDBC.
Enabling the logs would definitively help you investigating.

clive_g

Member

Registered: 2014-04-05

Posts: 10

Re: Retrieve multiple records based on a field value CreateAndFillPrepare

thanks, will do.

clive_g

Member

Registered: 2014-04-05

Posts: 10

Re: Retrieve multiple records based on a field value CreateAndFillPrepare

Hi

I've switched on the logging and can see that the query is altered by the time it reaches here:

20150421 16140126  +        TSQLDBZEOSStatement(018B7EA0).00608B19
20150421 16140126 SQL           TSQLDBZEOSStatement(018B7EA0) select ID,fullname,street,town from public.tperson where fullname='Jose' and fullname='Ulysses' order by fullname
20150421 16140126  -        00.002.194

The sql at the client side is:

'SELECT ID,fullname,street,town FROM tperson WHERE fullname=:('Jose'): OR fullname=:('Ulysses'): order by fullname;'

The sql at the server side is:

select ID,fullname,street,town from public.tperson where fullname='Jose' and fullname='Ulysses' order by fullname

Thanks

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,666

Website

Re: Retrieve multiple records based on a field value CreateAndFillPrepare

AFAIR the "public." prefix is added from TSQLDBZEOSConnectionProperties.

It is expected on PostgreSQL by default, but you may change it via the TSQLDBZEOSConnectionProperties.ForcedSchemaName property, to fit your own database settings:

    /// an optional Schema name to be used for SQLGetField() instead of UserID
    // - by default, UserID will be used as schema name, if none is specified
    // (i.e. if table name is not set as SCHEMA.TABLE)
    // - depending on the DBMS identified, the class may also set automatically
    // the default 'dbo' for MS SQL or 'public' for PostgreSQL
    // - you can set a custom schema to be used instead
    property ForcedSchemaName: RawUTF8 read fForcedSchemaName write fForcedSchemaName;

See the documentation
http://synopse.info/files/html/api-1.18 … C_609F105A

general-e

Member

From: Germany

Registered: 2013-04-11

Posts: 7

Re: Retrieve multiple records based on a field value CreateAndFillPrepare

Hi,

the sql at the server side has an AND operator not the wanted OR operator.
So you can't get any results.

clive_g

Member

Registered: 2014-04-05

Posts: 10

Re: Retrieve multiple records based on a field value CreateAndFillPrepare

Hi

Many thanks again for the feedback.

This is where I'm currently at ...

unit mORMotDB;

function TSQLRestStorageExternal.AdaptSQLForEngineList(var SQL: RawUTF8): boolean;
...
     // before call to SetText var SQL hoas OR
	 // 'SELECT ID,fullname,street,town FROM tperson WHERE fullname=:('Jose'): OR fullname=:('Ulysses'): order by fullname;'
     W.SetText(SQL);
     // after call to SetText var SQL has OR replaced with AND
	 // 'select ID,fullname,street,town from public.tperson where fullname=:('Jose'): and fullname=:('Ulysses'): order by fullname'  
      result := true;
    finally
      W.Free;
    end;
  finally
    Stmt.Free;
  end;
end;

clive_g

Member

Registered: 2014-04-05

Posts: 10

Re: Retrieve multiple records based on a field value CreateAndFillPrepare

Hi

Quick update.

The record property JoinedOR needs to be flagged as True.   Yet to understand how this is implemented.

/// one recognized WHERE expression for TSynTableStatement
TSynTableStatementWhere = record
	/// expressions are evaluated as AND unless this field is set to TRUE
	JoinedOR: boolean;

   

OR is switched for an AND here ...

function TSQLRestStorageExternal.AdaptSQLForEngineList(var SQL: RawUTF8): boolean;
...
		  // due to JoinedOR = False the or is omitted and the and added
          W.AddNoJSONEscape(ValueSQL,ValueSQLLen);
          if f<n then
            if JoinedOR then
              W.AddShort(' or ') else
              W.AddShort(' and ');
        end;

Thanks

clive_g

Member

Registered: 2014-04-05

Posts: 10

Re: Retrieve multiple records based on a field value CreateAndFillPrepare

Hi

A little bit further ...

This may not be the root cause but I think it's in this area. The Prop is evaluated for all the strings but there is a method call to GetWhereExpression which sets the property Where.JoinedOR to False Setting the Where.JoinedOR to True at run time allows the SQL to build correctly. I think the issue is that we are expecting to parse more than one character in the  operator evaluation i.e. pass through this method twice. which is why the "=>" works but "=" does not.

function GetWhereExpression(FieldIndex: integer; var Where: TSynTableStatementWhere): boolean;
begin
  result := false;
  Where.JoinedOR := whereWithOR;
  Where.NotClause := whereNotClause;
  Where.Field := FieldIndex; // 0 = ID, otherwise PropertyIndex+1
  case P^ of
  '=': Where.Operator := opEqualTo;
  '>': if P[1]='=' then begin

...
  // 3. get WHERE clause
  whereCount := 0;
  whereWithOR := false; // Implicitly set to False
  whereNotClause := false;
  GetNextFieldProp(P,Prop);
  if IdemPropNameU(Prop,'WHERE') then begin
    repeat
    ...
      inc(whereCount);
      GetNextFieldProp(P,Prop);
	  // This may not be the root cause but I think it's in this area. 
	  // The Prop is evaluated for all the strings but there is a method call to GetWhereExpression
	  // which sets the property Where.JoinedOR to False
	  // Setting the Where.JoinedOR to True at run time allows the SQL to build correctly
	  // I think the issue is that we are expecting to parse more than one character in the 
	  // operator evaluation i.e. pass through this method twice. which is why the => works but = does not.
      SetLength(fWhere,whereCount+1);
      if not GetWhereExpression(ndx,fWhere[whereCount]) then
        exit; // invalid SQL statement
      inc(whereCount);
      GetNextFieldProp(P,Prop);	  
	  if IdemPropNameU(Prop,'OR') then
        whereWithOR := true else
      if IdemPropNameU(Prop,'AND') then
        whereWithOR := false else
        goto lim2;
      whereNotClause := false;
    until false;
	

   

Thanks

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,666

Website

Re: Retrieve multiple records based on a field value CreateAndFillPrepare

Could please create a ticket?

With a link to this forum thread.

See http://synopse.info/fossil/tktnew

clive_g

Member

Registered: 2014-04-05

Posts: 10

Re: Retrieve multiple records based on a field value CreateAndFillPrepare

Done.

Ticket reference

1f95cbb5ad831fef28e9e32f9d0a9fca853cdf44

Thanks

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,666

Website

Re: Retrieve multiple records based on a field value CreateAndFillPrepare

The OR operator in the SQL Where clause has been fixed.
See http://synopse.info/fossil/info/6d166efd3e

I've also added the corresponding regression tests.

Thanks for the feedback.