Bug 144118 - "Connecting" to derby db that doesn't yet exist should offer default schema
"Connecting" to derby db that doesn't yet exist should offer default schema
Status: NEW
Product: db
Classification: Unclassified
Component: Derby
6.x
All All
: P3 (vote)
: TBD
Assigned To: Libor Fischmeistr
issues@db
:
Depends on:
Blocks:
  Show dependency treegraph
 
Reported: 2008-08-15 20:19 UTC by Patrick Keegan
Modified: 2013-08-01 12:53 UTC (History)
2 users (show)

See Also:
Issue Type: ENHANCEMENT
:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Patrick Keegan 2008-08-15 20:19:18 UTC
1. Right-click the embedded driver node and choose Connect Using.
2. For the connection URL type EmbeddedDBTest;create=true
3. For user name, type nbuser
4. When prompted to choose a schema in the Advanced tab, accept "APP"
5. Once the "connection" is established, right-click Execute Command and enter a script to create a simple empty table. 

create table products
(
	PRODUCT_ID INTEGER not null primary key,
	PRODTYPE VARCHAR(20),
	MODEL VARCHAR(50),
	PRICE DECIMAL(10)
)

After clicking the Run icon, it appears the script has executed successfully - this is the output:

Executed successfully in 0.1 s, 0 rows affected.
Line 1, column 1

Execution finished after 0.1 s, 0 error(s) occurred.

6. Right-click the connection node and choose Refresh.
7. Expand the Tables, but the table does not appear. 
8. Run the script again and the output shows that a table called products has already been created with the *nbuser* schema
Comment 1 Andrei Badea 2008-08-18 12:15:46 UTC
Sorry, but this is not a defect. Derby has a concept of default schema, which is the schema named after the user name
used to connect. All unqualified (not prepended by a schema name) object names are resolved against this schema. The
schema you select in the Advanced tab is not the default schema, just the schema that will be displayed by the DB
Explorer. A slight problem is that you do not see a NBUSER schema in the Advanced tab -- because when you are connecting
there isn't one. It is created when you first create an object in that schema.
Comment 2 Patrick Keegan 2008-08-18 12:46:20 UTC
yuck; that's pretty ugly, but I can see that it's a corner case that someone would run into this, since it's not typical
that you "connect" to a db that does not yet exist. That said, being able to create such a connection is useful for IDE
code generation features, so it would be nice to have the default schema detected as well for the not-as-yet created db. 

In addition, it would probably be good if the Advanced tab of the dialog explained what setting of the schema does (and
does not do)

Comment 3 Andrei Badea 2008-08-18 16:43:43 UTC
Note you are not connecting to a *database* that doesn't exist. It is the default schema that doesn't exist, and that
only happens in certain corner circumstances. Specifically, if you create your database using the NetBeans support
(Create Database action in the context menu of the Java DB node):

- if you don't specify an user name and password when creating the database, don't specify one when connecting either.
Then the default schema will be APP and everything will work.
- if you do specify and user name and password, then the matching schema is created and selected by default in the
Advanced tab.

Moreover, when you connect to the sample database only do so using the "app" user name and password.

Probably a warning could be added to the Advanced tab stating that the purpose of the schema selection is to restrict
the list of displayed database objects to that schema, and that selecting a schema doesn't affect the connection's
notion of default schema in any way. Actually, if you could come up with a more user-friendly message stating the
equivalent of the above, that would be very nice.
Comment 4 Patrick Keegan 2008-08-18 18:04:03 UTC
In fact, in the corner case I'm dealing with, the db doesn't yet exist (when using the Connect Using command on the
derby embedded driver and specifying create=true in the db URL).

On to the text... I suggest changing "Select a database schema to use" to 

"Select the database schema for which you 

For each database connection in the IDE, the Services window only displays objects of one database schema. Select the
schema of the tables that you want to be displayed.

Notes: 
 - The schema that you select on this page does not change the database's default schema. 
 - If you are creating a connection to a new Java DB database using the Embedded driver, the default schema will be
named after the user name that you specify in the Basic Setting tab. If that schema is not available here, you can later
change the schema by right-clicking the node for the connection, choosing Properties, and changing the Schema property."

I'm not sure about that last note, but it seems to address the situation I've had.
Comment 5 John Baker 2008-08-18 18:15:42 UTC
in the previous comment, did you forget to complete the suggested change ?

>On to the text... I suggest changing "Select a database schema to use" to 
> "Select the database schema for which you 
Comment 6 Patrick Keegan 2008-08-18 18:54:39 UTC
Oops, simply disregard that text. It should start with "For each database connection...."
Comment 7 Andrei Badea 2008-08-18 20:21:32 UTC
> In fact, in the corner case I'm dealing with, the db doesn't yet exist

Ah, I see. In that case I would recommend connecting with the user name "app". There is always an APP schema in a Derby
database, so everything will work. That is, by the way, the reason our sample database uses "app" as the user name.

Regarding the schema message, I would make it a bit shorter and also avoid mentioning any concrete database. Otherwise
we could end up adding another note for MySQL, one for Oracle, etc. So I would suggest:

"For each database connection in the IDE, the Services window only displays objects of one database schema. Select the
schema of the tables that you want to be displayed.

The schema that you select on this page does not change the connection's default schema."
Comment 8 Patrick Keegan 2008-08-19 12:36:23 UTC
OK, the shortened text is fine with me.
Comment 9 John Baker 2008-08-20 02:12:33 UTC
4d8ed87e64a8
Comment 10 Quality Engineering 2008-08-20 17:22:56 UTC
Integrated into 'main-golden', available in build *200808201401* on http://bits.netbeans.org/dev/nightly/
Changeset: http://hg.netbeans.org/main/rev/4d8ed87e64a8
User: John Baker <jbaker@netbeans.org>
Log: #144118 "Connecting" to derby db that doesn't yet exist should offer default schema
Comment 11 Patrick Keegan 2008-08-20 19:08:19 UTC
thanks for the wording addition, John. I've verified it in the builds.

I'm reopening this issue because it started as an RFE for smoother behavior if someone enters a derby password when
creating an embedded db through the Connect Using dialog. Note that it's now an ENHANCEMENT, not a DEFECT
Comment 12 Jiri Rechtacek 2009-10-16 14:14:56 UTC
Reassigned to new owner.


By use of this website, you agree to the NetBeans Policies and Terms of Use. © 2012, Oracle Corporation and/or its affiliates. Sponsored by Oracle logo