Problem with Schema on Informix IDS 10UC6

Hi, we are using DBVisualizer 5.x and now we migrate (quite a few user) to the new DBVisualizer Version 6.0.8.
With the new Version we have serious problems with the identification of Schemas. A Database A has 4 possible Schemas: informix/userid1/databasename/testdb
In the Dropdown Listbox you see only the 3 Schemata informix/userid1/databasename. The Schema testdb (which unfortunately is the default Schema for most tables in the database is not visible.
In the 5.x Versions it was not neccessary to provice a Schema. A typical SQL Statement in 5.x looks liek this:

      <Bookmark id="10828">
        <Name>select * from `oc_calculation`...</Name>
        <Note></Note>
        <SQLStatement>
          <Database id="132" />
          <Catalog>onlinecredit</Catalog>
          <Schema />
          <SQL>select * from `oc_calculation`;</SQL>
          <MaxRows>-1</MaxRows>
          <MaxCols>-1</MaxCols>
       ...

Where the Schema is empty. This is no problem with Informix because the tables must be unique over all Schemas in a Database.

But in Version 6 we are forced to set a Schema. You cannot empty the Schema DropDownListbox, nor can you use a setting (or I did not find it) to disable Schema use.

Thanx
Andreas

> {quote:title=Hans Bergsten wrote:}{quote} > In the case where you encounter this error, you do not specify a database in the URL, so the question is which schemas should be shown? Do you have any suggestion? > First of all, lets talk about databases, not schemas. The terms database/schema are somewhat wired in Informix, more on this below. It should show no database at all and the user should be able to choose one. Many People have these database connections predefined in there settings because in the 5.x Version of DBVis it was not possible to change the database in a running session when you did predefine the database. This behaviour has changed in DBVis 6 you can choose anothe database, so basically it is no longer necessary to keep these way of connecting, but we do have this on several maschines, so it would be more convenient for us to support this way of connecting. > The fact that the last chosen schema is used when you change the Schema list to the empty value is a bug, and it will be fixed. However, I'm not sure I follow how you want it to work. The "default schema" in Informix (as far as I can tell from the documentation) is the login name. This is also the schema selected by default in the Schema list. It seems like you want another schema to be the default ("testdb"), but I cannot find any Informix command for changing the default schema for a connection. If there is an Informix command for doing so that I may have missed, you could use it in a connection hook. Is the only problem that you have to select the schema you want as default from the Schema list? Maybe you can elaborate a bit on this? > Basically I do not want a any Schema. You connect to a Informix Server and to a specifix Database. Inside the database are some schemas ,but noone uses these Schemas here. When you create a table schema1.tableA you cannot create another table schema2.tableA. There is a unique index on the table name inside a database. So you can use schemas as a kind of Label, but is is possible to lookup a unique table name without the Schema qualifier. It would be great to have no schema by default and when the user chooses a schema, it could be used as a kind of filter. But having a default Schema userid is a bad filter condition because in 99% of the cases you want another schema. > > P.S.: Feel free to provide me a private beta download and I can verify these Informix specials before you release a fixed Version. Keep that in mind. I know that Informix databases are uncommon nowaday but unfortunately we have some here ;-) Kind Regards Andreas

Hi Andreas, I've had a look at these issues now. See below for some questions and comments. > {quote:title=Andreas Heidrich wrote:}{quote} > Hi Roger, > I downloaded and installed the 6.0.9 Version. Unfortunately it did not fully resolve my problems. > When I choose a Informix Server without qualifiing the database the Debug Window immediately appears with the error below (see stacktrace at the end of the message. This is clearly a bug that we will address. At its core, it's triggered by the execution of the SQL that gets all databases. Previously, this SQL was preceded by a "database sysmaster;" call. This was changed in 6.0.9 because the connection to the sysmaster database had the side-effect that the SQL for getting schemas then returned the schemas from the sysmaster database instead of the database you specified in the URL. In the case where you encounter this error, you do not specify a database in the URL, so the question is which schemas should be shown? Do you have any suggestion? > > Second problem: When I choose a Informix Server with database selected in JDBC URL (i.e. jdbc:informix-sqli://tkgenteab:1548/tobfin:informixserver=ttobiasprj) the database is selected in the SQL Editor and Schema is my userid. For most of the tables the schema is a common string such as testdb. So I have to choose a different Schema every time I open a DB Connection. A better solution would be to leave the Schema empty. Then the default Schema is choose which is testdb. Currently you cannot change the Schema to none (empty) because DBV always uses the last choosen Schema. The fact that the last chosen schema is used when you change the Schema list to the empty value is a bug, and it will be fixed. However, I'm not sure I follow how you want it to work. The "default schema" in Informix (as far as I can tell from the documentation) is the login name. This is also the schema selected by default in the Schema list. It seems like you want another schema to be the default ("testdb"), but I cannot find any Informix command for changing the default schema for a connection. If there is an Informix command for doing so that I may have missed, you could use it in a connection hook. Is the only problem that you have to select the schema you want as default from the Schema list? Maybe you can elaborate a bit on this? > > Third Problem. When I start DBV6.0.9 the Max Rows Setting is ignored and a Query on a large table leads to a memory problem. When you enter a different amount for max rows, it is used but when you restart DBV it is ignored again. Thanks for reporting this. It will be fixed. > > Kr > Andreas > > P.S.: Feel free to provide me a private beta download and I can verify these Informix specials before you release a fixed Version. Best Regards, Hans

A Informix Server with a specific Database runs fine (beside the other things mentioned above): "jdbc:informix-sqli://tkgenteab:1548/tobfin:informixserver=ttobiasprj" While a Informix Server URL in this Form causes the error with the Stacktrace above: "jdbc:informix-sqli://tkgenteab:1548:informixserver=ttobiasprj" We have several Informix Servers running, each with multiple Databases. The former Version of DBViz (5.0.x) did not had any problem with both configurations above. Kr Andreas Edited by: Andreas Heidrich on 17.03.2008 21:20

Hi Andreas, I'm sorry 6.0.9 didn't fully resolve the issue. When you say "When I choose a Informix Server without qualifiing the database ...", do you mean that you connect with a URL that does not specify a database, e.g., "jdbc:informix-sqli://tkgenteab:1548/tobfin:informixserver="? If not, please clarify because I can not reproduce this error. I'll have a look at the other problems. Best Regards, Hans

Another problem appeared

Hi Roger, I downloaded and installed the 6.0.9 Version. Unfortunately it did not fully resolve my problems. When I choose a Informix Server without qualifiing the database the Debug Window immediately appears with the error below (see stacktrace at the end of the message. Second problem: When I choose a Informix Server with database selected in JDBC URL (i.e. jdbc:informix-sqli://tkgenteab:1548/tobfin:informixserver=ttobiasprj) the database is selected in the SQL Editor and Schema is my userid. For most of the tables the schema is a common string such as testdb. So I have to choose a different Schema every time I open a DB Connection. A better solution would be to leave the Schema empty. Then the default Schema is choose which is testdb. Currently you cannot change the Schema to none (empty) because DBV always uses the last choosen Schema. Third Problem. When I start DBV6.0.9 the Max Rows Setting is ignored and a Query on a large table leads to a memory problem. When you enter a different amount for max rows, it is used but when you restart DBV it is ignored again. Kr Andreas P.S.: Feel free to provide me a private beta download and I can verify these Informix specials before you release a fixed Version. Stack: 15:35:00 [ERROR] Error populating Catalog/Schema lists com.onseven.dbvis.F.B.L: java.sql.SQLException: Database not selected yet. at com.onseven.dbvis.F.B.H.execute(Unknown Source) at com.onseven.dbvis.F.B.U.run(Unknown Source) at com.onseven.dbvis.F.B.B.?(Unknown Source) at com.onseven.dbvis.F.B.B.execute(Unknown Source) at com.onseven.dbvis.F.B.U.run(Unknown Source) at com.onseven.dbvis.db.AbstractFacade.getCatalogNames(Unknown Source) at com.onseven.dbvis.G.S.?(Unknown Source) at com.onseven.dbvis.C.J$A.run(Unknown Source) at java.lang.Thread.run(Unknown Source) Caused by: java.sql.SQLException: Database not selected yet. at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:373) at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3208) at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3518) at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2353) at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2269) at com.informix.jdbc.IfxSqli.executePrepare(IfxSqli.java:1153) at com.informix.jdbc.IfxPreparedStatement.e(IfxPreparedStatement.java:318) at com.informix.jdbc.IfxPreparedStatement.a(IfxPreparedStatement.java:298) at com.informix.jdbc.IfxPreparedStatement.(IfxPreparedStatement.java:168) at com.informix.jdbc.IfxSqliConnect.h(IfxSqliConnect.java:5918) at com.informix.jdbc.IfxSqliConnect.prepareStatement(IfxSqliConnect.java:1999) at com.onseven.dbvis.Y.B.C.?(Unknown Source) at com.onseven.dbvis.Y.B.C.invoke(Unknown Source) at $Proxy0.prepareStatement(Unknown Source) at com.onseven.dbvis.F.B.H.prepareStatement(Unknown Source) at com.onseven.dbvis.F.B.H.prepareStatement(Unknown Source) ... 9 more Caused by: java.sql.SQLException at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:373) at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3523) ... 22 more 15:35:00 [ERROR] Error populating Catalog/Schema lists com.onseven.dbvis.F.B.L: java.sql.SQLException: Database not selected yet. at com.onseven.dbvis.F.B.H.execute(Unknown Source) at com.onseven.dbvis.F.B.U.run(Unknown Source) at com.onseven.dbvis.F.B.B.?(Unknown Source) at com.onseven.dbvis.F.B.B.execute(Unknown Source) at com.onseven.dbvis.F.B.U.run(Unknown Source) at com.onseven.dbvis.db.AbstractFacade.getSchemaNames(Unknown Source) at com.onseven.dbvis.G.J.?(Unknown Source) at com.onseven.dbvis.C.J$A.run(Unknown Source) at java.lang.Thread.run(Unknown Source) Caused by: java.sql.SQLException: Database not selected yet. at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:373) at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3208) at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3518) at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2353) at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2269) at com.informix.jdbc.IfxSqli.executePrepare(IfxSqli.java:1153) at com.informix.jdbc.IfxPreparedStatement.e(IfxPreparedStatement.java:318) at com.informix.jdbc.IfxPreparedStatement.a(IfxPreparedStatement.java:298) at com.informix.jdbc.IfxPreparedStatement.(IfxPreparedStatement.java:168) at com.informix.jdbc.IfxSqliConnect.h(IfxSqliConnect.java:5918) at com.informix.jdbc.IfxSqliConnect.prepareStatement(IfxSqliConnect.java:1999) at com.onseven.dbvis.Y.B.C.?(Unknown Source) at com.onseven.dbvis.Y.B.C.invoke(Unknown Source) at $Proxy0.prepareStatement(Unknown Source) at com.onseven.dbvis.F.B.H.prepareStatement(Unknown Source) at com.onseven.dbvis.F.B.H.prepareStatement(Unknown Source) ... 9 more Caused by: java.sql.SQLException at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:373) at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3523) ... 22 more

Thanx for the info Roger (and beautiful new design for your website, congratulation !)

Thanx for the info Roger.

Kr
Andreas

Can you give me any information what taht means, in days, weeks or months ?
We bought an Update on DBVisualizer and we cannot upgrade due to this error because we have a lot of Informix Databases.

Thanx
Andreas

Hi Andreas, I will try to fix this for the next version. Thanks for reporting it, Hans

Ah, I forgot to mention that this causes problems with the code completion only. The statemnt itself is running fine but you cannot use code completion without the use of the correct Schema. This fact makes the code completion feature in V6 less useful for us.

Kr
Andreas