db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: Invoking methods directly in query
Date Thu, 07 Aug 2008 18:42:48 GMT
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
>
>
>


Mime
View raw message