• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 600
  • Last Modified:

ADO 2.5 compliant with Oracle ODBC Driver?

My ADODB.Connection object's underlying connection string points to a DSN using the Oracle ODBC Driver (Version 8.00.05.00).

Has anyone been able to use ADO 2.5 to get rows back from
a connection object using this driver?

I'm unable to get any rows back in my ADODB.Recordset no matter how I execute a query against this connection.

Strangely enough, the same code works if I change the DSN to use the Microsoft ODBC for Oracle driver.

I thought all ODBC drivers would work with ADO. I'm using VB6 SP6.
0
jfairbairn1
Asked:
jfairbairn1
  • 9
  • 6
  • 3
  • +1
1 Solution
 
EstebanFGCommented:
The only thing that I can think of is that Oracle you have to make sure that you have an entry in the tnsnames.ora file (in the net80 directory).  The entry will have information on the server and database.
0
 
OnkarCommented:
ConnectObject.ConnectionString = "DSN=NameofDSN;UID=" & UID & ";" & "PWD" = PWD & """"
           
DSN should be created using Oracle ODBC Driver

OR
           
WIth ConnectObject
 .ConnectionString = "SRVR=" & ServerName & _
   ";driver={Oracle ODBC Driver}" & _
   ";uid=" & UID & _
   ";pwd=" & PWD & _
   ";database=" & DBName
 .CursorLocation = adUseClient
 .Properties("prompt") = adPromptNever
 .ConnectionTimeout = 300
 .Open
End With

Dim Rs As ADODB.Recordset
Set Rs = New ADODB.Recordset
Rs.Open "TableName", ConnectObject, adOpenDynamic, adLockOptimistic, adCmdTable

Comments:If you are using SQL statement the you have to use adCmdText instead of adCmdTable.


I think this may Solve your Problem, if you need anything more please reply to Me

Good Luck

Onkar
0
 
nigelroweCommented:
Do you get an error message? If not, post the code which you use to open the connection and initialise the Rs.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
jfairbairn1Author Commented:
Thanks for taking the time to write these comments.

I'm still getting no rows back however. I don't get any errors, just no rows.

Here's the code:

(1) I create my connection object:

 Set m_cnAvantis = New ADODB.Connection
 m_cnAvantis.ConnectionString = "DSN=" & Trim$(sMcDsn)  & ";UID=" & Trim$(sMcUid) & ";PWD=" & Trim$(sMcPwd)
 m_cnAvantis.Open

where the DSN uses the Oracle ODBC Driver. The connection state = adStateOpen after this.

(2) I try to create my recordset object:

rsNodes.Open fsGetTopParent, g_connAvantis, adOpenDynamic, adLockOptimistic, adCmdText

where g_connAvantis is the connection object using the Oracle ODBC Driver...

rsNodes.EOF and rsNodes.BOF are always true after executing this statement...

0
 
jfairbairn1Author Commented:
Forgot to mention... in the code above, the fsGetTopParent function returns a string of SQL text...
0
 
OnkarCommented:
Hi

In your above code You are using a seperate connection object to connect to the ORACLE using DSN.
1. Please ensure the dsn created is using ORACLE ODBC Driver or Not.
Connection Object:"m_cnAvantis"
2. In your recordset opening you are using a seperate connection object please ensure is this same.
Connection Object:"g_connAvantis"

It is little confusing!!

Please see my previous comment for Code.

Onkar
0
 
nigelroweCommented:
Have you tried?...

Set rsNodes = m_cnAvantis.Execute(fsGetTopParent)
0
 
jfairbairn1Author Commented:
Sorry Onkar..it is a bit confusing...

But they both represent the same connection to the Oracle ODBC Driver.

Nigel... yes... I have tried getting a recordset back using the connection object but I get 0 rows back.

I have also tried using a command object but get the same result:

Dim cmdText as new ADODB.Command

With cmdText
  set .ActiveConnection = m_cnAvantis
  .CommandType = adCmdText
  .CommandText = sSQL
  set rsNodes = .Execute
End With

rsNodes.EOF = true and rsNodes.BOF = true

I'd hate to make the client use the Microsoft ODBC for Oracle Driver... even though it is free and comes with the
MDAC's.
0
 
nigelroweCommented:
Yes, in any case, the MS ODBC driver does not work too well. Don't you have Oracle OLEDB?

Might seem a daft question, but are you sure that the sql statement will return results in, for example, SQLPlus?
0
 
jfairbairn1Author Commented:
No, I don't have Oracle OLEDB.

I have been able to get records back if I execute the query within ODBC OpenLink (similar to SQLPlus) against the same DSN connection.

0
 
OnkarCommented:
Hi,

I possible will you give the whole code how you are trying to call the records, so that I can help you out in this regard.

Because I am using ODBC ORACLE driver for my project.

Onkar
0
 
jfairbairn1Author Commented:
Thanks... the populatetreeview subroutine calls the
fsGetTopParent function to return the SQL query string to be executed.

Private Sub PopulateTreeview()

    Dim rsNodes As New ADODB.Recordset  
    Dim objNode As clsNode
    Dim bDataAdded As Boolean
    Dim sKey As String      
    Dim sNodePath As String  

    On Local Error GoTo ErrHandler
     
    Screen.MousePointer = vbHourglass
   
    m_lCounter = 1          
   
    rsNodes.Open fsGetTopParent, g_connAvantis,  adopenKeyset, adLockOptimistic
   
    If rsNodes.EOF And rsNodes.BOF 'Always true here!!
        Screen.MousePointer = vbNormal
        MsgBox "No records!!!", App.Title
        DoEvents
        Exit Sub
    End If
End Sub


Private Function fsGetTopParent() As String
Dim sSQL As String
   
    sSQL = "select distinct mc.mtntype.mtoi from  mc.mtntype where mc.mtntype.mtoi not in (select mc.mt_ptyp.owner_oi from mc.mt_ptyp) "
   
    fsGetTopParent = sSQL

End Function
0
 
nigelroweCommented:
Strange, if I launch the following type of sql (using adodb) ...

"select distinct logs.wfstatistic.idstatistic from  logs.wfstatistic"

it generates a Dr. Watson. Whilst, if its like this...

"select distinct idstatistic from wfstatistic"

It works.

I don't know if this has any bearing on your problem, probably not, but interesting anyway.

Apart from that, I don't see any wrong with your code. I imagine it must be something to do with the Oracle ODBC driver and ADODB. Personally, I use ADODB 2.6 and Oracle ODBC driver 8.01.06.00

So I don't think I can ever reproduce your error. However, I'll keep watching any progress in your question.
0
 
jfairbairn1Author Commented:
OK... thanks. Actually, you verified something I wanted to check. I wanted to see if the Oracle ODBC Driver 8.1.6 worked with ADO and it seems to work for you.

I just found a comment at this link:

http://groups.yahoo.com/group/comdeveloper/message/2357

which says that Oracle won't support direct OLE-DB until Release 8.1. You're using 8.1.6. It also mentions that ADO is built to use OLE-DB, but by default, it goes through an OLE-DB/ODBC bridge.

Since, I'm using version 8.0.5 of the Oracle ODBC Driver, I don't think this OLE-DB/ODBC bridge is supported by my driver. This is probably my problem.

Thanks alot for your help guys!
0
 
jfairbairn1Author Commented:
I want to see if you guys agree with me after reading my comment above before I send over the points..

Thanks again for your time.
0
 
nigelroweCommented:
Can't you download the new ODBC driver from somewhere? That's how we got it, but I don't know if was free or where it came from.
Yes, I forgot to mention that we use Oracle 8.1i and use OLEDB, but I use Oracle ODBC DSN's to connect with Interdev and sometimes for testing in ActiveX dll's instanced from VB.

Best of luck!!
0
 
jfairbairn1Author Commented:
Thanks for your time.. I'm pretty much convinced it's an issue with the version of the driver I'm using (8.0.5).

I'll have to surf around to see if there's downloads available with the more recent Oracle Drivers (8.1 or later).
0
 
nigelroweCommented:
Thanks for the points, though I don't feel as I've been much help to you.

Check out the Oracle web site (ODBC drivers) at...

http://otn.oracle.com/software/utilities/software_index.htm

Cheers.
0
 
jfairbairn1Author Commented:
Thanks... I was having a devil of a time trying to find out where the page to download these drivers.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 9
  • 6
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now