How-To and Fixes
...
Client failed the test, but was unable to re-take the PCOC test after waiting mandatory 24hr period with the Token provided.
The PCOC Database Support team will investigate and issue a new token ASAP.
Solution: See Mike-WIP DOS 765 https://dev dot azure.com/DSD-Marine/MSS-Portfolio/_workitems/edit/765
Problem: System shows Unknown Result when client tried to get the result.
...
Solution: The user will get Unknown Result when the exam token is deleted. Exam tokens are deleted if the candidate fails an exam and a new token is re-issued. Exam tokens are also deleted in case a brand new token is issued to the candidate.
Updated (J. Green, 2019-04-30): Let's see if we can fix this issue the same way that we resolved DOS 717.
To get all tokens for Paul Harry, let's do a search on the LM109 table for all PCOCTO_OPERATOR_CD for this user.
select *
from TM110_PCOCTO_OPERATOR
where upper(first_name_nm) like upper('paul%')
and UPPER(LAST_NAME_NM) like upper('%harry%')
ORDER BY DATE_CREATED_DTE DESC;Use the PCOCTO_OPERATOR_CD values from the results and get all tokens:
select *
from lm109_exam_grp_token
where pcocto_operator_cd in (1670924,1670923,1670869,1669160,1669155);The token (and results for those tests) are as follows:
027888770BEA44CA8917B898291D348C FAILED (RE-ISSUE)
1F114A22931345E397B661734B1F9AFC NOT STARTED (RE-ISSUE)
2A636669501741AF9593592E5D8E86D8 FAILED
736EE9EC5F6048AC874200D74ED6DBB0 FAILED (RE-ISSUE)
98C4BCB471F6423393BF879C079F83D0 FAILED
Let's get all exam information for Paul's newest, unused exam token:
SELECT tm104.*
FROM TM104_EXAM_GROUP_VERSIONS tm104, lm109_exam_grp_token lm109
WHERE exam_grp_token_id = '1F114A22931345E397B661734B1F9AFC'
and tm104.exam_group_id = lm109.exam_group_id;The valid dates for the exam token not started are:
Generated Date: April 25, 2019 @ 1:17 PM
START: April 26, 2019 @ 1:17 PM
END: May 26, 2019 @ 1:17 PM
To summarize: the user has taken 4 tests and failed. The user has one attempt left and that attempt is valid from April 26 @ 1:17 PM until May 26 @ 1:17 PM.
To take this test, the user should use the following link to access the test:
https://wwwapps.tc.gc.ca/saf-sec-sur/4/pcocds-sbdccep/to/p1000.aspx?lang=0&token=1F114A22931345E397B661734B1F9AFC
The results from all other tests are no longer available. Test results can not be checked after an exam token is deleted. Exam tokens are deleted if the candidate fails an exam and a new token is re-issued. Exam tokens are also deleted if a brand new token is issued to the candidate (e.g. Fail twice and then authorize a new token for the candidate).
Sending details to PCOCDS client (see main ticket - DOS 716). See attachments. (J. Green)
--
Connect to PCOC (PROD) database (PCOCDSP.WORLD)
Step 1. Find the Operator
select *
from TM110_PCOCTO_OPERATOR
where upper(first_name_nm) like upper('%paul%')
and UPPER(LAST_NAME_NM) like upper('%harry%')
ORDER BY DATE_CREATED_DTE DESC;--
Found 5 records for this operator:
1670924, 1670923, 1669155, 1669160, 1670869
Step 2. Find their Token and Exam Group ID for all the operator id
Select *
From Lm109_Exam_Grp_Token
WHERE PCOCTO_OPERATOR_CD = '1669160'
ORDER BY DATE_CREATED_DTE DESC;from the five token below mentioned tokens are provided by the client
(PCOCTO_OPERATOR_CD, TOKEN, EXAM_GROUP_ID)
(1669160, 736EE9EC5F6048AC874200D74ED6DBB0, 1670376)
(1670869, 2A636669501741AF9593592E5D8E86D8, 1672085)
Step 3. Find their exam
SELECT *
FROM TM104_EXAM_GROUP_VERSIONS
WHERE EXAM_GROUP_ID IN ('1670376','1672085')
ORDER BY DATE_LAST_UPDATE_DTE DESC;Both exams shows are not passed (exam_passed_ind=0). What is the current state of these exams? Are they started, finished, etc?
SELECT *
FROM LM109_EXAM_GRP_TOKEN
WHERE EXAM_GROUP_ID IN ('1670376','1672085')
ORDER BY DATE_LAST_UPDATE_DTE DESC;Exam 1672085 (2A636669501741AF9593592E5D8E86D8):
Time remaining: 43.05, date_deleted_dte = 2019-04-25 13:17:16, exam result = FAIL
Exam 1670376 (736EE9EC5F6048AC874200D74ED6DBB0):
Time remaining: null, date_deleted_dte = 2019-04-20 00:34:26, exam result = FAIL
User is not getting results for these exams because they are deleted? Did the user finish all questions? If not, this can result in no value being returned. Let's take a closer look at the actual questions answered per exam.
SELECT COUNT(PASS_QUESTION_IND) AS QuestionsCompleted
FROM TM107_XREF_EXAM_GRP_QUESTION t
WHERE T.EXAM_GROUP_ID = '1670376';No questions answered at all: exam not even started.
SELECT COUNT(PASS_QUESTION_IND) AS QuestionsCompleted
FROM TM107_XREF_EXAM_GRP_QUESTION t
WHERE T.EXAM_GROUP_ID = '1672085';50 questions answered: 26 incorrect, 24 correct
Let's try to make a call to the PCOCDS web service using SOAP UI.
select *
from TC008_USER
where organization_id = 78
order by USER_ID DESC;
Look for the account that has the comment that specifies that the account is for using the PCOC web service. In this case, ISE8322 / C3i8vj5$.
Need to add header authorization (NTLM) to the web service call. See PCOCDS Web Service project ($/PCOCDS-SBDCCEP/PCOCSERVICE/DEVELOPMENT/PCOCService) to get the username and password. Look in the CPWebSiteEmulator project's web.config. Username is PCOCWCF, Domain and Password should be viewable in the config file.
Send the following XML to the PCOCDS web service:
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pcoc="https://wwwapps.tc.gc.ca/saf-sec-sur/4/PCOCWS-SWCCEP">
<soapenv:Header/>
<soapenv:Body>
<pcoc:GetFinalTestResult>
<!--Optional:-->
<pcoc:request>
<!--Optional:-->
<pcoc:Username>ISE8322 </pcoc:Username>
<!--Optional:-->
<pcoc:Password>C3i8vj5$</pcoc:Password>
<!--Optional:-->
<pcoc:Token>2A636669501741AF9593592E5D8E86D8</pcoc:Token>
</pcoc:request>
</pcoc:GetFinalTestResult>
</soapenv:Body>
</soapenv:Envelope>Result:
GetFinalTestResultResponse xmlns="https://wwwapps.tc.gc.ca/saf-sec-sur/4/PCOCWS-SWCCEP">
<GetFinalTestResultResult>
<ServiceMessages/>
<ScorePercentage>50.0</ScorePercentage>
<QuestionsPassed>25</QuestionsPassed>
<TotalQuestions>50</TotalQuestions>
<ResultStatus>Fail</ResultStatus>
</GetFinalTestResultResult>
</GetFinalTestResultResponse>
Let's try the other:
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:pcoc="https://wwwapps.tc.gc.ca/saf-sec-sur/4/PCOCWS-SWCCEP">
<soapenv:Header/>
<soapenv:Body>
<pcoc:GetFinalTestResult>
<!--Optional:-->
<pcoc:request>
<!--Optional:-->
<pcoc:Username>ISE8322 </pcoc:Username>
<!--Optional:-->
<pcoc:Password>C3i8vj5$</pcoc:Password>
<!--Optional:-->
<pcoc:Token>736EE9EC5F6048AC874200D74ED6DBB0</pcoc:Token>
</pcoc:request>
</pcoc:GetFinalTestResult>
</soapenv:Body>
</soapenv:Envelope>Code returned: TC0012
What is code TC0012? Let's check the web service code. This is the result of no results being found from the stored procedure spPCOCTO_OP_S07. This "stored procedure" is in a text file in the directory:
$/PCOCDS-SBDCCEP/PCOCSERVICE/DEVELOPMENT/PCOCService/TC.PCOCTO.Services/sp
SQL is as follows:
SELECT U.ORGANIZATION_ID
FROM TC008_USER U
WHERE U.USER_ID = '[0]'
AND U.ORGANIZATION_ID =
(SELECT PO.CURRENT_ORGANIZATION_ID
FROM TM110_PCOCTO_OPERATOR PO
WHERE PO.PCOCTO_OPERATOR_CD =
(SELECT EGT.PCOCTO_OPERATOR_CD
FROM LM109_EXAM_GRP_TOKEN EGT
WHERE EGT.EXAM_GRP_TOKEN_ID = '[1]'))where [0] = ISE8322 and [1] = 736EE9EC5F6048AC874200D74ED6DBB0;
The CP matches the candidate and exam token. So let's look at the next SQL "stored procedure' spEXAM_S01 (in the same directory):
SELECT EG.EXAM_GROUP_ID, COUNT(Q.QUESTION_ID) AS QuestionCount
FROM LM109_EXAM_GRP_TOKEN EG
INNER JOIN TM107_XREF_EXAM_GRP_QUESTION Q ON EG.EXAM_GROUP_ID = Q.Exam_Group_Id
WHERE EG.EXAM_GRP_TOKEN_ID = '736EE9EC5F6048AC874200D74ED6DBB0'
GROUP BY EG.EXAM_GROUP_IDNothing is returned by this query. There are no questions started for this exam. It was deleted before it could be started/finished and was set to a FAIL in the database. Why was this exam deleted?
select *
from lm109_exam_grp_token
where exam_grp_token_id = '736EE9EC5F6048AC874200D74ED6DBB0';
DATE_DELETED_DTE = 2019-04-20 00:34:26
LAST_UPDATE_USER_ID = 0 (admin).
According to the code,
//Any record with a value for date_deleted_dte is considered invalid and should not be considered
//Tokens are valid for 30 days from the effective date
//Effective date is 30 days from created date unless it was a re-issued token because of failure
//In case of failure re-issue, the effective date is 24 hours from the re-issued date and thus
//The Expiry date will be 30 days from that date
//To Summarize - Tokens that are re-issued after a failer have an expiry date of 31 days after the created date
// - as the operator must wait 24 hours to retake the test
This second token was deleted and is not valid. There are no results for it.
Let's send this info to client and see what they want to do with it (see attachments).
Problem: Client needs a list of all PCOC Test Online Questions, Answers, Answer Key, and Test Images.
...
Solution:
Although not a permanent fix, a temporary fix is described in TFS 14219.
Problem: Server error when student attempts exam
...
Solution:
Search the token in the database and change language to English. The detail fix is described below
Found a similar ticket in TFS 6683. Will refer to that to fix this issue,
Connect to PCOC (PROD) database (PCOCDSP.WORLD)
Step 1. Find the Operator
select *
from TM110_PCOCTO_OPERATOR
where upper(first_name_nm) like upper('%Claire%')
and UPPER(LAST_NAME_NM) like upper('%Leblanc%')
ORDER BY DATE_CREATED_DTE DESC;
Step 2. Find their Token
Select *
From Lm109_Exam_Grp_Token
WHERE PCOCTO_OPERATOR_CD = '1660093'
ORDER BY DATE_CREATED_DTE DESC;
WHERE PCOCTO_OPERATOR_CD = '1660093'
ORDER BY DATE_CREATED_DTE DESC;
Step 3. Find their Exam
SELECT *
FROM TM104_EXAM_GROUP_VERSIONS
WHERE EXAM_GROUP_ID = '1661308'
ORDER BY DATE_LAST_UPDATE_DTE DESC;
Step 4. Update their Language
update TM104_EXAM_GROUP_VERSIONS
set language_cd = 1
where EXAM_GROUP_ID = 1661308;
Now that the token's language has been updated to English, contact the Course Provider (cc PCOCDS team - DL OTT PCOC Team) to advise that token should now allow user to take the test in English.
Double check that the URL for the test is acessible via the following URL:
where
LANG=0 (for English), LANG=1 for French
TOKEN = exam_grp_token_id from Lm109_Exam_Grp_Token table.
TOKEN = exam_grp_token_id from Lm109_Exam_Grp_Token table.
If sending URL back to client, be sure to set lang and token in URL appropriately and verify that URL works before sending.