[gduan01]

Hello. I am also having same problem with Oracle and MySQL not creating the Entity Relationship lines between tables. With Oracle, I using Oracle 9i (9.2.0.4) and currently using the Oracle 9i Thin driver for Java 1.4

Answering your question above:

) Can you browse these schemas in the DB Explorer? Yes

2) When you see the columns of the schema in the DB Explorer, and in the Table Info view, is all the information present? i.e. Primary Keys, Foreign Keys, Indices? Or is some information missing?

I can see information in every column except Indexes (in TOAD I can see indexes).

3) What driver are you using to connect to the database?

I have tried using Oracle8i and Oracle 9i thin drivers, both Java 1.2 and Java 1.4. They all see to have the same result.

[Brian Fernandes]

gduan01,

Can you please look at your log file to see if there have been any exceptions logged relating to this?

A few more questions:
1) Do you see column information displayed in the ER Diagram? I guess you can only see title Nodes?
2) What driver are you using for MySQL? Does the index problem exist there as well? (i.e. you can see all information except the Index information in the Table Info view)?
3) What driver are you using for MySQL?

Thank you for your patience,
Brian.

[gduan01]

Where is the log file ?

1) In Oracle I only see title nodes. In MySQL, I see title nodes and column info.
2) MySql-AB driver v.3.0.5 MySql Database v.4.0.22 I see all table info (no foregn Keys defined)

[Brian Fernandes]

As I mentioned earlier in this thread,
You can find the log file in the .metadata folder under your workspace folder.
The file name is “.log”.

Thanks for the information on Oracle. We are looking it up.

I’m afraid you will not see relationships for MySQL using the standard storage engine because Foreign Key information is not stored. If you use other storage engines with mysql, InnoDB for instance, then FK constraints will be saved and the ER Designer will be able to draw the relationships.

Brian.

[gduan01]

Thanks for the info on MySQL.

As far as the log file, I don’t see exceptions or anything being written to it when creating the ER diagram

[Thomas Benz]

Hi Brian,

Thanks for your quick response.

1) I can browse all the schemas in DB2 and MySQL.

2) In some tables I see the primary- and foreign keys, in some not. The tables of the DB2 sample database do not show any primary- or foreign key (I don’t know if they should). These are the table which do not show the relations in the diagram as well. When I explicitly reference to a table column like in the following example, I get the lines in the diagram:

CREATE TABLE Telefon (
PID INT NOT NULL REFERENCES Person(PID),
TelNr VARCHAR(20) NOT NULL,
CONSTRAINT TelefoneUnique UNIQUE(PID, TelNr) );

The MySQL tables show the keys in the DB Explorer and in the diagram, but they don’t show the lines.

3) I am using these drivers:
mysql-connector-java-3.0.11-stable-bin.jar
com.ibm.db2.jcc.DB2Driver
db2jcc.jar

Thanks,
Thomas

[Brian Fernandes]

Thomas,

The ER Designer will only show relationship lines when they are explicit in the DB schema – just like the CREATE statement you typed out above. Otherwise it does not have enough information to draw them. So the behaviour you see in DB2 is expected.

MySQL by default does not store Foreign Key information, unless you are using a different storage engine. You probably see only Primary Keys in the ER Designer, not Foreign Keys. Could you confirm this? If this is the case, then it’s the same issue I explained above.

Bottom line for everyone: Relationship lines will only be drawn if your database stores Foreign Key Constraints in it’s schema.

Thanks,
Brian.

[Thomas Benz]

Hi Brian,
I can confirm that MySQL does not show the foreign keys while DB2 does.
Thanks
Thomas

[Anil K. Palat]

Hi Brian,

When I log into our database as the schema owner I get all metadata for tables, including foreign keys, column names and types etc. When I login with my account I don’t.

All tables are aliased (synonyms) to users other than the schema owner. The seems to be a problem with persmissions or perhaps synonym related(proabably again, permissions). If you can tell us what tables are used to retrieve the metadata in Oracle9i I can get permissions setup and try the test again.

There are no exceptions being reported in my log file actually there are no messages at all. I am using Eclipse 3.0.1 and MyEclipse 3.8.3GA on Linux.

Thanks for the GREAT product, P.S. any idea when the OSX version will be available?

Thanks
Anil

[james_render50]

@Support-Brian wrote:

James,

I would like you to answer these questions for both schemas (read schema and dba schema)

1) Can you browse these schemas in the DB Explorer?

2) When you see the columns of the schema in the DB Explorer, and in the Table Info view, is all the information present? i.e. Primary Keys, Foreign Keys, Indices? Or is some information missing?

