Hi all,
i am using MaxDB JDBC version 7.5.0.5 (sapdbc-7_5_0_5.jar) together with
OJB.
i have a problem with inner queries passed through the jdbc driver.
To be more concrete, i have some testcases that tests thoroughly the driver
with various scenarios and it all worked very well.
Then i have set up a testcase that uses inner queries. It works well with
Oracle and DB2, but fails on MaxDB. Investigating the driver communication
with p6spy,
i found out that the following SQL was passed with a prepared statement:
SELECT
A0.PERSONAL_NR,A0.ZW_ADRESSE,A0.VORNAME_TXT,A0.NACHNAME_TXT,A0.GESCHLECHT_ENUM,A0.VERSION_COUNTER,A0.GEHALT_NUM
FROM
MITARBEITER_TABLE A0,ADRESSE_TABLE A1
WHERE
A0.ZW_ADRESSE=A1.OID AND
((A0.VORNAME_TXT = 'Lotte' ) AND
A1.STRASSE_TXT IN
(SELECT
B1.STRASSE_TXT
FROM
MITARBEITER_TABLE B0,ADRESSE_TABLE B1,AUSLANDSADRESSE_TABLE B1E0
WHERE
B0.NACHNAME_TXT=B1.PERSON_NNAME(+) AND
B0.VORNAME_TXT=B1.PERSON_VNAME(+) AND
B0.NACHNAME_TXT=B1E0.PERSON_NNAME(+) AND
B0.VORNAME_TXT=B1E0.PERSON_VNAME(+) AND (B0.VORNAME_TXT = 'Hugo' ))
)
The speciality is i think that it is an inner query. However, i took this
SQL and tried it with the MaxDB SQL Studio and it returns one row.
Then i used the very detailed driver logging mechanism with the same query,
and i saw that the very same query returns an empty result set,
but see yourself:
[EMAIL PROTECTED] ()
=> true
[EMAIL PROTECTED] (SELECT
A0.PERSONAL_NR,A0.ZW_ADRESSE,A0.VORNAME_TXT,A0.NACHNAME_TXT,A0.GESCHLECHT_ENUM,A0.VERSION_COUNTER,A0.GEHALT_NUM
FROM MITARBEITER_TABLE A0,ADRESSE_TABLE A1 WHERE A0.ZW_ADRESSE=A1.OID AND
((A0.VORNAME_TXT = ? ) AND A1.STRASSE_TXT IN (SELECT B1.STRASSE_TXT FROM
MITARBEITER_TABLE B0,ADRESSE_TABLE B1,AUSLANDSADRESSE_TABLE B1E0 WHERE
B0.NACHNAME_TXT=B1.PERSON_NNAME(+) AND B0.VORNAME_TXT=B1.PERSON_VNAME(+)
AND B0.NACHNAME_TXT=B1E0.PERSON_NNAME(+) AND
B0.VORNAME_TXT=B1E0.PERSON_VNAME(+) AND (B0.VORNAME_TXT = ? )) ), 1003,
1007)
=> [EMAIL PROTECTED]
[EMAIL PROTECTED]
(true)
[EMAIL PROTECTED] (1, Lotte, 12)
[EMAIL PROTECTED] (2, Hugo, 12)
[EMAIL PROTECTED] ()
=> [EMAIL PROTECTED]
[EMAIL PROTECTED] ()
=> false
[EMAIL PROTECTED] ()
[EMAIL PROTECTED] ()
... any ideas? Why doesn't the JDBC driver return the same result as with
SQL Studio?
Regards, Andreas Schroeder
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
