Forum Discussion

sivech3333's avatar
sivech3333
New Contributor
2 years ago

All the showing as u0000 for a Jsonfile

Hi team,
I am trying to get a Json file from Database using a query. But the retruned variable is as below. 

"\"\\u0000\\u0000\\u0000\\u0000\\u0010\\u0000\\u0000\\u0000ߧ\\u0005\\u0011\\t\\f\\u001e꾀〉\\u0000\\u0000\\u0000\\u0000\\u0005\\u0000\\u0000\\u0000㍖ㄮ3\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000¦\\u0000\\u0000\\u0000\\u0000\\u0000\\u0000\"

 

I use JS and the returned varialbe is in nvarchar(max) format in database. How to resolve this

  • rraghvani's avatar
    rraghvani
    Champion Level 3
    2 years ago

    It would be helpful if you provide more context, such what database you are using, how are you connecting to the database, any code snippets, what does the actual data look like in the database etc?

     

    The expression \uXXXX is JSON Unicode escape notation

    • sivech3333's avatar
      sivech3333
      New Contributor
      2 years ago

      This is how i connect to database and extract a value. Script and quesry works fine with all the values,But when it came to Json, It returns everything in \\u0000 format
      function GetDataSetFromDatabase(sql,fieldName)
      {
      var AConnection, RecSet;
      AConnection = ADO.CreateADOConnection();
      AConnection.ConnectionString ="Provider=MSDASQL.1;Persist Security Info=False;Data Source=BSDSN";
      AConnection.LoginPrompt = false;
      AConnection.Open();
      RecSet = AConnection.Execute_(sql);
      let value=RecSet.Fields.Item(fieldName).Value
      AConnection.Close
      return value
      }

       

      BSDSN is the database name we gave in th ODBC.
      My query looks like 
      use B2B select top 10 * from database where table1 = 1235 and Type = 'Request'

      The data in Database looks like


      {"id":{"abc":"11111"},"productGroup":"FLEXLINE","flexline":{"purposeType":"NEW","purposeInfoType":"PURCHASE","propertyType":"PRIMARY_RESIDENCE","planLimit":552500.00,"revolvingCreditLimit":552500.00,"pricingProvince":"ONTARIO","closingDate":"2023-05-09","interestRate":{"rate":4.10000,"rateType":"VARIABLE","variance":0.000},"flexTerm":{"amortization":30,"amountRequested":552500.0,"rateEffectiveDate":"2023-05-09","flexTermProduct":"FLTV060C","interestRate":{"rate":5.140,"rateType":"VARIABLE","variance":-1.040},"paymentDetail":{"paymentFrequency":"MONTHLY","firstPaymentDate":"2023-06-09","paymentAccountNumber":{"bank":"002","branchNumber":"31682","number":"81922"}},"rateExceptionInd":"NO"},"preApproval":false}}

       

  • rraghvani's avatar
    rraghvani
    Champion Level 3
    2 years ago

    What database are you using, Oracle, MySQL, Microsoft SQL Server?

     

    Does the column in your table contain JSON string?

     

    Here's an example of how a table looks like. So I'm just trying to understand the format of your columns.

     

    • sivech3333's avatar
      sivech3333
      New Contributor
      2 years ago

      we use Microsoft SQL Server. Yes, column in your table contain JSON string in nvarchar(max) format

      This is my table and i can retrive all the other columns as it is. But, When it comes to this Details column, I was getting an error. Here is my table

       

  • rraghvani's avatar
    rraghvani
    Champion Level 3
    2 years ago

    In this example, I have the following data setup in MS SQL Server

    And I'm using MSOLEDBSQL19.1 as the Provider in my connection string. The output is correctly shown

    Ensure you are using the correct Provider for your connection string.