Changing DA schema SQL statement before linq query execution

[fvancrae (Frederic Vancraeyveldt)]

I would like to alter the SQL SELECT statement in a DA schema before execution with linq.

More specifically I would like to alter several table names

I have a .NET RO/DA server with a DA service.

On the server side I have regular RO function which does SQL queries based on a DA schema

Is it possible to alter the daschema select SQL before it is executed using the linq GetTable function?

I want to modify the tablenames dynamically.

My code looks like this

The dataAdapter is a new LinqLocalDataAdapter(this) from the service

I was thinking about using UnknownSqlMacroIdentifier but it does not look like the GetData (which I override) function of my service gets called

[EvgenyK (Evgeny Karpov)]

Hi,

by default, each service’s instance loads ServiceSchema in runtime so you can alter it before it was processed

[fvancrae (Frederic Vancraeyveldt)]

Can I do it per RO service call?

I have some extra parameters in the RO API function which then determines the exact SQL select.

FYI: I liked to macro system

For non linq I use this and it is IMO a clean solution. The GetData resolves some unknowns

[EvgenyK (Evgeny Karpov)]

Hi,

@fvancrae

Can I do it per RO service call?

yes, if you are using Per Request or Standard Class Factory

@fvancrae

FYI: I liked to macro system

fyi: you can set DASqlProcessor.AllowMacroSupport to true if DASQL Processor: Macro support was disabled exception on server-side.

[fvancrae (Frederic Vancraeyveldt)]

Can you confirm (or negate) that using LINQ should call the RO service GetData function?

If it should use GetData, what could be the cause that the function is not entered?

[EvgenyK (Evgeny Karpov)]

Hi,

I can confirm that LinqLocalDataAdapter uses standard GetData method for getting data.

@fvancrae

If it should use GetData, what could be the cause that the function is not entered?

do you mean UnknownSqlMacroIdentifier ? what value of DASqlProcessor.AllowMacroSupport you have?

[fvancrae (Frederic Vancraeyveldt)]

Hy Evgeny,

My issue is that the GetData function does not get called at all (for a new service I created)

Maybe it is related to how I set up my dataadapter.

This is my code:

I constructed the service like this

The base class is this

I set my breakpoint in my base class GetData override

This works from other services (with RemoteDataAdapter) but not with the LinqLocalDataAdapter you see above

Working callstack (other service)

[fvancrae (Frederic Vancraeyveldt)]

FYI 1: For a simple query the query gets executed by sql server but GetData breakpoint is not hit and my logging code in it does not trigger

FYI 2: I cheated by using a generated _TableDefinitions.cs file which did not belong to this service however when using the correct generated class it also fails

I guess this code

using(var lda = new LinqLocalDataAdapter(this))

does not suffice for some reason

Should I use another constructor?

[EvgenyK (Evgeny Karpov)]

Hi,

as I can see, GetData is used in LinqLocalDataAdapter & LinqRemoteDataAdapter almost the same:

• LinqLocalDataAdapter

method LinqLocalDataAdapter.FetchData(requests: array of TableRequestInfo; names: array of String; fillMethod: Action<Int32, array of Int32, IDataReader>);
begin
..
  self.ServiceInstance.GetData(names, requests, lStreamer);
..
end;

• LinqRemoteDataAdapter

method DataRequestRequest.SetupDefaultRequest;
begin
  Parameters.Clear();

  OutgoingTableNamesParameter := Parameters.Add('aTableNameArray',        'StringArray',           RemObjects.SDK.ParameterDirection.In).Name;
                                 Parameters.Add('aTableRequestInfoArray', 'TableRequestInfoArray', RemObjects.SDK.ParameterDirection.In);
  IncomingDataParameter :=       Parameters.Add('Result',                 'Binary',                RemObjects.SDK.ParameterDirection.Result).Name;
  MethodName := 'GetData';
end;

method LinqRemoteDataAdapter.FetchData(tableRequest: array of RemObjects.DataAbstract.Server.TableRequestInfo; tableNames: array of String; fillMethod: Action<Int32, array of Int32, IDataReader>);
begin
..
  self.DataRequestCall.MakeRequest();
..
end;

[fvancrae (Frederic Vancraeyveldt)]

Nope, I cannot get it to run my code.

I tried with some guids or servicename in the constructor.

Do you have any more ideas I can try out?

I am thinking about abandoning linq (i had a few issues with it which i posted on talk)

I don’t seem to find a LocalDataAdapter sample on C:\Users\Public\Documents\RemObjects Samples\Data Abstract for .NET\C#

Do you have sample code for C# .NET LocalDataAdapter?

Ideally code that already converts to (binding)list of a class in xx_TableDefinitions.cs

[fvancrae (Frederic Vancraeyveldt)]

FYI: I have just written some test code that uses LinqRemoteDataAdapter and connects from the outside and their the GetData function gets used.

So the cause of the issue is related to LinqLocalDataAdapter

I do not wish to make a remote connection to myself however,

can you make LinqLocalDataAdapter behave like LinqRemoteDataAdapter?

Other behaviour is fine but I am interested in 2 things

1. Being able to log the SQL statements
2. Being able to modify the SQL statement using this.UnknownSqlMacroIdentifier += resolveIdentifier; (or something similar)

[fvancrae (Frederic Vancraeyveldt)]

FYI: The second point (UnknownSqlMacroIdentifier) already works with the LinqLocalDataAdapter. I just have to move my resolveIdentifier code from GetTable (which does not get called) to some code that gets called

[artem.vuitsyk]

Hi,

There are two different members named GetData on DataAbstractService: the public virtual one you override, which the invoker calls on a network request, and a non-virtual explicit IDataAbstractLocalServiceAccess.GetData the one LinqLocalDataAdapter actually calls.

That is why your override is never entered, and no constructor changes it.

You don’t need a loopback connection though. Both things you asked for work locally, from your service constructor

// 1. the SQL, after table names and macros are resolved
`this.BeforeExecutingGetDataReader += (sender, e) =>`
`{`
`    // e.Command.CommandText`
`};`
`// 2. resolving a {TARGETTABLE} macro in the schema statement, per request`
`this.AllowCustomSqlMacros = true;   // otherwise the handler raises`
`this.UnknownSqlMacroIdentifier += (sender, e) =>`
`{`
`    if (e.Identifier != "TARGETTABLE") return;`
`    e.Value = ResolveIdentifier(e.Identifier);`
`    e.Processed = true;`
`};`

The macro is resolved at connection level, independently of the adapter what broke it for you is only where you subscribed: inside your GetData override that code never runs on the local path.

I verified both here before replying.
LocalAdapterSample.zip (4.9 KB)

Attached is a small console sample: one LinqLocalDataAdapter, the macro resolving the table name per request, and the SQL logged. It runs the same query twice against two different tables.

[fvancrae (Frederic Vancraeyveldt)]

Thanks,

I was indeed writing my ResolveIdentifier in a similar fashion.

Now I am only blocked by the issue with the complex SQL

See Can this complex DA schema SQL get to work with linq? - #3 by RemObjectsSoftware

Is this something you can fix?

[artem.vuitsyk]

Yes, it will be fixed soon.