You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Boris Stumm <st...@informatik.uni-kl.de> on 2008/08/07 18:11:37 UTC
Invoking methods directly in query
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
--
Dipl.-Inf. Boris Stumm; Department of Computer Science, University of
Kaiserslautern; P.O. Box 3049, 67653 Kaiserslautern, Germany
Tel.: +49 631 205 3264, Fax: -3299
http://lgis.informatik.uni-kl.de/cms/index.php?id=41
Re: Invoking methods directly in query
Posted by Boris Stumm <st...@informatik.uni-kl.de>.
Hello Rick,
Rick Hillegas wrote:
[...]
> 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.
I see... and found some points in the code to change, but I think
I first try out your advice on doing it.
[...]
> (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:
[...]
Thank you very much for this help, it will save me a lot of time :-)
I hoped to avoid opening a new connection and use a new query because
of performance reasons, but I guess I just give it a try and see if
it is really that bad.
[...]
> Hope this helps,
Yes, it did! Thanks a lot,
Boris
--
Dipl.-Inf. Boris Stumm; Department of Computer Science, University of
Kaiserslautern; P.O. Box 3049, 67653 Kaiserslautern, Germany
Tel.: +49 631 205 3264, Fax: -3299
http://lgis.informatik.uni-kl.de/cms/index.php?id=41
Re: Invoking methods directly in query
Posted by Rick Hillegas <Ri...@Sun.COM>.
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
>
>
>