• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

getExportedKeys() returns empty set in mysql

 
S Ganesh
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Esteban Herrera
author
Ranch Hand
Posts: 42
1
Eclipse IDE Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Ganesh,

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
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This one and this one might be helpful.
 
S Ganesh
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@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
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Maybe you can use some of the INFORMATION_SCHEMA tables to gather the information you want. The KEY_COLUMN_USAGE table describes which key columns have constraints.
 
S Ganesh
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@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).
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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).
 
S Ganesh
Greenhorn
Posts: 21
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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..).
 
Esteban Herrera
author
Ranch Hand
Posts: 42
1
Eclipse IDE Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Ganesh,

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.

Thanks for sharing the MySQL info.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic