db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jad <jad.el-houss...@my.westminster.ac.uk>
Subject SYSCOLUMNS Table Not Displaying Information
Date Tue, 24 Dec 2013 22:00:19 GMT
 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

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:


CUSTOMER_ID (Integer Primary Key Autoincrement (starts at 1, increments by

FULL_NAME (Varchar (50))


ACCOUNT_ID (Integer Primary Key Autoincrement (starts at 1, increments by

CUSTOMER _ID (Integer foreign key (references CUSTOMER_ID in CUSTOMER)

ACCOUNT_TYPE (Varchar (50))

BALANCE (Double)



EMPLOYEE_ID (Integer Primary Key Autoincrement (starts at 1, increments by

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:



(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

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.

View raw message