It sure would be great if somebody wrote up a tutorial or blog or whatever describing the different ways to initialize an embedded database for the first time (e.g. using JDBC, JPA, or by including a "starting" database in your JAR file that you copy over...)
David ---------- Forwarded message ---------- From: David Van Couvering <[EMAIL PROTECTED]> Date: Nov 21, 2007 4:38 PM Subject: Re: [nbusers] netbeans 6.0 RC1 SQLSyntaxException: table/view does not exist To: [EMAIL PROTECTED] Yes, that's right, there's no data there. I think it may be time to write a blog about this, or write an FAQ, this comes up a lot. The cool thing about the Java DB embedded database is that it is "just there". You can deploy your app to 1000 machines, and each of those apps will run out of the box, with its own database running on the user's machine. They'll never know it's there, but they get solid db functionality. The logical consequence of this is that when the app is started for the first time, the database needs to be created and initialized. You need to create the tables and load the initial data set that you want the users to work with. This makes sense, right, and so your application needs to handle that. The confusing thing about using the embedded DB inside NetBeans is that you can be led to believe that you just need to hook up your app to an existing connection, and everything should just work. That makes total sense with a database running as a server in a separate process. But an embedded database is a different beast - it doesn't have a unique identity at a specific network address. It belongs to your application, not to the server process. The database DB Explorer is looking at is a *different* database than the one your app is looking at, *even though it has exactly the same URL!* To summarize: DB Explorer points to one database, your app points to another. They have the same URL, and will have the same tables, but they are *not* the same database. What you need to do is detect if your database has not yet been initialized, and then load it up with its initial data set. Does that help? David On Nov 21, 2007 2:55 PM, Wildman <[EMAIL PROTECTED]> wrote: > > David, that suggestion was quite helpful. I edited the persistence.xml file > as you suggested. Now the application does start and shows the 'CRUD' GUI > that is automatically built to view/edit the table I have selected. > > There is just one problem: the table in the app GUI does not have any data, > although the table in the database does have data. I know you mentioned this > in your note, but I guess I am still a bit confused. I would think that the > database is the correct place to hold the table contents, and that the app > would just do a 'SELECT * from table;' SQL call on initialization to fetch > the data from the database, no? In fact, looking at the source, I see just > such a call at program init time. However, there is still apparently some > disconnect between the database and the application access to it. > > Perhaps you can expand on your previous reply? > > Thanks! > -Bill > > ----------------------------------------------- > > > David Van Couvering-2 wrote: > > > > Are you using Derby's embedded driver, and do you have ";create=true" > > in your URL? If so, this is a known issue. > > > > The problem is that an embedded database URL is relative to the > > VM/Classloader running it. So you end up with *two* databases - the > > one owned by the Database Explorer and the one owned by your > > application. The one owned by your application does not have any > > tables in it yet! > > > > This is actually what you want - the value of the embedded database is > > that it is embedded in your application and is created on the fly as > > needed. > > > > You can fix this by modifying your persistence.xml to indicate the > > tables should be created if they don't exist. It's an extra toplink > > property, > > > > <property name="toplink.ddl-generation" value="create-tables"/> > > > > I think this was fixed in RC2 so that the create-tables property is > > generated automatically if you're using the embedded driver. > > > > This won't fill your tables with data, however. Your application will > > need to load the initial data set on startup. Just create instances > > of your JPA objects, set values, and persist them. > > > > David > > > > On Nov 20, 2007 2:33 PM, Wildman <[EMAIL PROTECTED]> wrote: > >> > >> Having successfully built and run the CRUD database example (cars), I > >> created > >> my own database using the built-in java database (Derby) in Netbeans 6.0 > >> RC1. Creating tables, loading content into them, and connecting to the > >> database is all ok. Using the wizard in Netbeans, I built a simple viewer > >> desktop app to view one table in the database. It compiles fine, but I > >> get > >> an SQLException when trying to run it: > >> > >> Nov 20, 2007 5:04:18 PM org.jdesktop.application.Application$1 run > >> SEVERE: Application class linventory.LinventoryApp failed to launch > >> Local Exception Stack: > >> Exception [TOPLINK-4002] (Oracle TopLink Essentials - 2.0 (Build b58g-fcs > >> (09/07/2007))): oracle.toplink.essentials.exceptions.DatabaseException > >> Internal Exception: java.sql.SQLSyntaxErrorException: Table/View > >> 'PART_TYPE' > >> does not exist. > >> Error Code: -1 > >> Call: SELECT part_type_id, name, family, partnum, genus, image FROM > >> part_type > >> Query: ReportQuery(linventory.PartType) > >> at > >> oracle.toplink.essentials.exceptions.DatabaseException.sqlException(DatabaseException.java:319) > >> > >> (this is just the top part of a much longer exception stack dump.) > >> > >> I wonder what is going on here? My database does indeed have a table > >> named > >> 'part_type'. This is all auto-generated code: I have not made any changes > >> whatsoever. I did look at the code, and the SQL query that it is > >> complaining > >> about appears to be fine. > >> > >> Has anyone seen anything like this? Any suggestions? > >> > >> Thanks! > >> -Bill > >> -- > >> View this message in context: > >> http://www.nabble.com/netbeans-6.0-RC1-SQLSyntaxException%3A-table-view-does-not-exist-tf4846652.html#a13866722 > >> Sent from the Netbeans - Users mailing list archive at Nabble.com. > >> > > > > > > > > -- > > David W. Van Couvering > > http://davidvancouvering.blogspot.com > > > > > > -- > View this message in context: > http://www.nabble.com/netbeans-6.0-RC1-SQLSyntaxException%3A-table-view-does-not-exist-tf4846652.html#a13887600 > Sent from the Netbeans - Users mailing list archive at Nabble.com. > -- David W. Van Couvering http://davidvancouvering.blogspot.com -- David W. Van Couvering http://davidvancouvering.blogspot.com
