You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Jad <ja...@my.westminster.ac.uk> on 2013/12/24 23:00:19 UTC
SYSCOLUMNS Table Not Displaying Information
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.
Re: SYSCOLUMNS Table Not Displaying Information
Posted by Dyre Tjeldvoll <Dy...@oracle.com>.
Looks like this issue has been resolved, but I just wanted to add that if portability is a concern, you might consider obtaining the meta information from the Connection.getDatabaseMetaData() and ResultSet.getMetaData() methods insead.
Regards,
Dyre
On 27. des. 2013, at 12:38, Jad <ja...@my.westminster.ac.uk> wrote:
> I did try to query the SYSCOLUMNS table using SQL (using the 'SQL Command'
> tab in NetBeans), and I was getting back the correct response so it looks
> like there might not actually be an issue getting results back (which is
> what I want to do), in which case there is no problem!
>
> Thanks for the reply nonetheless. Will keep your answer in mind for future
> reference.
>
>
>
> --
> View this message in context: http://apache-database.10148.n7.nabble.com/SYSCOLUMNS-Table-Not-Displaying-Information-tp136205p136209.html
> Sent from the Apache Derby Developers mailing list archive at Nabble.com.
Re: SYSCOLUMNS Table Not Displaying Information
Posted by Jad <ja...@my.westminster.ac.uk>.
I did try to query the SYSCOLUMNS table using SQL (using the 'SQL Command'
tab in NetBeans), and I was getting back the correct response so it looks
like there might not actually be an issue getting results back (which is
what I want to do), in which case there is no problem!
Thanks for the reply nonetheless. Will keep your answer in mind for future
reference.
--
View this message in context: http://apache-database.10148.n7.nabble.com/SYSCOLUMNS-Table-Not-Displaying-Information-tp136205p136209.html
Sent from the Apache Derby Developers mailing list archive at Nabble.com.
Re: SYSCOLUMNS Table Not Displaying Information
Posted by Rick Hillegas <ri...@oracle.com>.
On 12/24/13 2:00 PM, Jad wrote:
> 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.
>
As you suspect, you are seeing a de-serialization error when your
application tries to read the TypeDescriptorImpl object from
SYS.SYSCOLUMNS.COLUMNDATATYPER. I don't know why you can't deserialize
those objects after including derby.jar on your client-side classpath.
The following client-side program successfully prints out all of the
datatype descriptors for me:
import java.sql.*;
public class w
{
public static void main( String... args ) throws Exception
{
Connection conn = DriverManager.getConnection (
"jdbc:derby://localhost:8246/memory:db1;create=true" );
ResultSet rs = conn.prepareStatement( "select columndatatype
from sys.syscolumns" ).executeQuery();
while( rs.next() )
{
Object obj = rs.getObject( 1 );
System.out.println( "datatype = " + obj + "\n" );
}
rs.close();
}
}
Another approach to introspecting the metadata is to use the optional
databaseMetaData tool introduced by release 10.10.1:
http://db.apache.org/derby/docs/10.10/tools/rtoolsoptdbmetadata.html
Hope this helps,
-Rick