Filtered dataset from server

Hi

Using D12, RODA 10.0.0.1605

When a user (client) connects to the server, the client ID or customer ID is stored within the session.
When a user wants to access the table of articles (e.g.), only records belonging to this user should be ‘transferred’ to the client. So, I need to filter the articles-table serverside.
How can I achieve this?
Tried to do this in the OnWriteDataSet event of the DataStreamer, but no luck so far…
Many thanks!

Hi,

you can use {SESSION_XXX} macro in sql, where XXX - value is stored inside session.

Session['myid'] := some_client_id;
select .. where table.clientid = {SESSION_myid}

When I adjust the SQL Statement in the schema like this…

I get an error: Invalid column name ‘SESSION_CUSTOMERNAME’.

Hi,

Schema Modeler knows nothing about SESSION_XXX macro because such macros are provided by DataService based on your session:

function TBaseDataAbstractService.DoOnUnknownIdentifier(Sender: TObject;
  const Name, OrgName: string; var Value: string): Boolean;
..
  // Is this a session value request?
  if Length(OrgName) > 8 then begin
    s := Copy(OrgName, 1, 8);
    if AnsiSameText(s, 'SESSION_') then begin
      lSessionValueName := Copy(OrgName, 9, Length(OrgName) - 8);
      lSessionValue := Self.Session[lSessionValueName];
      Result := not (VarIsNull(lSessionValue) or VarIsEmpty(lSessionValue));
      if Result then Value := VarToStr(lSessionValue);
      Exit;
    end;
  end;
..

You also can provide SESSION_CUSTOMERNAME macro via DynamicWhere expression.

review my simple testcase:
VCLApplication.zip (101.7 KB)

Hi Evgeny

I get the error runtime.

I’ve set the property AllowCustomSqlMacros to True. Added a SpiderMonkeyScriptProvider (don’t know if that is necessary).

I’ve set a breakpoint in the DoOnUnknownIdentifier event, but I don’t even get there.

The small demo you provided works. Maybe a driver-issue? Using FireDAC - MSSQL.

Filip.

Hi,

this error is raised when session variable isn’t set.
check that your code Session['CUSTOMERNAME'] := ... is executed.


you can change correspondent line of my sample to Session['ID1'] := lLoginString.Item['ID']; and similar error will be raised too:

Hi Evgeny

Changing your demo application results in a EDatabaseError while I get a EMSSQLNativeException.

The error in your demo occurs after the DoOnUnknownIdentifier event, while my application is not even getting there.

The Session[‘CustomerName’] has a value. There must be something else missing…

Filip.

Hi,

FireDAC may raise other error than SQLite driver. this is as designed.

Can you create a simple testcase like mine that reproducing your case, pls?
You can drop it to support@ for keeping privacy

Hi Evgeny

I managed to figure it out…
When a connection is assigned in the OnCreate of the DA Service, the fConnection is evaluated as assigned, so Exit is called and MacroProcessorEvent will not be called.

In my service this was declared as

procedure TArticleService.DataAbstractServiceCreate(Sender: TObject);
begin
  schArticle.ConnectionManager := dmServer.cmConnections; // ConnectionManager
  Connection := dmServer.GetDefaultConnection; // method to get the right connection
end;

schArticle is the Schema

the solution (for now) is

procedure TArticleService.DataAbstractServiceCreate(Sender: TObject);
begin
  schArticle.ConnectionManager := dmServer.cmConnections;
//  Connection := dmServer.GetDefaultConnection;
end;

How can I solve this issue and still set the Connection of the service?
(Because the connection is dependant on the session).

Regards,

Filip

Hi,

you can assign event manually in DataAbstractServiceCreate like we do:

//  lmacro: IDAHasMacroProcessor;
  if Connection.UseMacroProcessor then
    if Supports(Connection, IDAHasMacroProcessor, lmacro) then begin
      if Assigned(lmacro.GetMacroProcessor) and not Assigned(lmacro.GetMacroProcessor.OnDAServiceUnknownIdentifier) then begin
        lmacro.GetMacroProcessor.OnDAServiceUnknownIdentifier := self.DoOnUnknownIdentifier;
      end;
      lmacro := nil;
    end;

Hi

Wouldn’t it be better in TBaseDataAbstractService to have the AssignMacroProcesserEvent method public? Or not exiting the GetConnection method when fConnection is assigned?

Regards,

Filip

Hi,

I’d suggest to use OnBeforeAcquireConnection event and return required connection name instead of calling dmServer.GetDefaultConnection that return IDAConnection.

in this case AssignMacroProcesserEvent method will be called as expected.