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.