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: Extracting dependencies from SQL statements
Date Tue, 09 Mar 2010 18:13:59 GMT
Hi Pavel,

You are right, the XmlTreeWalker class also tries to stop the compiler 
after parsing. This can only be done with a debug engine. The error you 
are seeing comes from the bind() phase of compilation. Derby is 
complaining that the query doesn't make sense: the objects it mentions 
don't exist.

There's another issue linked from the XmlTreeWalker issue. Take a look 
at DERBY-4415. This is a slightly more involved solution but may get you 
closer. This solution assumes that you are trying to examine the various 
phases of the AST for a query against real objects in your database. 
This should work with the production jars.

Hope this is more useful,
-Rick

Pavel Bortnovskiy wrote:
>
> Once again, Rick, thanks for your prompt response. To answer your 
> questions:
>
> (1) The compilation errors initially had to do with the SanityManager 
> and ContextId not available in prod jars.
>
> (2) Since I am trying to get this code to work in our application, 
> using debug jars may not be desirable.
> More over, our app is using Derby's in-memory tables as well as its 
> NetworkServerController.
> So, this parsing/tree-walking code should not conflict with the other 
> two uses.
>
> In the comments for DERBY-791, I did notice "We should provide some 
> mechanism for printing these trees in production (non-debug) servers".
> So, I ran XmlTreeWalker linking only to 
> db-derby-10.5.3.0-bin/lib/derby.jar, but got the same exception:
>
> <?xml version="1.0" encoding="UTF-8"?>
> <queryTree>
>     <queryText>select a from t, s where t.a = s.a</queryText>
> Exception in thread "main" java.sql.SQLSyntaxErrorException: 
> Table/View 'T' does not exist.
>         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.EmbedPreparedStatement.<init>(Unknown Source)
>         at 
> org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown 
> Source)
>         at 
> org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown 
> Source)
>         at 
> org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown 
> Source)
>         at 
> org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
>         at 
> org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown 
> Source)
>         at 
> org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown 
> Source)
>         at XmlTreeWalker.execute(XmlTreeWalker.java:130)
>         at XmlTreeWalker.main(XmlTreeWalker.java:111)
>         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>         at 
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) 
>
>         at 
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

>
>         at java.lang.reflect.Method.invoke(Method.java:597)
>         at 
> com.intellij.rt.execution.application.AppMain.main(AppMain.java:110)
> Caused by: java.sql.SQLException: Table/View 'T' does not exist.
>         at 
> org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown 
> Source)
>         at 
> org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown

> Source)
>         ... 20 more
> Caused by: ERROR 42X05: Table/View 'T' does not exist.
>         at 
> org.apache.derby.iapi.error.StandardException.newException(Unknown 
> Source)
>         at 
> org.apache.derby.impl.sql.compile.FromBaseTable.bindTableDescriptor(Unknown 
> Source)
>         at 
> org.apache.derby.impl.sql.compile.FromBaseTable.bindNonVTITables(Unknown 
> Source)
>         at 
> org.apache.derby.impl.sql.compile.FromList.bindTables(Unknown Source)
>         at 
> org.apache.derby.impl.sql.compile.SelectNode.bindNonVTITables(Unknown 
> Source)
>         at 
> org.apache.derby.impl.sql.compile.DMLStatementNode.bindTables(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)
>         ... 14 more
>
> Regards,
> Pavel.
>
>
>
>
>
> *Rick Hillegas <Richard.Hillegas@Sun.COM>*
> Sent by: Richard.Hillegas@Sun.COM
>
> 03/09/2010 11:22 AM
> Please respond to
> "Derby Discussion" <derby-user@db.apache.org>
>
>
> 	
> To
> 	Derby Discussion <derby-user@db.apache.org>
> cc
> 	
> Subject
> 	Re: Extracting dependencies from SQL statements
>
>
>
> 	
>
>
>
>
>
> Hi Pavel,
>
> These programs should be run using the Derby debug jars. I have edited
> the introductory comment to try to make this clear. The debug logic must
> be compiled into Derby in order for compilation to stop after parsing.
> If you need a solution which runs against the production jars, take a
> look at the related issue DERBY-791.
>
> Hope this helps,
> -Rick
>
> Pavel Bortnovskiy wrote:
> >
> > I was able to compile both files by disabling SanityManager method
> > invocation and replacing ContextId.LANG_CONNECTION with its string
> > "LanguageConnectionContext".
> > No other changes to ASTParser and TreeWalker have been done, however,
> > when running them the following exception is thrown:
> >
> > Parsing:
> > select a from t, s where t.a = s.a
> > Exception in thread "main" java.sql.SQLSyntaxErrorException:
> > Table/View 'T' does not exist.
> >         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.EmbedPreparedStatement.<init>(Unknown Source)
> >         at
> > org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown
> > Source)
> >         at
> > org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown
> > Source)
> >         at
> > org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown
> > Source)
> >         at
> > org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
> >         at
> > org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
> > Source)
> >         at
> > org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
> > Source)
> >         at TreeWalker.execute(TreeWalker.java:95)
> >         at TreeWalker.main(TreeWalker.java:79)
> >         at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> >         at
> > 
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) 
>
> >
> >         at
> > 
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

