Several errors while doing a query

[Kais Haddadin]

Hello everyone,

I am getting different errors when trying to read from query "select * from MdtListe_jedox", this is itself a query, I am getting

UCAExc:::3.0.0 user lacks privilege or object not found: MDTLISTE_JEDOX

when I try to run the query itself:

UCAExc:::3.0.0 routine signature not found for: PUBLIC.IIF(BOOLEAN,VARCHAR,INTEGER):

I will attach the database.

Cheers,
Kais

[Kais Haddadin]

the database

[Gord Thompson]

Hi, Kais. Thanks for providing the sample file. It is very helpful.

I was able to recreate several of your issues under UCanAccess 3.0.2, and a number of them appear to trace back to cases where a boolean expression

[fieldName]=<someValue>

 
is evaluated when [fieldName] contains Null. For example, if I try to run the query [Kostenstellen] in its current form I get

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.2 external routine invocation exception: null value not allowed

 
but if I change the columns containing the IIf() expressions from

SELECT 
...
IIf([Kostenstellen (Reporting)].[KStArt]='1','Ja','Nein') AS [Niederlassung?], 
IIf([Kostenstellen (Reporting)].[KStArt]='4','Ja','Nein') AS [Werkvertrag?], 
IIf([Kostenstellen (Reporting)].[KStArt]='3','Ja','Nein') AS [On-Site?], 
IIf([Kostenstellen (Historie)].[Ausland?]=0,'Nein','Ja') AS [Ausland?], 
IIf([Kostenstellen (Reporting)].[Unternehmensbereich]=2,'Ja','Nein') AS [Vertrieb?], 
IIf([Kostenstellen (Reporting)].[Unternehmensbereich]=2,'Ja','Nein') AS [Verwaltung?], 
IIf([Kostenstellen (Reporting)].[Unternehmensbereich]=4,'Ja','Nein') AS [intern?],
...

 
to

SELECT 
...
IIf(Nz([Kostenstellen (Reporting)].[KStArt],'')='1','Ja','Nein') AS [Niederlassung?], 
IIf(Nz([Kostenstellen (Reporting)].[KStArt],'')='4','Ja','Nein') AS [Werkvertrag?], 
IIf(Nz([Kostenstellen (Reporting)].[KStArt],'')='3','Ja','Nein') AS [On-Site?], 
IIf([Kostenstellen (Historie)].[Ausland?]=0,'Nein','Ja') AS [Ausland?], 
IIf(Nz([Kostenstellen (Reporting)].[Unternehmensbereich],0)=2,'Ja','Nein') AS [Vertrieb?], 
IIf(Nz([Kostenstellen (Reporting)].[Unternehmensbereich],0)=2,'Ja','Nein') AS [Verwaltung?], 
IIf(Nz([Kostenstellen (Reporting)].[Unternehmensbereich],0)=4,'Ja','Nein') AS [intern?],
...

 
then UCanAccess can run the query without error.

I suspect that you probably don't want to make such changes to your database, so let's wait and see what Marco has to say.

[Marco Amadei]

Trivial bug. Fix will be in the 3.0.3

[Kais Haddadin]

Thanks everyone for the help.
I will wait for the vesion 3.0.3. Marco when is 3.0.3 expected to be released?

Cheers,
Kais

[Marco Amadei]

Not in the next week, I'm full at work and I've to consolidate several fixes and new implementations. In the first(or at latest second) week of December.

[Marco Amadei]

Fixed in svn trunk.

[Kais Haddadin]

I tested with version 3.0.3, update jackson to 2.1.3 as well, but the problems mentioned in my first note are not yet fixed. Should it be fixed then?

Cheers,
Kais

[Marco Amadei]

Hi Kais, sorry for later response,
the fix provided allows null values as first iif argument. I can't provide a iif implementation for each possible combination of the types of the second argument and the third argument. Notice that using different type values there is not correct for these arguments even if ms access supports that. Nevertheless you can add your own function "overloaded" implementation as explained in the ucanaccess website.

[Kais Haddadin]

Ok Marco, thanks for the reply.

I will try to document this to our customers.

Merry Christmas and enjoy the holidays,
Kais