Subject: Re: Create table if not exists? - msg#00134

Previous Message by Date: click to view message preview

Re: Derby and third party products

Hi Francois, btw. I don't mean alternation, but updating. Sorry for the confusion! Walter On Sat, 21 Feb 2009 10:22:22 +1000 Walter Rogura <mist@xxxxxxxx> wrote: > Hi Francois, > > As you know, I connect to the derby db as soon as tomcat starts. From > there on I can access the db via a tomcat servlet. In addition, I > wished to connect to the same db via openoffice concurrently. Thanks > to your solution this works now based on the derby network server. > > The issue is now, that my servlet has read-write permission and > openoffice read-only permission. But it would be great if openoffice > would be able to alternate content of the db too. > > Is that possible? Hope I could expressed myself. > > Thank you, > Stephan > > > On Fri, 20 Feb 2009 16:10:51 -0800 > Francois Orsini <francois.orsini@xxxxxxxxx> wrote: > > > Hi Walter, > > > > Glad it helped. Not sure I understand your last issue. > > Do you want alternate the database you want to connect within > > OpenOffice? Like connecting from OpenOffice to the remote database > > started by TomCat and then switching back to a local embedded one in > > OpenOffice? > > > > It would help if you could give some more context or/and a use case > > scenario to describe your last issue. > > > > Cheers > > > > --francois > > > > On Fri, Feb 20, 2009 at 3:54 PM, Walter Rogura <mist@xxxxxxxx> > > wrote: > > > > > Hi Francois, > > > > > > Your help was perfect. Thank you so much! Now I'm able to > > > establish multiple connections. There is one issue left. How do I > > > enable openoffice to alternate the database too and not only my > > > tomcat servlet? I guess I have to set up multiple concurrency > > > levels, but where to specify those? > > > > > > Any help is much appreciated! > > > > > > Thank you, > > > Walter > > > > > > > > > On Mon, 16 Feb 2009 02:07:38 -0800 > > > Francois Orsini <francois.orsini@xxxxxxxxx> wrote: > > > > > > > Hi Walter, > > > > > > > > You need to enable the Derby's instance running in TomCat to act > > > > as a server and allow remote connections. > > > > See > > > > > > > http://db.apache.org/derby/docs/10.4/adminguide/tadminconfig814963.html(for<http://db.apache.org/derby/docs/10.4/adminguide/tadminconfig814963.html%28for> > > > > turning on server mode) > > > > > > > > You would then be able to connect from an OpenOffice client > > > > using Derby's network client JDBC driver (instead of the > > > > embedded ones as the tutorial shows). Of course you can still > > > > use the embedded driver to access the Derby instance started > > > > from within TomCat. > > > > > > > http://db.apache.org/derby/docs/10.4/getstart/getstart-single.html#tgsactivity4 > > > > > > > > For instance, > > > > In 8) replace the Derby embedded connection URL with the one to > > > > connect to the remote instance of Derby started within TomCat. > > > > e.g. > > > > jdbc:derby://localhost:1527/*c:/myfolder/**EactsCongenitalDatabase* > > > > or jdbc:derby://localhost:1527/*EactsCongenitalDatabase* (if you > > > > have set DERBY_SYSTEM_HOME to point to a directory where to > > > > expect to find the database(s) [recommended way] of if Derby was > > > > started in a directory where the database is located). > > > > http://db.apache.org/derby/docs/dev/devguide/cdevdvlp27610.html > > > > > > > > in 9) Use Derby's network client driver instead of the embedded > > > > one class, such as: > > > > org.apache.derby.jdbc.ClientDriver > > > > (make sure to have DerbyClient.jar in OpenOffice classpath as > > > > you did with Derby.jar) > > > > > > > > Hope this helps > > > > > > > > --francois > > > > > > > > On Sun, Feb 15, 2009 at 2:27 PM, Walter Rogura <mist@xxxxxxxx> > > > > wrote: > > > > > > > > > Hey, > > > > > > > > > > Derby works fine with tomcat and even the connection to open > > > > > office runs smoothly (thanks to that tutorial > > > > > > > > http://www.eactscongenitaldb.org/docs/OpenOffice_and_JDBC_for_data_access > > > > > ). > > > > > > > > > > But what does not work is to connect tomcat and open office to > > > > > one and the same derby db concurrently. I always need to stop > > > > > one or the other application to successfully connect to the > > > > > db. > > > > > > > > > > How can I solve this issue? > > > > > > > > > > Thank you, > > > > > Walter > > > > > > > >

Next Message by Date: click to view message preview

Re: Create table if not exists?

Chris wrote: MySQL has a really nice syntax for creating tables if they don't exist already: CREATE TABLE IF NOT EXISTS foo (...) How can I get the same functionality in Derby? I haven't been able to figure out the SQL command to check for the existence of a table. You can use this (Scala code follows): val metadata = connection.getMetaData val rs = metadata.getTables(null, null, null, Array("TABLE")) This should work for any database, but I know it works for Derby, PostgreSQL and Oracle. Regards, Blair

Previous Message by Thread: click to view message preview

Create table if not exists?

MySQL has a really nice syntax for creating tables if they don't exist already: CREATE TABLE IF NOT EXISTS foo (...) How can I get the same functionality in Derby? I haven't been able to figure out the SQL command to check for the existence of a table.

Next Message by Thread: click to view message preview

Re: Create table if not exists?

Kathey Marsden wrote: alternately you can use the DatabaseMetaData to see if the table is there I don't know if this is still the case, but Derby could only do that in a transaction so make sure you call conn.setAutoCommit(false) before. DatabaseMetaData dmd = conn.getMetaData(); ResultSet rs = dmd.getTables(null,"APP", "FOO",null); If you just want the table (not views or system tables), you should specify it using: ResultSet rs = dmd.getTables(null,"APP", "FOO", new String[]{"TABLE"}); if (!rs.next()) { s.executeUpdate("CREATE TABLE FOO (I INT)"); } This is probably the cleanest method especially if you want this DDL statement to be part of a larger transaction, not triggering exception in the middle is usually much better. Hope this helps, Emmanuel -- Emmanuel Cecchet FTO @ Frog Thinker Open Source Development & Consulting -- Web: http://www.frogthinker.org email: manu@xxxxxxxxxxxxxxx Skype: emmanuel_cecchet