{"id":197,"date":"2010-05-30T21:58:54","date_gmt":"2010-05-31T02:58:54","guid":{"rendered":"http:\/\/www.dpriver.com\/blog\/?p=197"},"modified":"2011-09-26T21:40:12","modified_gmt":"2011-09-27T02:40:12","slug":"general-sql-parser-java-v0-4-release","status":"publish","type":"post","link":"https:\/\/www.dpriver.com\/blog\/2010\/05\/general-sql-parser-java-v0-4-release\/","title":{"rendered":"General SQL Parser Java v0.4 release"},"content":{"rendered":"

In this version, we can easily fetch table and column from stored procedures(packages) of Oracle and SQL Server.<\/p>\n

Let’s take this create trigger statement(Oracle) for example:<\/p>\n

\nCREATE<\/span> TRIGGER<\/span> hr<\/span>.<\/span>emp_permit_changes<\/span>
\nBEFORE<\/span> DELETE<\/span> OR<\/span> INSERT<\/span> OR<\/span> UPDATE<\/span> ON<\/span> hr<\/span>.<\/span>employees<\/span>
\nBEGIN<\/span>
\nINSERT<\/span> INTO<\/span> customers<\/span>
\n(<\/span>customer_id<\/span>,<\/span>
\ncust_last_name<\/span>,<\/span>
\ncust_first_name<\/span>)<\/span>
\nVALUES<\/span> (<\/span> :new<\/span>.<\/span>customer_id<\/span>,<\/span>
\n:new<\/span>.<\/span>cust_last_name<\/span>,<\/span>
\n:new<\/span>.<\/span>cust_first_name<\/span>)<\/span>;<\/span><\/span><\/p>\n

INSERT<\/span> INTO<\/span> orders<\/span>
\n(<\/span>order_id<\/span>,<\/span>
\norder_date<\/span>,<\/span>
\ncustomer_id<\/span>)<\/span>
\nVALUES<\/span> (<\/span> :new<\/span>.<\/span>order_id<\/span>,<\/span>
\n:new<\/span>.<\/span>order_date<\/span>,<\/span>
\n:new<\/span>.<\/span>customer_id<\/span>)<\/span>;<\/span>
\nEXCEPTION<\/span>
\nWHEN<\/span> duplicate_info<\/span> THEN<\/span>
\nRaise_application_error<\/strong><\/span> (<\/span>num<\/span> =><\/span> –<\/span>20107<\/span>,<\/span> msg<\/span> =><\/span> ‘Duplicate\u00a0customer\u00a0or\u00a0order\u00a0ID’<\/span>)<\/span>;<\/span>
\nEND<\/span>;<\/span><\/span><\/p>\n

\/<\/span><\/span><\/p>\n

Here are\u00a0 table and column in above SQL Statement fetched by demo gettablecolumns shipped together with this component:<\/p>\n

Tables:
\ncustomers
\nhr.employees
\norders<\/p>\n

Fields:
\ncustomers.cust_first_name
\ncustomers.cust_last_name
\ncustomers.customer_id
\nhr.employees.cust_first_name
\nhr.employees.cust_last_name
\nhr.employees.customer_id
\nhr.employees.order_date
\nhr.employees.order_id
\norders.customer_id
\norders.order_date
\norders.order_id<\/p>\n

Below is another create procedure SQL sample of SQL Server:<\/p>\n

\nCREATE<\/span> PROCEDURE<\/span> Purchasing<\/span>.<\/span>uspVendorAllInfo<\/span>
\nWITH<\/span> EXECUTE<\/span> AS<\/span> CALLER<\/span>
\nAS<\/span>
\nSET<\/span> NOCOUNT<\/span> ON<\/span>;<\/span>
\nSELECT<\/span> v<\/span>.<\/span>Name<\/span> AS<\/span> Vendor<\/span>,<\/span> p<\/span>.<\/span>Name<\/span> AS<\/span> ‘Product\u00a0name’<\/span>,<\/span>
\nv<\/span>.<\/span>CreditRating<\/span> AS<\/span> ‘Credit\u00a0Rating’<\/span>,<\/span>
\nv<\/span>.<\/span>ActiveFlag<\/span> AS<\/span> Availability<\/span>
\nFROM<\/span> Purchasing<\/span>.<\/span>Vendor<\/span> v<\/span>
\nINNER<\/span> JOIN<\/span> Purchasing<\/span>.<\/span>ProductVendor<\/span> pv<\/span>
\nON<\/span> v<\/span>.<\/span>VendorID<\/span> =<\/span> pv<\/span>.<\/span>VendorID<\/span>
\nINNER<\/span> JOIN<\/span> Production<\/span>.<\/span>Product<\/span> p<\/span>
\nON<\/span> pv<\/span>.<\/span>ProductID<\/span> =<\/span> p<\/span>.<\/span>ProductID<\/span>
\nORDER<\/span> BY<\/span> v<\/span>.<\/span>Name<\/span> ASC<\/span>;<\/span>
\nGO<\/span><\/span><\/div>\n
\n<\/span><\/span><\/div>\n

Here are table and column in above SQL Statement fetched by demo gettablecolumns shipped together with this component:<\/p>\n

Tables:
\nproduction.product
\npurchasing.productvendor
\npurchasing.vendor<\/p>\n

Fields:
\nproduction.product.name
\nproduction.product.productid
\npurchasing.productvendor.productid
\npurchasing.productvendor.vendorid
\npurchasing.vendor.activeflag
\npurchasing.vendor.creditrating
\npurchasing.vendor.name
\npurchasing.vendor.vendorid<\/p>\n

You can download General SQL Parser Java version here:
\nhttp:\/\/www.sqlparser.com\/download.php<\/a>
\nIf you have any questions or comments about this SQL Parser, feel free to contact me:\u00a0 info@sqlparser.com<\/p>\n","protected":false},"excerpt":{"rendered":"

In this version, we can easily fetch table and column from stored procedures(packages) of Oracle and SQL Server. Let’s take this create trigger statement(Oracle) for example: CREATE TRIGGER hr.emp_permit_changes BEFORE DELETE OR INSERT OR UPDATE ON hr.employees BEGIN INSERT INTO customers (customer_id, cust_last_name, cust_first_name) VALUES ( :new.customer_id, :new.cust_last_name, :new.cust_first_name); INSERT INTO orders (order_id, order_date, customer_id) VALUES ( :new.order_id, :new.order_date, :new.customer_id); EXCEPTION WHEN duplicate_info THEN Raise_application_error (num => –20107, msg => ‘Duplicate\u00a0customer\u00a0or\u00a0order\u00a0ID’); END; \/ Here are\u00a0 table and column in above SQL Statement fetched by demo gettablecolumns shipped together with this component: Tables: customers hr.employees orders Fields: customers.cust_first_name customers.cust_last_name customers.customer_id hr.employees.cust_first_name\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.4 release<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n