XL: SQLRetrieve Fails Using Oracle 7.0 ODBC Driver

• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Windows, version 5.0c
• Microsoft Excel for Windows 95, versions 7.0 and 7.0a
• Microsoft Excel for Macintosh, versions 5.0 and 5.0a

SYMPTOMS

In Microsoft Excel, if you use the XLODBC.XLA function SQLRetrieve to retrieve consecutive sets of data on the same channel, the SQLRetrieve function may fail to return any data and one of the following error messages will occur:

   Error returned by the Oracle driver SQORA7.DLL Version 1.11.0000:
   ={"S1002",0,"[Oracle][ODBC Oracle Driver]Invalid column number."}

   -or-

   Error returned by the Visigenic driver VSORAC32.DLL Version 2.00.0000.
   ={"S1002",0,"[Visigenic][ODBC Oracle driver]Invalid column number"}

RESOLUTION

This problem no longer occurs if you use the new Visigenic 32-bit Oracle 7 ODBC driver and Microsoft Excel for Windows 95 version 7.0.

STATUS

Microsoft has confirmed this to be a problem in the XLODBC.DLL that comes with Microsoft Excel version 5.0 for Windows NT.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

   Sub GetData()

      Dim Chan as Integer

      ' Establish a connection with the Oracle Data Source.
      Chan = SQLOpen("DSN=oracledata;UID=userid;PWD=password")

      ' Execute a query which is expected to return two columns of data.
      SQLExecQuery Chan, _
         "SELECT EMPS.LAST_NAME, EMPS.FIRST_NAME FROM NORTHWIND.EMPS EMPS"
      SQLRetrieve Chan, ActiveSheet.Range("A1")

      ' Execute a second query which is expected to return only one column 
      ' of data.
      SQLExecquery Chan, _
        "SELECT EMPS.EMPLOYEE_ID FROM NORTHWIND.EMPS EMPS"
      SQLRetrieve Chan, Activesheet.Range("D1")

      ' Close the connection to the Oracle data source.
      SQLClose Chan

   End Sub

If you are unable to use Microsoft Excel version 7.0 (as recommended in the "Resolution" section of this article), use either of the following sample macros:

Sample 1

You could successfully execute the macro example above if you interchange the SQLExecquery lines so that one column is returned in the first SQLExecquery and two columns are returned in the second. For example:

   Sub GetData1()

      Dim Chan as Integer

      ' Establish a connection with the Oracle data source.
      Chan = SQLOpen("DSN=oracledata;UID=userid;PWD=password")

      ' Execute a query which is expected to return only one column of 
      ' data.
      SQLExecquery Chan, _
         "SELECT EMPS.EMPLOYEE_ID FROM NORTHWIND.EMPS EMPS"
      SQLRetrieve Chan, Activesheet.Range("D1")

      ' Execute a second query which is expected to return two columns of 
      ' data
      SQLExecquery Chan, _
         "SELECT EMPS.LAST_NAME, EMPS.FIRST_NAME FROM NORTHWIND.EMPS EMPS"
      SQLRetrieve Chan, Activesheet.Range("A1")

      ' Close the connection to the Oracle data source.
      SQLClose Chan

   End Sub

Sample 2

You can also successfully execute the macro example GetData if you close the data source connection after the first query and then re-establish the connection prior to executing the second query. For example:

   Sub GetData2()

      Dim Chan as Integer

      ' Establish a connection with the Oracle data source.
      Chan = SQLOpen("DSN=oracledata;UID=userid;PWD=password")

      ' Execute a query which is expected to return two columns of data.
      SQLExecquery Chan, _
         "SELECT EMPS.LAST_NAME, EMPS.FIRST_NAME FROM NORTHWIND.EMPS EMPS"
      SQLRetrieve Chan, Activesheet.Range("A1")

      ' Close the connection to the Oracle data source.
      SQLClose Chan

      ' Re-establish the connection to the Oracle data source.
      Chan = SQLOpen("dsn=oracledata;uid=userid;pwd=password")

      ' Execute a second query which is expected to return only one column 
      ' of data.
      SQLExecquery Chan, _
         "SELECT EMPS.EMPLOYEE_ID FROM NORTHWIND.EMPS EMPS"
      SQLRetrieve Chan, Activesheet.Range("D1")

      ' Close the connection to the Oracle data source.
      SQLClose Chan

   End Sub