0 Replies Latest reply on Mar 27, 2002 10:14 PM by 36182

    Read from multiple schemas

    36182
      Oracle 8.1.7 OO4O

      When reading from multiple schemas I get errors when the dynaset on the remote schema is opened. Usually something like Method 'CreateDynaset' of object '_IOraDatabase' failed, but it is inconsistent. Sometimes VB just crashes.
      The following code example will only work if I close and reopen the database connection after closing the first dynaset.
      It also works if I read from the remote schema first, but that won't fit the code requirement.
      I have select privilege on the T2 schema.
      Is this a known problem?

      Thanks, Andy

      Dim sess As New OraSessionClass
      Dim db As OraDatabase
      Dim rs As OraDynaset
      Dim sSQL As String

      Set db = sess.OpenDatabase("myserver", "t1/t1", 0)

      sSQL = "SELECT * FROM TABLE1 ORDER BY NAME"

      Set rs = db.CreateDynaset(sSQL, ORADYN_NOCACHE)

      If Not rs.BOF And Not rs.EOF Then
      MsgBox rs.RecordCount
      End If

      rs.Close

      sSQL = "SELECT * FROM T2.TABLE2 ORDER BY NAME"

      Set rs = db.CreateDynaset(sSQL, ORADYN_NOCACHE)

      If Not rs.BOF And Not rs.EOF Then
      MsgBox rs.RecordCount
      End If

      rs.Close

      db.Close