XL: SQLRetrieve Fails Using Oracle 7.0 ODBC Driver (129570)

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to 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 requirements. 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