Join table error message

Data Abstract
1

Hi guys, some assistance on the following will be highly welcome.

Latest RO DA Server Delphi.
Delphi Berlin Update 2.

I’m doing a custom SQL on the schema that looks like:

‘
SELECT [TABLE1].[FIELD1], [TABLE1].[FIELD2], [TABLE1].[FIELD3]
FROM [TABLE1] INNER JOIN [TABLE2] ON [TABLE1].[FIELD1] = [TABLE2].[FIELD1]
WHERE {WHERE}
’

I want to use a field on table2 to filter the results, basically i want to do a Table2.Field2 = some value on the where clause.

The targettable will be [TABLE1] in case of any updates.

Now on DataAbstract service I have allowAllDynamicFields set to true.

On the client side I’m using an expression like

MemDataTable.DynamicWhere.Expression :=
MemDataTable.DynamicWhere.NewBinaryExpression(
MemDataTable.DynamicWhere.NewField(‘TABLE2’,‘FIELD2’),
MemDataTable.DynamicWhere.DynamicWhere.NewConstant(ID,datInteger), dboEqual);

It is unable to execute the query saying something like Field2 can not be used on the where clause. Whats wrong?

Additionally, what is the equivalent of CreateDataSet on a MemDataTable?

If I want to do additions on a table, and i dont want to open the table and bring all the records because it will bring too much data back to the client do I have to always include some WHERE clause with some invalid value so I can open it? or Could I use something like createdataset on TClientDataSet that will allow me to insert values without having to do the above?

Thank you in advance.

2

by security reasons, we allow to use in dynamic where only fields that are present in select. you can disable this on server side: TDataAbstractService.AllowAllFieldsInDynamicWhere.

I can recommend to open table with RemoteFetchEnabled = false. in this case data won’t be fetched from server.

3

Hi Evgeny,

Thank you for your quick response.

As I wrote originally, the AllowAllFieldsInDynamicWHere is to true. It is by default on false. Both ways, I got the “An exception was raised on the server: ‘FIELD2’ field can’t be used inside the where clause”.

I also tried to add the FIELD2 on the list of select fields but didnt work either.

Any other thing i can look for?

Thank you in advance.

4

your case should work
can you create a simple testcase (with DDL for tables) that reproduces this problem, pls?
I’ll retest this case

you can attach testcase here or send directly to support@