3) What driver are you using to connect to the database?

Thanks,
Brian.

Sorry for the delay in getting back to you Brian.

1). Yes its possible to browse the schemas in the explorer with results below.

2). Read Schema – Only Synonyms visible (but with no details, dunno if I should expect any) – this is what I’d expect.
DBA Schema – All the table information is visible, pk, fk etc. Looks good too

3). Am using the oracle thin driver, good old classes12.zip (well classes12.jar in this case)

Nothing in the .log file

thanks

James

[consistec]

Hi,

I have the same problem with a PostgreSQL database (Version 7.4.5).

1) Can you browse these schemas in the DB Explorer?
Yes.

2) When you see the columns of the schema in the DB Explorer, and in the Table Info view, is all the information present? i.e. Primary Keys, Foreign Keys, Indices? Or is some information missing?
The Foreign Key information is missing. I see a (error) message:
“Spaltenname »FKTABLE_SCHEM« nicht gefunden” (this is german and means:
column name »FKTABLE_SCHEM« not found).

3) What driver are you using to connect to the database?
Driver Version 7.4.1 JDBC 3

Here ist the .log information about this error:

!ENTRY com.genuitec.eclipse.sqlexplorer 4 4 Jan 26, 2005 10:30:12.05
!MESSAGE Error getting fk data
!STACK 0
org.postgresql.util.PSQLException: Spaltenname »FKTABLE_SCHEM« nicht gefunden
at org.postgresql.jdbc1.AbstractJdbc1ResultSet.findColumn(AbstractJdbc1ResultSet.java:673)
at org.postgresql.jdbc1.AbstractJdbc1ResultSet.getString(AbstractJdbc1ResultSet.java:475)
at com.genuitec.eclipse.sqlexplorer.views.details.W.<init>(Unknown Source)
at com.genuitec.eclipse.sqlexplorer.views.details.FKDetail$1.run(Unknown Source)
at org.eclipse.swt.custom.BusyIndicator.showWhile(BusyIndicator.java:69)
at com.genuitec.eclipse.sqlexplorer.views.details.FKDetail.activate(Unknown Source)
at com.genuitec.eclipse.sqlexplorer.views.DetailManager$1.widgetSelected(Unknown Source)
at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:89)
at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:82)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:954)
at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:2595)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:2298)
at org.eclipse.ui.internal.Workbench.runEventLoop(Workbench.java:1377)
at org.eclipse.ui.internal.Workbench.runUI(Workbench.java:1348)
at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:254)
at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:141)
at org.eclipse.ui.internal.ide.IDEApplication.run(IDEApplication.java:96)
at org.eclipse.core.internal.runtime.PlatformActivator$1.run(PlatformActivator.java:335)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:273)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:129)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.eclipse.core.launcher.Main.basicRun(Main.java:185)
at org.eclipse.core.launcher.Main.run(Main.java:704)
at org.eclipse.core.launcher.Main.main(Main.java:688)

Any ideas?

Thanks Georg

[support-jeff]

Postgresql’s 7.4.2 and earlier drivers had a bug in them. You need to upgrade to at least the 7.4.3.

[consistec]

Hi,

thanks for the support. With the new postgresql driver (7.4.4) is the problem (column name »FKTABLE_SCHEM« not found) solved.
and all informations are present in the DB Explorer (PK, FK,…).

> Do you see column information displayed in the ER Diagram?
Yes, I can see the column information displayed in the ER Diagram, with the foreign keys.
BUT not all the (realtions) lines are drawn in the ER diagram.

I am using Eclipse 3.0.1 and MyEclipse 3.8.3 on Debian-Linux.
Nothing in the .log file.

Can you help me again, please?

Thanks Georg

[Brian Fernandes]

3.8.3 had a bug where certain relationships were not displayed if there existed more than one relationship (constraint) in the schema with the same name.
This could occur in Postgres if you created an FK constraint without specifying a relationship name.

To fix this issue, you can modify your schema and naming each constraint. If it isn’t too much trouble, could you do this (maybe only for relationships you see missing) and refresh the diagram (View > Refresh Diagram). That should show all relationships.

This problem is fixed in the upcoming 3.8.4 release; but I would like to confirm that you are seeing the same issue.

Thanks,
Brian.

[consistec]

Thanks, but the renaming of the constraints does not solve the problem.

I have renamed all constraints like this:
CONSTRAINT “$1” FOREIGN KEY (item_id) REFERENCES …
into
CONSTRAINT fk_1 FOREIGN KEY (item_id) REFERENCES …

but the lines are not drawn after refreshing the diagram. Sorry.

Georg

[Author]

Posts