Cannot display data of Informix table in Database Explorer

[tpwu]

I was getting following error message: "Database not found or no
system permission. [SQL State=IX000, DB Errorcode=-329] Next: ISAM
error: no record found. [SQL State=IX000, DB Errorcode=-111]" while
trying to browse the data inside a table on informix using jdbc
driver. My running sql-workbench version is Build 112 (2012-03-25) on
64-bit windows 7. Any help is greatly appreciated.

Below is the section of the log related to the error:

2012-04-11 10:19 ERROR Error retrieving rowcount for
eqs_prod.informix.ems_keys Database not found or no system permission.
[SQL State=IX000, DB Errorcode=-329]
java.sql.SQLException: Database not found or no system permission.
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:400)
at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3293)
at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3602)
at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2410)
at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2326)
at com.informix.jdbc.IfxSqli.executeStatementQuery(IfxSqli.java:1492)
at com.informix.jdbc.IfxSqli.executeStatementQuery(IfxSqli.java:1465)
at com.informix.jdbc.IfxResultSet.a(IfxResultSet.java:206)
at com.informix.jdbc.IfxStatement.executeQueryImpl(IfxStatement.java:
1228)
at com.informix.jdbc.IfxStatement.executeQuery(IfxStatement.java:218)
at
workbench.gui.dbobjects.TableDataPanel.showRowCount(TableDataPanel.java:
463)
at
workbench.gui.dbobjects.TableDataPanel.showData(TableDataPanel.java:
979)
at
workbench.gui.dbobjects.TableListPanel.retrieveCurrentPanel(TableListPanel.java:
1589)
at workbench.gui.dbobjects.TableListPanel$18.run(TableListPanel.java:
1546)
Caused by: java.sql.SQLException: ISAM error: no record found.
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:400)
at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3607)
... 11 more

[Thomas Kellerer]

It seems that maybe the table name "eqs_prod.informix.ems_keys" used in the DbExplorer is incorrect.

Should it be something different?

My guess is that either eqs_prod or informix should not be used there, right?

I would need to know in which column of the table list (left hand side) those names appear?
How are those columns labelled?

Regards
Thomas

tpwu, 11.04.2012 16:23:

[tpwu]

It is incorrect by attaching database name(eqs_prod) in front of user
(informix). I am getting an error when issuing "select * from
eqs_prod.informix.ems_keys" but no error for "select * from
informix.ems_keys" in the query.

Regards,

TP

On Apr 11, 10:29 am, Thomas Kellerer <google-gro...@sql-workbench.net>
wrote:

[Thomas Kellerer]

OK.

In which column is the database name displayed in the table list?

Regards
Thomas

[tpwu]

The database drop-down list is shown next to User drop-down list right
below the Database Explorer tab. It works fine when I switched to
Vertica. It does not contain the database name in its query inside
the log. Do you think if there is a way to modify it for informix?

On Apr 11, 4:50 pm, Thomas Kellerer <google-gro...@sql-workbench.net>
wrote:

[Thomas Kellerer]

As you still didn't tell me which column of the table list the database name appears in, I'm guessing that it is the "catalog" (in JDBC terms).

Try to add the line:

workbench.sql.ignorecatalog.informix_dynamic_server=*

to the file workbench.settings (exact location is reported in Help -> About).

I'm not 100% that the value "informix_dynamic_server" is the correct key for you.

Please verify the DBID that is used for your connection. This is displayed in the "Connection Info" if your right click on the connection URL in the main window. If a different DBID is reported, please replace the "informix_dynamic_server" in the above line with the DBID shown in the connection info.

Please close SQL Workbench before editing workbench.settings

Regards
Thomas

[tpwu]

Sorry, Thomas. I might miss your earlier question about "column of
the table list the database name". I checked with DBA and Informix
could use the syntax "database_name:user_name.table_name" (instead of
"." between database_name and user_name) to identify a table. Its
catalog systables is used to display the tables within a connected
database. It does not have a column to list the database because the
database name is always the same. Please let me know I didn't answer
your question correctly.

Unfortunately it is still the same after
"workbench.sql.ignorecatalog.informix_dynamic_server=*" is applied to
workbench.settings. I did verify "informix_dynamic_server" is the
correct DBID.


On Apr 12, 12:23 pm, Thomas Kellerer <google-gro...@sql-workbench.net>

[Thomas Kellerer]

> Unfortunately it is still the same after
> "workbench.sql.ignorecatalog.informix_dynamic_server=*" is applied to
> workbench.settings. I did verify "informix_dynamic_server" is the
> correct DBID.

Then please try:

workbench.db.informix_dynamic_server.catalog.dml=false

> I checked with DBA and Informix could use the syntax "database_name:user_name.table_name"
>(instead of "." between database_name and user_name) to identify a table.

Mixing delimiters is not something I currently support.
The delimiter for each part of a fully qualified name must always be the same.

Regards
Thomas

[tpwu]

I am afraid "workbench.db.informix_dynamic_server.catalog.dml=false"
is still the same.

On Apr 12, 2:10 pm, Thomas Kellerer <google-gro...@sql-workbench.net>
wrote:

[Thomas Kellerer]

Can you please set the log level to debug (Tools -> Options -> General) and then send me the logfile after trying again?

The logfile's location is shown under Help -> About.

Btw: which version do you use?

Regards
Thomas

[TP Wu]

Please see the log file in attachment and the version is Build 112 (2012-03-25) on 64-bit windows 7.

--
You are receiving this email because you are a member of the Google Group "sql-workbench".
To post messages to the group, send an email to sql-wo...@googlegroups.com
To unsubscribe from this group, send an email to sql-workbench-unsubscribe@googlegroups.com
More options are available at http://groups.google.com/group/sql-workbench?hl=en

[tpwu]

Just sent you the log file. My version is Build 112 (2012-03-25) on 64-
bit windows 7.

On Apr 12, 3:23 pm, Thomas Kellerer <google-gro...@sql-workbench.net>

[Thomas Kellerer]

OK, I can see what happened:

The driver correctly returns : as the catalog separator, but SQL Workbench uses that for catalog *and* schema separator so the : goes between the schema and the table name as well. (I wasn't aware that there are DBMS that use different characters for both parts).

But you can override that separator using:

workbench.db.informix_dynamic_server.separator.catalog=.

(Note the . after the =)

That one, together with the other two should make it work.

Regards
Thomas

[tpwu]

Yap, it solves the problem. The data tab is working nicely for
Informix.

Thanks a lot!!!

On Apr 12, 4:26 pm, Thomas Kellerer <google-gro...@sql-workbench.net>