Working with BLOB or CLOB Objects in QTP

The below Code will Insert the CLOB or BLOB objects in Oracle DB using QTP.

Sample Code:

'CLOB or BLOB file Path
strClobFileName = "C:\Jay\SampleInputs\BLOBorCLOB.txt"

'Connection String
strOracleConnectionString = "Driver= {Microsoft ODBC for Oracle}; " &_
                  "ConnectString=(DESCRIPTION=" &_
                  "(ADDRESS=(PROTOCOL=TCP)" &_
                  "(HOST=hostName) (PORT=1234))" &_
                  "(CONNECT_DATA=(SERVICE_NAME=ABCD)));uid=userName; pwd=Password;"

'Create Required Objects
Set objCon = CreateObject("Adodb.Connection")
Set objRs = CreateObject("Adodb.Recordset")
Set objCom = CreateObject("Adodb.Command")
Set objStream = CreateObject("ADODB.Stream")

'Set the CursorLocation to Get the Record Count properly
objCon.CursorLocation = 3

'Open the Connection
objCon.Open strOracleConnectionString

'Set the required types for Stream Object
objStream.Charset = "us-ascii"
Const adTypeText = 2
objStream.Type = adTypeText

'Open the Stream Object
objStream.Open

'Load the CLOB or BLOB object from the File
objStream.LoadFromFile strClobFileName

'Command to Execute
'Here we should place the "?" for CLOB or BLOB object to
objCom.CommandText = "INSERT INTO TABLE(BLOBorCLOBColumnName,Col2,Col3) VALUES (?,1,2,)"

'set the Active Connection to ADODB Command
objCom.ActiveConnection = objCon

'Execute the Command with CLOB or BLOB object as Input as objStream.ReadText
set objrs1 = objCom.Execute(strMsg,objStream.ReadText,adExecuteNoRecords)

'Read the strMsg to validate wether Record is inserted or not
If strMsg >= 1 Then
    Print "Pass"
Else
    Print "Fail"
End If

Comments

Convert JSON to XML using QTP/UFT/VBScript

Sample Code : Dim strPage,strJSON,objIE strPage = "C:\Jay\JLoader.html" Set objIE = CreateObject("InternetExplorer.Application") objIE.Visible = True objIE.Navigate2 strPage While objIE.Busy : Wend strJSON = "{""FirstName"":""Jay"", ""LastName"":""Krishna""}" Set objWin = objIE.document.parentWindow objWin.execScript "var jsonStr2XML = function(strJSON) { return json2xml(JSON.parse(strJSON));};" Msgbox oWin.jsonStr2XML(strJSON) objIE.Quit In Detail: Converting The most popular data interchange format JSON(JavaScript Object Notation) to XML using QTP/UFT. Parsing JSON in UFT could be a challenge so we will use JavaScript in UFT to make it perfect. SO We need : Java Script API - To Convert JSON to XML JavaScript Files : http://goessner.net/download/prj/jsonxml/j

Read Outlook mail attachment and Body using Vb Script or QTP

Set olApp = CreateObject("Outlook.Application") Set olns = olApp.GetNameSpace("MAPI") Set ObjFolder = olns.GetDefaultFolder(6) j = 0 For each item1 in ObjFolder.Items iattachCnt = item1.Attachments.Count Print "Attachments Count: " & iattachCnt For i = 1 to iattachCnt Print "FileName : " & item1.Attachments(i).FileName Print "Display Name: " & item1.Attachments(i).DisplayName Print "Size: " & item1.Attachments(i).Size Next Print " Body : " & item1.body Print "--------------------------------------Mail Num - " & j & " -----------------------------------------------" j = j+1 Next

Excel Sorting By Rows and Columns

Excel Sorting By Row: Const xlAscending = 1 Const xlNo = 2 Const xlSortRows = 2 Set objExcel = CreateObject(“Excel.Application”) objExcel.Visible = True Set objWorkbook = objExcel.Workbooks.Open(“C:\Jay\Docs1.xls”) Set objWorksheet = objWorkbook.Worksheets(1) objWorksheet.Cells(1,1).activate Set objRange = objExcel.ActiveCell.EntireRow objRange.Sort objRange, xlAscending, , , , , , xlNo, , , xlSortRows set objExcel=nothing Excel Sorting By Column : Const xlAscending = 1′represents the sorting type 1 for Ascending 2 for Desc Const xlYes = 1 Set objExcel = CreateObject(“Excel.Application”)’Create the excel object objExcel.Visible = True’Make excel visible Set objWorkbook = _ objExcel.Workbooks.Open(“C:\Jay\Docs1.xls”)’Open the document Set objWorksheet = objWorkbook.Worksheets(1)’select the sheet based on the index .. 1,2 ,3 … Set objRange = objWorksheet.UsedRange’which select the range of the cells has some data other than blank Set objRange2 = objExcel.Range

How to Read or Select Context Menu or Right Click Menu using QTP.

Select The Item in Right Click Menu or Context Menu: Window("sampleWindow").WinMenu("MenuObjType:=1).Select"File;New" Here MenuObjtype can be 1 r 2 r 3 .......n Check wether the Item is Exist or Not: If Window("sampleWindow").WinMenu("MenuObjType:=1).GetItemProperty("1","Exist") Then Msgbox"Exist" Else Msgbox"Does Not Exist" End If Or If Window("sampleWindow").WinMenu("MenuObjType:=1).GetItemProperty("File","Exist") Then Msgbox"Exist" Else Msgbox"Does Not Exist" End If Get the Items in Context Menu: For i = 1 to 10 Print Window("sampleWindow").WinMenu("MenuObjType:=" & i).GetItemProperty("1","Label") Then Next

Compare Two Excel sheets cell by cell Using Vb Script

'This code will open two excel sheet and compare each sheet cell by cell, if any changes there in cells , it will highlight the cells in red color in the first sheet. Set objExcel = CreateObject(“Excel.Application”) objExcel.Visible = True Set objWorkbook1= objExcel.Workbooks.Open(“C:\Jaykrishna\Docs1.xls”) Set objWorkbook2= objExcel.Workbooks.Open(“C:\Jaykrishna\Docs2.xls”) Set objWorksheet1= objWorkbook1.Worksheets(1) Set objWorksheet2= objWorkbook2.Worksheets(1) For Each cell In objWorksheet1.UsedRange If cell.Value <> objWorksheet2.Range(cell.Address).Value Then cell.Interior.ColorIndex = 3′Highlights in red color if any changes in cells Else cell.Interior.ColorIndex = 0 End If Next set objExcel=nothing

Quick Learn Automation

Search This Blog