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> 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> 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
>         <yvespielusenet@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
>         >         >
>         >         >
>         >         >
>         >
>         >
>
>