Apache OpenOffice (AOO) Bugzilla – Issue 8753
[SAS][SAS ODBC Driver]Function sequence error
Last modified: 2013-08-07 15:45:41 UTC
SAS ODBC Driver v8.2 gives error: [SAS][SAS ODBC Driver]Function sequence error when attempting ODBC-setup access to SAS datasets using the following setup (under Win2000 and WinMe): Tools..Data Sources General: Name=SASUSER, Database type=ODBC, Data source URL=sdbc:odbc: SASUSER ODBC: User name=phil, no password required, no driver settings, Character set=System Tables: All required tables can be seen in list Data..DataPilot..Start Data source registered in OpenOffice.org Database=SASUSER, Data source=SHELP.CLASS, Type=Sheet Data can be viewed for Database type=Spreadsheet or dBase. Useful information about SAS ODBC: (1) supports SQLExtendedFetch function, and SQLSetSmtOption allows you to set the rowset size and to select row-wise or column-wise binding. (2) supports the scalar string functions: ASCII, CHAR, CONCAT, LCASE, LTRIM, REPEAT, REPLACE, RTRIM, SOUNDEX, SPACE, SUBSTRING, UCASE. (3) supports the scalar numeric functions: ABS, ACOS, ASIN, ATAN, CEILING, COS, EXP, FLOOR, LOG, LOG10, MOD, POWER, RAND1, ROUND, SIGN, SQRT, TAN. (4) supports the scalar timedate functions: CURDATE, CURTIME, DAYNAME, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, HOUR, MINUTE, MONTH, MONTHNAME, MON, QUARTER, SECOND, YEAR. (5) does not support the following fDescTypes for the SQLColAttributes: SQL_COLUMN_OWNER_NAME, SQL_COLUMN_TABLE_NAME. (6) uses the TCP/IP protocol to access SAS data. HTH....Phil Holland
Hi, Please be so kind and provide any more infos for this. - What database is SAS. - Does this also happend when you use the beamer. Bye Marc
Marc, In answer to your questions: - SAS is a software system developed by SAS Institute (Cary, NC, USA - www.sas.com), which includes its own programming languages, but also stores data in its own proprietary data files. They provide an ODBC Driver to access their data from external applications, eg. VB, Access, Excel, 1-2-3, Approach, but I wanted to demonstrate it was also possible from OOo Calc for a book I'm currently writing. - The error message is obtained from F4 (I presume this is what you called the beamer), as well as from the DataPilot. I suspect you may have difficulty getting hold of a copy of the SAS System, so I'll be very pleased to test any solutions you can suggest on my own licensed copies of SAS v8 and SAS v6. Please also note that I have also raised a problem report with SAS Institute (ref: uk1015973), as the error message may have been caused by OOo Calc requesting information from the SAS ODBC Driver that it can't cope with. I have found that the error message appears when accessing SAS v8 or SAS v6 data files, which suggests that the problem is localised to the ODBC Driver, and not the SAS System. It should also be noted that all the names of the tables (SAS data files) that are available through the ODBC Driver can be viewed both through the beamer and the DataPilot, but they cannot be opened. HTH......Phil Holland
Marc, Is it possible to provide details of what data is sent to the ODBC Driver from OOo Calc when you try to open a table in the beamer? I could then pass that information to the SAS Institute developers who are also investigating this problem. Thanks.......Phil Holland
Hi Phil, does the SAS driver also supports SQLFetchScroll calls? Or in other words which ODBC version does SAS support and which version of OpenOffice do you use? Best regards, Ocke
Hi Phil, I just found an article from SAS how to convert ODBC into ADO accessing. http://www2.sas.com/proceedings/sugi26/p012-26.pdf on the second page Your connection string should look like PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=your server id;Location=your.com Best regards, Ocke
Created attachment 4207 [details] Dr Watson error log file for SAS ADO error
Ocke, I'm not sure which ODBC version SAS supports, as there are mentions of ODBC 3, but only references to docs for ODBC 2. The driver does not support cursors, as the SAS System does not suppor real cursor functionality, but there are mentions of SQLExtendedFetch, which suggests only ODBC 2 support. Because I wasn't certain which end of the connection the problem lay, I reported a problem with the SAS support team too, which went all the way to the developers. They have now confirmed that OOo Calc has exposed a bug in the SAS ODBC Driver, so my interest now is in trying to find a workaround, hence my interest in what OOo actually sends to the ODBC interface. BTW I've tried my connections to the SAS ODBC Driver using 1.0.1 and 643 getting the same error messages, which made me suspect that the problem wasn't with OOo. Re the ADO connection to SAS, the string for SAS should look like Provider=SAS.LocalProvider.1;Data Source=c:\temp\sas\ but with Calc 1.0.1 and 643 I get an unrecoverable error in soffice.exe (see attached file). Maybe this is another bug? ........Phil
Hi Phil, the problem could that we use the ODBC call SQLFetchScroll which was first introduced in ODBC 3.0. But that should not be any problem as the reference says "When working with an ODBC 2.x driver, the Driver Manager maps this function to SQLExtendedFetch". Perhaps the developers could have a look into the code of OOo. http://dba.openoffice.org/source/browse/dba/connectivity/source/drivers/odbc/OResultSet.cxx?rev=1.52&content-type=text/x-cvsweb-markup The GPF with ADO is really bad, but without driver I think I'm unable to reproduce the bug. But as I know the sources for ADO changes a little bit, may be this bug will be fixed in a next release :-) which would be the same a win at a lottery.:-) Best regards, Ocke
set target
Hi, I have some questions concerning further handling of this issue. 1. Still actual? 2. Can we change status to NEW? 3. Is there any danger that the reported bug also might cause problems for database access to other known ODBC databases? I deleted the funny "URL" 'sdbc:odbc: SASUSER' Rainer
Rainer, In answer to your questions: 1. The problem still exists in 1.1rc4. According to contacts I have in the SAS Institute (the developers of the SAS ODBC Driver software), the driver is only compliant with ODBC 2, not ODBC 3. However, the SAS tables can be accessed via the SAS ODBC driver from MS-Access 2000, MS-Excel 2000, Lotus 1-2-3 9.5 and Lotus Approach 9.5! 2. What is the difference between UNCONFIRMED and NEW? 3. Yes, if this issue indicates that OOo Calc has problems accessing ODBC 2 data sources, then it could be affecting access to other ODBC 2 databases. Note that the 'funny' URL "sdbc:odbc: SASUSER" is the name of the ODBC Data Source. The 'SASUSER' string is user-defined, but the 'sdbc:odbc:' part is very relevant! If you need to carry out any tests on the OOo-SAS ODBC interface, then I'm more than happy to assist you in any way I can. .........Phil Holland
Deleted URL because not accessible. Further comments soon. Rainer
change subcomponent to 'none'
Hi, can someone test this issue in the current developer build? The current developer build can be found at http://download.openoffice.org/680/index.html Bye Marc
I raised this error as a problem with both OOo and SAS Institute. The bug in the SAS ODBC Driver was resolved in version 9.01.00.218. The OOo ODBC interface was expecting ODBC 3 compliance, which was stated in the SAS ODBC Driver documentation from version 8, but not fully achieved until version 9.01.00.218. This version of the SAS ODBC Driver has been tested successfully with OOo 1.1.4 and 1.9.65. Issue 8753 should be marked as CLOSED and VERIFIED, without any requirements for changes to OOo.
thackert -> prholland: Could you be so kind and close this issue yourself then?
I like to close it, but I don't seem to have anywhere on my view of the issue to change the STATUS. .........Phil
Hi Phil, that are great news. Thanks for testing. Closing it now. Best regards, Ocke
.