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 Donald McLean <dm...@gmail.com> on 2008/09/18 14:13:23 UTC
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 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 |