[dkirvan]

I am connecting to an AS400 using the MyEclipse DB Explorer. The connection works and I get a list of all libraries on the AS400. Under each library is listed
+System Tables
+Tables
+Views

But when I click on the tables tab nothing is displayed. I believe that I should see a list of tables in the library. I am able to run a query against any tables that I know are in the libraries and recieve records back.

[Riyad Kalla]

Can you please post all the information we request in the [URL=http://www.myeclipseide.com/PNphpBB2+file-viewtopic-t-393.html]Posting Guidelines[/URL] thread at the top of this forum? That will give us some context so that we can determine if this is an installation issue, a configuration problem, or a bug. Thanks.

[dkirvan]

Here is the additional information that you requested that is needed to resolve the issue.

*** Date: Thu Mar 09 07:56:19 GMT-05:00 2006

*** System properties:
OS=WindowsXP
OS version=5.1
Java version=1.5.0_04

*** MyEclipse details:
MyEclipse Enterprise Workbench

Version: 4.1.1 GA
Build id: 20060228-4.1.1-GA

*** Eclipse details:
Eclipse SDK

Version: 3.1.2
Build id: M20060118-1600

Eclipse Platform

Version: 3.1.2
Build id: M20060118-1600

Eclipse RCP

Version: 3.1.2
Build id: M20060118-1600

Eclipse Java Development Tools

Version: 3.1.2
Build id: M20060118-1600

Eclipse Plug-in Development Environment

Version: 3.1.2
Build id: M20060118-1600

Eclipse Project SDK

Version: 3.1.2
Build id: M20060118-1600

Eclipse startup command=-os
win32
-ws
win32
-arch
x86
-launcher
C:\Programs\eclipse-SDK-3.1.2-win32\eclipse\eclipse.exe
-name
Eclipse
-showsplash
600
-exitdata
4d8_2c
-vm
C:\WINDOWS\system32\javaw.exe
************************************************

!SESSION 2006-03-08 09:53:17.47 ————————————————
eclipse.buildId=M20060118-1600
java.version=1.5.0_04
java.vendor=Sun Microsystems Inc.
BootLoader constants: OS=win32, ARCH=x86, WS=win32, NL=en_US
Command-line arguments: -os win32 -ws win32 -arch x86

This is a continuation of log file C:\Documents and Settings\ittraining\workspace5\.metadata\.bak_0.log
Created Time: 2006-03-08 16:40:21.752

*********************
Other requested questions and answers
1. Eclipse was freshly installed 3.1.2
2. No additional plugins were installed.
3. Plugin screen shots shown below that are like org.eclipse.pde.*
org.eclipse.pde
org.eclipse.pde.build
org.eclipse.pde..core
org.eclipse.pde.doc.user
org.eclipse.pde.junit.runtime
org.eclipse.pde.runtime
org.eclipse.pde.source
org.eclipse.pde.ui
org.eclipse.pde.

4. I am using Tomcat5.5.15 but not for MyEclipse Database Explorer usage

5. Database driver shown below
IBM DB2 App Driver
jdbc:as400://99.9.9.9:6789/*LOCAL (where 99.9.9.9 is our as400 ipaddress)
com.ibm.as400.access.AS400JDBCDriver from jt400.jar

[/img]

[Riyad Kalla]

Are there any messages in your log file pertaining to the DB tooling? Try this, shut down MyEclipse, erase your log file, now start it back up and try and browse the AS DB, now paste the contents fo the new log file here for me.

[dkirvan]

!SESSION 2006-03-09 08:52:29.691 ———————————————–
eclipse.buildId=M20060118-1600
java.version=1.5.0_04
java.vendor=Sun Microsystems Inc.
BootLoader constants: OS=win32, ARCH=x86, WS=win32, NL=en_US
Command-line arguments: -os win32 -ws win32 -arch x86

!ENTRY com.genuitec.eclipse.sqlexplorer 4 4 2006-03-09 08:54:16.457
!MESSAGE Error getting table information
!STACK 0
java.sql.SQLException: [SQL0901] SQL system error.
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:485)
at com.ibm.as400.access.AS400JDBCDatabaseMetaData.getTables(AS400JDBCDatabaseMetaData.java:4134)
at net.sourceforge.squirrel_sql.fw.sql.SQLDatabaseMetaData.getTables(SQLDatabaseMetaData.java:603)
at com.genuitec.eclipse.sqlexplorer.model.TableObjectTypeNode.loadTables(TableObjectTypeNode.java:129)
at com.genuitec.eclipse.sqlexplorer.model.TableObjectTypeNode.getChildren(TableObjectTypeNode.java:189)
at com.genuitec.eclipse.sqlexplorer.views.DetailManager.activate(DetailManager.java:151)
at com.genuitec.eclipse.sqlexplorer.views.TableInfoView.setInput(TableInfoView.java:53)
at com.genuitec.eclipse.sqlexplorer.views.DatabaseTreeViewer$4.selectionChanged(DatabaseTreeViewer.java:131)
at org.eclipse.jface.viewers.Viewer$2.run(Viewer.java:162)
at org.eclipse.core.internal.runtime.InternalPlatform.run(InternalPlatform.java:1044)
at org.eclipse.core.runtime.Platform.run(Platform.java:783)
at org.eclipse.ui.internal.JFaceUtil$1.run(JFaceUtil.java:44)
at org.eclipse.jface.util.SafeRunnable.run(SafeRunnable.java:148)
at org.eclipse.jface.viewers.Viewer.fireSelectionChanged(Viewer.java:160)
at org.eclipse.jface.viewers.StructuredViewer.updateSelection(StructuredViewer.java:1784)
at org.eclipse.jface.viewers.StructuredViewer.handleSelect(StructuredViewer.java:1027)
at org.eclipse.jface.viewers.StructuredViewer$4.widgetSelected(StructuredViewer.java:1053)
at org.eclipse.jface.util.OpenStrategy.fireSelectionEvent(OpenStrategy.java:209)
at org.eclipse.jface.util.OpenStrategy.access$3(OpenStrategy.java:204)
at org.eclipse.jface.util.OpenStrategy$1.handleEvent(OpenStrategy.java:364)
at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:66)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:843)
at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3125)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:2758)
at org.eclipse.ui.internal.Workbench.runEventLoop(Workbench.java:1699)
at org.eclipse.ui.internal.Workbench.runUI(Workbench.java:1663)
at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:367)
at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:143)
at org.eclipse.ui.internal.ide.IDEApplication.run(IDEApplication.java:103)
at org.eclipse.core.internal.runtime.PlatformActivator$1.run(PlatformActivator.java:226)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:376)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:163)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.eclipse.core.launcher.Main.invokeFramework(Main.java:334)
at org.eclipse.core.launcher.Main.basicRun(Main.java:278)
at org.eclipse.core.launcher.Main.run(Main.java:973)
at org.eclipse.core.launcher.Main.main(Main.java:948)

