[jeffs]

I have following problem on browsing a local database
(IBM Informix Dynamic Server Version 10.00.TC5TL)
with DBExplorer.
When selecting the tables-node, no tables are listed.
Interestingly it works fine with a remote-db
(linux; Informix Dynamic Server 2000 Version 9.21.UC3)

On grabbing the last executed statement after selecting the table-node we found the following:

select ‘test’ as TABLE_CAT,
s1.owner as TABLE_SCHEM,
s1.tabname as TABLE_NAME,
s2.tabname as SUPERTABLE_NAME
from test:informix.systables s1,
test:informix.systables s2,
test:informix.sysinherits s3
where s1.tabname like ‘%’
and s1.owner like ‘josef’
and s1.tabid = s3.child
and s1.tabname != s2.tabname
and s2.tabid = s3.parent
order by TABLE_NAME , TABLE_SCHEM

One point is, that the user is named as ‘josef’ with small ‘j’ whereas I provided
‘Josef’ with capital ‘J’ in the login dialog.
The other point is, that there seems to be an issue with the sysinherits table. As soon as
it is part of the statement there is no result.

Could you please provide any hints about possible configuration issues or is this a bug somewhere in DBExplorer?
We tried different JDBC drivers (2.21, 2.30, 3.00) without success.

Environment info:

*** Date: Wed Jan 24 15:25:27 CET 2007

*** Platform Details:

*** System properties:
awt.toolkit=sun.awt.windows.WToolkit
eclipse.buildId=M20060921-0945
eclipse.commands=-os
win32
-ws
win32
-arch
x86
-launcher
C:\eclipse\eclipse.exe
-name
Eclipse
-showsplash
600
-exitdata
678_88
-product
com.genuitec.myeclipse.product.ide
-vm
C:\WINDOWS\system32\javaw.exe
eclipse.ee.install.verify=false
eclipse.product=org.eclipse.sdk.ide
eclipse.startTime=1169647490468
eclipse.vm=C:\WINDOWS\system32\javaw.exe
eclipse.vmargs=-Duser.language=en
-Xms128M
-Xmx512M
-XX:PermSize=64M
-XX:MaxPermSize=128M
-Dosgi.splashLocation=C:\MyEclipse\eclipse\MyEclipseSplash.bmp

SNIP, please use MyEclipse > Installation Summary > Installation Details when posting system information

[Haris Peco]

Jeff,

We don’t use direct select commands for grabing information about tables.It is JDBC call.I have made simple test for grabing table and you can try it.
You have to change username,password, url for your database and add jdbc driver’s jar to classpath.It will print all tables in your schema and we use this JDBC call.

It is possible that you haven’t permission and you can try with other user credentails.I haven’t access to some informix database and can’t help you more.

import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TableTest {

    private static String driverName = "YOUR_DRIVER";

    private static String url = "YOUR_URL";

    private static String username = "YOUR_USERNAME";

    private static String password = "YOUR_PASSWORD";
    
    private static String catalog=null;
    
    private static String schema = "YOUR_SCHEMA"; // Josef or josef

    private static Driver driver;

    public static void main(String[] args) {
        try {
            driver = (Driver) Class.forName(driverName).newInstance();
        } catch (Exception e) {
            e.printStackTrace();
        }

        Connection conn = null;
        ResultSet tableResultSet = null;
        try {
            conn = DriverManager.getConnection(url, username, password);
            tableResultSet = conn.getMetaData().getTables(catalog,schema,"%",new String[] {"TABLE"});
            while (tableResultSet.next()) {
                String tableName = tableResultSet.getString("TABLE_NAME");
                System.out.println(tableName);
            }
        } catch (Throwable e) {
            if (tableResultSet != null)
                try {
                    tableResultSet.close();
                } catch (SQLException e1) {
                }
            if (conn != null)
                try {
                    conn.close();
                } catch (SQLException e1) {
                }
            e.printStackTrace();
        }

    }
}

