Solved

ADO 2.5 compliant with Oracle ODBC Driver?

Posted on 2001-07-02
19
572 Views
Last Modified: 2011-10-03
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
Comment
Question by:jfairbairn1
  • 9
  • 6
  • 3
  • +1
19 Comments
 
LVL 1

Expert Comment

by:EstebanFG
ID: 6247165
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
 

Expert Comment

by:Onkar
ID: 6247378
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
 
LVL 3

Expert Comment

by:nigelrowe
ID: 6247676
Do you get an error message? If not, post the code which you use to open the connection and initialise the Rs.
0
 

Author Comment

by:jfairbairn1
ID: 6248540
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
 

Author Comment

by:jfairbairn1
ID: 6248544
Forgot to mention... in the code above, the fsGetTopParent function returns a string of SQL text...
0
 

Expert Comment

by:Onkar
ID: 6248600
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
 
LVL 3

Expert Comment

by:nigelrowe
ID: 6248643
Have you tried?...

Set rsNodes = m_cnAvantis.Execute(fsGetTopParent)
0
 

Author Comment

by:jfairbairn1
ID: 6248677
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
 
LVL 3

Expert Comment

by:nigelrowe
ID: 6248797
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:jfairbairn1
ID: 6250130
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
 

Expert Comment

by:Onkar
ID: 6251155
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
 

Author Comment

by:jfairbairn1
ID: 6252738
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
 
LVL 3

Accepted Solution

by:
nigelrowe earned 50 total points
ID: 6253021
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
 

Author Comment

by:jfairbairn1
ID: 6253052
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
 

Author Comment

by:jfairbairn1
ID: 6253063
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
 
LVL 3

Expert Comment

by:nigelrowe
ID: 6253096
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
 

Author Comment

by:jfairbairn1
ID: 6253118
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
 
LVL 3

Expert Comment

by:nigelrowe
ID: 6253177
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
 

Author Comment

by:jfairbairn1
ID: 6253358
Thanks... I was having a devil of a time trying to find out where the page to download these drivers.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now