Issue 87383

Summary: RFE: introduce option to ignore system driver's quoting character
Product: Base Reporter: jrtapper <jrtapp>
Component: codeAssignee: AOO issues mailing list <issues>
Status: CONFIRMED --- QA Contact:
Severity: Trivial    
Priority: P3 CC: issues, mechtilde
Version: OOo 2.3.1Keywords: oooqa
Target Milestone: ---   
Hardware: All   
OS: Windows XP   
Issue Type: DEFECT Latest Confirmation in: ---
Developer Difficulty: ---
Description Flags
Basic library to check certain ODBC driver results none

Description jrtapper 2008-03-24 20:46:38 UTC
This is similar to issue 58318 (which was not really resolved) in that when I am
trying to query an ODBC database (in my case it is a Progress database)
additional space characters are being inserted into the query and causing the
query to fail.  

I can successfully connect to the database and get a listing of all of the
tables and fields, the problem begins when I try to create a query.  

The SQL query I am trying to run is:

select * from p21.customer 

Where p21 is the database name and customer is the table name.  There are
several other databases, so I need to specify the database name.

When I send the query, the error I receive is:

"The data could not be loaded
[Openlink][ODBC][Progress Server] ** Table P21 does not exist or cannot be
accessed (962) "

When I look at the query in the "more, information" tab of the error, the query
looks like this:

select * from p21 . customer
Notice the extra spaces in the table designation.   I have tried to edit the
spaces out (using the "edit in SQL view") and I have tried quoting the table
name (eg. "p21.customer") and I get the same result every time. 

I am not a SQL expert, but I know that this query works with this ODBC driver
from within the MS Query application.  I understand from my ODBC driver vendor
(OpenLink) that there is some question as to the interpretation of the standard.
 If there is a possibility of changing the way OO handles this situation, I
would be very appreciative.

Thank you.
Comment 1 Mechtilde 2008-03-25 10:50:34 UTC
Where do you get your ODBC-Driver from?

how do you configure the ODBC-Driver?

Is the postgresql server on the same maschine?
Comment 2 jrtapper 2008-03-25 15:10:46 UTC
The ODBC driver is a commercial product from Openlink Software that we have
purchased to access our Progress database on a AIX server. 

Configuration is handled through a configuration tool provided by Openlink.  I
assume the configuration is correct because, I can use the same driver and
configuration to access the database using the MS Query tool that is included
with the MS Office 97 product.

As stated above, the database is a Progress database (not postgresql) and it is
located on a AIX machine on the network.  I am running on a Windows XP
machine on the same physical network.

Thank you for your time, please let me know if there is any other questions or
if there is anything else I can do.
Comment 3 Frank Schönheit 2008-04-02 13:33:34 UTC
Created attachment 52460 [details]
Basic library to check certain ODBC driver results
Comment 4 Frank Schönheit 2008-04-02 13:35:58 UTC
Attached is an extension which can be installed via Tools/Extensions. Once done
so, please go to "Tools / Macros / Run ...", select the
ConnectionCheck/ConnectionCheck/DisplayQuotingCharacter function, and run it.

The function will ask you for the name of a registered data source - please
enter the name of the data source which is causing you the trouble.

Then, a small message box will be displayed - please kindly report the result of
this message box here. Thanks.
Comment 5 jrtapper 2008-04-02 16:38:51 UTC
I loaded the extension and ran it but, I don't think it worked right.  When it
prompted me for the data source, I typed in P21 which is the name of the ODBC
data source on the PC (I verified this by connecting to it with base and through
the connection organizer).  The extension returned the message "There is no data
source named 'P21'. Retry - Cancel"    I also got this same message when I tried
a different source name from the organizer list(this was not one I use).

Please let me know if I am doing something wrong or if I can give you some more
info on this. 

Thank you!
Comment 6 Frank Schönheit 2008-04-02 21:18:17 UTC
sorry, the term "data source" was ambiguous here .... I didn't mean the ODBC
data source, but the OOo data source - see Tools / Options / Base
/ Databases. Add a new data source registration here, by pressing "New", then
browsing for your .odb file. The, in the macro's input box, enter the name which
you used to register your .odb.
Comment 7 jrtapper 2008-04-02 21:25:40 UTC
OK, I got it now.  I have a registered database called p21test040108 that I
created today for this issue.  

When I enter p21test040108 into the prompt, the extension returns "quote
character is a whitespace - OK"

Please let me know if I can provide any more info.
Comment 8 Frank Schönheit 2008-04-02 21:40:51 UTC
Great, in some sense ...

That's the expected result, in that it means that your DB's ODBC driver behaves

In SQL, identifiers (i.e. names of tables, columns, etc.) can be quoted, that
is, surrounded by a so-called quote character. The definition of this quote
character is in the responsibility of the database, respectively the driver for
this database.

What the macro does is checking what your database/driver reports back as quote
character (more precise, the API allows for multiple characters, in practice
it's only one character) - and obviously that's whitespaces only.

Now, since OOo must be able to cope with a wide variety of database backends, it
respects the quote character reported by the database/driver.

That is, when a statement such as
  select * from p21.customer
is to be sent to the database, then OOo changes the statement to
  select * from <quote>p21<quote>.<quote>customer<quote>

that's because the definition of the quote character is: Whenever a client talks
to the DB, it should use this character when quoting identifiers.

So, Base is just standard-compliant here. Unfortunately, your database driver
reports back a quote character which it cannot interpret afterwards, which I
consider a driver bug.

Two options:
- we change OOo to ignore quote characters when it's mere whitespace.
  (Actually, I am not *completely* sure if this would be conformant to the
  standards.) The down side of this is that in fact there *might* be some
  esoteric databases which also have a whitespace-only quote character,
  and in fact require it. Those would break.
- You convince the vendor of your DB/driver to fix their driver.

Well, the third would be
- we introduce a per-database option to ignore the quote character, which is
  OFF by default, and which you need to switch ON for your databases (i.e.
  for every .odb file)
Comment 9 jrtapper 2008-04-02 22:41:03 UTC
Thank you for the clear and concise explanation.  

I have addressed this with the ODBC software provider and they point to an
ambiguity in the standard definition of a blank delimiter.  They say they have
added an option in later versions (which will not work with my database) that
addresses this issue. 

I would vote for the third option of a per database setting that would control
this.  I don't like it when other software providers 'fix' things that are
working fine, so I wouldn't ask OOo to do that to other users. 

What can I do to get this into the queue?
Comment 10 Frank Schönheit 2008-04-03 12:28:52 UTC
What an offer ... sad that I'm forbidden to accept bribery ;-)

Changing summary from
  ODBC adds extra spaces to SQL query
  RFE: introduce option to ignore system driver's quoting character
, which better captures the issue by now.

Removing "needmoreinfo" keyword, since the problem is understood now.

Putting into the queue - not in the most prominent place, however. (Side note:
If you have a developer at hand which knows its way around it some C++ code, I'd
be glad to assist in implementing the fix. It wouldn't be too difficult, really.)

Promoting issue from UNCONFIRMED to NEW.

Assigning the developer.