Hello,
In this post, we will learn about how to create a connection to ALM using OTA API.
1. Introduction
Open Test Architecture is an API shipped with HP ALM/QC, for automating background tasks like modifying bulk field values or downloading all of the attachments from the defects module. We can also use this API for Generating bulk reports, custom graphs and have the functionality built which is not available otherwise using the ALM/QC front end application.
OTA API’s object will be downloaded and registered in the machine when we open ALM for the first time. Once the components are registered, you can use the OTA COM Type Library for connecting ALM using any programming language which supports COM objects.
we will use Excel 2010 for connecting and accessing ALM/QC using OTA API.
2. Connecting to ALM using OTA API
Below are the steps for using OTA API for ALM Connection
A) Open Excel and Click on Developer ribbon and Click Visual Basic.
B) Click on Tools-> References and Check “OTA COM Type Library”.
Once we have the reference for this library, we can use the following code for connecting to ALM/QC
Dim ErrorString As String 'Create Connection Object of OTA API Dim OQCConnection As New TDAPIOLELib.TDConnection Sub TestALMOTAConnection() If ConnectALM("http://yourQCServer/qcbin", "yourusername", "password", "yourdomainname", "yourprojectnane") = True Then MsgBox "Connected to ALM" If DisConnectALM = True Then MsgBox "Project Disconnected Successfuly" Else MsgBox ErrString End If Else MsgBox ErrorString End If End Sub Public Function ConnectALM(URL As String, UserName As String, Password As String, Domain As String, Project As String) On Error GoTo Err_Handler ErrorString = "" 'Initiate connection with ALM Server OQCConnection.InitConnectionEx URL 'Connect to User OQCConnection.Login UserName, Password 'Connect to Domain and Project OQCConnection.Connect Domain, Project '"Successfully Connected to the project, Enjoy!!!" ConnectALM = True Exit Function Err_Handler: 'Check if the connection is successfull If OQCConnection.Connected = False Then ErrorString = "QC Server URL Initialization failed!!!" ConnectALM = False Exit Function End If 'Check if Login is Successfull If OQCConnection.LoggedIn = False Then ErrorString = "Unable to Login to ALM using the provided Credentials" ConnectALM = False Exit Function End If 'Check if project is connected If OQCConnection.ProjectConnected = False Then ErrorString = "Unable to Connect to the Domain or Project" ConnectALM = False End If End Function Public Function DisConnectALM() On Error GoTo Err_Handler ErrString = "" If OQCConnection.ProjectConnected Then OQCConnection.Disconnect End If If OQCConnection.LoggedIn Then OQCConnection.Logout End If If OQCConnection.Connected Then OQCConnection.ReleaseConnection End If 'Logged Out of QC DisConnectQC = True Exit Function Err_Handler: If OQCConnection.Connected Then ErrString = "Error Occurred while Disconnecting the QC Connection Object" End If If OQCConnection.ProjectConnected Then ErrString = "Error Occurred while disconnecting project" End If If OQCConnection.LoggedIn Then ErrString = "Error Occurred while Loggin out the User" End If DisConnectQC = False End Function
Once, you copy the above code, update the variables for a call to the function ConnectALM and execute TestOTAALMConnection function. If everything works fine then you should see a message ‘Connected to ALM’.
Now, let’s understand how the code works here,
Variables used
1) ALM URL: For connecting to ALM using OTA API, we must use the URL till qcbin instead of full ALM URL, e.g. http://YourServer/qcbin.
All other variables should be provided with the values, you are using for connecting to ALM/QC
Functions used
1) InitConnectionEx: This function is used for initializing the connection between ALM and your code. If ALM server is accepting connections.
2) Connected: This property will be false if the connection initialization is not completed.
2) Login: Login functions accepts ALM Username and password and used for login to the ALM/QC Server.
3) LoggedIn: This property is used for identifying, if user login was successful or not in the previous step. Login function will through error and we will have to use
4) Connect: This function will accept domain name and project name and will connect to ALM Project.
5) ProjectConnected: This function will provide details about if the Connection to the project was successful.
Now let’s see how to disconnect ALM connection, which will be similar to Logout functionality using the ALM GUI.
For Logging out of ALM we have to call the corresponding logout functions to InitConnectionEx, Login and Connect. These functions are used in the DisConnectALM function. Below is the description of the function used for disconnecting ALM
Functions
1) Disconnect: This function will disconnect the ALM Project.
2) Logout: This function will log out the user connected.
3) ReleaseConnection: This function will release the ALM connection object, after calling this function we will loose all of the references with ALM server.
Below is the table for showing the method property relationship of the functions and properties used above
Method | (Initial) | Init-ConnectionEx | Login | Connect | Disconnect | Logout | Release-Connection |
Property | |||||||
LoggedIn | Error | FALSE | TRUE | TRUE | TRUE | FALSE | Error |
Connected | FALSE | TRUE | TRUE | TRUE | TRUE | TRUE | FALSE |
ServerName | Empty String | Server Name | Server Name | Server Name | Server Name | Server Name | Empty String |
ProjectName | Error | Empty String | Empty String | Project Name | Empty String | Empty String | Error |
Project-Connected | Error | FALSE | FALSE | TRUE | FALSE | FALSE | Error |
We will discuss about accessing and changing specific ALM modules in the later posts.
Thanks
Pingback: DOWNLOADING ATTACHMENTS FROM ALM/QC, Tests and Design Steps module | Automation Insights·
Pingback: Filtering Test from Test Plan using Test ID (TS_TEST_ID) in ALM/QC | Automation Insights·
Pingback: Filtering Defects using Defect ID (BG_BUG_ID) in ALM/QC | Automation Insights·
Hi Sumeet,
I want to run Single test case from ALM test set, can you help me in achieving through OTA, I want to create one single vbs to do this.
Thanks,
Manju
LikeLike
Hi,
I am looking for a sample to download the Results tab files using VBcode. Could you please advise.
LikeLike
I am not able to connect…says URL invalid 😦
LikeLike
What’s the URL you are using ??
LikeLike
Hi,
Following error thrown while creating connection object:
“Retrieving the COM class factory for component with CLSID {C5CBD7B2-490C-45F5-8C40-B8C3D108E6D7} failed due to the following error: 800703e6 Invalid access to memory location. (Exception from HRESULT: 0x800703E6).”
LikeLike
Hi Sumeeth,
can we add values while passing test case in test set like domain name,test type,Environment and can we click on pass all and end run and can be capture run id as well?
LikeLike
If u can do it manually, then you should be able to do it with OTA API..
LikeLike
Hi Sumeet,
I have an error ‘429’…
Could you please advise?
LikeLike
Hi Orel
What are the steps to replicate the error ? Which programming language you are using ?
What is the version of ALM you are using ?
I don’t see error code 429 in the list of error codes available for OTA API. Can you send me the description for the error ?
Thanks,
Sumeet singh kushwah
LikeLike
Same here on error ‘429’. I have ALM 14 and Excel 2016
LikeLike
Hi Sumeet,
The article is very informative, Thanks.
Using the below code, I am not able to open the ALM. There is no error also. Could you please guide
Dim QCConnection As Object;
Set QCConnection= createObject (‘TDApiOle80.TDConnection’)
Dim sUsername, sPassword
Dim sProject, sDomain
sUsername=sheet1.Range(A1).Value
sPassword=sheet1.Range(B1).Value
sProject=sheet1.Range(C1).Value
sDomain=sheet1.Range(D1).Value
QCConnection.InitConnectionEx sheet1.Range(E1).Value
QCConnection.login sUsername,sPassword
QCConnection.connect sDomain,sProject
LikeLike
What’s the URL used ??
LikeLike
Hi Sumeet,
When i run i am getting below error
Compile error:
User-defined type not defined
LikeLike
Have u installed ALM and tdconnect.exe on the machine ?
LikeLike
Hi Sumeet, do we need additional code to connect to ALM when we are using LDAP ID and Password?
LikeLike
I dont think so, but if you are receiving any errors then let me know.
LikeLike
Pingback: Executing Queries using OTA API | Automation Insights·
Pingback: Renaming test plan folder using OTA API | Automation Insights·
Pingback: Deleting ALM test plan folder using OTA API | Automation Insights·
Pingback: Find tests under a test plan folder using OTA API | Automation Insights·
Pingback: Find folders under test plan folder in ALM using OTA API | Automation Insights·
Hi Sumeet,
I am getting the below error when I try my c# code to check connection with HP ALM.
‘System.__ComObject’ to interface type ‘TDAPIOLELib.TDConnection’. This operation failed because the QueryInterface call on the COM component for the interface with IID ‘{5FFC8521-6228-433D-B6DE-0D24E9E1B2D2}’ failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)).’
LikeLike
Have you installed tdconnect.exe ?
LikeLike
Hello Sumit,
any help to connect to ALM Site Admin and to add user ?
LikeLike
Hi Sumeet,
I am looking for automation this process using Java. Could you shed some ideas on how to achieve my goal.
Regards
Thulasiram
LikeLike
Your code has an error at line 86. It should read DisConnectALM = True
Not DisConnectQC = True
LikeLike
Thanks for pointing it out. I will update it.
LikeLike
Hi ,
Getting error : “Not connected to Domain Server” while creating a project through SA Client.dll in c#
Kindly guide me in this .
LikeLike
Can you post your code ?
Thanks,
Sumeet Kushwah
LikeLike
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Web;
using SACLIENTLib;
namespace ServiceLibrary
{
public enum Resource
{
login
}
public class ALMService
{
SACLIENTLib.SAapi _saClinet = null;
public string m_Username;
public string m_Password;
public ALMService(string username, string password)
{
m_Username = username;
m_Password = password;
}
//Destructor for Class .
~ALMService()
{
m_Username = string.Empty;
m_Password = string.Empty;
}//END..
public bool IsAuthenciateALM(
Resource resource,
string m_BaseUrl = null
)
{
//string url = string.Format(“{0}{1}”, m_BaseUrl, resource.ToString());
string url = string.Format(“{0}”, m_BaseUrl);
try
{
_saClinet = new SAapi();
_saClinet.Login(url, m_Username, m_Password);
return true;
}
catch (Exception Ex)
{
_saClinet.Logout();
return false;
}
}
public string CreateProject(
Resource resource,
string domain ,
string project,
string m_BaseUrl = null
)
{
string result = string.Empty;
//string url = string.Format(“{0}{1}/”, m_BaseUrl, resource.ToString());
//SA_MSSQL_DB_TYPE (value: 2)
//SA_ORACLE_DB_TYPE (value: 3).
int SA_ACTIVATE_NEW_PROJECT = 1;
string url = string.Format(“{0}”, m_BaseUrl);
try
{
_saClinet = new SAapi();
result = _saClinet.CreateProject(“DEFAULT”, “NewProject123”,2, string.Empty, m_Username, m_Password, string.Empty, string.Empty, 0, 0, SA_ACTIVATE_NEW_PROJECT);
}
catch (Exception Ex)
{
result = Ex.Message.ToString();
_saClinet.Logout();
}
return result;
}
}
}
LikeLike
The Only thing I will check is your 4th argument, its supposed to be your server name and you have passed it as Empty. Have you tried passing your server name ? It should be in the below format.
sServerName = “http://myserver/sabin”
Thanks,
Sumeet
LikeLike
Able to login but while creating project getting error as mentioned
LikeLike
Yes Sumeet , am passing the 4th parameter in same string format only
https://myserver/qcbin
LikeLike
QC Server URL Initialization failed!!! what i need to do for this
LikeLike
What’s the URL you are using ?
LikeLike
I am not able to connect to ALM. Getting Runtime error ‘429’. ActiveX component can’t create object.
What I see different from the steps I mentioned is,
In VBA reference, for me it’s showing C:\Users\xxxx\AppData\Local\HP\ALM-Client\12.50.0.0
instead of
C:\Program Files (x86)\Common Files\Mercury Interactive\TDAPIClient
Is it a problem?
LikeLike
No that’s not the issue, you might have to delete the almaclient folder and reinstall alm and then reinstall TDconnect.exe.
LikeLike
Hi Sumeet! I’m trying to connect to ALM to download the test design steps. What would be the easiet way to do it ?
LikeLike
You can simply write a query to do it, you just need the folder ID which can also be found by another query.
/////// Replace folder id in below query
Select TEST_PLAN_PATH, TS_NAME, DS_STEP_NAME, DS_DESCRIPTION, DS_EXPECTED
from TEST
Inner Join
(
Select AL_ITEM_ID, AL_DESCRIPTION, LEVEL, SYS_CONNECT_BY_PATH(AL_DESCRIPTION, ‘\’) “TEST_PLAN_PATH”
from ALL_LISTS
START WITH AL_ITEM_ID = ‘24560’
CONNECT BY PRIOR AL_ITEM_ID = AL_FATHER_ID
) TESTFOLDER
ON TESTFOLDER.AL_ITEM_ID = TEST.TS_SUBJECT
Inner join DESSTEPS
on DS_TEST_ID = TS_TEST_ID
ORDER BY TEST_PLAN_PATH, TS_TEST_ID, DS_STEP_ORDER
//// Find folder ID by folder name using the below query
Select * from ALL_Lists where AL_DESCRIPTION Like ”
or you can use the API for downloading the design steps, you can find the examples below
https://github.com/sumeet-kushwah/ALM_OTA_Wrapper/blob/master/ALM_Wrapper/Test.cs
//Check GetDesignStepList on the above page.
LikeLike
Sumeet, the query helped, but i have more questions. What is –“TEST_PLAN_PATH” ??
LikeLike
Also. i have Subject>Folder. I used your query to get the test scripts and design steps.
1. What is the “TEST_PLAN_PATH” ? like i asked before.
2.How do I know i have all the scripts downloaded ? how do i verify the num of actual TC in ALM vs what i was able to get from the query.
3.My folder structure is Subject>Folder>Sub folder1>subfolder2 and so on. Is there a way to extract all info along with the folder hierarchy?
LikeLike
1) TEST_PLAN_PATH should be in the results and is the folder path for the test case starting the folder ID you provided to the query.
2) You can use live analysis in the test plan to verify it.
3) Your folder structure is in TEST_PLAN_PATH, it cant pull the names of parent folders but you can simply concatenate the string in the query to generate that or simply concatenate that in the excel sheet, parent folder will be same for all downloaded tests.
LikeLike