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

establish a direct connection to a Oracle table - Urgent

I need help to establish a direct connection to Oracle.

I have no idea what I need to do.  I took this information from the tnsnames.ora on my computer.  I also need to pass the User Name (Name) and Password (Pass).  I think my main problem is in building the connection string.  Any help would be appreciated.


# SAP P4S Environment
P4S =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = p4s-dbdl.sikorsky.com )(PORT = 1534))
    )
    (CONNECT_DATA =
      (SID = P4S)
    )
  )


Thank You!
0
Delirious
Asked:
Delirious
  • 5
  • 2
  • 2
1 Solution
 
DeliriousAuthor Commented:
If any other information is needed let me know.

Thanks
0
 
DeliriousAuthor Commented:
I need this connection to establish itself when the database opens and remain connected until the database closes.
0
 
DeliriousAuthor Commented:
I'm leaving for the day, be back tomorrow.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Leigh PurvisDatabase DeveloperCommented:
What do you mean by a direct connection?
In a table definition - or perhaps an ADO connection object?

Have a look here (the usual source of connection strings) - to see if this helps.
http://www.connectionstrings.com/
0
 
DeliriousAuthor Commented:
Yes the ADO object is what I need.  

So, using the link you supplied, I guess I need the following.

"Driver={Microsoft ODBC Driver for Oracle};ConnectString=OracleServer.world;Uid=myUsername;Pwd=myPassword;"

But I'm not sure what this means.  
0
 
Leigh PurvisDatabase DeveloperCommented:
What it means?
It's the connection object's connection string?
You'd substitute into it values for your server, username and password of course :-)


Dim cnn as New ADODB.Connection

cnn.ConnectionString = "Driver={Microsoft ODBC Driver for Oracle};Server=OracleServer.world;Uid=myUsername;Pwd=myPassword;"

Of course - if you have the OLE DB provider for Oracle installed then perhaps you could use that (for better performance).

"Provider=msdaora;Data Source=MyOracleDB;User Id=UserName;Password=asdasd;"
0
 
rockiroadsCommented:
I have done connections direct to Oracle before using ADO

Here are some simplified snippets of my code
All code to be placed in a module


Public Enum ConnectPrompts
    adPromptAlways = 1
    adPromptComplete = 2
    adPromptCompleteRequired = 3
    adPromptNever = 4
End Enum

Dim m_cnOracle As ADODB.Connection
Dim m_rsOracle As ADODB.Recordset

Public Function ConnectOracleADO(ByVal sSID As String, _
                                 ByVal sUserID As String, _
                                 ByVal sPassword As String) As Boolean

    On Error GoTo ConnectFailure
   
    If m_cnOracle Is Nothing Then
        Set m_cnOracle = New ADODB.Connection
    End If
   
    If m_cnOracle Is Nothing Then
        MsgBox "Cannot initialise Oracle ADO Object", vbCritical, "Ooops"
        Exit Function
    End If
   
    Debug.Print "Connecting..."
    'Connect to database
    m_cnOracle.Provider = "MSDAORA"
    m_cnOracle.Properties("Prompt") = ConnectPrompts.adPromptComplete
    m_cnOracle.Open sSID, sUserID, sPassword

    ConnectOracleADO = True
    Exit Function

ConnectFailure:
    ConnectOracleADO = False

End Function

Public Function ExecuteSQL(ByVal sSql As String) As Boolean

    On Error GoTo ExecuteFailure
   
    If sSql <> "" Then
        Debug.Print "Fetching data..."
        Set m_rsOracle = New ADODB.Recordset
        m_rsOracle.CursorLocation = adUseClient
        m_rsOracle.Open sSql, m_cnOracle, adOpenStatic, adLockOptimistic, adAsyncFetch
   
        'Loop thru
        Do While m_rsOracle.EOF = False
            Debug.Print m_rsOracle.Fields(0).Name, m_rsOracle.Fields(0).Value
            m_rsOracle.MoveNext
        Loop
        Debug.Print "Fetched"
    End If

    ExecuteSQL = True
    Exit Function
   
ExecuteFailure:
    ExecuteSQL = False
End Function

Public Function DisconnectOracleADO()

    On Error Resume Next
   
    m_rsOracle.Close
    m_cnOracle.Close
   
    Set m_rsOracle = Nothing
    Set m_cnOracle = Nothing

End Function



Do note, this is simplified. Not complete validation is there. I have other stuff and its all part of a class, but Ive taken samples to be used in a module.


'Your TNS Names SID is PS4

    If ConnectOracleADO("PS4", "myuserid", "mypswd") = True Then
        ExecuteSQL "SELECT * FROM MYTABLE"
        DisconnectOracleADO
    End If
0
 
rockiroadsCommented:
I have set the connection and recordset to be a module level variable. You could make it global by putting in public
But one call to Connect means your database connection is active. Simply make use of m_cnOracle

0
 
DeliriousAuthor Commented:
Here's what I did.  Thanks for both your help.


Public Function ConnectOracle()
On Error GoTo Err_ConnectOracle

Dim cn As New ADODB.Connection
   
    cn.ConnectionString = "Provider=OraOLEDB.Oracle.1;Password=password;Persist Security Info=True;User ID=user;Data Source=P4S"
    cn.Open

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now