Dataabstract Delphi custom server problem with join

Data Abstract
,
1

Hello,
I have a rather urgent issue.
I have a join in a customer Delphi server using IbDac.
The join works perfectly well in the schema modeler.
When opening the table on the client, everything is working perfectly also.

Problem is when I try to use a dynamicwhere on the table.
The join consists of 3 tables, each containing a field removed to indicate that the record is removed.
So I have query with the main table files that contains the required fields and the field removed. It also contains the fields needed from the 2 other tables with without the field removed.

When I try to use dynamicwhere on the field removed, it gives me ambigious field.
I’ve tried with an alias for the field but then the table gives me unknown field removed when trying to use dynamicwhere.

2

Hi,
can you show how you have declared SQL in schema and what DynamicWhere expression you have used?

if this is private info, you can drop email to support@ with this info.

3

Hi,
no problem.

This is the SQL:

SELECT FILES.ID, FILES.FILETYPE, FILES.STATUS, FILES.NAMEFILE, FILES.ORDERED,
FILES.SEND, FILES.DATESTARTEVENT, FILES.DATEENDEVENT, FILES.GENERALTOTAL, FILES.COMPANYID, FILES.REMOVED AS FILEREMOVED, RELATIONS.COMPANYNAME AS CUSTOMERCOMPANYNAME, RELATIONS.NAME || ’ ’ || RELATIONS.FIRSTNAME AS CUSTOMERFULLNAME, RELATIONS.REMOVED AS RELATIONREMOVED,
CONTACTS.NAME || ’ ’ || CONTACTS.FIRSTNAME AS CONTACTFULLNAME, CONTACTS.REMOVED AS CONTACTREMOVED
FROM FILES
LEFT OUTER JOIN RELATIONS ON (FILES.RELATION = RELATIONS.ID)
LEFT OUTER JOIN CONTACTS ON (FILES.CONTACT = CONTACTS.ID)
WHERE
{WHERE}

the relations table also contains a removed field and the contacts table also.

I have tried with this statement (now in comment):

tbl_VWFILES.Close;
lDynWhere := tbl_VWFILES.DynamicWhere;
lDynWhere.Clear;
//lDynWhere.Expression := //lDynWhere.NewBinaryExpression(
//lDynWhere.NewBinaryExpression(’’, ‘CompanyId’, dboEqual, ClientDataModule.Company_Id);
// lDynWhere.NewBinaryExpression(‘Files’, ‘FileRemoved’, dboEqual, ‘0’);
//dboAnd);

This is using an alias, I’ve tried directly with removed but then I get ambigious field.

Thx,
Dominique

4

have you tried to specify table name as 1st parameter in NewBinaryExpression ?

lDynWhere.NewBinaryExpression(tablename, ‘CompanyId’, dboEqual, ClientDataModule.Company_Id);

5

Yes,

no luck.
I can try again and give you the exact error message.

Dominique

6

sure, what error was raised?

in some cases, you should set DAService.AllowAllFieldsInDynamicWhere to True

7

Tried that also without luck.
I will send you the details in 10 minutes.

Dominique

8

Ok,

I have changed the query in the schema modeler to this, which validates:
SELECT FILES.ID, FILES.FILETYPE, FILES.STATUS, FILES.NAMEFILE, FILES.ORDERED,
FILES.SEND, FILES.DATESTARTEVENT, FILES.DATEENDEVENT, FILES.GENERALTOTAL, FILES.COMPANYID, FILES.REMOVED, RELATIONS.COMPANYNAME AS CUSTOMERCOMPANYNAME, RELATIONS.NAME || ’ ’ || RELATIONS.FIRSTNAME AS CUSTOMERFULLNAME,
CONTACTS.NAME || ’ ’ || CONTACTS.FIRSTNAME AS CONTACTFULLNAME
FROM FILES
LEFT OUTER JOIN RELATIONS ON (FILES.RELATION = RELATIONS.ID)
LEFT OUTER JOIN CONTACTS ON (FILES.CONTACT = CONTACTS.ID)
WHERE
{WHERE}

Ok,
when not specifying tablenames, I get the following error:
Ambiguous field name between table CONTACTS and table FILES and table RELATIONS REMOVED.

When specifying the table I get the following error:
Column unknow FILES.REMOVED

I have tried this with AllowAllFieldsInDynamicWhere true of false.

Dominique

9

can you check SQL that was sent to IBDAC?
it can be checked in TDAEBaseDataset.DoSetActive (uDAEBaseDataset.pas) .
set breakpoint at NativeDatabaseAccess.Active := True; line and evaluate SQL (or GetSQL).
also you can check properties of native IBDAC object with TIBCQuery(Dataset)

10

Hi,

I’m running through the sourcecode and what I find strange is that in uDAEBaseDataset in the DoSetActive I thet the following return value from GenereateDynmaicWhereStatement:

‘((“Files”.COMPANYID = 1) AND (“Files”.REMOVED = :DAPARAM_1))’

11

Params.Count in the sourcecode also gives me 1 parameter.

12

for Interbase/Firebird, FILES and Files are different tables.
you have to specify valid case

13

I’m so sorry, stupid me.
Solved with putting the tablename in uppercase…

Dominique