db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Maarten Meijer <mjmei...@xs4all.nl>
Subject Re: checking for the existence of tables, how?
Date Thu, 18 Sep 2008 15:07:34 GMT
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" <derby-user@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 <mm105@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                      |
>


Mime
View raw message