{"id":150,"date":"2010-03-25T04:13:11","date_gmt":"2010-03-25T09:13:11","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?p=150"},"modified":"2011-09-26T21:49:06","modified_gmt":"2011-09-27T02:49:06","slug":"general-sql-parser-java-v0-2-release","status":"publish","type":"post","link":"https:\/\/www.dpriver.com\/blog\/general-sql-parser-java-v0-2-release\/","title":{"rendered":"General SQL Parser Java v0.2 release"},"content":{"rendered":"

In this release, General SQL Parser can fetch all tables and columns in select statement. It\u00a0 supports SQL dialect of Oracle and SQL Server database . This feature will be very useful if you need to analyze\u00a0 tons of SQL scripts during database migration, apply some new SQL scripts to a production database, review other peoples scripts,\u00a0\u00a0 SQL trouble shooting.<\/p>\n

This SQL Parser will analyze SQL scripts in-depth,\u00a0 all SQL clauses such as select list, from clause, where predicate, group by clause, order by clause, update clause will be analyzed. Of course, nested subquery, complex join clause will be processed correctly. Columns in expression, predicate, and function parameter will be pick up correctly. Common Table Expression(CTE) will also be handled without any problems.<\/p>\n

Let’s take some SQL for example, and see how general sql parser works:<\/p>\n

select employee_id,last_name,
\n(case department_id when department_id =
\n(select department_id from departments
\nwhere location_id = 1800)
\nthen ‘Canada’ else ‘Usa’ end) location
\nfrom employees;<\/em><\/p>\n

This is a simple SQL, but still it not that easy to find out all tables and columns involved, General SQL Parser Java version can do it quickly and automatically:<\/p>\n

Tables:<\/strong>
\ndepartments
\nemployees<\/em><\/p>\n

Columns:<\/strong>
\ndepartments.department_id
\ndepartments.location_id
\nemployees.department_id
\nemployees.employee_id
\nemployees.last_name<\/em><\/p>\n

Here is a three way joins SQL:<\/p>\n

select employee_id,city,department_name
\nfrom employee e
\njoin department d
\non d.department_id = e.department_id
\njoin locations l
\non d.location_id = l.location_id;<\/em><\/p>\n

Tables and columns involved:<\/em><\/p>\n

Tables:<\/strong>
\nemployee
\ndepartment
\nlocations<\/em>
\n
\nColumns:<\/strong>
\nemployee.city
\nemployee.department_id
\nemployee.department_name
\nemployee.employee_id
\ndepartment.department_id
\ndepartment.location_id
\nlocations.location_id<\/em><\/p>\n

General SQL Parser can also process CTE correctly,<\/p>\n

with MyCTE(x)
\nas
\n(select x=’hello’)
\nselect x from MyCTE;<\/em><\/p>\n

General SQL Parser will tell you that there is no physical table involved in this SQL.<\/p>\n

You can download a demo (gettablecolumns.java) here <\/a>to check how it works with your own SQL.<\/p>\n

Support for Delete, Insert, Update and Create table statement will be added soon in next release. After that, General SQL Parser will be able to get tables and columns information from stored procedures such as function\/procedure in SQL Server and trigger, package in Oracle. Currently, all those features are available in .NET version. So I think it will not take too long to migrate those features to Java version.<\/p>\n

Introduce a new method getrawsqlstatements of TGSqlParser<\/strong><\/p>\n

By using this method, you can find out all SQL statements in script file even if\u00a0 there are syntax error. This method is useful if you only need to fetch individual statement from a big SQL script file. You can know what’s kind of SQL statement(Select\/Delete\/Insert\/Update) before send it to your database server will prevent you from SQL injection.<\/p>\n

You can check demo: getstatement.java in this SQL Parser library <\/a>for more information.<\/p>\n

Feel free to to contact me(info@sqlparser.com)\u00a0 if you have any questions or comments about this SQL Parser.<\/p>\n","protected":false},"excerpt":{"rendered":"

In this release, General SQL Parser can fetch all tables and columns in select statement. It\u00a0 supports SQL dialect of Oracle and SQL Server database . This feature will be very useful if you need to analyze\u00a0 tons of SQL scripts during database migration, apply some new SQL scripts to a production database, review other peoples scripts,\u00a0\u00a0 SQL trouble shooting. This SQL Parser will analyze SQL scripts in-depth,\u00a0 all SQL clauses such as select list, from clause, where predicate, group by clause, order by clause, update clause will be analyzed. Of course, nested subquery, complex join clause will be processed\u2026<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[14,15],"tags":[],"blocksy_meta":{"styles_descriptor":{"styles":{"desktop":"","tablet":"","mobile":""},"google_fonts":[],"version":5}},"yoast_head":"\nGeneral SQL Parser Java v0.2 release<\/title>\n<meta name=\"description\" content=\"General SQL Parser Java v0.2 release\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dpriver.com\/blog\/general-sql-parser-java-v0-2-release\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"General SQL Parser Java v0.2 release\" \/>\n<meta property=\"og:description\" content=\"General SQL Parser Java v0.2 release\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dpriver.com\/blog\/general-sql-parser-java-v0-2-release\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and Data Blog\" \/>\n<meta property=\"article:published_time\" content=\"2010-03-25T09:13:11+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2011-09-27T02:49:06+00:00\" \/>\n<meta name=\"author\" content=\"James\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"James\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\",\"name\":\"SQL and Data Blog\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"sameAs\":[],\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"contentUrl\":\"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png\",\"width\":251,\"height\":72,\"caption\":\"SQL and Data Blog\"},\"image\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\",\"url\":\"https:\/\/www.dpriver.com\/blog\/\",\"name\":\"SQL and Data Blog\",\"description\":\"SQL related blog for database professional\",\"publisher\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/general-sql-parser-java-v0-2-release\/\",\"url\":\"https:\/\/www.dpriver.com\/blog\/general-sql-parser-java-v0-2-release\/\",\"name\":\"General SQL Parser Java v0.2 release\",\"isPartOf\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#website\"},\"datePublished\":\"2010-03-25T09:13:11+00:00\",\"dateModified\":\"2011-09-27T02:49:06+00:00\",\"description\":\"General SQL Parser Java v0.2 release\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/general-sql-parser-java-v0-2-release\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dpriver.com\/blog\/general-sql-parser-java-v0-2-release\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/general-sql-parser-java-v0-2-release\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.dpriver.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"General SQL Parser Java v0.2 release\"}]},{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/general-sql-parser-java-v0-2-release\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/general-sql-parser-java-v0-2-release\/\"},\"author\":{\"name\":\"James\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/7bbdbb6e79c5dd9747d08c59d5992b04\"},\"headline\":\"General SQL Parser Java v0.2 release\",\"datePublished\":\"2010-03-25T09:13:11+00:00\",\"dateModified\":\"2011-09-27T02:49:06+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/general-sql-parser-java-v0-2-release\/\"},\"wordCount\":509,\"publisher\":{\"@id\":\"https:\/\/www.dpriver.com\/blog\/#organization\"},\"articleSection\":[\"gsp\",\"release\"],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/7bbdbb6e79c5dd9747d08c59d5992b04\",\"name\":\"James\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/eeddf4ca7bdafa37ab025068efdc7302?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/eeddf4ca7bdafa37ab025068efdc7302?s=96&d=mm&r=g\",\"caption\":\"James\"},\"sameAs\":[\"http:\/\/www.dpriver.com\"],\"url\":\"https:\/\/www.dpriver.com\/blog\/author\/james\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"General SQL Parser Java v0.2 release","description":"General SQL Parser Java v0.2 release","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dpriver.com\/blog\/general-sql-parser-java-v0-2-release\/","og_locale":"en_US","og_type":"article","og_title":"General SQL Parser Java v0.2 release","og_description":"General SQL Parser Java v0.2 release","og_url":"https:\/\/www.dpriver.com\/blog\/general-sql-parser-java-v0-2-release\/","og_site_name":"SQL and Data Blog","article_published_time":"2010-03-25T09:13:11+00:00","article_modified_time":"2011-09-27T02:49:06+00:00","author":"James","twitter_card":"summary_large_image","twitter_misc":{"Written by":"James","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Organization","@id":"https:\/\/www.dpriver.com\/blog\/#organization","name":"SQL and Data Blog","url":"https:\/\/www.dpriver.com\/blog\/","sameAs":[],"logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","contentUrl":"https:\/\/www.dpriver.com\/blog\/wp-content\/uploads\/2022\/07\/sqlpp-character.png","width":251,"height":72,"caption":"SQL and Data Blog"},"image":{"@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"WebSite","@id":"https:\/\/www.dpriver.com\/blog\/#website","url":"https:\/\/www.dpriver.com\/blog\/","name":"SQL and Data Blog","description":"SQL related blog for database professional","publisher":{"@id":"https:\/\/www.dpriver.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dpriver.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.dpriver.com\/blog\/general-sql-parser-java-v0-2-release\/","url":"https:\/\/www.dpriver.com\/blog\/general-sql-parser-java-v0-2-release\/","name":"General SQL Parser Java v0.2 release","isPartOf":{"@id":"https:\/\/www.dpriver.com\/blog\/#website"},"datePublished":"2010-03-25T09:13:11+00:00","dateModified":"2011-09-27T02:49:06+00:00","description":"General SQL Parser Java v0.2 release","breadcrumb":{"@id":"https:\/\/www.dpriver.com\/blog\/general-sql-parser-java-v0-2-release\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dpriver.com\/blog\/general-sql-parser-java-v0-2-release\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dpriver.com\/blog\/general-sql-parser-java-v0-2-release\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.dpriver.com\/blog\/"},{"@type":"ListItem","position":2,"name":"General SQL Parser Java v0.2 release"}]},{"@type":"Article","@id":"https:\/\/www.dpriver.com\/blog\/general-sql-parser-java-v0-2-release\/#article","isPartOf":{"@id":"https:\/\/www.dpriver.com\/blog\/general-sql-parser-java-v0-2-release\/"},"author":{"name":"James","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/7bbdbb6e79c5dd9747d08c59d5992b04"},"headline":"General SQL Parser Java v0.2 release","datePublished":"2010-03-25T09:13:11+00:00","dateModified":"2011-09-27T02:49:06+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dpriver.com\/blog\/general-sql-parser-java-v0-2-release\/"},"wordCount":509,"publisher":{"@id":"https:\/\/www.dpriver.com\/blog\/#organization"},"articleSection":["gsp","release"],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/7bbdbb6e79c5dd9747d08c59d5992b04","name":"James","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dpriver.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/eeddf4ca7bdafa37ab025068efdc7302?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eeddf4ca7bdafa37ab025068efdc7302?s=96&d=mm&r=g","caption":"James"},"sameAs":["http:\/\/www.dpriver.com"],"url":"https:\/\/www.dpriver.com\/blog\/author\/james\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/posts\/150"}],"collection":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/comments?post=150"}],"version-history":[{"count":26,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/posts\/150\/revisions"}],"predecessor-version":[{"id":1016,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/posts\/150\/revisions\/1016"}],"wp:attachment":[{"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/media?parent=150"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/categories?post=150"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dpriver.com\/blog\/wp-json\/wp\/v2\/tags?post=150"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}