2 Replies Latest reply on Jun 29, 2009 1:53 PM by marekprz

    ODBC Error with Oracle DSN in FM 10

    marekprz

      Summary

      ODBC Error with Oracle DSN in FM 10

      Description of the issue

      FileMaker Product(s) involved: FM 10 Server Advanced and FM 10 Pro AdvOperating System(s) involved: Windows XPDetailed description of the issue: I have an ODBC connection to an Oracle Database that works fine for FMSA9 and FMPro9, but does not work for FM10. (FM 9 and 10 were tested on the same machine, so same DSN). I get a message "ODBC error" when selecting a table from an Oracle DB in "Manage Database", and the table is not added. Various error logs indicate a problem. They are given below.  Exact steps to reproduce the issue: In the case of FMPro10,I create a database;go to "Manage Database";Relationships tab;Add Table...Add ODBC Data Source;Select the DSN (it's called "CUDMPROD"), The driver is "Microsoft ODBC for Oracle").Press "Continue..."Select "ODBC" (if not selected)Select Prompt user for user name and passwordLeave fields emptyFilter by Type: TablesType name and passwordNow I see the list of tables from the Data Source (although the error message shown below now appears in sqlnet.log)When I click on one of the tables (I think any one of them), I get a message "ODBC Error"I get no more info or fields from that table This works fine with FM9Pro (local file).As just describled, it does NOT work with FM10Pro (local file).It works fine with FM9Pro with a remote file served by FMSA9.It does NOT work with FM9Pro with a remote file served by FMSA10.(DSN was defined on local machine and server.) Exact text of any error message(s) that appeared: "ODBC Error" Any additional configuration information/troubleshooting that is relevant to the issueI am able to connect to the CUDMPROD DSN using Oracle SQL Developer.This is not a problem for a different ODBC connection to a SQL Server.The DSN is a system DSN.  It uses "Microsoft ODBC for Oracle" driver v. 2.575.1132.00)I am using Oracle 10g r2.I think this may be relevant: ldap.ora contains: DIRECTORY_SERVERS= (oidmaster1.xxxx.xxxxxxxx.edu:389:636, oidmaster2.xxxx.xxxxxxx.edu:389:636) Any workarounds that you have found:None Error Logs: I turned tracing on in the Windows ODBC Data Source Administrator. Here are some snippets of the results: For FM10 (where the problem occurs): FileMaker Pro A 1770-b4    ENTER SQLFreeHandle        SQLSMALLINT                  3         SQLHANDLE           03D326F0FileMaker Pro A 1770-b4    EXIT  SQLFreeHandle  with return code 0 (SQL_SUCCESS)        SQLSMALLINT                  3         SQLHANDLE           03D326F0FileMaker Pro A 1770-b4    ENTER SQLColumnsW        HSTMT               00000000        WCHAR *             0x00000000        SWORD                        0        WCHAR *             0x0012C3E0 [       4] "ACCT"        SWORD                        4        WCHAR *             0x0012C3C4 [       7] "ACCOUNT"        SWORD                        7        WCHAR *             0x00000000        SWORD                        0FileMaker Pro A 1770-b4    EXIT  SQLColumnsW  with return code -2 (SQL_INVALID_HANDLE)        HSTMT               00000000        WCHAR *             0x00000000        SWORD                        0        WCHAR *             0x0012C3E0 [       4] "ACCT"        SWORD                        4        WCHAR *             0x0012C3C4 [       7] "ACCOUNT"        SWORD                        7        WCHAR *             0x00000000        SWORD                        0  For FM9 (where NO problem occurs):  FileMaker Pro A 12e8-94c    ENTER SQLFreeHandle        SQLSMALLINT                  3         SQLHANDLE           02EA2798FileMaker Pro A 12e8-94c    EXIT  SQLFreeHandle  with return code 0 (SQL_SUCCESS)        SQLSMALLINT                  3         SQLHANDLE           02EA2798FileMaker Pro A 12e8-94c    ENTER SQLAllocStmt        HDBC                02EA1C58        HSTMT *             0012BDE0FileMaker Pro A 12e8-94c    EXIT  SQLAllocStmt  with return code 0 (SQL_SUCCESS)        HDBC                02EA1C58        HSTMT *             0x0012BDE0 ( 0x02ea2798)FileMaker Pro A 12e8-94c    ENTER SQLColumnsW        HSTMT               02EA2798        WCHAR *             0x00000000        SWORD                        0        WCHAR *             0x0012BAFC [       4] "ACCT"        SWORD                        4        WCHAR *             0x0012BAE0 [       7] "ACCOUNT"        SWORD                        7        WCHAR *             0x00000000        SWORD                        0FileMaker Pro A 12e8-94c    EXIT  SQLColumnsW  with return code 0 (SQL_SUCCESS)        HSTMT               02EA2798        WCHAR *             0x00000000        SWORD                        0        WCHAR *             0x0012BAFC [       4] "ACCT"        SWORD                        4        WCHAR *             0x0012BAE0 [       7] "ACCOUNT"        SWORD                        7        WCHAR *             0x00000000        SWORD                        0Filemaker Error File sqlnet.log : it's created by FM10 BEFORE ODBC error occurs. It happens when you click on CUDMPROD (the Oracle DSN) when adding an ODBC connection, as it's asking you for a username and password:  *********************************************************************** Fatal NI connect error 12560, connecting to:  (DESCRIPTION=(ADDRESS=(PROTOCOL=BEQ)(PROGRAM=oracle)(ARGV0=oracleORCL)(ARGS='(DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))'))(CONNECT_DATA=(SID=ORCL)(CID=(PROGRAM=C:\Program Files\FileMaker\FileMaker Pro 10\FileMaker Pro.exe)(HOST=SILVERTEMP)(USER=map18))))    VERSION INFORMATION:     TNS for 32-bit Windows: Version 10.2.0.1.0 - Production     Oracle Bequeath NT Protocol Adapter for 32-bit Windows: Version 10.2.0.1.0 - Production     Windows NT TCP/IP NT Protocol Adapter for 32-bit Windows: Version 10.2.0.1.0 - Production   Time: 20-MAY-2009 14:47:28   Tracing not turned on.   Tns error struct:     ns main err code: 12560     TNS-12560: TNS:protocol adapter error     ns secondary err code: 0     nt main err code: 530     TNS-00530: Protocol adapter error     nt secondary err code: 126     nt OS err code: 0