db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Lance J. Andersen" <Lance.Ander...@Sun.COM>
Subject Re: Retreive colummn name of a table from java
Date Fri, 15 Sep 2006 13:08:59 GMT


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 
> <mailto: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
>     <mailto: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
>     <mailto: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 <mailto: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
>     >         >         >
>     >         >         >
>     >         >         >
>     >         >
>     >         >
>     >
>     >
>
>

Mime
View raw message