Need to see ColdFusion query params being sent and return data

Question

Newbie to ColdFusion.

I have a script that works for all vendors except one. The script passes a few params and receives a list of claims for the specified vendor with the exception of the vendor previously mentioned.

I'd like to debug one function so I can ensure I'm passing the correct params for this one vendor and also see the response being returned for the same vendor.

What's the best way to debug my script? Are either of these a good option?

<cfdump var="#VARIABLES#">
<cfdump var="#getPageContext().getBuiltInScopes()#"/>

I'm sure you've all had a case where you had to PROVE it's a database issue and not a script issue.

here's the function

<!--- function getAllRenewalRequestsChrisTest for testing --->   
<cffunction name="getAllRenewalRequestsChrisTest" access="public" returnType="Query" hint="">
   <cfargument name="Domain" type="String" required="true" hint="Domain for Database Identification.">
   <cfargument name="Org_ID" type="Numeric" required="true" hint="Org_Id - Primary Key">
   <cfargument name="UserKey" type="Numeric" required="true" hint="UserPK - Primary Key">

   <cfset var Local = StructNew()>

   <cftry>
       <cfset Local.ERXInfo = CreateObject("component","cfc.org.Org").getEprescribeStatus("#Arguments.Domain#","#Arguments.Org_ID#")>
       <cfset Local.credentials = StructNew()>
       <cfset Local.credentials.PartnerName = "#Local.ERXInfo.eRxPartnerName#">
       <cfset Local.credentials.Name = "#Local.ERXInfo.eRxName#">
       <cfset Local.credentials.Password = "#Local.ERXInfo.eRxPassword#">

       <cfset Local.accountRequest = StructNew()>
       <cfset Local.accountRequest.AccountId = "#getEMRDataDSN(Arguments.Domain)#-#Arguments.Org_ID#">
       <cfset Local.accountRequest.SiteId = "#Local.ERXInfo.eRxSiteId#">

       <cfset Local.wsargs = StructNew()>
       <cfset Local.wsargs.timeout = 5>
       <cfset Local.objWebService = CreateObject("webservice","#getErxServer(Arguments.Domain)#v7/WebServices/Update1.asmx?WSDL", Local.wsargs)>

       <cfset Local.objSearchResponse = Local.objWebService.GetAllRenewalRequestsV2 (Local.credentials, Local.accountRequest, "","")>

       <cfset Local.qTemp = QueryNew("DoctorFullName,DrugInfo,ExternalPrescriptionId,ExternalDoctorId,NumberOfRefills,PatientDOB,PatientName,FirstName,LastName,MiddleName,PatientGender,ReceivedTimestamp,RenewalRequestGuid,Quantity,Sig,RenewalRequestMed,EcastPharmacyId,EcastPharmacyName,EcastPharmacyInfo,EcastPharmacyInfoToolTip")>

        <cfset Local.renewalRequests = Local.objSearchResponse.getRenewalSummaryArray().getRenewalSummaryV2()>

        <cfset Local.objPharmacy = CreateObject("component","cfc.pharmacy")> 

        <cfloop from="1" to="#ArrayLen(Local.renewalRequests)#" index="Local.indexB">
            <cfset QueryAddRow(Local.qTemp)>
            <cfset QuerySetCell(Local.qTemp, "DoctorFullName", "#Local.renewalRequests[Local.indexB].getDoctorFullName()#")>
            <cfset QuerySetCell(Local.qTemp, "DrugInfo", "#Local.renewalRequests[Local.indexB].getDrugInfo()#")>
            <cfset QuerySetCell(Local.qTemp, "ExternalPrescriptionId", "#Local.renewalRequests[Local.indexB].getExternalPrescriptionId()#")>
            <cfif Trim(Local.renewalRequests[Local.indexB].getExternalDoctorId()) EQ "">
                <cfset QuerySetCell(Local.qTemp, "ExternalDoctorId", "0")>
            <cfelse>
                <cfset QuerySetCell(Local.qTemp, "ExternalDoctorId", "#Trim(Local.renewalRequests[Local.indexB].getExternalDoctorId())#")>
            </cfif>

            <cfset QuerySetCell(Local.qTemp, "NumberOfRefills", "#Local.renewalRequests[Local.indexB].getNumberOfRefills()#")>

            <cfif Len(trim(Local.renewalRequests[Local.indexB].getPatientDOB())) EQ 8>  
               <cfset QuerySetCell(Local.qTemp, "PatientDOB", "#DateFormat(CreateDate(Left(trim(Local.renewalRequests[Local.indexB].getPatientDOB()), 4), Mid(trim(Local.renewalRequests[Local.indexB].getPatientDOB()), 5, 2), Mid(trim(Local.renewalRequests[Local.indexB].getPatientDOB()), 7, 2)),'mm/dd/yyyy')#")>
            <cfelse>
               <cfset QuerySetCell(Local.qTemp, "PatientDOB", "#Trim(Local.renewalRequests[Local.indexB].getPatientDOB())#")>
            </cfif>

            <cfset QuerySetCell(Local.qTemp, "PatientName", "#Local.renewalRequests[Local.indexB].getPatientLastName()#, #Local.renewalRequests[Local.indexB].getPatientFirstName()# #Local.renewalRequests[Local.indexB].getPatientMiddleName()#")>
            <cfset QuerySetCell(Local.qTemp, "FirstName", "#Local.renewalRequests[Local.indexB].getPatientFirstName()#")>
            <cfset QuerySetCell(Local.qTemp, "LastName", "#Local.renewalRequests[Local.indexB].getPatientLastName()#")>
            <cfset QuerySetCell(Local.qTemp, "MiddleName", "#Local.renewalRequests[Local.indexB].getPatientMiddleName()#")>
            <cfset QuerySetCell(Local.qTemp, "PatientGender", "#UCase(Local.renewalRequests[Local.indexB].getPatientGender())#")>

            <cfset Local.tempPharmacyInfo = Local.objPharmacy.getPharmacyInfoByNCPDID(Arguments.Domain,Arguments.Org_ID,Local.renewalRequests[Local.indexB].getNcpdpID(),0)>
            <cfif Local.tempPharmacyInfo.recordcount GT 0><!--- non hidden pharmacy already exists--->
               <cfset Local.currentPharmacyInfo = Local.objPharmacy.getPharmacyInfoById(Arguments.Domain,Local.tempPharmacyInfo.Pharmacy_Id)>
               <cfif Trim(Local.currentPharmacyInfo.PharmacyDisplayName) NEQ "">
                  <cfset QuerySetCell(Local.qTemp, "EcastPharmacyName", "#Trim(Local.currentPharmacyInfo.PharmacyDisplayName)#")> 
               <cfelse>
                  <cfset QuerySetCell(Local.qTemp, "EcastPharmacyName", "#Trim(Local.currentPharmacyInfo.Pharmacy_Name)#")>
               </cfif>
               <cfset QuerySetCell(Local.qTemp, "EcastPharmacyId", "#Local.currentPharmacyInfo.Pharmacy_Id#")>
            <cfelse>
               <cfset Local.tempPharmacyInfo = Local.objPharmacy.getPharmacyInfoByNCPDID(Arguments.Domain,Arguments.Org_ID,Local.renewalRequests[Local.indexB].getNcpdpID(),1)>
               <cfif Local.tempPharmacyInfo.recordcount GT 0><!--- hidden pharmacy already exists--->
                  <cfset Local.currentPharmacyInfo = Local.objPharmacy.getPharmacyInfoById(Arguments.Domain,Local.tempPharmacyInfo.Pharmacy_Id)>
                  <cfset QuerySetCell(Local.qTemp, "EcastPharmacyId", "#Local.currentPharmacyInfo.Pharmacy_Id#")>
                  <cfif Trim(Local.currentPharmacyInfo.PharmacyDisplayName) NEQ "">
                     <cfset QuerySetCell(Local.qTemp, "EcastPharmacyName", "#Trim(Local.currentPharmacyInfo.PharmacyDisplayName)#")> 
                  <cfelse>
                     <cfset QuerySetCell(Local.qTemp, "EcastPharmacyName", "#Trim(Local.currentPharmacyInfo.Pharmacy_Name)#")>
                  </cfif>
               <cfelse> <!--- add pharmacy to org --->
                  <cfset Local.tempPharmacyInfo = getPharmacyByNCPDID(Arguments.Domain,Arguments.Org_Id,Local.renewalRequests[Local.indexB].getNcpdpID())>
                  <cfset Local.newPharmacyId = Local.objPharmacy.setPharmacy(Arguments.domain,Arguments.Org_ID,Arguments.UserKey,Local.tempPharmacyInfo.NAME,Local.tempPharmacyInfo.NAME,Local.tempPharmacyInfo.PHONE,Local.tempPharmacyInfo.FAX,Local.tempPharmacyInfo.ADDRESS,Local.tempPharmacyInfo.CITY,Local.tempPharmacyInfo.ZIP,Local.tempPharmacyInfo.STATE,-1,Local.tempPharmacyInfo.NCPDID,Local.tempPharmacyInfo.PHARMACYTYPE,1)>
                  <cfset Local.currentPharmacyInfo = Local.objPharmacy.getPharmacyInfoById(Arguments.Domain,Local.newPharmacyId)>
                  <cfset QuerySetCell(Local.qTemp, "EcastPharmacyId", "#Local.newPharmacyId#")>
                  <cfif Trim(Local.currentPharmacyInfo.PharmacyDisplayName) NEQ "">
                     <cfset QuerySetCell(Local.qTemp, "EcastPharmacyName", "#Trim(Local.currentPharmacyInfo.PharmacyDisplayName)#")> 
                  <cfelse>
                     <cfset QuerySetCell(Local.qTemp, "EcastPharmacyName", "#Trim(Local.currentPharmacyInfo.Pharmacy_Name)#")>
                  </cfif>
               </cfif>
            </cfif>
            <cfif Trim(Local.currentPharmacyInfo.PharmacyDisplayName) NEQ "">
               <cfset QuerySetCell(Local.qTemp, "EcastPharmacyInfoToolTip", "#Trim("Name: #Local.currentPharmacyInfo.PharmacyDisplayName#$SPLIT$Address: #Local.currentPharmacyInfo.Pharmacy_Address# #Local.currentPharmacyInfo.Pharmacy_City# #Local.currentPharmacyInfo.Pharmacy_State# #Local.currentPharmacyInfo.Pharmacy_Zip#$SPLIT$Phone: #Local.currentPharmacyInfo.Pharmacy_Phone#$SPLIT$Fax: #Local.currentPharmacyInfo.Pharmacy_Fax#")#")>
               <cfset QuerySetCell(Local.qTemp, "EcastPharmacyInfo", "#Trim("#Local.currentPharmacyInfo.PharmacyDisplayName#$SPLIT$#Local.currentPharmacyInfo.Pharmacy_Address#$SPLIT$#Local.currentPharmacyInfo.Pharmacy_City#, #Local.currentPharmacyInfo.Pharmacy_State# #Local.currentPharmacyInfo.Pharmacy_Zip#$SPLIT$#Local.currentPharmacyInfo.Pharmacy_Phone# (phone)$SPLIT$#Local.currentPharmacyInfo.Pharmacy_Fax# (fax)")#")>
            <cfelse>
               <cfset QuerySetCell(Local.qTemp, "EcastPharmacyInfoToolTip", "#Trim("Name: #Local.currentPharmacyInfo.Pharmacy_Name#$SPLIT$Address: #Local.currentPharmacyInfo.Pharmacy_Address# #Local.currentPharmacyInfo.Pharmacy_City# #Local.currentPharmacyInfo.Pharmacy_State# #Local.currentPharmacyInfo.Pharmacy_Zip#$SPLIT$Phone: #Local.currentPharmacyInfo.Pharmacy_Phone#$SPLIT$Fax: #Local.currentPharmacyInfo.Pharmacy_Fax#")#")>
               <cfset QuerySetCell(Local.qTemp, "EcastPharmacyInfo", "#Trim("#Local.currentPharmacyInfo.Pharmacy_Name#$SPLIT$#Local.currentPharmacyInfo.Pharmacy_Address#$SPLIT$#Local.currentPharmacyInfo.Pharmacy_City#, #Local.currentPharmacyInfo.Pharmacy_State# #Local.currentPharmacyInfo.Pharmacy_Zip#$SPLIT$#Local.currentPharmacyInfo.Pharmacy_Phone# (phone)$SPLIT$#Local.currentPharmacyInfo.Pharmacy_Fax# (fax)")#")>
            </cfif>
            <cfset QuerySetCell(Local.qTemp, "ReceivedTimestamp", "#Local.renewalRequests[Local.indexB].getReceivedTimestamp()#")>
            <cfset QuerySetCell(Local.qTemp, "RenewalRequestGuid", "#Local.renewalRequests[Local.indexB].getRenewalRequestGuid()#")>
            <cfset QuerySetCell(Local.qTemp, "Quantity", "#Local.renewalRequests[Local.indexB].getQuantity()#")>
            <cfset QuerySetCell(Local.qTemp, "Sig", "#Local.renewalRequests[Local.indexB].getSig()#")>

            <cfif Trim(Local.renewalRequests[Local.indexB].getQuantity()) NEQ "" AND Trim(Local.renewalRequests[Local.indexB].getSig()) NEQ "">
                <cfset QuerySetCell(Local.qTemp, "RenewalRequestMed", "#Local.renewalRequests[Local.indexB].getDrugInfo()# (#Trim(Local.renewalRequests[Local.indexB].getQuantity())#, #Trim(Local.renewalRequests[Local.indexB].getSig())#)")>
            <cfelse>
                <cfif Trim(Local.renewalRequests[Local.indexB].getQuantity()) NEQ "">
                    <cfset QuerySetCell(Local.qTemp, "RenewalRequestMed", "#Local.renewalRequests[Local.indexB].getDrugInfo()# (#Trim(Local.renewalRequests[Local.indexB].getQuantity())#)")>
                <cfelse>
                    <cfset QuerySetCell(Local.qTemp, "RenewalRequestMed", "#Local.renewalRequests[Local.indexB].getDrugInfo()# (#Trim(Local.renewalRequests[Local.indexB].getSig())#)")>
                </cfif>
            </cfif>
        </cfloop>

        <cfcatch type="Any">
            <cfset Local.qTemp = QueryNew("DoctorFullName,DrugInfo,ExternalPrescriptionId,ExternalDoctorId,NumberOfRefills,PatientDOB,PatientName,FirstName,LastName,MiddleName,PatientGender,ReceivedTimestamp,RenewalRequestGuid,Quantity,Sig,RenewalRequestMed,EcastPharmacyId,EcastPharmacyName,EcastPharmacyInfo,EcastPharmacyInfoToolTip")>
        </cfcatch>
    </cftry>
  <cfreturn Local.qTemp>
    <cfdump var="getAllRenewalRequestsChrisTest">
 </cffunction>

`` That will give you the query executed and from there you can determine if it's a database issue. The query should also show up if you dump the variables scope though. or you can also turn on debugging in your CF admin to see the query executed. — Matt Busche, Oct 21 '13 at 16:45

score 1 · Accepted Answer · answered Oct 21 '13 at 16:55

Are your parameters being passed on the form or url scope? Or do you mean that you have several parameters that are being passed into the query?

It sounds like what you want to do is dump the parameters before they get used (or just after they get passed in) in order to see what they are for your problem vendor.

For instance if your parameters are being passed in the form scope, you could dump the form scope first thing in the script which is supposed to perform the query.

<!--- params passed in via form scope --->
<cfdump var="#form#" output="browser" />

<!--- do query --->
<cfquery name="myVendorData" datasource="...">
    ... SQL which uses the suspect parameters goes here...
</cfquery>

Does that help?

A code sample would go a long way to helping me give you a better answer.

Matt's comment suggests a simpler and more effective approach. — Dan Bracuk, Oct 21 '13 at 21:53

Henry · Answer 2 · 2013-10-21T22:29:36.297

1

Turn on Debug Output -> Database Activity

edited Oct 21 '13 at 22:29

answered Oct 21 '13 at 21:01

Henry

32,689
19
120
221

Need to see ColdFusion query params being sent and return data

2 Answers2