Adding where clause where DA fieldname differs from DB fieldname using DynamicWhere

Can you add a where clause using DA4 DynamicWhere for fields where DA fieldname differs from DB fieldname?

My SQL is something like
vdp_prd_cnk cnk,
FROM xxx

On the client side I do

Both fieldname ‘vdp_prd_cnk’ and ‘cnk’ fail with the errors shown below

Error 1: Field “vdp_prd_cnk” is not found in the table.
Invalid column name ‘cnk’.

My SQL mapping looks correct

How should this be done?


for usual tables (i.e. non-DASQL requests), you can set DataService.AllowAllFieldsInDynamicWhere to True

I tried to fix it with a custom where clause (via a custom RO get data call) but this failed.

Can you tell me what’s wrong with this code

public RemObjects.SDK.Types.Binary OffGetData(string[] aTableNameArray, TableRequestInfo[] aTableRequestInfoArray, int MaxRecords, string StatementName,string StatementSuffix,string OrderClause, int PrfPrimkey,string SecurityContext,string ExtraWhereClause)
if (!string.IsNullOrEmpty(ExtraWhereClause)) {
foreach (var req in aTableRequestInfoArray)
req.UserFilter = ExtraWhereClause;
return this.GetData(aTableNameArray, aTableRequestInfoArray);

This is something we have been doing for a while in delphi but now fails in .NET

FYI: The query gives a result but the ExtraWhereClause is not added to the WHERE statement


UserFilter support is not implemented in DataAbstract for .NET. It is considered as a serious security vulnerability and is disabled by default on DataAbstract for Delphi servers as well.

If you need to amend your incoming requests then a better way to do this is to

  1. Disable DA SQL via setting service property AllowExecuteDASQL to false
  2. To inject server side Where condition by adding event handler for the BeforeGetTableData event and setting the eventArgs.AdditionalWhere property

It is also possible to inject DynamicWhere condition directly using code like

    var lRequestInfo = (TableRequestInfoV5)requestInfoV5;
    var lOriginalWhere = RemObjects.DataAbstract.Expressions.WhereExpression.FromXmlNode(requestInfoV5.WhereClause);
    lRequestInfo.WhereClause = RemObjects.DataAbstract.Expressions.WhereExpression.Combine(whereExpression, lOriginalWhere).ToXmlNode();


Please provide a testcase. Dynamic Where processing should not fail in such cases.

Regarding the UserFilter issue I still have 2 issues

  1. I have an SQL string (e.g. ‘(field_sold=1)’) but eventArgs.AdditionalWhere requires type WhereExpression

  2. How should I elegantly implement the BeforeGetTableData solution with custom UserFilter?

I guess the code below would work but we temporarily modify the DataAbstractService which I do not like very much (probably OK if RO does not concurrently service multiple requests with a single Service instance).

BeforeGetTableDataEventHandler decorateWhere = (BusinessProcessor sender, BeforeGetTableDataEventArgs e) =>
string sqlWhere = ExtraWhereClause;
//DOES NOT WORK e.AdditionalWhere = sqlWhere;
this.BeforeGetTableData += decorateWhere;
var ret=this.GetData(aTableNameArray, aTableRequestInfoArray);
this.BeforeGetTableData -=decorateWhere;
return ret;

Yes. This saves server from parsing and validating a string SQL expression. Actually think as if you pass a pre-parsed representation of a SQL statement.

Expression instantiation code should look like

WhereExpression where =
    new BinaryExpression(
      new FieldExpression("OrderDetails","price"),  
      new ConstantExpression(20),

(Note that you can cache this expression in a static variable and reuse it between service calls)

Data Abstract cannot use the same service instance to handle several requests at the same time. Even more, in default configuration service instance is recycled after each request (however this is configurable)

The SQL string is created as a string on the client side and arrives as a string on the server side.
How do I adjust the SQL SELECT statement on the server side?

Note: This (together with issue 26099) means that I am stuck now.
I really need a way to inject where clauses server side.
Can you provide me with a solution?
FYI: I use a RO login service to only allow valid clients so the security constraints can be loosened.

Try the RemObjects.DataAbstract.Server.Parse method. It allows to parse a sql statement into a WhereExpression.

I did not find your function but I found RemObjects.DataAbstract.Server.WhereParser.Parse.
This gives me error
CloseRound expected
wehn I feed it
(vdp_prd_cnk in (2026664,2038073,4227187,2037133,3756152,2028355,2770618,1080886,2028330,3804861,2028348,3583846,2749679,2834943,2038065,2034353,3493731,3549557,3243300,2028371,3188745,2029072,3344264,3689528,4200150,3449493,2973220,2029106,2023000,3344280,3786621,3324811,3188737,2770659,2033488,2022572,4271912,0300855)) AND (vdp_dostock=1)

Any suggestions?

A second attempt with a simpler where clause gives me the validation error
RemObjects.DataAbstract.DAException: 'Validation of Dynamic WHERE for table VerkoopDetailProductGroupPrdEnMaand fails with error(s):
Error 1: Field “vdp_dostock” is not found in the table.

I need a solution where I can tweak the SQL statement (we only use MS SQL server as backend)

Let me try to create a prototype (will take some time)

1 Like

Unfortunately it is not so easy to directly modify service schema. Unlike Delphi, in .NET Schema is shared between service instances, so modifying it without side effects is possible yet can have performance issues.

There is a different approach that might work.
Here is the full source code of a DataService and explanations below:

using RemObjects.DataAbstract.Server;
using RemObjects.SDK.Types;

namespace WinFormsApplication35
	public class DataService : RemObjects.DataAbstract.Server.DataAbstractService
		public DataService()

			// For logging purposes only
			this.BeforeExecutingGetDataReader += DataService_BeforeExecutingGetDataReader;

		private void DataService_BeforeExecutingGetDataReader(RemObjects.DataAbstract.Schema.ServiceSchema sender, RemObjects.DataAbstract.Schema.BeforeExecutingGetDataReaderEventArgs e)

		// Required for designer support
		private void InitializeComponent()
			RemObjects.DataAbstract.Bin2DataStreamer dataStreamer;
			dataStreamer = new RemObjects.DataAbstract.Bin2DataStreamer();
			this.AcquireConnection = true;
			this.ServiceDataStreamer = dataStreamer;
			this.ServiceSchemaName = "WinFormsApplication35Dataset";

		public override Binary GetDataFiltered(string[] tableNames, TableRequestInfo[] requestInfo)
			this.AllowCustomSqlMacros = true;
			this.UnknownSqlMacroIdentifier += (IChannelSender, e) =>
				if (e.Identifier == "ADDITIONAL_WHERE")
					e.Value = "(Id in (1000, 1100, 1048))";
					e.Processed = true;

			return this.GetData(tableNames, requestInfo);

Here I will assume that we have a table Orders in the Schema with statement type set to AutoSQL

The handler for the BeforeExecutingGetDataReader is used to log the SQL statements sent to the server.

Step 1.
Change Orders table Statement Type from AutoSQL to SQL and let Schema Modeler generate the statement
It will be

	"Id", "OrderDate", "OrderStatus", 

Step 2.
Amend statement as

	"Id", "OrderDate", "OrderStatus", 

Note that DASM will think that the statement is broken - that is expected. Also the table won’t be accessible by client apps at this point

Step 3.
Check the GetDataFiltered method code

Enabling AllowCustomSqlMacros for all remote calls by default is very dangerous. This event allows to modify the underlying SQL statements directly and can be used for SQL injection. That’s why it is disabled by default.

In the code above it is enabled and a special event handler is attached. This event handler will take the ADDITIONAL_WHERE placeholder and replace it with the required SQL

This appears to work thanks.
One question: Shouldn’t the UnknownSqlMacroIdentifier eventhandler be removed after the GetData?


The short answer is “it depends, most probably no”

The long answer:
Services can be instantiated using different class factories ( Class Factories (.NET & Delphi) )

By default there is no need to remove the event handler if it is a method in the same service. The entire service instance will be recycled once the request processing is complete.

If the data service uses any ClassFactory other than the Standard one then it would be a good idea to remove the event handler and to disable the AllowCustomSqlMacros flag.

Still in most cases there is no need to use non-default Class Factory of a Data Access service