- Create a connection to a database using a default schema
- Execute 'create table foo(integer id primary key)'
- Edit the properties for the database and change the schema
- Note that the connection name has changed
- 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
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.