Hib Conf View - not seeing some tables

jruffin · **Posted:** Thu Dec 08, 2005 1:38 pm

Hibernate version:3.1
Eclipse: 3.1
DB: DB2/400
OS: AS/400 v5r3m5
Driver/class: jt400.jar / com.ibm.as400.access.AS400JDBCDriver

Three different views - two different results.
Using SQuirrel I can see the entire db folder structure on the 400.
Using MyEclipse 4.0.2 DB Explorer, I can see the entire db folder structure on the 400.
Using the Hibernate Configurations in the Hibernate Console View (in Eclipse) I do not see the entire db folder structure. Actually, I think I only see Views not Tables - as visible in SQuirrel tabs.

I created a hibernate configuration using the same drivers/classes as views named above.
<hibernate-configuration>

Code:

    <session-factory>
        <property name="hibernate.connection.driver_class">com.ibm.as400.access.AS400JDBCDriver</property>
        <property name="hibernate.connection.password">foo</property>
        <property name="hibernate.connection.url">jdbc:as400://<system-ip-address>/<schema></property>
        <property name="hibernate.connection.username">bar</property>
        <property name="hibernate.dialect">org.hibernate.dialect.DB2400Dialect</property>
    </session-factory>
</hibernate-configuration>

Important: When I r-click on my hibernate configuration and select CreateSessionFactory, then expand the Database - I get a host of warnings e.g.:

Code:

WARN Worker-30 org.hibernate.cfg.JDBCBinder - Rev.eng. strategy did not report any primary key columns for OBTML100
WARN Worker-30 org.hibernate.cfg.JDBCBinder - The JDBC driver didn't report any primary key columns in OBTML100. Asking rev.eng. strategy

Lastly, this error:

Code:

org.hibernate.exception.GenericJDBCException: Problems getting meta data for primary keys
   at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:91)
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:79)
   at org.hibernate.cfg.reveng.JDBCReader.processPrimaryKey(JDBCReader.java:284)
   at org.hibernate.cfg.reveng.JDBCReader.readDatabaseSchema(JDBCReader.java:57)
   at org.hibernate.eclipse.console.workbench.LazyDatabaseSchemaWorkbenchAdapter$1.execute(Unknown Source)
   at org.hibernate.console.execution.DefaultExecutionContext.execute(Unknown Source)
   at org.hibernate.eclipse.console.workbench.LazyDatabaseSchemaWorkbenchAdapter.readDatabaseSchema(Unknown Source)
   at org.hibernate.eclipse.console.workbench.LazyDatabaseSchemaWorkbenchAdapter.getChildren(Unknown Source)
   at org.hibernate.eclipse.console.workbench.LazyDatabaseSchemaWorkbenchAdapter.getChildren(Unknown Source)
   at org.hibernate.eclipse.console.workbench.BasicWorkbenchAdapter.fetchDeferredChildren(Unknown Source)
   at org.eclipse.ui.progress.DeferredTreeContentManager$1.run(DeferredTreeContentManager.java:192)
   at org.eclipse.core.internal.jobs.Worker.run(Worker.java:76)
Caused by: java.sql.SQLException: [CPF2207] Not authorized to use object xxxxxx in library yyyyyy type *FILE.
   at com.ibm.as400.access.JDError.throwSQLException(JDError.java:650)
   at com.ibm.as400.access.AS400JDBCDatabaseMetaData.getPrimaryKeys(AS400JDBCDatabaseMetaData.java:2906)
   at org.hibernate.cfg.reveng.JDBCReader.processPrimaryKey(JDBCReader.java:254)
   ... 9 more

The tables/files I want to reverse engineer are not visible? Any clarity is appreciated.

jruffin · **Posted:** Thu Dec 08, 2005 1:59 pm

My bad... It appeared that the list of tables/view/folders were in alpha order in the Hibernate Configurations window. Not so.

They are in alpha order *but* by folder i.e. list all elements in the View folder in alpha order, then list all the elements in the Tables folder in alpha order, etc...

Looks like I can't use the reverse engineering anyway because there are no primary keys on these flat files termed tables on the 400???

The files I want to reverse engineer (expanded) don't have any attributes or columns listed. But some of the other files, of which I'm not interested and also don't have primary keys *do* have attribues/columns listed after clicking the + to expand.

What gives?

max · **Posted:** Thu Dec 08, 2005 2:41 pm

your show an exception which indicates that you dont have the proper permission to reverse engineer aqnd that result in an exception which we can't do much about except stopping...and we then show what ever we could read from the databae until then.

what you can do now is to limit the tables that we look at by creating a reveng.xml.

if the tables really dont have any primary keys then you can also define them in the reveng.xml.

jruffin · **Posted:** Thu Dec 08, 2005 5:33 pm

Thanks Max - credit posted!

I went about this a little backwards (newbie) but it worked according to what you've posted (almost :-) ).

I need tables from 2 different schemas. Before I knew better, I had a default schema defined. You probably don't want to do this if you need tables from more than 1 schema.

I generated artifacts for this (1) schema. Then tweaked the reveng.xml file to include the second schema and tables, removed the default-schema from the cfg.xml file. You probably don't want to define a schema on your connection URL either.

Now, I have N schema's / tables defined in my reveng.xml file and no default schema defined in the cfg.xml file... generate-artifacts...

Problem: artifacts are only being generated for the second schema/ tables (SCHEMANAME2). My reveng.xml is:

Code:

