I have a database that uses the Apache Derby RDBMS and I have added three tables to it called Accounts, Customers and Employees. The database runs behind a Java application that runs queries against these three tables to perform any one of the CRUD functions (these queries work fine so no issue there)
What I want to do is to get meta data about the tables I created through the system tables in the database (the table names, column names, data in the tables etc) using SQL (not Java's classes, which I do know about) rather than simply querying the actual tables themselves. I've looked at the reference to the tables on the Derby system tables page and I think that the tables that I should be focusing on are SYSCOLUMNS and SYSTABLES. What's odd is that SYSTABLES shows information about my tables but SYSCOLUMNS does not. There are other tables in the SYS schema that contain data about my tables, such as SYSCONTSTRAINTS, SYSFOREIGNKEYS, SYSKEYS and (as mentioned above) SYSTABLES. So to me it's somewhat peculiar that SYSCOLUMNS doesn't also have information about my tables inside them. When I actually run a query against SYSCOLUMNS I get this error: I think this refers to the COLUMNDATATYPE column (based on reading the reference of the SYSCOLUMNS table in the link above) but I have looked at other pages and don't really understand it. If it helps here are the columns and data types for my tables: *CUSTOMERS* CUSTOMER_ID (Integer Primary Key Autoincrement (starts at 1, increments by 1)) FULL_NAME (Varchar (50)) *ACCOUNTS* ACCOUNT_ID (Integer Primary Key Autoincrement (starts at 1, increments by 1)) CUSTOMER _ID (Integer foreign key (references CUSTOMER_ID in CUSTOMER) ACCOUNT_TYPE (Varchar (50)) BALANCE (Double) CREDIT_LIMIT (Double) *EMPLOYEES* EMPLOYEE_ID (Integer Primary Key Autoincrement (starts at 1, increments by 1)) FULL_NAME (Varchar (50)) USERNAME (Varchar (50)) PASSWORD (Varchar (50)) Here are a couple of images if what happens when I query the SYSTABLES table and when I query the SYSCOLUMNS table: <http://apache-database.10148.n7.nabble.com/file/n136205/SYSTABLES_Table.jpg> <http://apache-database.10148.n7.nabble.com/file/n136205/SYSCOLUMNS_Table.jpg> (apologies for the image sizes, I want them to be as clear as possible). I was told on another forum to either add derbytools.jar and derby.jar to the libraries of the program or to switch from a client-server driver to an embedded driver. The former didn't work and I'm not sure how to do the latter. Any help would be appreciated. -- View this message in context: http://apache-database.10148.n7.nabble.com/SYSCOLUMNS-Table-Not-Displaying-Information-tp136205.html Sent from the Apache Derby Developers mailing list archive at Nabble.com.
