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.
To reproduce: * Create an embedded Java DB connection, choose APP schema (only one available), specify a user and password *other* than app/app (e.g. davidvc/davidvc) * Create a table * Create a new Java SE CRUD project * Select the embedded connection * Select columns from the table * Run You get the following exception: Caused by: java.sql.SQLException: Schema 'DAVIDVC' does not exist
I've seen at least two users complain about this, here is one of them: http://www.nabble.com/Need-Java-DB-embedded-setup-walk-through-for-NB6b2-tf4749375.html#a13580661
Created attachment 52671 [details] Message log file
I tried changing the persistence.xml file to be user 'app', password 'app', and now I get the error: Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.0 (Build b58g-fcs (09/07/2007))): oracle.toplink.essentials.exceptions.DatabaseException Internal Exception: java.sql.SQLException: Table/View 'MYTABLE' does not exist. Error Code: 30000 Call: SELECT ID, LASTNAME FROM MYTABLE I think the reason for this is that the Java application is in a different classloader than the database explorer, and also has a different value for the system property "user.dir", so it actually ends up creating a different database (note that the URL for the embedded driver has ";create=true" so it automatically creates the database if it doesn't exist). If I change the URL to eliminate the ";create=true" property, then I get java.sql.SQLException: Database 'mydb' not found. which indicates that my theory is correct. In other words, the connection I create in the DB Explorer is to a *different* database than the one used by TopLink when you run the application. I'm not sure how we could fix this, given that the project runs in a completely different classloader and with a completely different user.dir than the DB Explorer. It may be we need to say that we don't support the embedded driver for Java DB with this project.
I'm not sure what the motivation is for moving this to the db module. As far as I can tell the bug has to do with your assumption that you can take an embedded database connection and translate that to a JDBC URL in persistence.xml. It just Doesn't Work. It's nothing that the db module can do to fix this, it's how you are using the Database Explorer API.
Created attachment 52678 [details] Updated message log with new exceptions
Adding Andrei, I think we're going to have to have a dialog about who owns this bug.
Just to clarify: the approach the wizard takes assumes that the JDBC URL will connect you to the same database, which is only true if you're using a database server. An embedded database is closely tied with the classloader it runs in; the same URL refers to a different database if the value of the system property "user.dir" is different. I really don't see how to make this work. I need to spend some time thinking about this and will get back to you. But for NB6 I think we should do a release note saying "not supported for embedded Java DB", and I would like the wizard to not allow you to pick a connection that uses the Java DB embedded driver. When you let people pick a connection, see if the driver class is "org.apache.derby.jdbc.EmbeddedDriver". If it is, grey it out or don't show it on the list.
Nothing in common with j2se project impl.
The error saying "no such schema" it turns out can be reproduced with the client driver connecting to the network server as well, so I have opened up a new issue for that, issue 121493. So now *this* issue is solely about the "no such database exists" error, for which there is no workaround that I can figure out.
If the user is trying to use Embedded Java DB then (s)he probably expects the application to create the DB and the corresponding tables if they do not exist already. As for the DB the situation is simple because the URL of DB (created in NetBeans) will contain ";create=true". As for the DB tables, I believe, the solution is to set Table Generation Strategy to 'Create' e.g. add something like <property name="toplink.ddl-generation" value="create-tables"/> into properties of the corresponding persistence unit. Of course, this should happen automatically.
Fixed - when CRUD project is created over Java Embedded DB then the application is set up such that it creates the DB and DB tables (if they don't exist already).
Thanks, jstola. I think you have a good point that an embedded database needs to be "portable" and that the application needs to take care of initializing the database if it doesn't exist (e.g. creating the tables). But what if the connection the user uses has data in it. Wouldn't they expect that the data would also be available? In a hand-crafted embedded app, it is the app developers responsibility to not only create the tables but also set up any initial data sets. If we are building an app with a wizard, perhaps we should take care of this for the user since that is the correct model. I know I would be surprised if, when I run the app, the data I set up is no longer there. Particularly because most people don't understand the embedded model (especially if they are using a wizard) and usually have no idea that there are two copies of the same database. They would be very confused to see no data when they run the app, then they go back to the connection in the DB Explorer, and, whoa, they data is there again! What the @#%$@ is going on? I can see the emails on nbusers now... :) We also need to consider what to do if the URL does *not* include the ";create=true" property, which is actually what we recommend once the database is created. Petr Jiricka mentioned on an email thread about this issue: "trying to prevent the user from shooting herself to the foot often fires back." I'm worried that the more we try to hide the semantics of an embedded database from the user, the more confused they will be. I think we need to let them know what's going on, or disable use of embedded connections. If we want to make this work, I can imagine something like this: * user picks an embedded connection * the wizard says "This is a connection to an embedded Java DB database. The application needs to automatically create and initialize the database on startup. The wizard can generate the code to do this using the connection you have specified as a model. Is this what you want?"
The additional steps mentioned by davidvc are more request for enhancement than a defect.
I agree