Some Utility Codes

Some Utility Codes

While working with Oracle DB's,  you require TNS entry file (tnsnames.ora) in your system whenever you fire a query to a particular DB.
So when you fire query using ADODB objects to oracle DB's , you may require to have the TNS entry in that file else you would find error stating "TNS entry missing".

I struggled this kind of scenario, to have someone from Admin team to replace file, due to lack of administrative privileges. Some organizations don't allow that. I thought to eliminate this, so Googled for TNS entrly less Connection String.

I saw that a TNS file have below type of information

................................................................

SomeRefrenceName=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP) (HOST = theDBHostServer.Companyname.com) (PORT = 1530))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = SomeServiceName)
    )
  )

................................................................

So I thought can we enter this TNS Entry into our connection string? I found below solution.

Below provider is from MicroSoft for Oracle DB, This seemed to be reliable for me.

ConStr =  "provider=msdaora;" _ 

          & "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)" _ 

          & "HOST=" & strHOST & ")" _

          & "(PORT="& strPORT & "))" _

          & "(CONNECT_DATA=(SERVICE_NAME="& strSERVICE & ")))" _

          & ";User Id=" & strUID & ";Password=" strPWD & ";"

You can use other providers as well like

Provider=MSDAORA.1 

provider=OraOLEDB.Oracle.1

-Pankaj Dhapola 

Let's Think on it

An Example of DotNetFactory in QTP

An Example of DotNetFactory in QTP

Function DotnetUIDPWDForm(strDetails)
 'Creating instances of DotNetfactory objects
Set frmProd = DotNetFactory.CreateInstance("System.Windows.Forms.Form", "System.Windows.Forms")
Set lblDBDetails  = DotNetFactory.CreateInstance("System.Windows.Forms.Label", "System.Windows.Forms")
Set lblUID = DotNetFactory.CreateInstance("System.Windows.Forms.Label", "System.Windows.Forms")
Set txtUID = DotNetFactory.CreateInstance("System.Windows.Forms.TextBox", "System.Windows.Forms")
Set lblPWD = DotNetFactory.CreateInstance("System.Windows.Forms.Label", "System.Windows.Forms")
Set txtPWD = DotNetFactory.CreateInstance("System.Windows.Forms.TextBox", "System.Windows.Forms")
Set btnSubmit = DotNetFactory.CreateInstance("System.Windows.Forms.Button", "System.Windows.Forms")

Set oPoint = DotNetFactory.CreateInstance("System.Drawing.Point", "System.Drawing", x, y)

oPoint.x = 10
oPoint.y = 10
lblDBDetails.Location = oPoint
lblDBDetails.Width = 350
lblDBDetails.Height = 70
lblDBDetails.Text = strDetails

oPoint.x = 10
oPoint.y = 80
lblUID.Location = oPoint
lblUID.Text = "PROD User ID"

oPoint.x = 10
oPoint.y = 110
lblPWD.Location = oPoint
lblPWD.Text = "PROD Password."

oPoint.x = 150
oPoint.y = 80
txtUID.Location = oPoint
txtUID.Width = 100

oPoint.x = 150
oPoint.y = 110
txtPWD.Location = oPoint
txtPWD.Width = 100
txtPWD.UseSystemPasswordChar=true

oPoint.x = 150
oPoint.y = 150
btnSubmit.Location = oPoint
btnSubmit.Width = 100
btnSubmit.Text = "Submit"
     
'Add Controls
frmProd.Controls.Add lblDBDetails
frmProd.Controls.Add lblUID
frmProd.Controls.Add txtUID
frmProd.Controls.Add lblPWD
frmProd.Controls.Add txtPWD
frmProd.Controls.Add btnSubmit
frmProd.CancelButton=btnSubmit
frmProd.Text = "This is a PROD RUN"

frmProd.Height = 250
frmProd.Width = 350
frmProd.ShowDialog
DotnetUIDPWDForm = txtUID.Text & ":" & txtPWD.Text

Set frmProd=Nothing
Set lblDBDetails=Nothing
Set lblUID=Nothing
Set txtUID=Nothing
Set lblPWD=Nothing
Set txtPWD=Nothing
Set btnSubmit=Nothing
Set oPoint=Nothing
End Function

-Pankaj Dhapola 

Let's Think on it

Entry point in your Automation Framework

*getGlobalDataxlsPath()shall return the complete path of "GlobalData.xls" file

This is a small part to understand  getGlobalDataxlsPath()

Now the logic behind having an entry point for your Automation framework is whenever you execute any script, your framework structure should be available in Execution QTP environment. Since "GlobalData.xls" itself contain the structure, how you gonna access this excel sheet seamlessly.

One Idea, let's leverage QTP's Enviroment("TestDir") variable.

So your function called getGlobalDataxlsPath() Should return a path like for example 
"C:\\whereveryouhaveplaced\GlobalData.xls"
Colored path is what we need to extract.

Look at below code

Function  getGlobalDataxlsPath()

                 getGlobalDataxlsPath = Mid(Environment("TestDir"), 1, instr(1,Environment("TestDir"), "Framework")+10)

End Function

I hope you got dat!!!

Notice the dependency, your Framework Base folder should contain the word "Framework".

-Pankaj Dhapola

Let's Think on it