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 yves pielusenet <yv...@free.fr> on 2006/09/15 00:07:55 UTC
Retreive colummn name of a table from java
Hello,
I read this :
http://wiki.apache.org/db-derby/ListTableColumns
but I can't retreive the column of my table 'data'. Here is what i
tested :
ij> select columnnumber, columnname, columndatatype
from sys.systables t, sys.syscolumns, sys.sysschemas s
where tableid=referenceid and t.schemaid=s.schemaid
and schemaname='SYS' and tablename='data'
order by columnnumber;
And the result is :
COLUMNNUMB&|COLUMNNAME|COLUMNDATATYPE
------------------------------------------------------------------------------------------------------------------------------------------------------------
0 lignes sélectionnées
How can I do ?
thanks,
--
yves piel
Re: Retreive colummn name of a table from java
Posted by Francois Orsini <fr...@gmail.com>.
On 9/15/06, yves pielusenet <yv...@free.fr> wrote:
>
> Ok thank you Francois (are you french as me ?) :)
De rien Yves :)
I understand now. It should be specified into this doc :
> http://wiki.apache.org/db-derby/ListTableColumns
>
> that schemaname='SYS' is for the example but if we want to retreive
> columns of a personal table, we should use schemaname='APP' :)
That is correct - the example query was for the SYS.SYSCONSTRAINTS system
table/catalog. Good suggestion about the doc. Also, 10.2 version of Derby
now has the IJ 'describe' command to display table columns information (
http://db.apache.org/derby/docs/dev/tools/rtoolsijcomrefdescribe.html).
If you want to try out the 10.2 beta version of Derby - You can find it
here:
http://wiki.apache.org/db-derby/TenTwoSnapshot
Le vendredi 15 septembre 2006 à 00:08 -0700, Francois Orsini a écrit :
> > All the Derby database system tables reside in the 'SYS' schema
> > whereas the default "user" schema is named 'APP'. When you log in to
> > derby, the default schema where the objects are created is 'APP'.
> >
> > In your (own) query, you had not restricted the query against any
> > particular schema, hence it picked it up.
> >
> > On 9/15/06, yves pielusenet <yv...@free.fr> wrote:
> > Thank you Francois :)
> > It works with that schema. but what is the difference between
> > APP and
> > SYS ?
> > And the result is the same as my statement. Is my own wrong ?
> >
> > Le jeudi 14 septembre 2006 à 23:51 -0700, Francois Orsini a
> > écrit :
> > > Ok - Yves, can you try against the schema 'APP' (default)
> > such as:
> > >
> > > ij> select columnnumber, columnname, columndatatype
> > > from sys.systables t, sys.syscolumns, sys.sysschemas s
> > > where tableid=referenceid and t.schemaid=s.schemaid
> > > and schemaname='APP' and tablename='DATA'
> > > order by columnnumber;
> > >
> > > On 9/14/06, yves pielusenet <yv...@free.fr> wrote:
> > > Yes I have tried it but there is any rows back :(
> > >
> > > ij> select columnnumber, columnname, columndatatype
> > > from sys.systables t, sys.syscolumns,
> > sys.sysschemas s
> > > where tableid=referenceid and
> > t.schemaid=s.schemaid
> > > and schemaname='SYS' and tablename='DATA'
> > > order by columnnumber;
> > > COLUMNNUMB&|COLUMNNAME|COLUMNDATATYPE
> > >
> >
> ------------------------------------------------------------------------------------------------------------------------------------------------------------
> > >
> > > 0 lignes sélectionnées
> > >
> > >
> > > Le jeudi 14 septembre 2006 à 15:37 -0700, Francois
> > Orsini a
> > > écrit :
> > > > Have you tried the following below?
> > > >
> > > > ij> select columnnumber, columnname,
> > columndatatype
> > > > from sys.systables t, sys.syscolumns,
> > sys.sysschemas s
> > > > where tableid=referenceid and
> > t.schemaid=s.schemaid
> > > > and schemaname='SYS' and tablename='DATA'
> > > > order by columnnumber;
> > > >
> > > > are you getting any rows back?
> > > >
> > > > On 9/14/06, yves pielusenet
> > <yv...@free.fr> wrote:
> > > > after tests I found this statement :
> > > > ij> select columnname, columnnumber from
> > > sys.systables,
> > > > sys.syscolumns where referenceid=tableid
> > and
> > > tablename='DATA'
> > > > order by columnnumber;
> > > >
> > > > which works well for me :)
> > > >
> > > > do you see something wrong ?
> > > >
> > > >
> > > > Le jeudi 14 septembre 2006 à 15:16 -0700,
> > Francois
> > > Orsini a
> > > > écrit :
> > > > > Yes,
> > > > >
> > > > > It all depends how you created the table
> > - have
> > > you tried
> > > > running the
> > > > > query by specifying you table name in
> > uppercase
> > > such as:
> > > > > j> select columnnumber, columnname,
> > columndatatype
> > > > > from sys.systables t, sys.syscolumns,
> > > sys.sysschemas s
> > > > > where tableid=referenceid and
> > > t.schemaid=s.schemaid
> > > > > and schemaname='SYS' and
> > tablename='DATA'
> > > > > order by columnnumber;
> > > > >
> > > > > _or_ you can also run "select * from
> > > sys.systables" and find
> > > > out if
> > > > > your table appears in the list being
> > > retrieved...then check
> > > > the actual
> > > > > table identifier (aka name)...
> > > > >
> > > > > Hope this helps a bit,
> > > > >
> > > > > --francois
> > > > >
> > > > > On 9/14/06, yves pielusenet
> > > <yv...@free.fr> wrote:
> > > > > Hello,
> > > > > I read this :
> > > > >
> > > http://wiki.apache.org/db-derby/ListTableColumns
> > > > >
> > > > > but I can't retreive the column
> > of my
> > > table 'data'.
> > > > Here is
> > > > > what i
> > > > > tested :
> > > > > ij> select columnnumber,
> > columnname,
> > > columndatatype
> > > > > from sys.systables t,
> > sys.syscolumns,
> > > > sys.sysschemas s
> > > > > where tableid=referenceid
> > and
> > > > t.schemaid=s.schemaid
> > > > > and schemaname='SYS' and
> > > tablename='data'
> > > > > order by columnnumber;
> > > > >
> > > > > And the result is :
> > > > >
> > > > > COLUMNNUMB&|COLUMNNAME|
> > COLUMNDATATYPE
> > > > >
> > > >
> > >
> >
> ------------------------------------------------------------------------------------------------------------------------------------------------------------
> > > > >
> > > > > 0 lignes sélectionnées
> > > > >
> > > > > How can I do ?
> > > > >
> > > > > thanks,
> > > > >
> > > > > --
> > > > > yves piel
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Re: Retreive colummn name of a table from java
Posted by yves pielusenet <yv...@free.fr>.
Ok thank you Francois (are you french as me ?) :)
I understand now. It should be specified into this doc :
http://wiki.apache.org/db-derby/ListTableColumns
that schemaname='SYS' is for the example but if we want to retreive
columns of a personal table, we should use schemaname='APP' :)
Le vendredi 15 septembre 2006 à 00:08 -0700, Francois Orsini a écrit :
> All the Derby database system tables reside in the 'SYS' schema
> whereas the default "user" schema is named 'APP'. When you log in to
> derby, the default schema where the objects are created is 'APP'.
>
> In your (own) query, you had not restricted the query against any
> particular schema, hence it picked it up.
>
> On 9/15/06, yves pielusenet <yv...@free.fr> wrote:
> Thank you Francois :)
> It works with that schema. but what is the difference between
> APP and
> SYS ?
> And the result is the same as my statement. Is my own wrong ?
>
> Le jeudi 14 septembre 2006 à 23:51 -0700, Francois Orsini a
> écrit :
> > Ok - Yves, can you try against the schema 'APP' (default)
> such as:
> >
> > ij> select columnnumber, columnname, columndatatype
> > from sys.systables t, sys.syscolumns, sys.sysschemas s
> > where tableid=referenceid and t.schemaid=s.schemaid
> > and schemaname='APP' and tablename='DATA'
> > order by columnnumber;
> >
> > On 9/14/06, yves pielusenet <yv...@free.fr> wrote:
> > Yes I have tried it but there is any rows back :(
> >
> > ij> select columnnumber, columnname, columndatatype
> > from sys.systables t, sys.syscolumns,
> sys.sysschemas s
> > where tableid=referenceid and
> t.schemaid=s.schemaid
> > and schemaname='SYS' and tablename='DATA'
> > order by columnnumber;
> > COLUMNNUMB&|COLUMNNAME|COLUMNDATATYPE
> >
> ------------------------------------------------------------------------------------------------------------------------------------------------------------
> >
> > 0 lignes sélectionnées
> >
> >
> > Le jeudi 14 septembre 2006 à 15:37 -0700, Francois
> Orsini a
> > écrit :
> > > Have you tried the following below?
> > >
> > > ij> select columnnumber, columnname,
> columndatatype
> > > from sys.systables t, sys.syscolumns,
> sys.sysschemas s
> > > where tableid=referenceid and
> t.schemaid=s.schemaid
> > > and schemaname='SYS' and tablename='DATA'
> > > order by columnnumber;
> > >
> > > are you getting any rows back?
> > >
> > > On 9/14/06, yves pielusenet
> <yv...@free.fr> wrote:
> > > after tests I found this statement :
> > > ij> select columnname, columnnumber from
> > sys.systables,
> > > sys.syscolumns where referenceid=tableid
> and
> > tablename='DATA'
> > > order by columnnumber;
> > >
> > > which works well for me :)
> > >
> > > do you see something wrong ?
> > >
> > >
> > > Le jeudi 14 septembre 2006 à 15:16 -0700,
> Francois
> > Orsini a
> > > écrit :
> > > > Yes,
> > > >
> > > > It all depends how you created the table
> - have
> > you tried
> > > running the
> > > > query by specifying you table name in
> uppercase
> > such as:
> > > > j> select columnnumber, columnname,
> columndatatype
> > > > from sys.systables t, sys.syscolumns,
> > sys.sysschemas s
> > > > where tableid=referenceid and
> > t.schemaid=s.schemaid
> > > > and schemaname='SYS' and
> tablename='DATA'
> > > > order by columnnumber;
> > > >
> > > > _or_ you can also run "select * from
> > sys.systables" and find
> > > out if
> > > > your table appears in the list being
> > retrieved...then check
> > > the actual
> > > > table identifier (aka name)...
> > > >
> > > > Hope this helps a bit,
> > > >
> > > > --francois
> > > >
> > > > On 9/14/06, yves pielusenet
> > <yv...@free.fr> wrote:
> > > > Hello,
> > > > I read this :
> > > >
> > http://wiki.apache.org/db-derby/ListTableColumns
> > > >
> > > > but I can't retreive the column
> of my
> > table 'data'.
> > > Here is
> > > > what i
> > > > tested :
> > > > ij> select columnnumber,
> columnname,
> > columndatatype
> > > > from sys.systables t,
> sys.syscolumns,
> > > sys.sysschemas s
> > > > where tableid=referenceid
> and
> > > t.schemaid=s.schemaid
> > > > and schemaname='SYS' and
> > tablename='data'
> > > > order by columnnumber;
> > > >
> > > > And the result is :
> > > >
> > > > COLUMNNUMB&|COLUMNNAME|
> COLUMNDATATYPE
> > > >
> > >
> >
> ------------------------------------------------------------------------------------------------------------------------------------------------------------
> > > >
> > > > 0 lignes sélectionnées
> > > >
> > > > How can I do ?
> > > >
> > > > thanks,
> > > >
> > > > --
> > > > yves piel
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Re: Retreive colummn name of a table from java
Posted by Francois Orsini <fr...@gmail.com>.
On 9/15/06, Lance J. Andersen <La...@sun.com> wrote:
>
>
>
> Francois Orsini wrote:
>
> All the Derby database system tables reside in the 'SYS' schema whereas
> the default "user" schema is named 'APP'. When you log in to derby, the
> default schema where the objects are created is 'APP'.
>
> However, if you provide a user name when you make your connection , the
> default schema will be the name of the user that you provided, not APP,
> correct?
>
Correct - omitted to mention this - The default username is 'APP'.
Thanks Lance.
In your (own) query, you had not restricted the query against any particular
> schema, hence it picked it up.
>
> On 9/15/06, yves pielusenet < yvespielusenet@free.fr> wrote:
> >
> > Thank you Francois :)
> > It works with that schema. but what is the difference between APP and
> > SYS ?
> > And the result is the same as my statement. Is my own wrong ?
> >
> > Le jeudi 14 septembre 2006 à 23:51 -0700, Francois Orsini a écrit :
> > > Ok - Yves, can you try against the schema 'APP' (default) such as:
> > >
> > > ij> select columnnumber, columnname, columndatatype
> > > from sys.systables t, sys.syscolumns, sys.sysschemas s
> > > where tableid=referenceid and t.schemaid=s.schemaid
> > > and schemaname='APP' and tablename='DATA'
> > > order by columnnumber;
> > >
> > > On 9/14/06, yves pielusenet <yv...@free.fr> wrote:
> > > Yes I have tried it but there is any rows back :(
> > >
> > > ij> select columnnumber, columnname, columndatatype
> > > from sys.systables t, sys.syscolumns, sys.sysschemas s
> > > where tableid=referenceid and t.schemaid=s.schemaid
> > > and schemaname='SYS' and tablename='DATA'
> > > order by columnnumber;
> > > COLUMNNUMB&|COLUMNNAME|COLUMNDATATYPE
> > >
> > ------------------------------------------------------------------------------------------------------------------------------------------------------------
> >
> > >
> > > 0 lignes sélectionnées
> > >
> > >
> > > Le jeudi 14 septembre 2006 à 15:37 -0700, Francois Orsini a
> > > écrit :
> > > > Have you tried the following below?
> > > >
> > > > ij> select columnnumber, columnname, columndatatype
> > > > from sys.systables t, sys.syscolumns, sys.sysschemas s
> > > > where tableid=referenceid and t.schemaid=s.schemaid
> > > > and schemaname='SYS' and tablename='DATA'
> > > > order by columnnumber;
> > > >
> > > > are you getting any rows back?
> > > >
> > > > On 9/14/06, yves pielusenet < yvespielusenet@free.fr> wrote:
> > > > after tests I found this statement :
> > > > ij> select columnname, columnnumber from
> > > sys.systables,
> > > > sys.syscolumns where referenceid=tableid and
> > > tablename='DATA'
> > > > order by columnnumber;
> > > >
> > > > which works well for me :)
> > > >
> > > > do you see something wrong ?
> > > >
> > > >
> > > > Le jeudi 14 septembre 2006 à 15:16 -0700, Francois
> > > Orsini a
> > > > écrit :
> > > > > Yes,
> > > > >
> > > > > It all depends how you created the table - have
> > > you tried
> > > > running the
> > > > > query by specifying you table name in uppercase
> > > such as:
> > > > > j> select columnnumber, columnname, columndatatype
> > > > > from sys.systables t, sys.syscolumns,
> > > sys.sysschemas s
> > > > > where tableid=referenceid and
> > > t.schemaid=s.schemaid
> > > > > and schemaname='SYS' and tablename='DATA'
> > > > > order by columnnumber;
> > > > >
> > > > > _or_ you can also run "select * from
> > > sys.systables" and find
> > > > out if
> > > > > your table appears in the list being
> > > retrieved...then check
> > > > the actual
> > > > > table identifier (aka name)...
> > > > >
> > > > > Hope this helps a bit,
> > > > >
> > > > > --francois
> > > > >
> > > > > On 9/14/06, yves pielusenet
> > > <yv...@free.fr> wrote:
> > > > > Hello,
> > > > > I read this :
> > > > >
> > > http://wiki.apache.org/db-derby/ListTableColumns
> > > > >
> > > > > but I can't retreive the column of my
> > > table 'data'.
> > > > Here is
> > > > > what i
> > > > > tested :
> > > > > ij> select columnnumber, columnname,
> > > columndatatype
> > > > > from sys.systables t, sys.syscolumns,
> > > > sys.sysschemas s
> > > > > where tableid=referenceid and
> > > > t.schemaid=s.schemaid
> > > > > and schemaname='SYS' and
> > > tablename='data'
> > > > > order by columnnumber;
> > > > >
> > > > > And the result is :
> > > > >
> > > > > COLUMNNUMB&|COLUMNNAME|COLUMNDATATYPE
> > > > >
> > > >
> > >
> > ------------------------------------------------------------------------------------------------------------------------------------------------------------
> >
> > > > >
> > > > > 0 lignes sélectionnées
> > > > >
> > > > > How can I do ?
> > > > >
> > > > > thanks,
> > > > >
> > > > > --
> > > > > yves piel
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
Re: Retreive colummn name of a table from java
Posted by "Lance J. Andersen" <La...@Sun.COM>.
Francois Orsini wrote:
> All the Derby database system tables reside in the 'SYS' schema
> whereas the default "user" schema is named 'APP'. When you log in to
> derby, the default schema where the objects are created is 'APP'.
However, if you provide a user name when you make your connection , the
default schema will be the name of the user that you provided, not APP,
correct?
>
> In your (own) query, you had not restricted the query against any
> particular schema, hence it picked it up.
>
> On 9/15/06, *yves pielusenet* < yvespielusenet@free.fr
> <ma...@free.fr>> wrote:
>
> Thank you Francois :)
> It works with that schema. but what is the difference between APP and
> SYS ?
> And the result is the same as my statement. Is my own wrong ?
>
> Le jeudi 14 septembre 2006 à 23:51 -0700, Francois Orsini a écrit :
> > Ok - Yves, can you try against the schema 'APP' (default) such as:
> >
> > ij> select columnnumber, columnname, columndatatype
> > from sys.systables t, sys.syscolumns, sys.sysschemas s
> > where tableid=referenceid and t.schemaid=s.schemaid
> > and schemaname='APP' and tablename='DATA'
> > order by columnnumber;
> >
> > On 9/14/06, yves pielusenet <yvespielusenet@free.fr
> <ma...@free.fr>> wrote:
> > Yes I have tried it but there is any rows back :(
> >
> > ij> select columnnumber, columnname, columndatatype
> > from sys.systables t, sys.syscolumns, sys.sysschemas s
> > where tableid=referenceid and t.schemaid=s.schemaid
> > and schemaname='SYS' and tablename='DATA'
> > order by columnnumber;
> > COLUMNNUMB&|COLUMNNAME|COLUMNDATATYPE
> >
> ------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> >
> > 0 lignes sélectionnées
> >
> >
> > Le jeudi 14 septembre 2006 à 15:37 -0700, Francois Orsini a
> > écrit :
> > > Have you tried the following below?
> > >
> > > ij> select columnnumber, columnname, columndatatype
> > > from sys.systables t, sys.syscolumns, sys.sysschemas s
> > > where tableid=referenceid and t.schemaid=s.schemaid
> > > and schemaname='SYS' and tablename='DATA'
> > > order by columnnumber;
> > >
> > > are you getting any rows back?
> > >
> > > On 9/14/06, yves pielusenet < yvespielusenet@free.fr
> <ma...@free.fr>> wrote:
> > > after tests I found this statement :
> > > ij> select columnname, columnnumber from
> > sys.systables,
> > > sys.syscolumns where referenceid=tableid and
> > tablename='DATA'
> > > order by columnnumber;
> > >
> > > which works well for me :)
> > >
> > > do you see something wrong ?
> > >
> > >
> > > Le jeudi 14 septembre 2006 à 15:16 -0700, Francois
> > Orsini a
> > > écrit :
> > > > Yes,
> > > >
> > > > It all depends how you created the table - have
> > you tried
> > > running the
> > > > query by specifying you table name in uppercase
> > such as:
> > > > j> select columnnumber, columnname,
> columndatatype
> > > > from sys.systables t, sys.syscolumns,
> > sys.sysschemas s
> > > > where tableid=referenceid and
> > t.schemaid=s.schemaid
> > > > and schemaname='SYS' and tablename='DATA'
> > > > order by columnnumber;
> > > >
> > > > _or_ you can also run "select * from
> > sys.systables" and find
> > > out if
> > > > your table appears in the list being
> > retrieved...then check
> > > the actual
> > > > table identifier (aka name)...
> > > >
> > > > Hope this helps a bit,
> > > >
> > > > --francois
> > > >
> > > > On 9/14/06, yves pielusenet
> > <yvespielusenet@free.fr <ma...@free.fr>>
> wrote:
> > > > Hello,
> > > > I read this :
> > > >
> > http://wiki.apache.org/db-derby/ListTableColumns
> > > >
> > > > but I can't retreive the column of my
> > table 'data'.
> > > Here is
> > > > what i
> > > > tested :
> > > > ij> select columnnumber, columnname,
> > columndatatype
> > > > from sys.systables t,
> sys.syscolumns,
> > > sys.sysschemas s
> > > > where tableid=referenceid and
> > > t.schemaid=s.schemaid
> > > > and schemaname='SYS' and
> > tablename='data'
> > > > order by columnnumber;
> > > >
> > > > And the result is :
> > > >
> > > > COLUMNNUMB&|COLUMNNAME|COLUMNDATATYPE
> > > >
> > >
> >
> ------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> > > >
> > > > 0 lignes sélectionnées
> > > >
> > > > How can I do ?
> > > >
> > > > thanks,
> > > >
> > > > --
> > > > yves piel
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Re: Retreive colummn name of a table from java
Posted by Francois Orsini <fr...@gmail.com>.
All the Derby database system tables reside in the 'SYS' schema whereas the
default "user" schema is named 'APP'. When you log in to derby, the default
schema where the objects are created is 'APP'.
In your (own) query, you had not restricted the query against any particular
schema, hence it picked it up.
On 9/15/06, yves pielusenet <yv...@free.fr> wrote:
>
> Thank you Francois :)
> It works with that schema. but what is the difference between APP and
> SYS ?
> And the result is the same as my statement. Is my own wrong ?
>
> Le jeudi 14 septembre 2006 à 23:51 -0700, Francois Orsini a écrit :
> > Ok - Yves, can you try against the schema 'APP' (default) such as:
> >
> > ij> select columnnumber, columnname, columndatatype
> > from sys.systables t, sys.syscolumns, sys.sysschemas s
> > where tableid=referenceid and t.schemaid=s.schemaid
> > and schemaname='APP' and tablename='DATA'
> > order by columnnumber;
> >
> > On 9/14/06, yves pielusenet <yv...@free.fr> wrote:
> > Yes I have tried it but there is any rows back :(
> >
> > ij> select columnnumber, columnname, columndatatype
> > from sys.systables t, sys.syscolumns, sys.sysschemas s
> > where tableid=referenceid and t.schemaid=s.schemaid
> > and schemaname='SYS' and tablename='DATA'
> > order by columnnumber;
> > COLUMNNUMB&|COLUMNNAME|COLUMNDATATYPE
> >
> ------------------------------------------------------------------------------------------------------------------------------------------------------------
> >
> > 0 lignes sélectionnées
> >
> >
> > Le jeudi 14 septembre 2006 à 15:37 -0700, Francois Orsini a
> > écrit :
> > > Have you tried the following below?
> > >
> > > ij> select columnnumber, columnname, columndatatype
> > > from sys.systables t, sys.syscolumns, sys.sysschemas s
> > > where tableid=referenceid and t.schemaid=s.schemaid
> > > and schemaname='SYS' and tablename='DATA'
> > > order by columnnumber;
> > >
> > > are you getting any rows back?
> > >
> > > On 9/14/06, yves pielusenet <yv...@free.fr> wrote:
> > > after tests I found this statement :
> > > ij> select columnname, columnnumber from
> > sys.systables,
> > > sys.syscolumns where referenceid=tableid and
> > tablename='DATA'
> > > order by columnnumber;
> > >
> > > which works well for me :)
> > >
> > > do you see something wrong ?
> > >
> > >
> > > Le jeudi 14 septembre 2006 à 15:16 -0700, Francois
> > Orsini a
> > > écrit :
> > > > Yes,
> > > >
> > > > It all depends how you created the table - have
> > you tried
> > > running the
> > > > query by specifying you table name in uppercase
> > such as:
> > > > j> select columnnumber, columnname, columndatatype
> > > > from sys.systables t, sys.syscolumns,
> > sys.sysschemas s
> > > > where tableid=referenceid and
> > t.schemaid=s.schemaid
> > > > and schemaname='SYS' and tablename='DATA'
> > > > order by columnnumber;
> > > >
> > > > _or_ you can also run "select * from
> > sys.systables" and find
> > > out if
> > > > your table appears in the list being
> > retrieved...then check
> > > the actual
> > > > table identifier (aka name)...
> > > >
> > > > Hope this helps a bit,
> > > >
> > > > --francois
> > > >
> > > > On 9/14/06, yves pielusenet
> > <yv...@free.fr> wrote:
> > > > Hello,
> > > > I read this :
> > > >
> > http://wiki.apache.org/db-derby/ListTableColumns
> > > >
> > > > but I can't retreive the column of my
> > table 'data'.
> > > Here is
> > > > what i
> > > > tested :
> > > > ij> select columnnumber, columnname,
> > columndatatype
> > > > from sys.systables t, sys.syscolumns,
> > > sys.sysschemas s
> > > > where tableid=referenceid and
> > > t.schemaid=s.schemaid
> > > > and schemaname='SYS' and
> > tablename='data'
> > > > order by columnnumber;
> > > >
> > > > And the result is :
> > > >
> > > > COLUMNNUMB&|COLUMNNAME|COLUMNDATATYPE
> > > >
> > >
> >
> ------------------------------------------------------------------------------------------------------------------------------------------------------------
> > > >
> > > > 0 lignes sélectionnées
> > > >
> > > > How can I do ?
> > > >
> > > > thanks,
> > > >
> > > > --
> > > > yves piel
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Re: Retreive colummn name of a table from java
Posted by yves pielusenet <yv...@free.fr>.
Thank you Francois :)
It works with that schema. but what is the difference between APP and
SYS ?
And the result is the same as my statement. Is my own wrong ?
Le jeudi 14 septembre 2006 à 23:51 -0700, Francois Orsini a écrit :
> Ok - Yves, can you try against the schema 'APP' (default) such as:
>
> ij> select columnnumber, columnname, columndatatype
> from sys.systables t, sys.syscolumns, sys.sysschemas s
> where tableid=referenceid and t.schemaid=s.schemaid
> and schemaname='APP' and tablename='DATA'
> order by columnnumber;
>
> On 9/14/06, yves pielusenet <yv...@free.fr> wrote:
> Yes I have tried it but there is any rows back :(
>
> ij> select columnnumber, columnname, columndatatype
> from sys.systables t, sys.syscolumns, sys.sysschemas s
> where tableid=referenceid and t.schemaid=s.schemaid
> and schemaname='SYS' and tablename='DATA'
> order by columnnumber;
> COLUMNNUMB&|COLUMNNAME|COLUMNDATATYPE
> ------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> 0 lignes sélectionnées
>
>
> Le jeudi 14 septembre 2006 à 15:37 -0700, Francois Orsini a
> écrit :
> > Have you tried the following below?
> >
> > ij> select columnnumber, columnname, columndatatype
> > from sys.systables t, sys.syscolumns, sys.sysschemas s
> > where tableid=referenceid and t.schemaid=s.schemaid
> > and schemaname='SYS' and tablename='DATA'
> > order by columnnumber;
> >
> > are you getting any rows back?
> >
> > On 9/14/06, yves pielusenet <yv...@free.fr> wrote:
> > after tests I found this statement :
> > ij> select columnname, columnnumber from
> sys.systables,
> > sys.syscolumns where referenceid=tableid and
> tablename='DATA'
> > order by columnnumber;
> >
> > which works well for me :)
> >
> > do you see something wrong ?
> >
> >
> > Le jeudi 14 septembre 2006 à 15:16 -0700, Francois
> Orsini a
> > écrit :
> > > Yes,
> > >
> > > It all depends how you created the table - have
> you tried
> > running the
> > > query by specifying you table name in uppercase
> such as:
> > > j> select columnnumber, columnname, columndatatype
> > > from sys.systables t, sys.syscolumns,
> sys.sysschemas s
> > > where tableid=referenceid and
> t.schemaid=s.schemaid
> > > and schemaname='SYS' and tablename='DATA'
> > > order by columnnumber;
> > >
> > > _or_ you can also run "select * from
> sys.systables" and find
> > out if
> > > your table appears in the list being
> retrieved...then check
> > the actual
> > > table identifier (aka name)...
> > >
> > > Hope this helps a bit,
> > >
> > > --francois
> > >
> > > On 9/14/06, yves pielusenet
> <yv...@free.fr> wrote:
> > > Hello,
> > > I read this :
> > >
> http://wiki.apache.org/db-derby/ListTableColumns
> > >
> > > but I can't retreive the column of my
> table 'data'.
> > Here is
> > > what i
> > > tested :
> > > ij> select columnnumber, columnname,
> columndatatype
> > > from sys.systables t, sys.syscolumns,
> > sys.sysschemas s
> > > where tableid=referenceid and
> > t.schemaid=s.schemaid
> > > and schemaname='SYS' and
> tablename='data'
> > > order by columnnumber;
> > >
> > > And the result is :
> > >
> > > COLUMNNUMB&|COLUMNNAME|COLUMNDATATYPE
> > >
> >
> ------------------------------------------------------------------------------------------------------------------------------------------------------------
> > >
> > > 0 lignes sélectionnées
> > >
> > > How can I do ?
> > >
> > > thanks,
> > >
> > > --
> > > yves piel
> > >
> > >
> > >
> >
> >
>
>
Re: Retreive colummn name of a table from java
Posted by Francois Orsini <fr...@gmail.com>.
Ok - Yves, can you try against the schema 'APP' (default) such as:
ij> select columnnumber, columnname, columndatatype
from sys.systables t, sys.syscolumns, sys.sysschemas s
where tableid=referenceid and t.schemaid=s.schemaid
and schemaname='APP' and tablename='DATA'
order by columnnumber;
On 9/14/06, yves pielusenet <yv...@free.fr> wrote:
>
> Yes I have tried it but there is any rows back :(
>
> ij> select columnnumber, columnname, columndatatype
> from sys.systables t, sys.syscolumns, sys.sysschemas s
> where tableid=referenceid and t.schemaid=s.schemaid
> and schemaname='SYS' and tablename='DATA'
> order by columnnumber;
> COLUMNNUMB&|COLUMNNAME|COLUMNDATATYPE
>
> ------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> 0 lignes sélectionnées
>
>
> Le jeudi 14 septembre 2006 à 15:37 -0700, Francois Orsini a écrit :
> > Have you tried the following below?
> >
> > ij> select columnnumber, columnname, columndatatype
> > from sys.systables t, sys.syscolumns, sys.sysschemas s
> > where tableid=referenceid and t.schemaid=s.schemaid
> > and schemaname='SYS' and tablename='DATA'
> > order by columnnumber;
> >
> > are you getting any rows back?
> >
> > On 9/14/06, yves pielusenet <yv...@free.fr> wrote:
> > after tests I found this statement :
> > ij> select columnname, columnnumber from sys.systables,
> > sys.syscolumns where referenceid=tableid and tablename='DATA'
> > order by columnnumber;
> >
> > which works well for me :)
> >
> > do you see something wrong ?
> >
> >
> > Le jeudi 14 septembre 2006 à 15:16 -0700, Francois Orsini a
> > écrit :
> > > Yes,
> > >
> > > It all depends how you created the table - have you tried
> > running the
> > > query by specifying you table name in uppercase such as:
> > > j> select columnnumber, columnname, columndatatype
> > > from sys.systables t, sys.syscolumns, sys.sysschemas s
> > > where tableid=referenceid and t.schemaid=s.schemaid
> > > and schemaname='SYS' and tablename='DATA'
> > > order by columnnumber;
> > >
> > > _or_ you can also run "select * from sys.systables" and find
> > out if
> > > your table appears in the list being retrieved...then check
> > the actual
> > > table identifier (aka name)...
> > >
> > > Hope this helps a bit,
> > >
> > > --francois
> > >
> > > On 9/14/06, yves pielusenet <yv...@free.fr> wrote:
> > > Hello,
> > > I read this :
> > > http://wiki.apache.org/db-derby/ListTableColumns
> > >
> > > but I can't retreive the column of my table 'data'.
> > Here is
> > > what i
> > > tested :
> > > ij> select columnnumber, columnname, columndatatype
> > > from sys.systables t, sys.syscolumns,
> > sys.sysschemas s
> > > where tableid=referenceid and
> > t.schemaid=s.schemaid
> > > and schemaname='SYS' and tablename='data'
> > > order by columnnumber;
> > >
> > > And the result is :
> > >
> > > COLUMNNUMB&|COLUMNNAME|COLUMNDATATYPE
> > >
> >
> ------------------------------------------------------------------------------------------------------------------------------------------------------------
> > >
> > > 0 lignes sélectionnées
> > >
> > > How can I do ?
> > >
> > > thanks,
> > >
> > > --
> > > yves piel
> > >
> > >
> > >
> >
> >
>
>
Re: Retreive colummn name of a table from java
Posted by yves pielusenet <yv...@free.fr>.
Yes I have tried it but there is any rows back :(
ij> select columnnumber, columnname, columndatatype
from sys.systables t, sys.syscolumns, sys.sysschemas s
where tableid=referenceid and t.schemaid=s.schemaid
and schemaname='SYS' and tablename='DATA'
order by columnnumber;
COLUMNNUMB&|COLUMNNAME|COLUMNDATATYPE
------------------------------------------------------------------------------------------------------------------------------------------------------------
0 lignes sélectionnées
Le jeudi 14 septembre 2006 à 15:37 -0700, Francois Orsini a écrit :
> Have you tried the following below?
>
> ij> select columnnumber, columnname, columndatatype
> from sys.systables t, sys.syscolumns, sys.sysschemas s
> where tableid=referenceid and t.schemaid=s.schemaid
> and schemaname='SYS' and tablename='DATA'
> order by columnnumber;
>
> are you getting any rows back?
>
> On 9/14/06, yves pielusenet <yv...@free.fr> wrote:
> after tests I found this statement :
> ij> select columnname, columnnumber from sys.systables,
> sys.syscolumns where referenceid=tableid and tablename='DATA'
> order by columnnumber;
>
> which works well for me :)
>
> do you see something wrong ?
>
>
> Le jeudi 14 septembre 2006 à 15:16 -0700, Francois Orsini a
> écrit :
> > Yes,
> >
> > It all depends how you created the table - have you tried
> running the
> > query by specifying you table name in uppercase such as:
> > j> select columnnumber, columnname, columndatatype
> > from sys.systables t, sys.syscolumns, sys.sysschemas s
> > where tableid=referenceid and t.schemaid=s.schemaid
> > and schemaname='SYS' and tablename='DATA'
> > order by columnnumber;
> >
> > _or_ you can also run "select * from sys.systables" and find
> out if
> > your table appears in the list being retrieved...then check
> the actual
> > table identifier (aka name)...
> >
> > Hope this helps a bit,
> >
> > --francois
> >
> > On 9/14/06, yves pielusenet <yv...@free.fr> wrote:
> > Hello,
> > I read this :
> > http://wiki.apache.org/db-derby/ListTableColumns
> >
> > but I can't retreive the column of my table 'data'.
> Here is
> > what i
> > tested :
> > ij> select columnnumber, columnname, columndatatype
> > from sys.systables t, sys.syscolumns,
> sys.sysschemas s
> > where tableid=referenceid and
> t.schemaid=s.schemaid
> > and schemaname='SYS' and tablename='data'
> > order by columnnumber;
> >
> > And the result is :
> >
> > COLUMNNUMB&|COLUMNNAME|COLUMNDATATYPE
> >
> ------------------------------------------------------------------------------------------------------------------------------------------------------------
> >
> > 0 lignes sélectionnées
> >
> > How can I do ?
> >
> > thanks,
> >
> > --
> > yves piel
> >
> >
> >
>
>
Re: Retreive colummn name of a table from java
Posted by Francois Orsini <fr...@gmail.com>.
Have you tried the following below?
ij> select columnnumber, columnname, columndatatype
from sys.systables t, sys.syscolumns, sys.sysschemas s
where tableid=referenceid and t.schemaid=s.schemaid
and schemaname='SYS' and tablename='DATA'
order by columnnumber;
are you getting any rows back?
On 9/14/06, yves pielusenet <yv...@free.fr> wrote:
>
> after tests I found this statement :
> ij> select columnname, columnnumber from sys.systables, sys.syscolumnswhere referenceid=tableid and tablename='DATA' order by columnnumber;
>
> which works well for me :)
>
> do you see something wrong ?
>
>
> Le jeudi 14 septembre 2006 à 15:16 -0700, Francois Orsini a écrit :
> > Yes,
> >
> > It all depends how you created the table - have you tried running the
> > query by specifying you table name in uppercase such as:
> > j> select columnnumber, columnname, columndatatype
> > from sys.systables t, sys.syscolumns, sys.sysschemas s
> > where tableid=referenceid and t.schemaid=s.schemaid
> > and schemaname='SYS' and tablename='DATA'
> > order by columnnumber;
> >
> > _or_ you can also run "select * from sys.systables" and find out if
> > your table appears in the list being retrieved...then check the actual
> > table identifier (aka name)...
> >
> > Hope this helps a bit,
> >
> > --francois
> >
> > On 9/14/06, yves pielusenet <yv...@free.fr> wrote:
> > Hello,
> > I read this :
> > http://wiki.apache.org/db-derby/ListTableColumns
> >
> > but I can't retreive the column of my table 'data'. Here is
> > what i
> > tested :
> > ij> select columnnumber, columnname, columndatatype
> > from sys.systables t, sys.syscolumns, sys.sysschemas s
> > where tableid=referenceid and t.schemaid=s.schemaid
> > and schemaname='SYS' and tablename='data'
> > order by columnnumber;
> >
> > And the result is :
> >
> > COLUMNNUMB&|COLUMNNAME|COLUMNDATATYPE
> >
> ------------------------------------------------------------------------------------------------------------------------------------------------------------
> >
> > 0 lignes sélectionnées
> >
> > How can I do ?
> >
> > thanks,
> >
> > --
> > yves piel
> >
> >
> >
>
>
Re: Retreive colummn name of a table from java
Posted by yves pielusenet <yv...@free.fr>.
after tests I found this statement :
ij> select columnname, columnnumber from sys.systables, sys.syscolumns where referenceid=tableid and tablename='DATA' order by columnnumber;
which works well for me :)
do you see something wrong ?
Le jeudi 14 septembre 2006 à 15:16 -0700, Francois Orsini a écrit :
> Yes,
>
> It all depends how you created the table - have you tried running the
> query by specifying you table name in uppercase such as:
> j> select columnnumber, columnname, columndatatype
> from sys.systables t, sys.syscolumns, sys.sysschemas s
> where tableid=referenceid and t.schemaid=s.schemaid
> and schemaname='SYS' and tablename='DATA'
> order by columnnumber;
>
> _or_ you can also run "select * from sys.systables" and find out if
> your table appears in the list being retrieved...then check the actual
> table identifier (aka name)...
>
> Hope this helps a bit,
>
> --francois
>
> On 9/14/06, yves pielusenet <yv...@free.fr> wrote:
> Hello,
> I read this :
> http://wiki.apache.org/db-derby/ListTableColumns
>
> but I can't retreive the column of my table 'data'. Here is
> what i
> tested :
> ij> select columnnumber, columnname, columndatatype
> from sys.systables t, sys.syscolumns, sys.sysschemas s
> where tableid=referenceid and t.schemaid=s.schemaid
> and schemaname='SYS' and tablename='data'
> order by columnnumber;
>
> And the result is :
>
> COLUMNNUMB&|COLUMNNAME|COLUMNDATATYPE
> ------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> 0 lignes sélectionnées
>
> How can I do ?
>
> thanks,
>
> --
> yves piel
>
>
>
Re: Retreive colummn name of a table from java
Posted by Francois Orsini <fr...@gmail.com>.
Yes,
It all depends how you created the table - have you tried running the query
by specifying you table name in uppercase such as:
j> select columnnumber, columnname, columndatatype
from sys.systables t, sys.syscolumns, sys.sysschemas s
where tableid=referenceid and t.schemaid=s.schemaid
and schemaname='SYS' and tablename='DATA'
order by columnnumber;
_or_ you can also run "select * from sys.systables" and find out if your
table appears in the list being retrieved...then check the actual table
identifier (aka name)...
Hope this helps a bit,
--francois
On 9/14/06, yves pielusenet <yv...@free.fr> wrote:
>
> Hello,
> I read this :
> http://wiki.apache.org/db-derby/ListTableColumns
>
> but I can't retreive the column of my table 'data'. Here is what i
> tested :
> ij> select columnnumber, columnname, columndatatype
> from sys.systables t, sys.syscolumns, sys.sysschemas s
> where tableid=referenceid and t.schemaid=s.schemaid
> and schemaname='SYS' and tablename='data'
> order by columnnumber;
>
> And the result is :
>
> COLUMNNUMB&|COLUMNNAME|COLUMNDATATYPE
>
> ------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> 0 lignes sélectionnées
>
> How can I do ?
>
> thanks,
>
> --
> yves piel
>
>
>
Re: Retreive colummn name of a table from java
Posted by Thomas Kellerer <sp...@gmx.net>.
yves pielusenet wrote on 15.09.2006 00:07:
> but I can't retreive the column of my table 'data'. Here is what i
> tested :
> ij> select columnnumber, columnname, columndatatype
> from sys.systables t, sys.syscolumns, sys.sysschemas s
> where tableid=referenceid and t.schemaid=s.schemaid
> and schemaname='SYS' and tablename='data'
> order by columnnumber;
>
> And the result is :
>
> COLUMNNUMB&|COLUMNNAME|COLUMNDATATYPE
> ------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> 0 lignes sélectionnées
>
> How can I do ?
Tablenames are stored in upper case, so it should be tablename = 'DATA' if I'm
not mistaken.
Regards
Thomas