Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: neutral (asf.osuosl.org: local policy)
Message-ID: <43A30DF8.1020608@Sourcery.Org>
Date: Fri, 16 Dec 2005 10:56:56 -0800
From: Satheesh Bandaram <satheesh@Sourcery.Org>
User-Agent: Mozilla Thunderbird 0.7.3 (Windows/20040803)
MIME-Version: 1.0
To: Derby Development <derby-dev@db.apache.org>
Subject: Re: derby user (Forwarding to DerbyDev)
References: <20051216131129.88383.qmail@web8411.mail.in.yahoo.com>
 <43A30B22.9020603@bristowhill.com>
In-Reply-To: <43A30B22.9020603@bristowhill.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

We keep seeing many requests for this kind of behavior... It should be
pretty easy to enhance IJ to add DESCRIBE like functionality. This would
be a good project for anyone interested in learning Derby system tables
and some JDBC programming, so I am forwarding this to DerbyDev.

We do have a Jira entry for this new feature already:
http://issues.apache.org/jira/browse/DERBY-457

Satheesh

Jean T. Anderson wrote:

> meenakshi selvi wrote:
>
>> hi
>>  
>> 1.is there any way to describe the table in ij tools.
>
>
> You can execute SQL select statements that query the system tables.
> The system tables are listed here
>
>    http://db.apache.org/derby/docs/dev/ref/rrefsistabs38369.html
>
> Obtaining the description for a table is pretty simple if you just
> want the column names and types; for example, given this table:
>
>    ij> create table hotelavailability
>    (hotel_id int not null,
>    booking_date date not null,
>    rooms_taken int default 0,
>    primary key (hotel_id, booking_date)
>    );
>
> This query gets you the information in the sys.systables table:
>
>   select * from sys.systables where tablename = 'HOTELAVAILABILITY';
>
> This query gets the columns for that table:
>
>    ij> select c.columnnumber,
>        c.columnname,
>        c.columndatatype
>    from   sys.syscolumns c, sys.systables t
>    where  c.referenceid=t.tableid
>    and    t.tablename='HOTELAVAILABILITY'
>    order by 1;
>
>    COLUMNNUMB&|COLUMNNAME             |COLUMNDATATYPE
>    -----------------------------------------------------
>    1          |HOTEL_ID               |INTEGER NOT NULL
>    2          |BOOKING_DATE           |DATE NOT NULL
>    3          |ROOMS_TAKEN            |INTEGER
>
>    3 rows selected
>
>
> Add a join to sys.syscontraints to get constraint information --
> here's where the query can start getting really messy.  dblook might
> be an easier option; see the next point.
>
>> 2.is there any way to show the tables in a database.
>
>
> dblook dumps the schema for a database or table; for more information,
> see
>
>  http://db.apache.org/derby/docs/dev/tools/ctoolsdblook.html
>
> regards,
>
>  -jean
>
>
>