6 Replies Latest reply on Sep 15, 2014 11:42 AM by emelkay

    Why no data from RDB into FM11 using ODBC?

    emelkay

      FMP11 on Win7.

       

      We want to use ODBC to read from Oracle RDB on an Alpha.

       

      We've defined the 32-bit DSN in Win7, which tests fine.

       

      We've built SQL in FMP, and can see real data in RDB when we use the Value option.

       

      Problem is: no data is ever returned to FMP! We get the table structure but never any data.

       

      Ideas?

        • 1. Re: Why no data from RDB into FM11 using ODBC?
          wimdecorte

          Can you explain a bit more?  Are you trhing to use ESS?  Import from ODBC?

           

           

          emelkay wrote:

           

          We've built SQL in FMP, and can see real data in RDB when we use the Value option.

           

           

          What does this mean exactly?  What is the "value" option?

          • 2. Re: Why no data from RDB into FM11 using ODBC?
            emelkay

            Import Records from ODBC data source. The SQL Query Builder 'Where' tab lets us actually see RDB data when we click on the Value radio button.

             

            So we know we are connecting with the RDB data source and that data is there.

             

            However, we have yet to be successful in actually getting any of that data back into FM11.

            • 3. Re: Why no data from RDB into FM11 using ODBC?
              wimdecorte

              I'm assuming that you are doing this in a script. What is the error code when you execute it?

              • 4. Re: Why no data from RDB into FM11 using ODBC?
                emelkay

                Open from ODBC Data Source and Import from ODBC Data Source both 'work' with no FM11 errors. They simply report that 0 records were added.

                 

                I did try an Execute SQL statement in a script followed by Get(LastODBCError). Script ran fine but no data was added and the ODBC Error was empty.

                 

                From the DSN sql log after an Import from ODBC Data Source:

                 

                test            199c-165cENTER SQLGetStmtAttrW


                SQLHSTMT            0x0228FB90


                SQLINTEGER                  14 <SQL_ATTR_ROW_NUMBER>


                SQLPOINTER          0x00DDE7E8


                SQLINTEGER                  -6


                SQLINTEGER *        0x00000000

                 

                 

                test            199c-165cEXIT  SQLGetStmtAttrW  with return code -1 (SQL_ERROR)


                SQLHSTMT            0x0228FB90


                SQLINTEGER                  14 <SQL_ATTR_ROW_NUMBER>


                SQLPOINTER          0x00DDE7E8


                SQLINTEGER                  -6


                SQLINTEGER *        0x00000000

                 

                 



                DIAG [24000] [Microsoft][ODBC Driver Manager] Invalid cursor state (0)

                 

                 

                test            199c-165cENTER SQLGetDiagRecW


                SQLSMALLINT                  3 <SQL_HANDLE_STMT>


                SQLHANDLE           0x0228FB90


                SQLSMALLINT                  1


                SQLWCHAR *          0x00DDE4F0


                SQLINTEGER *        0x00DDE4EC


                SQLWCHAR *          0x00DDE4FC


                SQLSMALLINT                350


                SQLSMALLINT *       0x00DDE4E8

                 

                 

                test            199c-165cEXIT  SQLGetDiagRecW  with return code 0 (SQL_SUCCESS)


                SQLSMALLINT                  3 <SQL_HANDLE_STMT>


                SQLHANDLE           0x0228FB90


                SQLSMALLINT                  1


                SQLWCHAR *          0x00DDE4F0 [       5] "24000"


                SQLINTEGER *        0x00DDE4EC (0)


                SQLWCHAR *          0x00DDE4FC [      53] "[Microsoft][ODBC Driver Manager] Invalid cursor state"


                SQLSMALLINT                350


                SQLSMALLINT *       0x00DDE4E8 (53)

                 

                 

                test            199c-165cENTER SQLGetDiagRecW


                SQLSMALLINT                  3 <SQL_HANDLE_STMT>


                SQLHANDLE           0x0228FB90


                SQLSMALLINT                  2


                SQLWCHAR *          0x00DDE4F0


                SQLINTEGER *        0x00DDE4EC


                SQLWCHAR *          0x00DDE4FC


                SQLSMALLINT                350


                SQLSMALLINT *       0x00DDE4E8

                 

                 

                test            199c-165cEXIT  SQLGetDiagRecW  with return code 100 (SQL_NO_DATA_FOUND)


                SQLSMALLINT                  3 <SQL_HANDLE_STMT>


                SQLHANDLE           0x0228FB90


                SQLSMALLINT                  2


                SQLWCHAR *          0x00DDE4F0


                SQLINTEGER *        0x00DDE4EC


                SQLWCHAR *          0x00DDE4FC


                SQLSMALLINT                350


                SQLSMALLINT *       0x00DDE4E8

                 

                 

                test            199c-165cENTER SQLFetch


                HSTMT               0x0228FB90

                 

                 

                test            199c-165cEXIT  SQLFetch  with return code -1 (SQL_ERROR)


                HSTMT               0x0228FB90

                 

                 



                DIAG [24000] [Oracle][ODBC][Rdb]%SQL-F-CURNOTOPE, Cursor SQL_CUR7001DB0 is not opened (-501)

                 

                 

                test            199c-165cENTER SQLFreeHandle


                SQLSMALLINT                  3 <SQL_HANDLE_STMT>


                SQLHANDLE           0x0228FB90

                 

                 

                test            199c-165cEXIT  SQLFreeHandle  with return code 0 (SQL_SUCCESS)


                SQLSMALLINT                  3 <SQL_HANDLE_STMT>


                SQLHANDLE           0x0228FB90

                 

                 

                test            199c-165cENTER SQLDisconnect


                HDBC                0x0228F3E8

                 

                 

                test            199c-165cEXIT  SQLDisconnect  with return code 0 (SQL_SUCCESS)


                HDBC                0x0228F3E8

                 

                 

                test            199c-165cENTER SQLFreeHandle


                SQLSMALLINT                  2 <SQL_HANDLE_DBC>


                SQLHANDLE           0x0228F3E8

                 

                 

                test            199c-165cEXIT  SQLFreeHandle  with return code 0 (SQL_SUCCESS)


                SQLSMALLINT                  2 <SQL_HANDLE_DBC>


                SQLHANDLE           0x0228F3E8

                 

                Does that help at all?

                • 5. Re: Why no data from RDB into FM11 using ODBC?
                  greglane

                  Are you certain the query should return records? Have you tested the same query with another program (such as Microsoft Query in Excel) via the same DSN?

                   

                  BTW, the Execute SQL script step is not intended for SELECT statements. It's designed for INSERT, UPDATE, and DELETE statements.

                   

                  Greg

                  • 6. Re: Why no data from RDB into FM11 using ODBC?
                    emelkay

                    Hi Greg,

                     

                    Yes, using the same DSN in Excel with MS Query returns results. The problem seems to be the 'last mile' (or hurdle, link, chain) in FMP. [Very frustrating!]

                     

                    Good to know that about the Execute SQL script step. Still trying to get Open - ODBC and/or Import - ODBC to work first before moving on to Execute SQL. Thanks!