Pages: 1

#1 Re: mORMot 1 » Retrieve multiple records based on a field value CreateAndFillPrepare » 2015-04-22 20:04:00

clive_g

Done.

Ticket reference

1f95cbb5ad831fef28e9e32f9d0a9fca853cdf44

Thanks

#2 Re: mORMot 1 » Retrieve multiple records based on a field value CreateAndFillPrepare » 2015-04-22 11:43:47

clive_g

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

#3 Re: mORMot 1 » Retrieve multiple records based on a field value CreateAndFillPrepare » 2015-04-22 11:03:45

clive_g

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

#4 Re: mORMot 1 » Retrieve multiple records based on a field value CreateAndFillPrepare » 2015-04-22 09:17:47

clive_g

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;

#5 Re: mORMot 1 » Retrieve multiple records based on a field value CreateAndFillPrepare » 2015-04-21 15:21:28

clive_g

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

#6 Re: mORMot 1 » Connecting to Postgre sample database » 2015-04-20 19:27:05

clive_g

Hi
Please feel free to use this as a starting point
http://synopse.info/forum/viewtopic.php?id=2516


This implementation connects to a PostGres database using a Zeos connection.
Using this implementation you will need to ensure you have the library files in the server directory

  • libeay32.dll
    libiconv.dll
    libintl.dll
    libpq.dll
    libxml2.dll
    libxslt.dll
    ssleay32.dll

#7 Re: mORMot 1 » Retrieve multiple records based on a field value CreateAndFillPrepare » 2015-04-17 14:14:05

clive_g

thanks, will do.

#8 Re: mORMot 1 » Retrieve multiple records based on a field value CreateAndFillPrepare » 2015-04-17 13:54:10

clive_g

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.

#9 Re: mORMot 1 » Retrieve multiple records based on a field value CreateAndFillPrepare » 2015-04-17 12:48:49

clive_g

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 smile, 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

#10 mORMot 1 » Retrieve multiple records based on a field value CreateAndFillPrepare » 2015-04-16 14:18:29

clive_g
Replies: 17

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;

Pages: 1

Board footer

Powered by FluxBB