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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 > <[EMAIL PROTECTED]> 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 > > <[EMAIL PROTECTED]> 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 > > > > > > > > > > > > > > > > > > > > > > > >
