Issue 72252 - SQL queries to Oracle stop working
Summary: SQL queries to Oracle stop working
Status: CLOSED IRREPRODUCIBLE
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOo 2.0.4
Hardware: PC All
: P3 Trivial with 2 votes (vote)
Target Milestone: ---
Assignee: marc.neumann
QA Contact: issues@dba
URL:
Keywords: oooqa, regression
: 93692 112505 (view as issue list)
Depends on:
Blocks:
 
Reported: 2006-12-04 11:33 UTC by glsarto
Modified: 2011-06-03 14:25 UTC (History)
3 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Attachments

Note You need to log in before you can comment on or make changes to this issue.
Description glsarto 2006-12-04 11:33:58 UTC
Windows XP Sp2 - Oracle ODBC driver 10.02.00.01 - OpenOffice 2.0.4
Oracle Database 10g Release 10.2.0.1.0 - 64bit Production

Queries that worked fine with OO 2.0.3 stop working with 2.0.4 update and report
Oracle error 933

Oracle support reports:
SELECT DUMMY FROM DUAL AS FOO
is invalid syntax - AS identifies an alternative name for a column, not a table.
This correctly fails with ORA-933.
Comment 1 Frank Schönheit 2006-12-04 12:16:18 UTC
SQL-99 (perhaps also earlier version) allow
  table_reference ::= ... | <table_name> [ [AS] <correlation_name> ] | ...
So, this is valid SQL.

Nonetheless, if Oracle doesn't support this notation, we should allow to switch
off the generation of AS. This option would be used automatically when creating
new .odbs for Oracle databases, and would need a small manual interaction for
existing .odbs.
Comment 2 glsarto 2006-12-04 13:30:33 UTC
something must have changed from 2.0.3 to 2.0.4, because the same queries do not
work anymore.
Comment 3 Frank Schönheit 2006-12-04 14:12:54 UTC
Yes, something changed, that's why I set the "regression" keyword:
In 2.0.3, we spared the "AS" keyword, when we generated the SQL statement (it's
re-generated on various occasions, sometimes even if not necessary). In 2.0.4,
we always generate it.

Sorry that this caused trouble with Oracle, I wasn't aware of it not knowing
this (perfectly valid) syntax.

We will introduce some per-database option to switch of the generation of AS.
Unfortunately, this will be too late for 2.1.
Comment 4 glsarto 2006-12-04 14:20:32 UTC
thank you very much for your time and support.

BTW, I cannot find anymore v2.0.3.
Is there a repository where I can download it ?

kind regards,
-GianLuca
Comment 5 Frank Schönheit 2006-12-04 15:00:50 UTC
try
http://download.openoffice.org/2.0.3/contribute.html?product=OpenOffice.org&os=win&lang=en&version=2.0.3
- this is the 2.0.4 ULR, with version numbers replaced. Other than that, I do
not know.
Comment 6 Frank Schönheit 2006-12-06 08:47:13 UTC
Fixed in CWS dba22ui

Added a new option "Use keyword AS before table alias names" to "Edit / Database
/ Advanced Settings / Special Settings". When unchecked (default is checked),
then AS will be supppressed when generating statements.

Spec at http://dba.openoffice.org/specifications/Data_Source_Property_Dialog.sxw
has been adjusted accordingly.
Comment 7 Frank Schönheit 2006-12-06 12:41:52 UTC
fs-> msc: please verify in CWS dba22ui
Comment 8 Frank Schönheit 2006-12-11 09:47:17 UTC
targeting to 2.2, since the fix is part of a CWS aiming for this release
Comment 9 marc.neumann 2006-12-13 13:14:58 UTC
verified in cws dba22ui

find more information about this CWS, like when it is available in the master
builds, in EIS, the Environment Information System:
http://eis.services.openoffice.org/EIS2/cws.ShowCWS?Path=SRC680%2Fdba22ui
Comment 10 ace_dent 2008-05-16 03:34:17 UTC
This Issue is 'Verified' and not updated in 1yr+, so Closing.
A Closed Issue is a Happy Issue (TM).

Regards,
Andrew
 
Cleaning-up and Closing old Issues as part of:
~ The Grand Bug Squash, pre v3 ~
Comment 11 gbpacheco 2008-07-29 17:18:52 UTC
 Hi!
 
 unfortunatelly, I found this issue in OpenOffice.org 2.4.1.

 I tested two cases:

 1) Oracle connection - Windows XP SP2 with Driver Microsoft ODBC for Oracle
2.575.1117.00. 

 2) Microsoft Access connection - Windows XP SP2 with Microsoft Access ODBC
4.00.6305.00.

 In the second case, the option "Use keyword AS before table alias names" to
"Edit/Database/Advanced Settings/Special Settings" works fine. When the user
edit the SQL statement, then AS is suppress or not, in accordance with the option.

 But in the first case (the Oracle connection), the option "Use keyword AS
before table alias names" don´t work. The AS in statement isn´t suppressed.

 Someone can confirm this with Oracle ODBC driver? If yes, is possible reopen
this issue?

 Thanks!
 Gustavo Pacheco
Comment 12 tatanka7 2008-08-05 07:47:39 UTC
Hi.
I have the same problem on : 
Ubuntu 8.04 - the Hardy Heron
OpenOffice.org 2.4.1.
Oracle JDBC - ojdbc14.jar

It does not matter if I use database type "Oracle JDBC" or "JDBC" or use "Use
keyword AS before table alias names" or not. 

It still give a error : 

SQL Status: 42000
Error code: 933
ORA-00933: SQL command not properly ended

The SQL command leading to this error is:
SELECT "STATE", "POZNAMKA_AR" FROM "ERTTEST"."AKADEMICKY_ROK_KS" AS
"AKADEMICKY_ROK_KS"

Thanks !
Tomas D.




Comment 13 ace_dent 2008-08-06 08:11:12 UTC
Re-openining.
Comment 14 woliveirajr 2008-08-13 20:13:11 UTC
Using the "Run SQL command directly" I found that it doesn't matter if you
use the

SELECT t.dummy FROM table t;

or

SELECT t.dummy FROM table AS t;

enclosing the t with " " or not.
Comment 15 gbpacheco 2008-09-19 21:53:12 UTC
Hi!

Thanks for your suggestion, but this don´t solve the real problem.

We need to save the queries correctly and, at this moment, the option "Use
keyword AS before table alias names" simply don´t work. It´s a real bug and not
a  case of using other alternative.

Thanks,
Gustavo Pacheco.
Comment 16 Mathias_Bauer 2009-05-25 14:27:54 UTC
Assigning no target for now. Someone in the know should find correct one. The
current one definitely isn't.
Comment 17 ocke.janssen 2010-07-06 06:38:55 UTC
Tested in a DEv300_m84 and it works.
Please add a description how to reproduce it, when it still occurs.
Comment 18 ocke.janssen 2010-07-06 06:39:22 UTC
Closing
Comment 19 ocke.janssen 2010-07-06 06:40:44 UTC
*** Issue 93692 has been marked as a duplicate of this issue. ***
Comment 20 ocke.janssen 2010-07-06 06:45:16 UTC
*** Issue 112505 has been marked as a duplicate of this issue. ***
Comment 21 h_cahn 2011-06-03 14:25:22 UTC
i try with 3.3 and get 

Impossible de charger le contenu des données.
Statut SQL: 42000
Code d'erreur: 933

ORA-00933: La commande SQL ne se termine pas correctement

KO => FROM SCHEMA.TABLETOTO AS TABLETOTO