Why do I see Chinese, Kanji, or Other Unexpected Character Glyphs in Reports or Queries?

Why do I see Chinese, Kanji, or Other Unexpected Character Glyphs in Reports or Queries?

There are two main reasons why Chinese, Kanji, or other unexpected character Glyphs show up in reports and other queries.

1. VARCHAR verses NVARCHAR datatypes in database columns. 
      When converting from older version of Visual (prior to Version 8) all text data was defined as VARCHAR (non-Unicode, 128-character ASCII style representation). In newer versions of Visual (8.x and latter) all text data is stored as NVARCHAR (UNICODE) which can represent multi-byte or extended character sets. If the character encodings do not match between the storage and presentation the characters can look unexpected. NVARCHAR (Unicode) can be a 2-byte data storage, when 2-byte Unicode binary data is referenced as singleVARCHAR the result in a report is either only the first character is displayed or spaces are inserted between each pair of characters. 

2. Selecting Image data instead of Text from the BINARY tables.
      The Binary Tables hold both Spec (Text, Type= 'D') and Picture (Image, Type='B') data for some windows, but the one where it comes up most is for Parts. Open Part Maintenace and go to Edit->Picture/Object... (or hit F12) and the picture window pops up. You can drop images into it, and they will land in the PART_BINARY table with a TYPE='B' designation. The SPEC data is in the PART_BINARY table with the TYPE = 'D' designation. If you have a report that is loading SPEC data and there are no pictures the report will work perfectly for years. Then someone adds a picture, and you start seeing large blocks of unusual characters. This is especially true if running a Unicode version of Visual because the multi-byte character sets include many more unusual character glyphs, and the binary data is interpreted as random characters via the encoding in use.


Example queries for VARCHAR->NVARCHAR conversions in Notations:
Note: If using these in the BINARY tables the TYPE must be set to 'D' in the WHERE clause! The TYPE field in the NOTATION table specifies the Visual Document Type associated with the OWNER_ID.

--List all VARCHAR NOTATIONS

SELECT TYPE, OWNER_ID, CAST(CAST(NOTE AS VARBINARY(MAX)) AS VARCHAR(MAX)) NOTE FROM dbo.NOTATION WHERE SUBSTRING(NOTE,2,1) <> 0

 

-- List all Notaitions with proper character conversions

SELECT TYPE, OWNER_ID, CASE WHEN SUBSTRING(NOTE,2,1) = 0 THEN

       CAST(CAST(NOTE AS VARBINARY(MAX)) AS NVARCHAR(MAX))

ELSE

       CAST(CAST(NOTE AS VARBINARY(MAX)) AS VARCHAR(MAX))

END NOTE

FROM dbo.NOTATION

 


    • Related Articles

    • Unexpected Error #0 logging into CRM on a newly installed client

      Description: Unexpected Error #0 logging into CRM on a newly installed client.  An unexpected error occured while Infor Visual CRM was initializing. 0: Resolution: Support recommends that CRM is installed while logged in as the Network User w/ Local ...

    • Moving VISUAL Quality reports to new location

      Description: Moved VISUAL Quality reports to a new location. Update the VISUAL Quality SQLBase or Oracle database after moving the VISUAL Quality reports to a new location. Symptom: Invalid file name Resolution: Here's a procedure for reinstalling ...

    • SmartViews - Using Report Parameters

      In addition to the Filter Criteria window found in Reports and Views, Reports also have parameters that can be set up in the Report Layout Editor. One advantage to using parameters over Filter Criteria is that the parameter values can be displayed in ...

    • Launchpad Won't Export Crystal Reports

      If Launchpad suddenly stops exporting reports (even though reports are still running fine), the issue may be that the TEMP directory on the server is full. Newer versions of Launchpad should automatically clean up the directory, though this can ...

    • Launchpad - Won't Export Crystal Reports

      If Launchpad suddenly stops exporting reports (even though reports are still running fine), the issue may be that the TEMP directory on the server is full. Newer versions of Launchpad should automatically clean up the directory, though this can ...