Regards,

[jeffs]

Peco,

thanks for replying. First I want to clarify that it is possible to get user table names with an appropriate statement. The issue is, that after connecting with DatabaseExplorer I cannot see the tables in the tree of the DB browser view. However I need the tables there in order to generate hibernate mapping files from them.

As of the aforementioned select statement: We grabbed this on the target database and assumed it being issued from DatabaseExplorer on querying the tables (it was the last executed statement after clicking on the TABLES node).

On a remote DB it works and on a local one it does not. Using a proprietary tool (also using JDBC) the tables can be displayed correctly, using the same credentials.

I guess that there might be an issue between the statements issued by DatabaseExplorer and some shortcoming of the JDBC driver, but I have no clue what it might be.

[Haris Peco]

Jeff,

Db Explorer doesn’t create some select commands for list of tables .We use simple JDBC call and I have sent you example.
Please, can you try example and test if you got list of tables.
I would like help you more, but I haven’t informix installation for testing.I suppose that Informix JDBC driver expect some nonstandard for
tables listing and I will know more if you can try example from previous post.

Regards

[jeffs]

I executed the example code on both the remoted db (which does cause no problems) and the local (problematic) one.
For the remote database it works as expected. For the local one it throws a NullPointerException in the line “while (tableResultSet.next()) {“.
It seems that in this case the meta data is not correct. I experimented a bit, setting all query parameters in the getTables() call to null, but to no avail.
This behavior seems to explain, why Database Explorer shows no tables. However, why no meta data is returned in the first place is a riddle to me.

I am not sure now where the problem really is. Database Explorer works on the remote database and I can see the tables of the local database with another tool working also with JDBC (though I do not know how they access the table informations).

The issue is now not that critical as we can use the remote database for our mapping process. Though if anyone encounters the same issue (and probably knows the solution) it would be great to hear about.

[Haris Peco]

Jeff,

It looks that you have some strange JDBC issue.I suppose that your another tool (what is it ?) have workaround for this.Unfortunatelly, I haven’t Informix for this, but could you try next :

set line
private static String catalog=null;
to
private static String catalog=”%”; or
private static String catalog=””;

and send me your URL for remote and local database

Thanks,

[jeffs]

Peco,

changing the catalog does not change the outcome. As of the URL I found that we used two different formats.
This kind of URL we used for the remote DB (that worked):
jdbc:informix-sqli://<host>:<port>/<dbname>:INFORMIXSERVER=<dbservername>

and that we used locally (which threw NPE):
jdbc:informix-sqli://<host>:<port>:InformixServer=<dbservername>;DatabaseName=<dbname>

Now we tried the first format for the local DB as well. The NPE goes away, but the ResultSet.next() returns initially false (so no tables again).
It seems indeed to be a JDBC issue of some kind.

[jeffs]

I forgot: The name of the other database tool is “Server Studio JE”.

[Haris Peco]

Jeff,

I’m sure that you can access in local database as well, if you have same setup and same driver.
Are your databases the same (version and setup) ?
Is ‘Server Studio JE’ product from IBm (Informix’s vendor) or independent tool ?

Regards,

[jeffs]

@support-snpe wrote:

Are your databases the same (version and setup) ?

No they are not. The local one is a newer version (see first post). Though I tried the same JDBC drivers.
Concerning the setup I think they are similar, but I didn’t set them up myself.

@support-snpe wrote:

Is ‘Server Studio JE’ product from IBm (Informix’s vendor) or independent tool ?

See here for more information about the product:
http://www.serverstudio.com/products/ssje/

I do not think that I can change the deployment. But we managed to perform the hibernate mapping (what was our initial intention) on the remote db, which has the same structure. So I would say that it is not necessary anymore to connect to the local db with Database Explorer. So I suggest to close this issue for the time being. As the investigations and experimentations showed, the problem seems to reside more on the Informix side of things.
In any case I thank you for your support and your patience.

[Author]

Posts