RE: issues.apache.org/jira/browse/DERBY-3946
Yes, thanks. This was my take as well. It is similar to the 3946 approach in the sense you still have to catch the throw exception - it just not as clearly defined. Now you need to trap the does not exist exception statement ( SQL State:[42x05] ). Again I appreciate your help. -Original Message- From: Rick Hillegas [mailto:rick.hille...@gmail.com] Sent: Sunday, October 19, 2014 9:58 AM To: Derby Discussion Cc: Rick Hillegas; Kenton Garner Subject: Re: issues.apache.org/jira/browse/DERBY-3946 Hi Kent, I have used XmlASTPrinter on queries against tables which don't exist. Although you get errors at bind-time, the parse tree is still dumped to derbyAST.xml. You just need to catch the bind-time exception and soldier on. Hope this helps, -Rick On 10/14/14 8:40 AM, Rick Hillegas wrote: Hi Kent, I'm moving this discussion to the derby-user@db.apache.org mailing list so that it will be archived in case anyone else is interested later on. The work on DERBY-3946 was superseded by the work on DERBY-4415. I've resolved DERBY-3946. Hopefully that will help point people at DERBY-4415 so that they don't spend too much time wrestling with the old approach. The solution on DERBY-4415 works with production (non-debug) engines. I have just verified that it still works with the latest 10.11.1.1 release. What I recommend is this: Write your own custom version of XmlASTPrinter. Then follow DERBY-4415's instructions for printing an xml-formatted tree: connect 'jdbc:derby:memory:dummy;create=true'; create table t( a int ); call syscs_util.syscs_register_tool( 'customTool', true, 'ASTInspector', 'XmlASTPrinter' ); select * from t; call syscs_util.syscs_register_tool( 'customTool', false, 'ASTInspector' ); - now look for the output in derbyAST.xml Feel free to ask more questions or make more suggestions, either on this email thread or on DERBY-4415. Hope this helps, -Rick On 10/13/14 3:36 PM, Kenton Garner wrote: Rick, I have been looking for a SQL Parser for a while now with no success ( various ANTLR/ JAVACC examples came close - but no joy ). Today I came across a discussion about the support you added to Derby to allow it to be used to generate Abstract Syntax Trees. This is great! I have to be able to take SQL statements parsed from a log for basic DML ( INSERT, UPDATE, DELETE ) and be able to convert the SQL to a custom XML format and potentially re-apply the SQL programmatically to another database. I know your work for this was done a while ago, but I would love to make use of it. This is what I have done so far. 1.Download the debug version of the Derby libs from db.apache.org (Derby distributed with Java 7 JDK does not work here). 2.Update your sample ASTParser.java and TreeWalker.java to use the org.apache.derby.shared.common.sanity.SanityManager. 3.Read your discussions on the Derby jira page in regards to the TreeWalker/Visitor and class relationships. 4.Ran some tests with basic INSERT SQL Statement. It appears that since this is using an in memory database that I do not have to have a data dictionary or other details pre-existing in the database for the basic SQL Parsing to work. - Great News! Note: I would be glad to ask my questions in another forum if you prefer, but I do not know how to add questions to the Derby jira page. My question is basically... Since both the ASTParser and the TreeWalker make use of package level calls that I have no access to; And knowing the node class from the TreeWalker does not provide me any greater access to the information that I need. How would you recommend I make best use of this code? My first though was that I would have to modify the Derby code to get access to package, protected and private methods/variables - but I would obviously rather not alter the Derby code. Do you have any suggestions? Perhaps I am missing the obvious. Thank you so much. -Kent -- Kent Garner Software Engineer, CDS Engineering Global Enterprise Solutions Division cid:image001.jpg@01CE8142.F3353C20 *Intelligent Software Solutions* 111 Langley Road Rome, NY 13441-4003 315.371.4850
Re: issues.apache.org/jira/browse/DERBY-3946
Hi Kent, I have used XmlASTPrinter on queries against tables which don't exist. Although you get errors at bind-time, the parse tree is still dumped to derbyAST.xml. You just need to catch the bind-time exception and soldier on. Hope this helps, -Rick On 10/14/14 8:40 AM, Rick Hillegas wrote: Hi Kent, I'm moving this discussion to the derby-user@db.apache.org mailing list so that it will be archived in case anyone else is interested later on. The work on DERBY-3946 was superseded by the work on DERBY-4415. I've resolved DERBY-3946. Hopefully that will help point people at DERBY-4415 so that they don't spend too much time wrestling with the old approach. The solution on DERBY-4415 works with production (non-debug) engines. I have just verified that it still works with the latest 10.11.1.1 release. What I recommend is this: Write your own custom version of XmlASTPrinter. Then follow DERBY-4415's instructions for printing an xml-formatted tree: connect 'jdbc:derby:memory:dummy;create=true'; create table t( a int ); call syscs_util.syscs_register_tool( 'customTool', true, 'ASTInspector', 'XmlASTPrinter' ); select * from t; call syscs_util.syscs_register_tool( 'customTool', false, 'ASTInspector' ); – now look for the output in derbyAST.xml Feel free to ask more questions or make more suggestions, either on this email thread or on DERBY-4415. Hope this helps, -Rick On 10/13/14 3:36 PM, Kenton Garner wrote: Rick, I have been looking for a SQL Parser for a while now with no success ( various ANTLR/ JAVACC examples came close – but no joy ). Today I came across a discussion about the support you added to Derby to allow it to be used to generate Abstract Syntax Trees. This is great! I have to be able to take SQL statements parsed from a log for basic DML ( INSERT, UPDATE, DELETE ) and be able to convert the SQL to a custom XML format and potentially re-apply the SQL programmatically to another database. I know your work for this was done a while ago, but I would love to make use of it. This is what I have done so far. 1.Download the debug version of the Derby libs from db.apache.org (Derby distributed with Java 7 JDK does not work here). 2.Update your sample ASTParser.java and TreeWalker.java to use the “org.apache.derby.shared.common.sanity.SanityManager”. 3.Read your discussions on the Derby jira page in regards to the TreeWalker/Visitor and class relationships. 4.Ran some tests with basic INSERT SQL Statement. It appears that since this is using an in memory database that I do not have to have a data dictionary or other details pre-existing in the database for the basic SQL Parsing to work. – Great News! Note: I would be glad to ask my questions in another forum if you prefer, but I do not know how to add questions to the Derby jira page. My question is basically… Since both the ASTParser and the TreeWalker make use of package level calls that I have no access to; And knowing the node class from the TreeWalker does not provide me any greater access to the information that I need. How would you recommend I make best use of this code? My first though was that I would have to modify the Derby code to get access to package, protected and private methods/variables – but I would obviously rather not alter the Derby code. Do you have any suggestions? Perhaps I am missing the obvious. Thank you so much. -Kent -- Kent Garner Software Engineer, CDS Engineering Global Enterprise Solutions Division cid:image001.jpg@01CE8142.F3353C20 *Intelligent Software Solutions* 111 Langley Road Rome, NY 13441-4003 315.371.4850
Re: issues.apache.org/jira/browse/DERBY-3946
Hi Kent, If what you need to do is simply parse the query, I don't have a quick fix. You will have to build a hacked version of the Derby engine to do this. There is a debug flag which short-circuits query compilation after the parsing phase. Look in the codeline for StopAfterParsing. That will lead you to the spot in the code where short-circuiting takes place. Your custom XmlASTPrinter will need to turn on short-circuiting after it is installed. If you want, you can log a JIRA for reviving the old behavior. Hope this helps, -Rick On 10/14/14 5:40 AM, Rick Hillegas wrote: Hi Kent, I'm moving this discussion to the derby-user@db.apache.org mailing list so that it will be archived in case anyone else is interested later on. The work on DERBY-3946 was superseded by the work on DERBY-4415. I've resolved DERBY-3946. Hopefully that will help point people at DERBY-4415 so that they don't spend too much time wrestling with the old approach. The solution on DERBY-4415 works with production (non-debug) engines. I have just verified that it still works with the latest 10.11.1.1 release. What I recommend is this: Write your own custom version of XmlASTPrinter. Then follow DERBY-4415's instructions for printing an xml-formatted tree: connect 'jdbc:derby:memory:dummy;create=true'; create table t( a int ); call syscs_util.syscs_register_tool( 'customTool', true, 'ASTInspector', 'XmlASTPrinter' ); select * from t; call syscs_util.syscs_register_tool( 'customTool', false, 'ASTInspector' ); – now look for the output in derbyAST.xml Feel free to ask more questions or make more suggestions, either on this email thread or on DERBY-4415. Hope this helps, -Rick On 10/13/14 3:36 PM, Kenton Garner wrote: Rick, I have been looking for a SQL Parser for a while now with no success ( various ANTLR/ JAVACC examples came close – but no joy ). Today I came across a discussion about the support you added to Derby to allow it to be used to generate Abstract Syntax Trees. This is great! I have to be able to take SQL statements parsed from a log for basic DML ( INSERT, UPDATE, DELETE ) and be able to convert the SQL to a custom XML format and potentially re-apply the SQL programmatically to another database. I know your work for this was done a while ago, but I would love to make use of it. This is what I have done so far. 1.Download the debug version of the Derby libs from db.apache.org (Derby distributed with Java 7 JDK does not work here). 2.Update your sample ASTParser.java and TreeWalker.java to use the “org.apache.derby.shared.common.sanity.SanityManager”. 3.Read your discussions on the Derby jira page in regards to the TreeWalker/Visitor and class relationships. 4.Ran some tests with basic INSERT SQL Statement. It appears that since this is using an in memory database that I do not have to have a data dictionary or other details pre-existing in the database for the basic SQL Parsing to work. – Great News! Note: I would be glad to ask my questions in another forum if you prefer, but I do not know how to add questions to the Derby jira page. My question is basically… Since both the ASTParser and the TreeWalker make use of package level calls that I have no access to; And knowing the node class from the TreeWalker does not provide me any greater access to the information that I need. How would you recommend I make best use of this code? My first though was that I would have to modify the Derby code to get access to package, protected and private methods/variables – but I would obviously rather not alter the Derby code. Do you have any suggestions? Perhaps I am missing the obvious. Thank you so much. -Kent -- Kent Garner Software Engineer, CDS Engineering Global Enterprise Solutions Division cid:image001.jpg@01CE8142.F3353C20 *Intelligent Software Solutions* 111 Langley Road Rome, NY 13441-4003 315.371.4850
RE: issues.apache.org/jira/browse/DERBY-3946
Rick, sorry it took me so long to respond. I was just starting to look at this preferred way when I noticed that you have included the line create table t( a int ); In your sample instructions. Does this imply that it will need to bind all of the SQL statements to a current data dictionary in order for it to parse the SQL? Unfortunately, I will not have tables associated with the SQL I parse. That was one of the advantages of the previous method - it was not required. It stopped immediately after parsing and did not require a valid data dictionary. -Kent -Original Message- From: Rick Hillegas [mailto:rick.hille...@oracle.com] Sent: Tuesday, October 14, 2014 8:41 AM To: Kenton Garner; Derby Discussion Subject: Re: issues.apache.org/jira/browse/DERBY-3946 Hi Kent, I'm moving this discussion to the derby-user@db.apache.org mailing list so that it will be archived in case anyone else is interested later on. The work on DERBY-3946 was superseded by the work on DERBY-4415. I've resolved DERBY-3946. Hopefully that will help point people at DERBY-4415 so that they don't spend too much time wrestling with the old approach. The solution on DERBY-4415 works with production (non-debug) engines. I have just verified that it still works with the latest 10.11.1.1 release. What I recommend is this: Write your own custom version of XmlASTPrinter. Then follow DERBY-4415's instructions for printing an xml-formatted tree: connect 'jdbc:derby:memory:dummy;create=true'; create table t( a int ); call syscs_util.syscs_register_tool( 'customTool', true, 'ASTInspector', 'XmlASTPrinter' ); select * from t; call syscs_util.syscs_register_tool( 'customTool', false, 'ASTInspector' ); - now look for the output in derbyAST.xml Feel free to ask more questions or make more suggestions, either on this email thread or on DERBY-4415. Hope this helps, -Rick On 10/13/14 3:36 PM, Kenton Garner wrote: Rick, I have been looking for a SQL Parser for a while now with no success ( various ANTLR/ JAVACC examples came close - but no joy ). Today I came across a discussion about the support you added to Derby to allow it to be used to generate Abstract Syntax Trees. This is great! I have to be able to take SQL statements parsed from a log for basic DML ( INSERT, UPDATE, DELETE ) and be able to convert the SQL to a custom XML format and potentially re-apply the SQL programmatically to another database. I know your work for this was done a while ago, but I would love to make use of it. This is what I have done so far. 1.Download the debug version of the Derby libs from db.apache.org (Derby distributed with Java 7 JDK does not work here). 2.Update your sample ASTParser.java and TreeWalker.java to use the org.apache.derby.shared.common.sanity.SanityManager. 3.Read your discussions on the Derby jira page in regards to the TreeWalker/Visitor and class relationships. 4.Ran some tests with basic INSERT SQL Statement. It appears that since this is using an in memory database that I do not have to have a data dictionary or other details pre-existing in the database for the basic SQL Parsing to work. - Great News! Note: I would be glad to ask my questions in another forum if you prefer, but I do not know how to add questions to the Derby jira page. My question is basically... Since both the ASTParser and the TreeWalker make use of package level calls that I have no access to; And knowing the node class from the TreeWalker does not provide me any greater access to the information that I need. How would you recommend I make best use of this code? My first though was that I would have to modify the Derby code to get access to package, protected and private methods/variables - but I would obviously rather not alter the Derby code. Do you have any suggestions? Perhaps I am missing the obvious. Thank you so much. -Kent
Re: issues.apache.org/jira/browse/DERBY-3946
Hi Kent, I'm moving this discussion to the derby-user@db.apache.org mailing list so that it will be archived in case anyone else is interested later on. The work on DERBY-3946 was superseded by the work on DERBY-4415. I've resolved DERBY-3946. Hopefully that will help point people at DERBY-4415 so that they don't spend too much time wrestling with the old approach. The solution on DERBY-4415 works with production (non-debug) engines. I have just verified that it still works with the latest 10.11.1.1 release. What I recommend is this: Write your own custom version of XmlASTPrinter. Then follow DERBY-4415's instructions for printing an xml-formatted tree: connect 'jdbc:derby:memory:dummy;create=true'; create table t( a int ); call syscs_util.syscs_register_tool( 'customTool', true, 'ASTInspector', 'XmlASTPrinter' ); select * from t; call syscs_util.syscs_register_tool( 'customTool', false, 'ASTInspector' ); – now look for the output in derbyAST.xml Feel free to ask more questions or make more suggestions, either on this email thread or on DERBY-4415. Hope this helps, -Rick On 10/13/14 3:36 PM, Kenton Garner wrote: Rick, I have been looking for a SQL Parser for a while now with no success ( various ANTLR/ JAVACC examples came close – but no joy ). Today I came across a discussion about the support you added to Derby to allow it to be used to generate Abstract Syntax Trees. This is great! I have to be able to take SQL statements parsed from a log for basic DML ( INSERT, UPDATE, DELETE ) and be able to convert the SQL to a custom XML format and potentially re-apply the SQL programmatically to another database. I know your work for this was done a while ago, but I would love to make use of it. This is what I have done so far. 1.Download the debug version of the Derby libs from db.apache.org (Derby distributed with Java 7 JDK does not work here). 2.Update your sample ASTParser.java and TreeWalker.java to use the “org.apache.derby.shared.common.sanity.SanityManager”. 3.Read your discussions on the Derby jira page in regards to the TreeWalker/Visitor and class relationships. 4.Ran some tests with basic INSERT SQL Statement. It appears that since this is using an in memory database that I do not have to have a data dictionary or other details pre-existing in the database for the basic SQL Parsing to work. – Great News! Note: I would be glad to ask my questions in another forum if you prefer, but I do not know how to add questions to the Derby jira page. My question is basically… Since both the ASTParser and the TreeWalker make use of package level calls that I have no access to; And knowing the node class from the TreeWalker does not provide me any greater access to the information that I need. How would you recommend I make best use of this code? My first though was that I would have to modify the Derby code to get access to package, protected and private methods/variables – but I would obviously rather not alter the Derby code. Do you have any suggestions? Perhaps I am missing the obvious. Thank you so much. -Kent -- Kent Garner Software Engineer, CDS Engineering Global Enterprise Solutions Division cid:image001.jpg@01CE8142.F3353C20 *Intelligent Software Solutions* 111 Langley Road Rome, NY 13441-4003 315.371.4850
