DynamicWhere: Call result in "Token unknown" error

Data Abstract
1

Hello,

I have some problems with a complex SQL-Statement and DynamicWhere, which results in a Token unknown error.

Given is this SQL-Statement:

SELECT TRANS_PREISE.ID, TRANS_PREISE.TRANSFERID, TRANS_PREISE.STRECKENID, TRANS_PREISE.SAID, TRANS_PREISE.EKCURRENCY, TRANS_PREISE.EKBASISCURRENCY, TRANS_PREISE.VKMINITRANSFER, TRANS_PREISE.VKBUSTRANSFER, TRANS_PREISE.VKBOOTSTRANSFER, TRANS_PREISE.VKBUSTRANSFERCHILD, TRANS_PREISE.VKBOOTSTRANSFERCHILD, TRANS_PREISE.EKCURRENCY_TRANS2, TRANS_PREISE.EKBASISCURRENCY_TRANS2, TRANS_PREISE.EKCURRENCY_TRANS3, TRANS_PREISE.EKBASISCURRENCY_TRANS3, 
TRANS_BASE.KREDITORID, TRANS_BASE.EKCURRENCY AS CURRENCYCODE, 
SAISONABSCHNITTE.DATUMVON, SAISONABSCHNITTE.DATUMBIS, 
TRANS_STRECKEN.BEZEICHNUNG AS STRECKENBEZEICHNUNG,
ADRESSEN.NAME AS KREDITORNAME, 
TRANS_EINSATZGEBIETE.AIRPORTCODE, 
AIRPORTS.ISOLAND
FROM TRANS_PREISE 
JOIN TRANS_BASE on TRANS_PREISE.TRANSFERID = TRANS_BASE.ID
JOIN SAISONABSCHNITTE on TRANS_PREISE.SAID = SAISONABSCHNITTE.SAID
JOIN TRANS_STRECKEN on TRANS_PREISE.STRECKENID = TRANS_STRECKEN.ID
join TRANS_EINSATZGEBIETE on TRANS_STRECKEN.EINSATZGEBIETID = TRANS_EINSATZGEBIETE.ID
join AIRPORTS on TRANS_EINSATZGEBIETE.AIRPORTCODE = AIRPORTS.CODE
JOIN ADRESSEN on TRANS_BASE.KREDITORID = ADRESSEN.ID
where
 {WHERE}

On runtime I add one (or more) where-clause(s) by DynamicWhere like this:

if AAirportCode <> '' then
    FQuery.DynamicWhere.Expression := FQuery.DynamicWhere.NewBinaryExpression('TRANS_EINSATZGEBIETE', 'AirportCode', dboEqual, AAirportCode, datString)

Which results in this xml (with AAirportCode = ‘TAG’):

<?xml version="1.0"?><query xmlns="http://www.remobjects.com/schemas/dataabstract/queries/5.0" version="5.0"><where><binaryoperation operator="Equal"><field tablename="TRANS_EINSATZGEBIETE">AirportCode</field><constant type="String" null="0">TAG</constant></binaryoperation></where></query>

This looks ok for me,

When I open the TDAMemDataSet behind FQuery, I got an error “SQL error code -104 Token unknown - line 16, column 51 =.”

Any idea what is going wrong here? I did it this way hundreds of time and it works all fine, only in this statement it goes wrong. For shure I am overlooking something, but I don’t get it.

BTW: Without WhereClause or with a parameterized whereClause the Statement works like a charm.

Environment is Delphi Tokyo 10.2.3 Pro, IBDAC 6.1.7, RODA 9.4.107.1363. Firebird 2.5. 32 bit Windows VCL Application

Kind regards

Udo Treichel

2

Can you set breakpoint at TDAEBaseDataset.DoSetActive method at NativeDatabaseAccess.Active := True; line and check in debugger GetSQL ?

it should show actual SQL that will be opened

3

Hello Evgeny,

thanks for your quick answer.

Here is the resulting SQL-Statement:

‘SELECT TRANS_PREISE.ID, TRANS_PREISE.TRANSFERID, TRANS_PREISE.STRECKENID, TRANS_PREISE.SAID, TRANS_PREISE.EKCURRENCY, TRANS_PREISE.EKBASISCURRENCY, TRANS_PREISE.VKMINITRANSFER, TRANS_PREISE.VKBUSTRANSFER, TRANS_PREISE.VKBOOTSTRANSFER, TRANS_PREISE.VKBUSTRANSFERCHILD, TRANS_PREISE.VKBOOTSTRANSFERCHILD, TRANS_PREISE.EKCURRENCY_TRANS2, TRANS_PREISE.EKBASISCURRENCY_TRANS2, TRANS_PREISE.EKCURRENCY_TRANS3, TRANS_PREISE.EKBASISCURRENCY_TRANS3, ‘#$A’TRANS_BASE.KREDITORID, TRANS_BASE.EKCURRENCY AS CURRENCYCODE, ‘#$A’SAISONABSCHNITTE.DATUMVON, SAISONABSCHNITTE.DATUMBIS, ‘#$A’TRANS_STRECKEN.BEZEICHNUNG AS STRECKENBEZEICHNUNG,’#$A’ADRESSEN.NAME AS KREDITORNAME, ‘#$A’TRANS_EINSATZGEBIETE.AIRPORTCODE, ‘#$A’AIRPORTS.ISOLAND’#$A’FROM TRANS_PREISE ‘#$A’JOIN TRANS_BASE on TRANS_PREISE.TRANSFERID = TRANS_BASE.ID’#$A’JOIN SAISONABSCHNITTE on TRANS_PREISE.SAID = SAISONABSCHNITTE.SAID’#$A’JOIN TRANS_STRECKEN on TRANS_PREISE.STRECKENID = TRANS_STRECKEN.ID’#$A’join TRANS_EINSATZGEBIETE on TRANS_STRECKEN.EINSATZGEBIETID = TRANS_EINSATZGEBIETE.ID’#$A’join AIRPORTS on TRANS_EINSATZGEBIETE.AIRPORTCODE = AIRPORTS.CODE’#$A’JOIN ADRESSEN on TRANS_BASE.KREDITORID = ADRESSEN.ID’#$A’where’#$A’ ((TRANS_EINSATZGEBIETE. = :DAPARAM_1))’

The fieldname after TRANS_EINSATZGEBIETE. is missing. How can this happen?

ut

4

pls check mapping for this table in Schema Modeller. looks like you have empty mapping for this field

5

@EvgenyK, should this maybe generate a cleaner error?

6

Thanks, logged as bugs://80449

7

Hello Evgeny, that was the reason why! There were some empty mappings in the schema. I deleted the target table and recreated the mappings, and all went fine now.

A better error would be helpfull in this case, as Marc mentioned.

Thanks for your support

ut

8

bugs://80449 got closed with status fixed.