Tuesday, May 26, 2009

Connect to Oracle DataBase using QTP

'Reference: Microsoft ActiveX Data Objects 2.7 library

'Dim cnn As ADODB.Connection
Set cnn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.RecordSet")

' Open a connection by referencing the ODBC driver in sql server
' cnn.ConnectionString = "driver={SQL Server};&server=MySqlServer;uid=MyUserName;pwd=MyPassword;database=pubs"
'cnn.Open

' Open a Connection using an ODBC for oracle
cnn.ConnectionString = "DRIVER={Microsoft ODBC for Oracle};DSN=real;UID=gopi;PWD=orcl;"
cnn.ConnectionTimeout = 30
cnn.Open

' Find out if the attempt to connect worked.
If cnn.State = adStateOpen Then ' or cnn.State=1
MsgBox "connected to database"
Else
MsgBox "connectin failed.."
End If

'execute sql query
rs.Open "SELECT * FROM promotion.t_p_promo_master WHERE f_promo_id = 60082", cnn

' Create a Recordset by executing an SQL statement.
' Set rs = cnn.Execute("Select * From authors")
'Remember that the returned Recordset object from connection.execute is always a read-only,
'forward-only cursor. If you need a Recordset object with more functionality, you should
'first create a Recordset object with the desired property settings and then use the
'Recordset object's Open method to execute the query and return the desired cursor type.

' Send a Delete statement to the database.
'cnn.Execute ("Delete From authors Where au_id = '011-01-0111'")

' Find out how many rows were affected by the Delete.
'Set rs = cnn.Execute("Select @@rowcount")
' Display the first field in the recordset.
'MsgBox rs(0) & " rows deleted"
'in above the command passed to the data source is a Delete statement. Because no rows are returned, you do not need to explicitly use a Recordset object. How many rows were deleted? You can use the recordsAffected parameter to find out.

'Running stored prcedure
' Create a recordset by running a stored procedure.
' Set rs = cnn.Execute("Exec byroyalty 50")

Do While Not rs.EOF
'copy each field
'Worksheets("Sheet1").Range("A3").End(xlDown).Offset(1, 0) = rs.Fields(0)

'display each field in the record set
For Each Field In rs.Fields
MsgBox Field
Next
rs.MoveNext

Loop

' Close the connection.
cnn.Close

2 comments: