Hi Satheesh,
Thanks for all the time you spent on this.
I copied the suggested changes into my codeline and tried running derbyall against it. The existing metadata.java and odbc_metadata.java fail and don't return any row for getImportedKeys test. So, looks like more tweaking is needed to fix the sql in
metadata.properties for getImportedKeys. If you/anyone else think of any tips, please let me know. In the mean time, I will continue to work on my end too.
thanks,
Mamta
On 1/27/06, Satheesh Bandaram <[EMAIL PROTECTED]> wrote:
If I add one following line, getImportedKeys returns only one row for T1.
@@ -544,6 +580,7 @@
CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) ELSE 0 END) <> 0 \
AND K.CONGLOMERATEID = CONGLOMS.CONGLOMERATEID \
AND C.TABLEID = COLS.REFERENCEID \
+ AND CONGLOMS.CONGLOMERATENAME = C.CONSTRAINTNAME \
ORDER BY PKTABLE_CAT, \
PKTABLE_SCHEM, \
PKTABLE_NAME, \
With the change it returns two rows for T2 and no rows for T3. I am not sure if this output is correct nor if the change is OK.
Satheesh
PS: After changing metadata.properties, a new database needs to be created to see changed behavior.
[bandaram:satheesh] java keys T1
******* Call getImportedKeys
****************************************
Imported keys# 1
****************************************
PKTABLE_CAT:
PKTABLE_SCHEM: APP
PKTABLE_NAME: T2
PKCOLUMN_NAME: C21_ID
FKTABLE_CAT:
FKTABLE_SCHEM: APP
FKTABLE_NAME: T1
FKCOULMN_NAME: C11_ID
KEY_SEQ: 1
UPDATE_RULE: 3
DELETE_RULE: 0
FK_NAME: F_12
PK_NAME: SQL060127103319020
DEFERRABILITY: 7
****************************************
[bandaram:satheesh] java keys T2
******* Call getImportedKeys
****************************************
Imported keys# 1
****************************************
PKTABLE_CAT:
PKTABLE_SCHEM: APP
PKTABLE_NAME: T3
PKCOLUMN_NAME: C31_ID
FKTABLE_CAT:
FKTABLE_SCHEM: APP
FKTABLE_NAME: T2
FKCOULMN_NAME: C21_ID
KEY_SEQ: 1
UPDATE_RULE: 3
DELETE_RULE: 0
FK_NAME: F_443
PK_NAME: SQL060127103320650
DEFERRABILITY: 7
****************************************
****************************************
Imported keys# 2
****************************************
PKTABLE_CAT:
PKTABLE_SCHEM: APP
PKTABLE_NAME: T3
PKCOLUMN_NAME: C31_ID
FKTABLE_CAT:
FKTABLE_SCHEM: APP
FKTABLE_NAME: T2
FKCOULMN_NAME: C21_ID
KEY_SEQ: 1
UPDATE_RULE: 3
DELETE_RULE: 0
FK_NAME: F_443
PK_NAME: SQL060127103320650
DEFERRABILITY: 7
****************************************
[bandaram:satheesh] java keys T3
******* Call getImportedKeys
[bandaram:satheesh]
Satheesh Bandaram wrote:
Daniel John Debrunner wrote:Mamta Satoor wrote:My only advice is to break the query down from its inner elements out. Ensure each of those in isolation is returning the correct data. Then work on the next level out. Maybe even creating a view for the working inner elements so the next one to tackle is somewhat readable. E.g. with something like SELECT * FROM T, (SELECT * FROM A,B WHERE ...) AS X WHERE ...I tried to break up the query and run... The inner SELECT is returning just one row, which seems to be correct. So, I suspect we have a problem with the outer query, which joins several system tables with the derived table... I suspect we are missing one join condition, either between system catalogs or between one of the system catalog and the derived table. I will try little bit more... SatheeshStart with SELECT * FROM A,B WHERE ... ensure that works, then do create view SUB_AB AS SELECT * FROM A,B WHERE ... then work on SELECT * FROM T, SUB_AB WHERE ... Hope this is clear, just an idea to make the SQL visually understandable. Maybe remove all the optimizer overrides as well to clear out the clutter. Dan.
