Some Eclipse Foundation services are deprecated, or will be soon. Please ensure you've read this important communication.
Bug 319927 - Query Builder generates invalid Oracle syntax causing ora-00933 (AS keyword is not supported by Oracle)
Summary: Query Builder generates invalid Oracle syntax causing ora-00933 (AS keyword i...
Status: RESOLVED FIXED
Alias: None
Product: Data Tools
Classification: Tools
Component: SQL Query Builder (show other bugs)
Version: 1.8   Edit
Hardware: PC Linux
: P3 normal (vote)
Target Milestone: 1.8.1   Edit
Assignee: Brian Payton CLA
QA Contact:
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2010-07-14 17:56 EDT by Frits Jalvingh CLA
Modified: 2010-07-27 16:32 EDT (History)
2 users (show)

See Also:

Attachments
Proposed patch (2.95 KB, patch)
2010-07-21 19:43 EDT, Linda Chan CLA 		no flags Details | Diff
Patch for plugin o.e.d.sqltools.sqlbuilder (2.34 KB, patch)
2010-07-27 15:24 EDT, Brian Payton CLA 		no flags Details | Diff
Show Obsolete (1) View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Frits Jalvingh CLA 2010-07-14 17:56:45 EDT 
Build Identifier: 20100617-1415

Select "DataSet" in Data Explorer; create a new one on a data source connected to an Oracle (10g in my case) database.
Create a query using a table alias using the query builder
The generated SQL looks like:
SELECT NAAM, SYST_ID, IND_GEBLOKKEERD, ADMN_ID, CLUS_ID, COMT_ID,
  VLTA_ID, ID
  FROM DEMO.GEBRUIKERS AS GEBR
This SQL is invalid for Oracle- it does not support the 'as' keyword. The query causes the error:

A BIRT exception occurred.
  Plug-in Provider:Eclipse.org
  Plug-in Name:BIRT Data Engine
  Plug-in ID:org.eclipse.birt.data
  Version:2.6.0.v20100524
  Error Code:odaconsumer.CannotGetResultSetMetaData
  Error Message:Cannot get the result set metadata.
    org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1: ORA-00933: SQL command not properly ended

Whatever you do- since the query is always regenerated the as will reappear..

Reproducible: Always

Steps to Reproduce:
See description
Comment 1 Linda Chan CLA 2010-07-21 19:43:28 EDT 
Created attachment 174931 [details]
Proposed patch
Comment 2 Brian Payton CLA 2010-07-27 15:24:11 EDT 
Created attachment 175346 [details]
Patch for plugin o.e.d.sqltools.sqlbuilder

Fix looks OK.  I reworked patch slightly so all "if"s have brackets and to move the "new VendorHelper" out of the if-test.  (Simple if-tests make debugging easier.)
Comment 3 Brian Payton CLA 2010-07-27 16:32:00 EDT 
Checked in fix, tagged to v201007280345.