I am using getExportedKeys method. It is working fine in oracle but in mysql it is returning empty set. I tried using useInformationSchema=true , still it return empty set. Could someone please help me out. Also kindly let me know whether getTables,getExportedKeys,getImportedKeys works for mysql , sql , DB2 , postgres database
DatabaseMetaData is one of the JDBC interfaces that must be implemented by the drivers, so this must be a driver problem. Try using the latest version. If there's still a problem, maybe you could share some code or the version you're using so we can help you more.
In theory, drivers must support the methods of this interface (like the ones you mention), but this is not always true in practice (or only in certain cases or with some special parameters). It's better if you try your program in each database to make sure it's working properly.
@Roel De Nijs I already saw these two sites that you mentioned. One says that he was able to get getExportedKeys working using latest driver. Other says it might be a driver issue and not working. I tried using latest driver but it is not working. So i am not sure whether it works for mysql or not.
@Esteban Herrera I have attached the code herewith. I used the latest driver only but still unsuccessful.
@Roel De Nijs Thanks for the information. Actually my main objective is to avoid using queries. Since I would be connecting to databases of all types(mysql,oracle,db2,postgres,sql ,etc) , I cannot write separate queries for each database(it would be tedious). That's why I opted for this JDBC methods(getTables,getExportedKeys,getImportedKeys).
S Ganesh wrote:Actually my main objective is to avoid using queries. Since I would be connecting to databases of all types(mysql,oracle,db2,postgres,sql ,etc) , I cannot write separate queries for each database(it would be tedious). That's why I opted for this JDBC methods(getTables,getExportedKeys,getImportedKeys).
Because you are using a feature which is not required to be implemented, you can have a database driver where these methods are not implemented. And if it's not supported in one of the databases, you'll have to find a workaround for those specific databases. But with some decent OO design, you could have different implementations of your business logic to retrieve the primary and foreign keys. And based on the database you are connecting to, you use the general implementation (using the JDBC methods) or the specific implementation for that database (cfr. the different Hibernate dialects).
I tested JDBC against Oracle , SQL , DB2 , SYBASE and MySQL too and it works fine. In MySQL alone , getExportedKeys() doesn't work if the storage engine isn't InnoDB.
getExportedKeys() returns the direct child table information only. What if I need grand child information too. Think of it as a tree.
Example: If A->B . If B is child of A , getExportedKeys returns B as child of A. Say if B->C , B has C as child. getExportedKeys of A would not return both B,C and would return only B.
Now I am using recursive calls (getExportedKeys()) to get grand child information. But if I use recursive calls , it is pretty slow. Is there a direct method in JDBC to get child + grand child information too + table info that falls under A (grand child , grand grand child,etc..).
Sorry, I can't think of any way to get the child information directly, but if it's slow, maybe you can get the information when the application starts or cache it somewhere, depending how often it changes or how you use it.