SQL Queries in API

Tags:
Question ID: 104504
0
0

Hi All,

Could you please show me how to connect QC10 from Excel and run SQL query.

Thanks a lot!

Marked as spam
Posted by (Questions: 1, Answers: 1)
Asked on April 18, 2012 1:26 pm
19 views
Answers (4)
0
Private answer

QCadmin,

Below is a template that I use often. This would require the QC Connectivity Add-in installed on the local machine in order for Excel to have the access to the libraries. You should be able to adjust the variables and go. You can add more columns by adding more rows under the ''Write the values to the worksheet'' section (be sure to increment the column letters).

Sub Query()

Dim qcServer, qcDomain, qcProject, qcUser, qcPassword, sSql

qcServer = ''http://qc:8080/qcbin/''
qcDomain = ''DEFAULT''
qcProject = ''QualityCenter_Demo''
qcUser = ''alex_qc''
qcPassword = ''''

Set tdc = CreateObject(''tdapiole80.tdconnection'')

'Check to see that the tdc object exists
If tdc Is Nothing Then
MsgBox ''The tdc object is empty''
End If

'Establish the connection and log in
tdc.InitConnectionEx qcServer
tdc.Login qcUser, qcPassword
tdc.Connect qcDomain, qcProject

'Create the tdc Command Object
Set oCommand = tdc.Command

'Build the query
sSql = ''SELECT REQ.RQ_REQ_ID As 'ReqID', '' & _
''REQ.RQ_REQ_NAME As 'Req Name' '' & _
''FROM REQ ''

'Set the SQL command to the Test Coverage query
oCommand.CommandText = sSql

'Execute the query and store in the SQLResults resultset.
Set SQLResults = oCommand.Execute

'Prepare the worksheet
Worksheets(''Sheet1'').Range(''A:B'').ClearContents

'Set the header row titles.
Worksheets(''Sheet1'').Range(''A1'') = ''Requirement ID''
Worksheets(''Sheet1'').Range(''B1'') = ''Requirement Name''

'Start populating data on row 2 (leaving the header information above).
iExcelRow = 2

'Iterate through the query results and populate the worksheet.
For iRecord = 1 To SQLResults.RecordCount

'Write the values to the worksheet
Worksheets(''Sheet1'').Range(''A'' & iExcelRow) = SQLResults.FieldValue(''ReqID'')
Worksheets(''Sheet1'').Range(''B'' & iExcelRow) = SQLResults.FieldValue(''Req Name'')

'Increment the iteration
iExcelRow = iExcelRow + 1
SQLResults.Next
Next

'Disconnect from Quality Center
If tdc.Connected = True Then
tdc.Disconnect
End If

'Log off the server
If tdc.LoggedIn Then
tdc.Logout
End If

'Release the TDConnection object.
tdc.ReleaseConnection

'Adjust the column width
Worksheets(''Sheet1'').Columns(''A:B'').EntireColumn.AutoFit

Set SQLResults = Nothing
Set oCommand = Nothing
Set tdc = Nothing

MsgBox ''Done''

End Sub

Marked as spam
Posted by (Questions: 15, Answers: 10)
Answered on April 23, 2012 4:02 pm
0
Private answer

Thanks a lot!

It works!!!

Marked as spam
Posted by (Questions: 1, Answers: 1)
Answered on April 24, 2012 1:34 pm
0
Private answer

@Young Steve and @QCadmin, I totally know its over 6 yrs now.
I was looking for a similar method. So I tested your above code in Excel macro. But, ended with an error. Error is ''Failed to run Query:. Its failing at line 'Execute the query and store in the SQLResults resultset.
Set SQLResults = oCommand.Execute.

Can you please investigate and let me know where is an issue?
But, if I modify the sSql statement as below, the script runs and display the mesbox ''Done. However, it does not export Requirements from the project though projects has requirement.
modified sSql is ''SELECT REQ.RQ_REQ_ID, REQ.RQ_REQ_NAME FROM REQ''

NOte: My DB instance is Oracle, ALM version 11.00

Marked as spam
Posted by (Questions: 30, Answers: 35)
Answered on January 22, 2019 3:35 am
0
Private answer

I figured the issue and have a solution so that it can help other.
CAUSE: Each Heading are in single quote ('ReqID')
SOLUTION: Each Heading should be with in double quote (''''''''ReqID'''''''').
So the query would look like this. ''SELECT REQ.RQ_REQ_ID As ''''ReqID'''', '' & _
''REQ.RQ_REQ_NAME As ''''Req Name'''' '' & _
''FROM REQ ''

Note: This applies if you are n Oracle.

Marked as spam
Posted by (Questions: 30, Answers: 35)
Answered on January 22, 2019 4:38 pm