XL: SQLRetrieve Fails Using Oracle 7.0 ODBC Driver

ID: Q129570

The information in this article applies to:

Microsoft Excel for Windows NT, version 5.0
Microsoft Excel for Windows, version 5.0c
Microsoft Excel for Windows 95, versions 7.0, 7.0a
Microsoft Excel for the Macintosh, versions 5.0, 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"}

For example, the second SQLRetrieve command called may fail if it is expected to return fewer columns of data than the first SQLRetrieve command called on the same channel.

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 professionals 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/overview/overview.asp

The following macro example illustrates a situation in which the second SQLRetrieve command does not retrieve any data:


    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

This macro fails because the second query returns fewer columns than the first query--the second query retrieves only one column of data (EMPLOY_ID) and the first query retrieves two columns of data (LAST_NAME, FIRST_NAME).

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

Additional query words: 5.00c addin

Keywords : kbcode kbprg ODBCGen
Version : MACINTOSH:5.0,5.0a; WINDOWS:5.0c,7.0,7.0a; winnt:5.0
Platform : MACINTOSH WINDOWS winnt
Issue type : kbbug