Hi Boris,
I think that you are asking two questions here:
1) Why doesn't the syntax in metadata.properties work for you
2) How can you get the same information out of the columndatatype column
Here are some answers:
(1) The syntax in metadata.properties can only be used internally by
Derby itself. Derby evolved out of a product called Cloudscape. Older
versions of Cloudscape let you declare columns to be Serializable Java
types and to execute those types' public methods in your queries. Some
of the columns in system tables, including columndatatype, were
Cloudscape-specific Serializable objects. And some JDBC metadata calls
were implemented using the method-calling syntax.
Unfortunately, the syntax used to declare those object types was not the
SQL Standard syntax for declaring abstract data types. Before Derby was
open-sourced, that non-standard syntax was removed so that Derby could
conform closely to the SQL Standard--that is an important part of
Derby's charter. The system tables and metadata queries were not
changed, however. Internally, Derby can still store objects in columns
and execute methods off those columns in queries.
As you can see, object-savvy syntax is just under the surface in Derby.
There is a JIRA for exposing this capability via SQL Standard syntax:
http://issues.apache.org/jira/browse/DERBY-651 However, no-one is
working on this right now.
(2) You can write your own functions to unpack the information in the
TypeDescriptor that is stored in the columndatatype column. In a moment,
I will show you how to do this. Please bear in mind that TypeDescriptor
is not part of Derby's public API and that the following code is not
guaranteed to work in future releases of Derby. On the positive side,
TypeDescriptor has tended to evolve in upward compatible ways.
Here is a function which extracts the type name out of a TypeDescriptor:
import java.sql.*;
import org.apache.derby.catalog.TypeDescriptor;
public class z
{
public static String getTypeName( String referenceID, String
columnName )
throws Exception
{
Connection conn =
DriverManager.getConnection("jdbc:default:connection");
PreparedStatement ps = conn.prepareStatement
( "select columndatatype from sys.syscolumns where
referenceID = ? and columnName = ?" );
ps.setString( 1, referenceID );
ps.setString( 2, columnName );
ResultSet rs = ps.executeQuery();
rs.next();
TypeDescriptor td = (TypeDescriptor) rs.getObject( 1 );
rs.close();
ps.close();
return td.getTypeName();
}
}
And here is a little sql script which registers that function and then
uses it in a query:
create function getTypeName
(
referenceID char( 36 ),
columnName varchar( 128 )
)
returns varchar( 50 )
language java
parameter style java
reads sql data
external name 'z.getTypeName'
;
select getTypeName( c.referenceID, c.columnName )
from sys.syscolumns c
where c.columnName = 'TABLEID'
;
Hope this helps,
-Rick
Boris Stumm wrote:
Hi,
I have an application that uses several INFORMATION_SCHEMA views.
This app I now want to use with derby, and found out that derby
has no INFORMATION_SCHEMA. I decided that writing the needed views
myself will be the easiest way, but stumbled over a problem that
I could not solve by googling etc.:
I need to get info from the type descriptor in SYS.SYSCOLUMNS,
and wanted it to do like in the following code snippet:
Connection c = DriverManager.getConnection("jdbc:derby:testdb");
c.createStatement()
.executeQuery("select c.columndatatype.getTypeName()
from sys.syscolumns c");
That does not work, so I was looking in the derby source code,
and found
java/engine/org/apache/derby/impl/jdbc/metadata.properties
There, for example in the getColumns query string, it uses
basically the same method that I tried. How can I make this
work in my code?
I use derby 10.4.1.3 and Java 6.
Thanks for your help!
The exception thrown with above query is:
Exception in thread "main" java.sql.SQLSyntaxErrorException:
Syntaxfehler: org.apache.derby.catalog.TypeDescriptor.getTypeName.
at
org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown
Source)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown
Source)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown
Source)
at
org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown
Source)
at
org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedStatement.executeQuery(Unknown
Source)
at TestDerby.main(TestDerby.java:59)
Caused by: java.sql.SQLException: Syntaxfehler:
org.apache.derby.catalog.TypeDescriptor.getTypeName.
at
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
Source)
at
org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
Source)
... 9 more
Caused by: ERROR 42X01: Syntaxfehler:
org.apache.derby.catalog.TypeDescriptor.getTypeName.
at
org.apache.derby.iapi.error.StandardException.newException(Unknown
Source)
at
org.apache.derby.impl.sql.compile.MethodCallNode.resolveMethodCall(Unknown
Source)
at
org.apache.derby.impl.sql.compile.NonStaticMethodCallNode.bindExpression(Unknown
Source)
at
org.apache.derby.impl.sql.compile.JavaToSQLValueNode.bindExpression(Unknown
Source)
at
org.apache.derby.impl.sql.compile.ResultColumn.bindExpression(Unknown
Source)
at
org.apache.derby.impl.sql.compile.ResultColumnList.bindExpressions(Unknown
Source)
at
org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(Unknown
Source)
at
org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(Unknown
Source)
at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown
Source)
at
org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown
Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown
Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown
Source)
... 3 more