[Haris Peco]

dkirvan,

I suppose that you have some system error – try upgrade to last patch for your system

Your driver does’nt handle jdbc DatabaseMetatdata#getTables correct.

See this thread on IBM forum http://www-128.ibm.com/developerworks/forums/dw_thread.jsp?message=13770564&cat=19&thread=101026&treeDisplayType=threadmode1&forum=292#13770564

and this is test case for you

Best

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

import junit.framework.TestCase;

public class As400Test extends TestCase {

    private String driverName = "com.ibm.as400.access.AS400JDBCDriver";
    

    private String url = "jdbc:as400://99.9.9.9:6789/*LOCAL";
    
    
    private String username = "YOUR_USERNAME";

    private String password = "YOUR_PASSWORD";
    private static Driver driver;

    public As400Test(String name) {
        super(name);
        if (driver == null) {
            try {
                driver = (Driver) Class.forName(driverName).newInstance();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    protected void setUp() throws Exception {
        super.setUp();
    }

    protected void tearDown() throws Exception {
        super.tearDown();
    }

    public void testGetTables() {
        Connection connection = null;
        try {
            connection = getConnection();
            DatabaseMetaData md = connection.getMetaData();
            ResultSet tableRs = md.getTables(null,null,"%",new String[] { "TABLE" });
            while(tableRs.next()) {
                String tableName = tableRs.getString("TABLE_NAME");
                System.out.println(tableName);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            fail();
        } finally {
            try {
                if (connection != null)
                    connection.close();
            } catch (SQLException e) {}
        }
    }

    public Connection getConnection() throws SQLException {
        Properties props = new Properties();
        props.setProperty("user", username);
        props.setProperty("password", password);
        return DriverManager.getConnection(url, props);
    }

}

[dkirvan]

I have ran your script and table names were returned from it. I don’t believe that I have a system error since when I use the IBM Rational Application Developer (eclipse based) I can see the tables and fields in the tables.

[Haris Peco]

Fine.Your system is correct (probably), but can you help me that I try find problem.

try change line with getTables with next :

ResultSet tableRs = md.getTables(null,null,”%”,new String[] { “TABLE”});

first 2 parameters are catalog and schema
Can you try set this :

ResultSet tableRs = md.getTables(“YOUR_LIBRARY_NAME”,null,”%”,new String[] { “TABLE”});
and
ResultSet tableRs = md.getTables(null,”YOUR_LIBRARY_NAME”,”%”,new String[] { “TABLE”});

change YOUR_LIBRARY_NAME with name of your libraries

Does it work , too ?

[dkirvan]

I have not been able to get the java app to work when I enter a library name in place of either of the “nulls”. I can enter the catalog name in place of the first null value and still obtain the table names. the following is the errors from trying to use the library name in place of the null.

[SQL0901] SQL system error.
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:485)
at com.ibm.as400.access.AS400JDBCDatabaseMetaData.getTables(AS400JDBCDatabaseMetaData.java:4134)
at HelloJavaPkg.As400Test.testGetTables(As400Test.java:55)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)

[Haris Peco]

dkirvan,

Please, can you execute next test (just add method and previous code) and send us output
What is your library names – I think that myeclipse use your library name like catalog or schema name
We will try fix this with your assistance

public void testGetCatalogsAndSchemas() {
        Connection connection = null;
        try {
            connection = getConnection();
            DatabaseMetaData md = connection.getMetaData();
            System.out.println("productName=" + md.getDatabaseProductName());
            System.out.println("supportCatalogs=" + md.supportsCatalogsInDataManipulation());
            System.out.println("supportSchemas=" + md.supportsSchemasInDataManipulation());
            ResultSet catalogRs = md.getCatalogs();
            System.out.println("Catalogs :");
            while(catalogRs.next()) {
                String name = catalogRs.getString(1);
                System.out.println(name);
            }
            ResultSet schemaRs = md.getSchemas();
            System.out.println("Schemas :");
            while(schemaRs.next()) {
                String schemaName = schemaRs.getString(1);
                System.out.println(schemaName);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            fail();
        } finally {
            try {
                if (connection != null)
                    connection.close();
            } catch (SQLException e) {}
        }
    }

Thanks

[dkirvan]

I have added the additional code to the Testcase program that you have provided. and the results are below. Thank you again for you time and effort in looking into this issue.

java.sql.SQLException: [SQL0901] SQL system error.
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:485)
at com.ibm.as400.access.AS400JDBCDatabaseMetaData.getTables(AS400JDBCDatabaseMetaData.java:4134)
at HelloJavaPkg.As400Test.testGetTables(As400Test.java:55)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
productName=DB2 UDB for AS/400
supportCatalogs=false
supportSchemas=true
Catalogs :
AGRI400
Schemas :
#COBLIB
#LIBRARY
#RPGLIB
ACTIVEXSDK
AGLIB
ASCC
ASCCTOOLS
ASN
CANEMI
CGIDEV2
CRTPLFLIB
CVTSPLV4R1
DBFEXE
DBUINSTALL
DBU60
DIRECTORY
DKIRVAN1
DKIRVAN2
DOUG
EGGEMAIL
EMLIB
EMTRKY
EMTST
ET171017
FMOSS
GDESS
GNLIB
IFSLIB
IFSLIB2
IFSTOOL
JATKINSO
LCONSTAN
LCPGMR
LKSAGLIB
LKSEGGS
LKSEGGS2
LKSEGGS3
LKSEGGS4
LKSHELL
LKSSALES
LS155454
LYNNJRN
MBPFR
MMAIL
MMOFFITT
MPGLIB
NETSEND
PDSQLCOL
PKZ560
QADMBKUP
QADMDISTP
QAFP
QAFPLIB1
QAFPLIB2
QAFPLIB3
QBRM
QCAEXP
QCAP3
QCA400W
QCBL
QCBLLE
QCBLLEP
QCE3
QCLE
QCPPLE
QDEVTOOLS
QDIRSRV2
QDNS
QDOC
QDP4
QDSNX
QFAX
QFNTCPL
QFPNTWE
QGPL
QGPLTEMP
QGY
QHLPSYS
QHTTP
QHTTPSVR
QICSS
QICU
QIJS
QINMEDIA
QINPRIOR
QINSYS
QIWA
QIWA2
QIWS
QIXWSA
QJAVA
QJT400
QLBL
QMGTC
QMGTC2
QMPGDATA
QMSE
QPASE
QPC5250K
QPC5250P
QPC5250T
QPDA
QPFR
QPFRDATA
QPFRD52001
QPFRD53001
QQALIB
QQFTEMP
QQRYLIB
QRCL
QRDARS
QRECOVERY
QRJE
QRPG
QRPGLE
QRPGLEP
QRPG38
QRPLOBJ
QSHELL
QSPL
QSPTLIB
QSQL
QSRV
QSRVAGT
QSYS
QSYSCGI
QSYSDIR
QSYSINC
QSYS2
QSYS2924
QTCP
QTEMP
QTILIB
QTMELCF
QUSRBRM
QUSRDIRDB
QUSRIJS
QUSRINFSKR
QUSRRDARS
QUSRSYS
QUSRTEMP
QWIN16
QWIN16D
QWIN16S
QWIN32
Q1ABRMSF
Q1ABRMSF01
RESTORE
SASINPDEV
SASINPUT
SASJUNK
SASMITH
SASMITHX
SASPMS
SHLIB
SQLINSTALL
SQLPRO
SUPPLYMGR
SYSIBM
TEMP_SQL
TMAIL
TULIB
TWINTER
WHLIB

[Haris Peco]

It’s strange.Database doesn’t support catalogs and return catalog for command
Can you send code in line 55 (throw exception) in your application now ?

Thanks

[dkirvan]

I believe this is what you asked for if not my appologies.

This is line 55 in the program that threw the exception.

ResultSet tableRs = md.getTables(“AGRI400″,”DKIRVAN1″,”%”,new String[] { “TABLE” });

Thank you,
David Kirvan

[Haris Peco]

yes.It is my question.Thanks

I see problem – your driver return catalog AGRI400 and doesn’t support catalogs – it isn’t correct and I will make that MyEclipse handle this case correct
Please, can you tell me next : when you expand connection profile you get tree

xxx-profile
– Connected to xxx
– AGRI400

or what ?

Please, call next commands in your test case :

System.out.println("supportCatalogsInDataManipulation=" + md.supportsCatalogsInDataManipulation());
            System.out.println("supportCatalogsInTableDefinitions=" + md.supportsCatalogsInTableDefinitions());
            System.out.println("supportCatalogsInDataProcedureCalls=" + md.supportsCatalogsInProcedureCalls());

Thanks

[dkirvan]

Here are the results for the added three lines of code. I did not resend the same results I provided earlier.

supportCatalogsInDataManipulation=false
supportCatalogsInTableDefinitions=false
supportCatalogsInDataProcedureCalls=false

The Tree looks like:

– Connected to djk
+#COBLIB
+#LIBRARY
…
+AGLIB
+ASCC
…
+DIRECTORY
– DKIRVAN1
+SYSTEM TABLE
+TABLE
+VIEW
+DKIRVAN
+DOUG

** The … are lines I skipped to show you an example of the tree the tree continues past +DOUG to the rest of the AS400 Libraries (schemas). When I click on the “+” by TABLE under DKIRVAN1 it does not expand. This is the same for all of the schemas.

[Author]

Posts