Apache OpenOffice (AOO) Bugzilla – Full Text Issue Listing
|Summary:||RFE: introduce option to ignore system driver's quoting character|
|Component:||code||Assignee:||AOO issues mailing list <issues>|
|Status:||CONFIRMED ---||QA Contact:|
|Issue Type:||DEFECT||Latest Confirmation in:||---|
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 OO.org 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 / OpenOffice.org 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 strange. 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 to 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.