This Bugzilla instance is a read-only archive of historic NetBeans bug reports. To report a bug in NetBeans please follow the project's instructions for reporting issues.
- Create a connection to a database using a default schema - Execute 'create table foo(integer id primary key)' - Disconnect - Edit the properties for the database and change the schema - Note that the connection name has changed - Reconnect - Do a 'select * from foo'. You get an empty result set when you should get an error saying the table doesn't exist - Execute 'create table foo(integer id primary key)' and you get an error saying the table already exists This is true on Java DB, PostgreSQL and MySQL The workaround is to create a new connection and select the schema when you connect (for Java DB and PostgreSQL) or use the new schema name for the database name for MySQL.
I think our schema work will fix this...
Now that the db explorer shows all schemas, and can also show all catalogs for db's like MySQL, I think the execution of sql statements from the sql editor run into some trouble. I'm wondering if we shouldn't do the following behind the scenes: - if multiple databases are available on the connection, insert the following prior to the users statements: " use <defaultdb>;" where <defaultdb> is the name of the default db/catalog - if multiple schemas are available on the connection, insert the following prior to the user's statements: " set schema <defaultschema>;" where <defaultschema> is the name of the default schema Without this, things like create table done using direct sql statements are likely to be applied to the wrong schema. Maybe there's another way to handle this? This seemed like a simple approach. I'm raising this to a P2. The current explorer always shows all schemas for a connection, so this is a serious problem.
I thought we always had a default schema. I was going to ask you about that. The default schema should be shown in bold, and there should be a new action called "change default schema" that lets you pick a new default schema. Right now none of that is happening - do you want me to open a separate issue for that? Also, there are other tools that assume a default schema, such as the Entity generator, so we need to make sure that the connection has a default schema selected. When you first open a connection, a specific schema is already selected by the database depending on how you connect, and that's the one we should show in bold. The exception is MySQL when you don't specify a database, in which case we should not have any schema bolded and tools like the Entity generator will fail because it won't be able to find the tables for the current schema (there not being any). This would have been true before your changes anyway. When the user chooses a new default schema, then on the connection *under the covers* we should issue the appropriate command (it differs for each database vendor) to select the schema, whether it's "use <schema>" or "set <schema>". I don't think it's right to show this in the SQL itself. We should probably remember what this new schema is, and when the user reconnects we should set the default schema back to that, so they don't have to change it every time they reconnect. Although it would be odd to use a connection like "jdbc:mysql://localhost:3036/sakila" and the database actually selected is "travel". But if that's what the users do, we should probably remember it. Now, I don't know how each vendor works for databases/catalogs. For Java DB, there is only one database per connection, you can't change this. For MySQL, a schema and a database are identical. I think this deserves further discussion on the dev alias and probably a short spec describing how this all works.
Saying that none of this is happening is simply incorrect. Yes, of course we always had a default schema. But until now we have never shown the other schemas. Yes, the default is selected based on how you connect. The default schema IS being shown in bold. Are you saying that this isn't working for you? Then that's a bug. I agree that the commands should not be shown in the editor. For MySQL, we'd be selecting a default Catalog, not a default Schema. So the command is different. And I assume there are DB's that support both catalogs AND schemas, which gets us into a further mess. I'll start the discussion on the alias.
It wasn't working for me when I was showing this stuff to Jayashri earlier this week. I'll check again.
What makes me think you were using MySQL? Which doesn't show schemas since schemas are not supported. If you were using Derby, for instance, you would see the default schema in bold. It's been there for quite a while now. Let me know if it's not working.
NB6.7 was released already - http://www.netbeans.org/community/releases/67/ Postpone fixing this issue on the next release.
I think everything works as expected. Default schema/catalog is bold and it is possible to set default one.