<table-filter match-catalog="catalog-name" match-schema="SCHEMANAME1" match-name="MSCMP"/>
<table-filter match-catalog="catalog-name" match-schema="SCHEMANAME1" match-name="OBSTP"/>
<table-filter match-catalog="catalog-name" match-schema="SCHEMANAME1" match-name="ARCCP"/>
<table-filter match-catalog="catalog-name" match-schema="SCHEMANAME2" match-name="MSCMQ"/>
<table-filter match-catalog="catalog-name" match-schema="SCHEMANAME2" match-name="OBSTQ"/>

Looks like SCHEMANAME1 is not found unless I define it as the default schema???

Oh, one more thing - eclipse is complaining about the formal public identifier not being found in reveng.xml

Code:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-reverse-engineering PUBLIC "-//Hibernate/Hibernate Reverse Engineering DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-reverse-engineering-3.0.dtd" >

max · **Posted:** Thu Dec 08, 2005 6:02 pm

are you sure the schemaname1 is the proper case/spelling and you have the proper rights ?

try and enable a debug log to see if you can track it down...

jruffin · **Posted:** Fri Dec 09, 2005 4:42 am

Thanks, yes - schemaname1 is spelled correctly. I didn't change it. I generated artifacts for it initially. Once I added the schemaname2 tables to the reveng.xml, schemaname1 artifacts we're not generated again - only schemaname2 artifacts.

Quote:

try and enable a debug log to see if you can track it down...

That sounds like it means something, but I'm not sure what :-). Please elaborate...

max · **Posted:** Fri Dec 09, 2005 4:59 am

please show the configuration of the two different 'runs' that you say are different.

(you enable logging easiest by using ant and put a log4j.properties in the classpath)

jruffin · **Posted:** Fri Dec 09, 2005 1:12 pm

I think I'm almost there...

Manually adding <table-filter> elements to reveng.xml is not preferrable unless you can see the schema for which your table belongs - in your hibernate.configuration (under the +Database element).

To add multiple schema tables/elements to the reveng.xml - create multiple hibernate.configurations for all schema's not visible under the +Database element.

If you manually add <table-filter> elements for non-visible schema's when you generate artifacts - artifacts will only be generated for schemas visible (again, under the +Database element).

This makes total scence, because if you don't see the schema you want to reveng then it's not in the SessionFactory, if it's not in the SF then the metadata can't be read. It's not enough, to hava a connection to the DB (SF) even if all your schemas are on that DB but some are not visible. To reveng, the schema must be visible (i.e. in the SF).

So, if you add multiple hibernate.configurations then create the reveng.xml *include* all the tables you want from one hibernate.configuration. Then, use the drop down to select another hibernate.configuration, *include* the tables in the visible schemas, etc...

My problem now, my doc's only include 1 schema (i.e. not all schema's/tables have generated html documentation).

max · **Posted:** Fri Dec 09, 2005 1:47 pm

im sorry but i got completely lost in that last posting....could you try and reexplain what your problem is ?

jruffin · **Posted:** Fri Dec 09, 2005 3:33 pm

Sorry Max, I was trying to document a fix & mention a problem.
Here my attempt at clearing it up:

I want to reveng tables from N schemas. I can't see all the schemas from the same DB using 1 hibernate.configuration. So, creating multiple hibernate.configurations does the trick.

Quote:

Each hib.config using a different cfg.xml and specifing different schema names on the connection url

STEPS:
In my Hibernate Configurations Tab I have 2 hibernate.configurations. I created session-factories for both. Now, all the schemas I want to reveng are visible.

Next, I proceed to create the reveng.xml...
... select then include the schemas/tables from one hib.config
... select then include the schema/tables from the second hib.config.

At this point, the tool has created my reveng.xml with N schema's/tables (no manual editing).

Next, I generate-artifacts. No problem here. Classes and hbm.xml for all schema/tables included above are generated.

Problem (& a small one at that :-) ): the html that's generated, which documents the fields in the schema's/tables - only documents 1 schema not *.

I have some other problems like # signs in db column names but not tools related.

Is that any clearer / make any scence :-)?

max · **Posted:** Fri Dec 09, 2005 3:53 pm

crystal ;)

Couple of questions:

1. why do you limit the url to one schema if you want multiple schemas ?
(because of the sf db will be too big ?)

2. You do know you can open two editors on the same file in Eclipse ? (Window/New Editor) then you can have two pages from the reveng.xml visible at the same time

stuff that might help you when i get around to add it:

- support for a reveng.xml to use when showing tables in the config outline

- support in the reveng.xml to explicit list the schema/catalog it should use when calling metadata.getTables() will allow it to be way faster than now and still have the flexibility it provides

- (other ideas?)

jruffin · **Posted:** Fri Dec 09, 2005 5:40 pm

Quote:

1. why do you limit the url to one schema if you want multiple schemas ?
(because of the sf db will be too big ?)

I haven't figured out why, but when I don't specify a schema on the url - none of the schema's I want to reveng are visible.

I think you alluded to the amount or type of error's generated...

Quote:

...and we then show what ever we could read from the databae until then

I'm thinking the tool gets to an exception that it can't handle (maybe a permissions exception) then returns what it could from the DB and that's why the other schemas are not visible.

However, if I specify the schema, then the tool appears to read the specified one first then proceed and stop on the error(s) / exceptions.

- (other ideas?)
I think the two you mentioned will add value.
<dangerous>I'm thinking,</dangerous> :-) if there were a way to explicitly state the target schemas *before* populating the reveng.xml (i.e. in the cfg.xml) that would be cool. I think, reveng.xml gets it's starting db parameters from this file. If reveng.xml could gather a list instead of (1)hibernate.default-schema || (1)schema on the URL connection - maybe that list could be passed to metadata.getTables() and you would have acheived your second idea.

Excuse my attempt at humor </dangerous>.

Seriously, that probably would have allowed me to see *only* my target schemas in 1 hibernate.configuration(sf) and avoided all this fun!