Hello Andreas, would you mind creating a vtrace? (http://sapdb.2scale.net/moin.cgi/VTrace)
Also, please send me the complete JDBC trace, if possible. Have you tried to execute the command without parameters from within JDBC? E.g. when you do it in SQL Studio you execute: > 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' )) > ) but from JDBC you prepare > 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 > = '? )) > ) and later set parameter 1 to 'Lotte' and parameter 2 to 'Hugo'. Alexander Schr�der SAP DB, SAP Labs Berlin > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] > Sent: Thursday, March 18, 2004 11:09 AM > To: [EMAIL PROTECTED] > Subject: MaxDB JDBC & inner queries > > > 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] -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