>
> >
> >         at java.lang.reflect.Method.invoke(Method.java:597)
> >         at
> > com.intellij.rt.execution.application.AppMain.main(AppMain.java:110)
> >
> >
> >
> >
> >
> > *Rick Hillegas <Richard.Hillegas@Sun.COM>*
> > Sent by: Richard.Hillegas@Sun.COM
> >
> > 03/09/2010 08:35 AM
> > Please respond to
> > "Derby Discussion" <derby-user@db.apache.org>
> >
> >
> >                  
> > To
> >                  Derby Discussion <derby-user@db.apache.org>
> > cc
> >                  
> > Subject
> >                  Re: Extracting dependencies from SQL statements
> >
> >
> >
> >                  
> >
> >
> >
> >
> >
> > Hi Pavel,
> >
> > I don't know of an easy way to do this. You could run the statement
> > through the Derby parser to get the parsed representation, the Abstract
> > Syntax Tree. Then you could write a Visitor to walk the AST, looking for
> > the nodes which represent tables. See the following JIRAs for some
> > pointers on how to produce and walk the AST: DERBY-3946 and DERBY-791.
> >
> > Unfortunately, there is no systematic primer on the AST nodes
> > themselves. All we have is the javadoc for the package
> > org.apache.derby.impl.sql.compile.
> >
> > Hope this helps,
> > -Rick
> >
> > Pavel Bortnovskiy wrote:
> > >
> > > Hello:
> > >
> > > is it possible to use Derby's SQL parser to "extract" dependencies
> > > from a given SQL statement?
> > > (or access the parser once the statement has been parsed).
> > >
> > > Whether it's a simple SELECT or a JOIN, UNION or a more complex
> > > statement, I would like to get a list of tables that this statement
> > > would depend on.
> > > Looking for FROM clauses and attempting to do the parsing myself seems
> > > like a difficult, error prone and impractical way to approach this.
> > >
> > > Any suggestions, please?
> > >
> > > Thanks,
> > > Pavel.
> > >
> > >
> > >
> > > Jefferies archives and monitors outgoing and incoming e-mail. The
> > > contents of this email, including any attachments, are confidential to
> > > the ordinary user of the email address to which it was addressed. If
> > > you are not the addressee of this email you may not copy, forward,
> > > disclose or otherwise use it or any part of it in any form whatsoever.
> > > This email may be produced at the request of regulators or in
> > > connection with civil litigation. Jefferies accepts no liability for
> > > any errors or omissions arising as a result of transmission. Use by
> > > other than intended recipients is prohibited.  In the United Kingdom,
> > > Jefferies operates as Jefferies International Limited; registered in
> > > England: no. 1978621; registered office: Vintners Place, 68 Upper
> > > Thames Street, London EC4V 3BJ.  Jefferies International Limited is
> > > authorised and regulated by the Financial Services Authority.
> >
> >
> >
> >
> >
> >
> > Jefferies archives and monitors outgoing and incoming e-mail. The
> > contents of this email, including any attachments, are confidential to
> > the ordinary user of the email address to which it was addressed. If
> > you are not the addressee of this email you may not copy, forward,
> > disclose or otherwise use it or any part of it in any form whatsoever.
> > This email may be produced at the request of regulators or in
> > connection with civil litigation. Jefferies accepts no liability for
> > any errors or omissions arising as a result of transmission. Use by
> > other than intended recipients is prohibited.  In the United Kingdom,
> > Jefferies operates as Jefferies International Limited; registered in
> > England: no. 1978621; registered office: Vintners Place, 68 Upper
> > Thames Street, London EC4V 3BJ.  Jefferies International Limited is
> > authorised and regulated by the Financial Services Authority.
>
>
>
>
>
>
> Jefferies archives and monitors outgoing and incoming e-mail. The 
> contents of this email, including any attachments, are confidential to 
> the ordinary user of the email address to which it was addressed. If 
> you are not the addressee of this email you may not copy, forward, 
> disclose or otherwise use it or any part of it in any form whatsoever. 
> This email may be produced at the request of regulators or in 
> connection with civil litigation. Jefferies accepts no liability for 
> any errors or omissions arising as a result of transmission. Use by 
> other than intended recipients is prohibited.  In the United Kingdom, 
> Jefferies operates as Jefferies International Limited; registered in 
> England: no. 1978621; registered office: Vintners Place, 68 Upper 
> Thames Street, London EC4V 3BJ.  Jefferies International Limited is 
> authorised and regulated by the Financial Services Authority. 


Mime
View raw message