Troubleshooting : Error while syncing records from Salesforce to JIRA

 
1
0
-1

We receive the below error in Salesforce when trying to Sync from Salesforce to JIRA:

Bad response when GET to /services/data/v54.0/query/?q=SELECT+Global_Industry_c%2cCompany_Revenue_c+FROM+Account+WHERE+Name=%27Coeur d' Alene Tribe%27 - 400 [{"message":"
nWHERE Name='Coeur d' Alene Tribe'
n ^
nERROR at Row:1:Column:91
nline 1:91 mismatched character '' expecting '''","errorCode":"MALFORMED_QUERY"}]



    CommentAdd your comment...

    5 answers

    1.  
      1
      0
      -1

      We removed the Single quote from the Account name but again we received error for another Account name. Why Exalate is not supporting special characters? Is there any way to resolve this issue.


      Error Detail Message:


      Bad response when GET to /services/data/v54.0/query/?q=SELECT+Global_Industry__c%2cCompany_Revenue__c+FROM+Account+WHERE+Name=%27FARO Consulting & Solutions, LLC.%27 - 400 [{"message":"\nAccount WHERE Name='FARO Consulting \n ^\nERROR at Row:1:Column:85\nline 1:85 mismatched character '' expecting '''","errorCode":"MALFORMED_QUERY"}]

      1. Sherine

        Could someone please look into this issue? 

      2. Javier Pozuelo

        Hello Sherine,


        The error you are encountering is due to a "Malformed Query". This is commonly caused by incorrect handling or escaping of special characters in the Salesforce SOQL (Salesforce Object Query Language) query.


        Per the SOQL documentation, the only reserved characters are the single quote and the backslash characters. I will update you tomorrow on how to resolve this

      3. Sherine

        Hi, Do we have an update on how to resolve this?

      4. Javier Pozuelo

        Hello Sherine,


        I apologize for the delay in response. I have a couple of questions: What Jira are you using? Also, the error is coming from a trigger or the Outgoing Sync of Salesforce? If so, could you provide the Outgoing Sync script or the Trigger?

      5. Sherine

        Hi Javier,

        We use Jira Data Center and the error we get from Outgoing sync of Salesforce.

        Outgoing script:

        if(replica.AccountId != null)
        {
        def Account_Info = httpClient.get("/services/data/v54.0/query/?q=SELECT+Global_Industry__c%2cCompany_Revenue__c+FROM+Account+WHERE+Name=%27${replica.AccountId}%27")
        replica.AccountGlobal=Account_Info.records.Global_Industry__c[0]
        replica.Accountcompanyrevenue=Account_Info.records.Company_Revenue__c[0]
        //debug.error("${contact_name2.records.Global_Industry__c}")
        }
        else
        {
               replica.AccountGlobal = "None"
               replica.Accountcompanyrevenue = "None"
        }

      6. Javier Pozuelo

        Could you replace def Account_Info with the following and test it?

        def Account_Info = httpClient.get("/services/data/v54.0/query/?q=SELECT+Global_Industry__c%2cCompany_Revenue__c+FROM+Account+WHERE+Name='${replica.AccountId}'")


      7. Sherine

        Hi Javier,

        I tried the script you added above but still I get the following message.

        Bad response when GET to /services/data/v54.0/query/?q=SELECT+Global_Industry__c%2cCompany_Revenue__c+FROM+Account+WHERE+Name='Coeur d'Alene Tribe' - 400 [{"message":"\nWHERE Name='Coeur d'Alene Tribe'\n ^\nERROR at Row:1:Column:90\nline 1:90 mismatched character '' expecting '''","errorCode":"MALFORMED_QUERY"}]

      8. Javier Pozuelo

        Hello Sherine,


        Let's try something else, we will try escaping the single quote by doing the following:

        def escapedAccountId = replica.AccountId.replaceAll("'", "\\\\'")

def query = "/services/data/v54.0/query/?q=SELECT+Global_Industry__c%2cCompany_Revenue__c+FROM+Account+WHERE+Name='${escapedAccountId}'"
def Account_Info = httpClient.get(query)

        Let me know how it goes

      9. Sherine

        Thank you Javier for your help. I tried the script you added above and I get the following error


        Bad response when GET to /services/data/v54.0/query/?q=SELECT+Global_Industry__c%2cCompany_Revenue__c+FROM+Account+WHERE+Name='FARO Consulting & Solutions, LLC.' - 400 [{"message":"\nAccount WHERE Name='FARO Consulting \n ^\nERROR at Row:1:Column:85\nline 1:85 mismatched character '' expecting '''","errorCode":"MALFORMED_QUERY"}]

      10. Javier Pozuelo

        I can see that it's giving an error with a different account name, the previous error was with the following value: 'Coeur d'Alene Tribe'. Did the solution that I provided fixed the error with that value?

      11. Sherine

        yes the solution solved the other account name with single quotes

      CommentAdd your comment...
    2.  
      1
      0
      -1

      Hi,

      Still face the same issue. Tried the following: 'Coeur d\' Alene Tribe' but still get the same error message.

      Thank you

        CommentAdd your comment...
      1.  
        1
        0
        -1

        The Single quote must be escaped using backslash, so try the following: 'Coeur d\' Alene Tribe'

          CommentAdd your comment...
        1.  
          1
          0
          -1

          Do we have any update here?

            CommentAdd your comment...
          1.  
            1
            0
            -1

            Could someone please look into this issue? 

              CommentAdd your comment...