You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Maarten Meijer <mm...@xs4all.nl> on 2008/09/18 13:42:34 UTC
checking for the existence of tables, how?
I have an application that store data locally in a embedded database.
it uses ;create=true in the jdbc URL so the db is created if it is not
already there.
But next I would like to query it for the existence of some tables.
How can I do that?
More in general, how can i get meta data such as column names from a
Derby DB?
Re: checking for the existence of tables, how?
Posted by Maarten Meijer <mj...@xs4all.nl>.
Hi,
Had to produce a query result, not an exception in Ibatis, so I solved
it 100% in SQL this way:
<statement id="validate" resultClass="integer">
SELECT COUNT(*) - 3 FROM SYS.SYSTABLES WHERE (TABLENAME =
'ISSUES' OR TABLENAME = 'COMMENTS' OR TABLENAME= 'ATTACHMENTS')
</statement>
A return value of < 0 means it is not fully initialized. Thanks for
the tips!
All this will contribute to making a Derby based local task repository
for Mylyn 3.0.x.
See Eclipse bug report 184532: [connector] Generic SQL connector
https://bugs.eclipse.org/bugs/show_bug.cgi?id=184532
Connect a local Derby DB with task like data to the Eclipse Mylyn Task
List manager.
Maarten
Op 18 sep 2008, om 14:34 heeft Mikael het volgende geschreven:
> This will return a ResultSet of all tables in your database if conn
> is a valid
> Connection object to your database:
>
> DatabaseMetaData metadata = conn.getMetaData();
> String[] names = { "TABLE"};
> ResultSet tableNames = metadata.getTables( null, null, null, names);
>
>
> ----- Original Message ----- From: "Donald McLean" <dmclean62@gmail.com
> >
> To: "Derby Discussion" <de...@db.apache.org>
> Sent: Thursday, September 18, 2008 2:13 PM
> Subject: Re: checking for the existence of tables, how?
>
>
>> On Thu, Sep 18, 2008 at 7:42 AM, Maarten Meijer <mm...@xs4all.nl>
>> wrote:
>>> I have an application that store data locally in a embedded
>>> database.
>>> it uses ;create=true in the jdbc URL so the db is created if it is
>>> not
>>> already there.
>>> But next I would like to query it for the existence of some tables.
>>> How can I do that?
>>>
>>> More in general, how can i get meta data such as column names from
>>> a Derby
>>> DB?
>>
>> In IJ you can use "show tables" to see the system tables (see below).
>> sys.systables and sys.syscolumns should tell you what you want to
>> know.
>>
>> Donald
>>
>> ij> show tables;
>> TABLE_SCHEM |TABLE_NAME |REMARKS
>> ------------------------------------------------------------------------
>> SYS |SYSALIASES |
>> SYS |SYSCHECKS |
>> SYS |SYSCOLPERMS |
>> SYS |SYSCOLUMNS |
>> SYS |SYSCONGLOMERATES |
>> SYS |SYSCONSTRAINTS |
>> SYS |SYSDEPENDS |
>> SYS |SYSFILES |
>> SYS |SYSFOREIGNKEYS |
>> SYS |SYSKEYS |
>> SYS |SYSROLES |
>> SYS |SYSROUTINEPERMS |
>> SYS |SYSSCHEMAS |
>> SYS |SYSSTATEMENTS |
>> SYS |SYSSTATISTICS |
>> SYS |SYSTABLEPERMS |
>> SYS |SYSTABLES |
>> SYS |SYSTRIGGERS |
>> SYS |SYSVIEWS |
>
Re: checking for the existence of tables, how?
Posted by Mikael <mi...@telia.com>.
This will return a ResultSet of all tables in your database if conn is a
valid
Connection object to your database:
DatabaseMetaData metadata = conn.getMetaData();
String[] names = { "TABLE"};
ResultSet tableNames = metadata.getTables( null, null, null, names);
----- Original Message -----
From: "Donald McLean" <dm...@gmail.com>
To: "Derby Discussion" <de...@db.apache.org>
Sent: Thursday, September 18, 2008 2:13 PM
Subject: Re: checking for the existence of tables, how?
> On Thu, Sep 18, 2008 at 7:42 AM, Maarten Meijer <mm...@xs4all.nl> wrote:
>> I have an application that store data locally in a embedded database.
>> it uses ;create=true in the jdbc URL so the db is created if it is not
>> already there.
>> But next I would like to query it for the existence of some tables.
>> How can I do that?
>>
>> More in general, how can i get meta data such as column names from a
>> Derby
>> DB?
>
> In IJ you can use "show tables" to see the system tables (see below).
> sys.systables and sys.syscolumns should tell you what you want to
> know.
>
> Donald
>
> ij> show tables;
> TABLE_SCHEM |TABLE_NAME |REMARKS
> ------------------------------------------------------------------------
> SYS |SYSALIASES |
> SYS |SYSCHECKS |
> SYS |SYSCOLPERMS |
> SYS |SYSCOLUMNS |
> SYS |SYSCONGLOMERATES |
> SYS |SYSCONSTRAINTS |
> SYS |SYSDEPENDS |
> SYS |SYSFILES |
> SYS |SYSFOREIGNKEYS |
> SYS |SYSKEYS |
> SYS |SYSROLES |
> SYS |SYSROUTINEPERMS |
> SYS |SYSSCHEMAS |
> SYS |SYSSTATEMENTS |
> SYS |SYSSTATISTICS |
> SYS |SYSTABLEPERMS |
> SYS |SYSTABLES |
> SYS |SYSTRIGGERS |
> SYS |SYSVIEWS |
Re: checking for the existence of tables, how?
Posted by Donald McLean <dm...@gmail.com>.
On Thu, Sep 18, 2008 at 7:42 AM, Maarten Meijer <mm...@xs4all.nl> wrote:
> I have an application that store data locally in a embedded database.
> it uses ;create=true in the jdbc URL so the db is created if it is not
> already there.
> But next I would like to query it for the existence of some tables.
> How can I do that?
>
> More in general, how can i get meta data such as column names from a Derby
> DB?
In IJ you can use "show tables" to see the system tables (see below).
sys.systables and sys.syscolumns should tell you what you want to
know.
Donald
ij> show tables;
TABLE_SCHEM |TABLE_NAME |REMARKS
------------------------------------------------------------------------
SYS |SYSALIASES |
SYS |SYSCHECKS |
SYS |SYSCOLPERMS |
SYS |SYSCOLUMNS |
SYS |SYSCONGLOMERATES |
SYS |SYSCONSTRAINTS |
SYS |SYSDEPENDS |
SYS |SYSFILES |
SYS |SYSFOREIGNKEYS |
SYS |SYSKEYS |
SYS |SYSROLES |
SYS |SYSROUTINEPERMS |
SYS |SYSSCHEMAS |
SYS |SYSSTATEMENTS |
SYS |SYSSTATISTICS |
SYS |SYSTABLEPERMS |
SYS |SYSTABLES |
SYS |SYSTRIGGERS |
SYS |SYSVIEWS |
Re: checking for the existence of tables, how?
Posted by Johnny Kewl <jo...@kewlstuff.co.za>.
----- Original Message -----
From: "Maarten Meijer" <mm...@xs4all.nl>
To: <de...@db.apache.org>
Sent: Thursday, September 18, 2008 1:42 PM
Subject: checking for the existence of tables, how?
>I have an application that store data locally in a embedded database.
> it uses ;create=true in the jdbc URL so the db is created if it is not
> already there.
> But next I would like to query it for the existence of some tables.
> How can I do that?
>
> More in general, how can i get meta data such as column names from a
> Derby DB?
I havnt tried META functions in java but in theory you should be able to
query and iterate thru tables...
In practice.. most coders will simply create it again... it will throw an
exception... already exists... you trap that and thats how you know...
Have fun...
---------------------------------------------------------------------------
HARBOR : http://www.kewlstuff.co.za/index.htm
The most powerful application server on earth.
The only real POJO Application Server.
See it in Action : http://www.kewlstuff.co.za/cd_tut_swf/whatisejb1.htm
---------------------------------------------------------------------------