[laurent.k]

I use MyEclipse DB Explorer plug-in 3.8.2 within Eclipse 3.0.1

I have created a new driver for Informix-JDBC and all my profiles connections work fine !
I see all the DB tables and I have no problem with SELECT orders, but when I execute a sql script containing simple INSERT orders nothing happened, we have no message indicating us the number of rows inserted or saying us that the operation was successfull and when I look into tables with or without refreshing it, there’s no new row inserted !!!

I don’t think it deals with my informix .jar driver, and when i execute the same script with “Server Studio” all work fine.
My wish is to not use “Server Studio” more longer and work only with MyEclipse DB Explorer so that i only have to open Eclipse to manage my web projects.

Thanks for your help.

[Riyad Kalla]

Can you check your log file (workspace dir)\.metadata\.log for any exceptions related to the DB Explorer? In the mean time I’ve asked our dev to have a look adn see what might be wrong.

[support-jeff]

Couple of things I can think of off the top of my head:
(1) Do you have auto-commit disabled? This could cause the problem you are seeing. You can turn it back on from the password prompt when you connect, or globally via preferences. Be forewarned that your database may not like this. In which case, try issuing a commit after executing the statements – either manually via the editor, or right click on the connection in the browser and select “Commit”

(2) Are you qualifying table names with schema name? If there are multiple schema with tables with the same name, it may be inserting into the wrong one.

Do you see the rows in the table in Server Studio after inserting them in ME?

jeff

[laurent.k]

(1) Do you have auto-commit disabled?
No, it’s enable.

(2) Are you qualifying table names with schema name?
No, but what did you mean exactly with “schema” ?
To my side when i create a connection profile i always select the 2nd option “Display only default schema”, because when i select the first option “Display all schema” it displays me all the databases of my informix instance, each database containing the same tables which are the tables of the databse corresponding to the dbname “toto” specified in the URL (jdbc:informix-sqli://1x.x.xx.xx:1529/toto:INFORMIXSERVER=xxxxx).
And when i select the third option “Display the selected schema”, it lets me choose between different schemas which seem to be different db account (each account having different privileges access) !!!
I don’t understand very well, choosing the third option should permit me to change the user (and its privileges) , in conclusion I create a connection profile with a user and then i can choose another account using the third option “Display the selected schema” ???
I haven’t try to do this, but if it’s work like this what a big security hole !!!
But i’m sure i’m making a big misunderstanding mistake.

That’s why I’m wondering what does a “schema” mean fo you ?
Could you explain me in more details what does each option have to do ?

(3)Do you see the rows in the table in Server Studio after inserting them in ME?
No

Thanks for your reactivity.

[support-jeff]

Schema is a term in SQL standard that is used to give a namespace to tables in the database. The implementation of the standard is up to the RDBMS vendor. So whatever DatabaseMetaData.getSchemaTerm() and getSchemas() (or alternately getCatalogTerm() and getCatalogs(), if the driver does not support schemas) is what you get. By default, if the driver/RDBMS supports neither catalogs or schema, the database name in your connection script is used.

In DE, the “default schema” is the schema with the same name as the username you are using to logon as. If such a schema does not exist, this may be the source of the problem. Do you actually see tables in the browser? The first option should show all schema in the browser; the third should list the same schemas and allow you to choose a subset to display in the browser. If you are seeing different things in the browser than listed under the third option, this might also be an issue. Could you post screenshots of what you see under each option (DE browser for options 1 and 2, the list of schema in the connection wizard under option 3)?

Security is not the realm of JDBC (or the DB Explorer for that matter). Security is controlled by the RDBMS. While you may have the ability to see other users’ schemas, you may not be allowed to alter anything in them. Again, it is up to the RDBMS server to enforce security rules.

[laurent.k]

>Do you actually see tables in the browser?

Yes.

>The first option should show all schema in the browser;

I see all the databases of my informix instance, each database containing the same tables which are the tables of the database corresponding to the dbname “toto” specified in the URL (jdbc:informix-sqli://1x.x.xx.xx:1529/toto:INFORMIXSERVER=xxxxx).

>the third should list the same schemas and allow you to choose a subset >to display in the browser.

It lets me choose between different schemas which seem to be different db account (each account having different privileges access) !!!
I don’t understand very well, choosing the third option should permit me to change the user (and its privileges) , in conclusion I create a connection profile with a user and then i can choose another account using the third option “Display the selected schema” ???

>Could you post screenshots of what you see under each option (DE >browser for options 1 and 2, the list of schema in the connection wizard >under option 3)?

For the screenshots I sent you an e-mail at “support@genuitec.com”.

Thanks for yourreactivity.

[support-jeff]

Ok, apparently it is next to impossible to upload files. Please email them to me directly at jeff at genuitec dot com. Thanx!

[support-jeff]

Got the screenshots, and I think I now understand what is going on with the “schema” selection and display behavior you are seeing. Seems that Informix JDBC Driver supports *both* schemas and catalogs, but interprets them in some specific ways. For the Informix driver, schema means “names of Informix users who own tables” – these are what you see under Option 3 when creating/editing your connection profile in ME (and the “default” schema of option 2). Likewise, the driver interprets catalogs to be “the names of all the databases that currently exist in the Informix database server to which your Java program is connected”. These are what you see if you select Option 1 (show all “schema”). These definitions come straight from the Informix JDBC driver Programmer’s Guide, v 1.22.

So basically, the Connection Profile wizard is looking at schemas first if they are supported, whereas the browser considers catalogs first. Hence the confusion. This is a bug that will need to be addressed in a future release.

Still does not explain the original problem with INSERT. I still suspect that it is because you need to qualify the table name somehow. Please try something like:

INSERT INTO cliserv.TABLE values(…)

I suspect that since you see no errors it is actually inserting into a different “catalog” than the one you are currently looking at in the browser. SInce you have the same set of tables in different catalogs, this is entirely possible and I suspect that if you look at the same table in all catalogs you will find one that Informix is defaulting to (not the default in the DB Explorer, mind you). You might also see if you can restrict to a single catalog in the connection URL, like:

jdbc:informix-sqli://1x.x.xx.xx:1529/toto:INFORMIXSERVER=xxxxx:CATALOGNAME=yyyy

or something. Also, the informix documentation mentioned turning debugging on … this might help diagnose what is happening.

Thanx for your continued patience in dealing with the problem.

[Author]

Posts