Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (1)

Resolved JsonImport trim trailing white spaces

  1. Issue
0
Votes
Undo
  1. jayesh Gupta
    jayesh Gupta
  2. PowerBuilder
  3. Wednesday, 30 June 2021 05:16 AM UTC

SQL Server setting is "ansi_padding"=ON and using JSONIMPORT to import data into datawindow but wherever data type of column in SQL server is CHAR, trailing spaces are coming.

If we don't use JSONImport functionality to import data into Datawindow so trailing spaces not come due to INI setting as provided below

SQLCA.DBParm = "Provider='SQLNCLI11',Database='"+ls_database_name +"',TrimSpaces=1,NCharBind=0, PBTRIMCHARCOLUMNS='YES'".

 

I tried everything feasible thing, please give me the solution as I don't want to change the data type from CHAR to VARCHAR for many table's columns.

 

you can mail me also at sunil.agrawal@diaspark.com.

 

Thanks in advance.

  1. JSON
Re: Calling an event in an (instance) window from...
Generate PDF/A from RTE?
Responses (1)
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 30 June 2021 13:43 PM UTC
  2. PowerBuilder
  3. # 1

Hi Jayesh;

  The DBParm setting only affects the DBMS interaction and not the JSONImport() command. I would suggest the following:

1) Open a support ticket for the JSONImport command as an enhancement request to add an option to remove trailing spaces.

2) As a workaround ...

  1. Perform the JSONImport on DWO-A and use that to update a temporary Table in your DBMS.
  2. Create a another DWO (ie DWO-B) and perform a retrieve on the temporary table with the DBParm set to TrimSpaces=1
  3. Copy the Rows data from DWO-B to your working DWO and you should now have all the spaces removed.

3) Alternative to #2 ... Perform the JSONImport() command and then perform a loop traversing through the DWO's rows/columns trimming the training spaces on each CHAR column type.

4) Alternative to #2 ... use the built in JSON object classes to read the JSON file and remove the spaces from the affected input data. Then perform the JSONImport() command.

 HTH

Regards ... Chris

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.