Inserting Japanese characters into a NAV database from outside NAV

In a previous post I described some steps on how to enable you NAV environment to support DBCS. Read more about it here: http://kennyvaes.wordpress.com/2009/06/08/nav-vs-dbcs/

Now, the next challange we faced was inserting Japanese characters from an external system into the NAV database.

In other countries we mangage this by running a webservice which insert the information into the SQL database. This webservice is built in C#.

The problem now on the Japanese system was that the strings passed on to the webservice are all Unicode, because .NET handles all strings as Unicode.

Just inserting these Unicode bytes in NAV without conversion will result in a string like “日本 アンユージュアル エスケープ” to be inserted as “???? ??? ????? ???”.

What Navision does is convert this string to a series of Non-Unicode bytes like “ƒAƒ“ƒ†[ƒWƒ…ƒAƒ‹@ƒGƒXƒP[ƒv"

After some time figuring out how to insert these Unicode strings into NAV we came up with the following.

Step 1: Determine the codepages

First of all you need to determine the codepages the server and NAV use. In my case the server was running codepage 1252 Latin encoding. And NAV, because of the Non-Unicode settings, was running under codepage Japanese Shift-JIS (932).

Step 2: Convert your strings

So now you know what codepages you use, the solution is actually pretty straight-forward. What does NAV do? Well, when you input this Japanese string 日本 アンユージュアル エスケープ in NAV. Navision is running codepage 932 and gets the Bytes for this string in 932 encoding. The OS is running codepage 1252 so the SQL server also uses this codepage. What you need to do is convert these 932 bytes into a 1252 encoded string.

The following C# code will do this for you. Remeber to replace the 932 and 1252 encodings with your encoding.

string JapString = "日本 アンユージュアル エスケープ";
string NavString = "";
Encoding nav = Encoding.GetEncoding(1252);
Encoding unicode = Encoding.GetEncoding(932);
byte[] unicodeBytes = unicode.GetBytes(JapString);
NavString = nav.GetString(unicodeBytes);

now the NavString variable contains “ƒAƒ“ƒ†[ƒWƒ…ƒAƒ‹@ƒGƒXƒP[ƒv”.

Great!!

 

Convert a Navision Filter to a SQL Where clause

I wrote this SQL script a while ago.
It will convert most of the Navision filters into a SQL WHERE clause.

@ will be converted to lower(..)
* will result in a LIKE %
.. will result in a BETWEEN clause
& = AND
= OR
<> = NOT

Also brackets are supported.

To use this script you should pass the fieldname and the filter to this function.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- ===============================================================
-- Author:  Kenny Vaes
-- Create date: 19/03/2007
-- Description: Converts a Navision Filter to a SQL Where clause
-- ===============================================================
ALTER FUNCTION [dbo].[CONVERT_NAV_FILTER]
(
-- Add the parameters for the function here
@parFieldName VARCHAR(MAX),
@parFilter  VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @CurrentStringPOSPoints INT
DECLARE @CurrentStringPOSPipe INT
DECLARE @CurrentStringPOSAmp INT
DECLARE @CurrentStringPOSAt  INT
DECLARE @CurrentStringPOSNot INT
DECLARE @CurrentStringPOSHaakOpen iNT
DECLARE @CurrentStringPOSHaaksluit int
DECLARE @CurrentStringPOSStar int

DECLARE @From VARCHAR(MAX)
DECLARE @To  VARCHAR(MAX)
Declare @IndexToRead  INT
Declare @ObjectToRead  INT
Declare @IndexRead   INT
Declare @ObjectRead   INT
DECLARE @NextIndexToRead INT
Declare @NextObjectToRead INT
DECLARE @FirstRun    INT

DECLARE @ReturnValue   VARCHAR(MAX)

SET @ReturnValue = ''
SET @IndexToRead = 0
SET @CurrentStringPOSPoints  = 0
SET @CurrentStringPOSPipe  = 0
SET @CurrentStringPOSAmp  = 0
SET @CurrentStringPOSAt   = 0
SET @CurrentStringPOSNot  = 0
SET @CurrentStringPOSHaakOpen = 0
SET @CurrentStringPOSHaaksluit = 0
SET @ObjectToRead = 0
SET @IndexRead = 0
SET @FirstRun = 1
WHILE 1 = 1
BEGIN

SET @NextIndexToRead = LEN(@parFilter)
SET @IndexToRead = LEN(@parFilter)
SET @ObjectToRead = 0
SET @NextObjectToRead = 0

--Find the index to read
IF @IndexToRead >= Charindex('..', @parFilter, @IndexRead + 1) and (Charindex('..', @parFilter, @IndexRead + 1) > 0)
 BEGIN
  SET @IndexToRead =  Charindex('..', @parFilter, @IndexRead+1)
  SET @ObjectToRead = 1
 END
IF @IndexToRead >= Charindex('', @parFilter, @IndexRead + 1) and (Charindex('', @parFilter, @IndexRead + 1) > 0)
 BEGIN
  SET @IndexToRead = Charindex('', @parFilter, @IndexRead+1)
  SET @ObjectToRead = 2
 END

IF @IndexToRead >= Charindex('&', @parFilter, @IndexRead + 1) and (Charindex('&', @parFilter, @IndexRead + 1) > 0)
 BEGIN
  SET @IndexToRead = Charindex('&', @parFilter, @IndexRead+1)
  SET @ObjectToRead = 3
 END

IF @IndexToRead >= Charindex('<>', @parFilter, @IndexRead + 1) and (Charindex('<>', @parFilter, @IndexRead + 1) > 0)
 BEGIN
  SET @IndexToRead = Charindex('<>', @parFilter, @IndexRead+1)
  SET @ObjectToRead = 5
 END

IF @IndexToRead >= Charindex('(', @parFilter, @IndexRead + 1) and (Charindex('(', @parFilter, @IndexRead + 1) > 0)
 BEGIN
  SET @IndexToRead = Charindex('(', @parFilter, @IndexRead +1)
  SET @ObjectToRead = 6
 END

IF @IndexToRead >= Charindex(')', @parFilter, @IndexRead + 1) and (Charindex(')', @parFilter, @IndexRead + 1) > 0)
 BEGIN
  SET @IndexToRead = Charindex(')', @parFilter, @IndexRead +1)
  SET @ObjectToRead = 7
 END

-- Find the next index to read
IF @NextIndexToRead >= Charindex('..', @parFilter, @IndexToRead + 1)  and (Charindex('..', @parFilter, @IndexToRead + 1) > 0)
 BEGIN
  SET @NextIndexToRead =  Charindex('..', @parFilter, @IndexToRead+1)
  SET @NextObjectToRead = 1
 END
IF @NextIndexToRead >= Charindex('', @parFilter, @IndexToRead + 1)  and (Charindex('', @parFilter, @IndexToRead + 1) > 0)
 BEGIN
  SET @NextIndexToRead = Charindex('', @parFilter, @IndexToRead+1)
  SET @NextObjectToRead = 2
 END

IF @NextIndexToRead >= Charindex('&', @parFilter, @IndexToRead + 1)  and (Charindex('&', @parFilter, @IndexToRead + 1) > 0)
 BEGIN
  SET @NextIndexToRead = Charindex('&', @parFilter, @IndexToRead+1)
  SET @NextObjectToRead = 3
 END

IF @NextIndexToRead >= Charindex('<>', @parFilter, @IndexToRead + 1) and (Charindex('<>', @parFilter, @IndexToRead + 1) > 0)
 BEGIN
  SET @NextIndexToRead = Charindex('<>', @parFilter, @IndexToRead+1)
  SET @NextObjectToRead = 5
 END

IF @NextIndexToRead >= Charindex('(', @parFilter, @IndexToRead + 1) and (Charindex('(', @parFilter, @IndexToRead + 1) > 0)
 BEGIN
  SET @NextIndexToRead = Charindex('(', @parFilter, @IndexToRead+1)
  SET @NextObjectToRead = 6
 END

IF @NextIndexToRead >= Charindex(')', @parFilter, @IndexToRead + 1) and (Charindex(')', @parFilter, @IndexToRead + 1) > 0)
 BEGIN
  SET @NextIndexToRead = Charindex(')', @parFilter, @IndexToRead+1)
  SET @NextObjectToRead = 7
 END

-- *********************************************
--  Checks Complete start converting
-- *********************************************


-- If this is the first time the conversion is done and there is a  or & sign,
-- Set a filter on the first part
IF @FirstRun = 1 and @IndexToRead > 1
 BEGIN
  IF @ObjectToRead = 2 -- 
   BEGIN
    SET @From = Substring(@parFilter, 1, @IndexToRead - 1)

    if charindex('@', @From, 0) > 0
     if charindex('*', @From, 0) > 0
      SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName +  ') LIKE ''' + lower(replace(replace(@From, '*', '%'), '@', '')) + ''') OR '
     else
      SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName +  ') = ''' + lower(replace(@From, '@', '')) + ''') OR '
    else
     if charindex('*', @From, 0) > 0
      SET @ReturnValue = @ReturnValue + '(' + @parFieldName +  ' LIKE ''' + replace(@From, '*', '%') + ''') OR '
     else
      SET @ReturnValue = @ReturnValue + '(' + @parFieldName +  ' = ''' + @From + ''') OR '

   END
  IF @ObjectToRead = 3 -- &
   BEGIN
    SET @From = Substring(@parFilter, 1, @IndexToRead - 1)

    if charindex('@', @From, 0) > 0
     if charindex('*', @From, 0) > 0
      SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName +  ') LIKE ''' + lower(replace(replace(@From, '*', '%'), '@', '')) + ''') AND '
     else
      SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName +  ') = ''' + lower(replace(@From, '@', '')) + ''') AND '
    else
     if charindex('*', @From, 0) > 0
      SET @ReturnValue = @ReturnValue + '(' + @parFieldName +  ' LIKE ''' + replace(@From, '*', '%') + ''') AND '
     else
      SET @ReturnValue = @ReturnValue + '(' + @parFieldName +  ' = ''' + @From + ''') AND '

   END

 END
SET @FirstRun = 0

IF @ObjectToRead = 1  -- 1 = ..
 BEGIN
  SET @From = Substring(@parFilter, @IndexRead + 1, @IndexToRead - (@IndexRead + 1))

  IF @nextobjecttoread = 0
   SET @To  = substring(@parFilter, @IndexToRead + 2 ,  @NextIndexToRead -(@IndexToRead + 1))
  else
   SET @To  = substring(@parFilter, @IndexToRead + 2 ,  @NextIndexToRead -(@IndexToRead + 2))

  if charindex('@', @From, 0) > 0 or charindex('@', @To, 0) > 0
   SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') BETWEEN ''' + lower(replace(@From, '@', '')) + ''' AND ''' + lower(replace(@To,'@','')) + ''')'
  ELSE
   SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' BETWEEN ''' + @From + ''' AND ''' + @To + ''')'

  IF @NextObjectToRead = 2 --
   SET @ReturnValue =  @ReturnValue + ' OR '

  IF @NextObjectToRead = 3 -- &
   SET @ReturnValue =  @ReturnValue + ' AND '
 END

IF @ObjectToRead = 2 -- 2 = 
 BEGIN

  IF @NextObjectToRead <> 6 and @NextObjectToRead <> 5 and @NextObjectToRead <> 1
   BEGIN
    if @NextObjectToRead = 0
     SET @From = substring(@parFilter, @IndexToRead + 1,  @NextIndexToRead - @IndexToRead)
    else
     SET @From = substring(@parFilter, @IndexToRead + 1,  @NextIndexToRead - (@IndexToRead + 1))

    if charindex('@', @From, 0) > 0
     if charindex('*', @From, 0) > 0
      SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName +  ') LIKE ''' + lower(replace(replace(@From, '*', '%'), '@', '')) + ''')'
     else
      SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName +  ') = ''' + lower(replace(@From, '@', '')) + ''')'
    else
     if charindex('*', @From, 0) > 0
      SET @ReturnValue = @ReturnValue + '(' + @parFieldName +  ' LIKE ''' + replace(@From, '*', '%') + ''')'
     else
      SET @ReturnValue = @ReturnValue + '(' + @parFieldName +  ' = ''' + @From + ''')'
   END

  IF @NextObjectToRead = 2 --
   SET @ReturnValue =  @ReturnValue + ' OR '

  IF @NextObjectToRead = 3 -- &
   SET @ReturnValue =  @ReturnValue + ' AND '
 END

IF @ObjectToRead = 3 -- 3 = &
 BEGIN

  IF @NextObjectToRead <> 6 and @NextObjectToRead <> 5 and @NextObjectToRead <> 1
   BEGIN
    if @NextObjectToRead = 0
     SET @From = substring(@parFilter, @IndexToRead + 1,  @NextIndexToRead - @IndexToRead)
    else
     SET @From = substring(@parFilter, @IndexToRead + 1,  @NextIndexToRead - (@IndexToRead + 1))

    if charindex('@', @From, 0) > 0
     if charindex('*', @From, 0) > 0
      SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName +  ') LIKE ''' + lower(replace(replace(@From, '*', '%') , '@', '')) + ''')'
     else
      SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName +  ') = ''' + lower(replace(@From, '@', '')) + ''')'
    else
     if charindex('*', @From, 0) > 0
      SET @ReturnValue = @ReturnValue + '(' + @parFieldName +  ' LIKE ''' + replace(@From, '*', '%') + ''')'
     else
      SET @ReturnValue = @ReturnValue + '(' + @parFieldName +  ' = ''' + @From + ''')'
   END

  IF @NextObjectToRead = 2 --
   SET @ReturnValue =  @ReturnValue + ' OR '

  IF @NextObjectToRead = 3 -- &
   SET @ReturnValue =  @ReturnValue + ' AND '
 END

IF @ObjectToRead = 4 -- 4 = @
 BEGIN
  SET @ReturnValue = @ReturnValue
 END

IF @ObjectToRead = 5 -- 5 = <>
 BEGIN
  SET @ReturnValue = @ReturnValue + ' NOT '

  SET @From = substring(@parFilter, @IndexToRead + 2,  @NextIndexToRead - (@IndexToRead + 2))

  if charindex('@', @From, 0) > 0
   if charindex('*', @From, 0) > 0
    SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName +  ') LIKE ''' + lower(replace(replace(@From, '*', '%'), '@', '')) + ''')'
   else
    SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName +  ') = ''' + lower(replace(@From, '@', '')) + ''')'
  else
   if charindex('*', @From, 0) > 0
    SET @ReturnValue = @ReturnValue + '(' + @parFieldName +  ' LIKE ''' + replace(@From, '*', '%') + ''')'
   else
    SET @ReturnValue = @ReturnValue + '(' + @parFieldName +  ' = ''' + @From + ''')'

  IF @NextObjectToRead = 2 --
   SET @ReturnValue =  @ReturnValue + ' OR '

  IF @NextObjectToRead = 3 -- &
   SET @ReturnValue =  @ReturnValue + ' AND '
 END

IF @ObjectToRead = 6 -- 6 = (
 BEGIN
  SET @ReturnValue = @ReturnValue + '('

  SET  @From = Substring(@parFilter, @IndexToRead + 1, @NextIndexToRead - (@IndexToRead +1))

  IF @NextObjectToRead = 2 --
   BEGIN
    if charindex('@', @From, 0) > 0
     if charindex('*', @From, 0) > 0
      SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName +  ') LIKE ''' + lower(replace(replace(@From, '*', '%'), '@', '')) + ''') OR '
     else
      SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName +  ') = ''' + lower(replace(@From, '@', '')) + ''') OR '
    else
     if charindex('*', @From, 0) > 0
      SET @ReturnValue = @ReturnValue + '(' + @parFieldName +  ' LIKE ''' + replace(@From, '*', '%') + ''') OR '
     else
      SET @ReturnValue = @ReturnValue + '(' + @parFieldName +  ' = ''' + @From + ''') OR '

   END
  IF @NextObjectToRead = 3 -- &
   BEGIN
    if charindex('@', @From, 0) > 0
     if charindex('*', @From, 0) > 0
      SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName +  ') LIKE ''' + lower(replace(replace(@From, '*', '%'), '@', '')) + ''') AND '
     else
      SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName +  ') = ''' + lower(replace(@From, '@', '')) + ''') AND '
    else
     if charindex('*', @From, 0) > 0
      SET @ReturnValue = @ReturnValue + '(' + @parFieldName +  ' LIKE ''' + replace(@From, '*', '%') + ''') AND '
     else
      SET @ReturnValue = @ReturnValue + '(' + @parFieldName +  ' = ''' + @From + ''') AND '
   ENd
 END

IF @ObjectToRead = 7 -- 7 = )
 BEGIN
  SET @ReturnValue = @ReturnValue + ')'

  IF @NextObjectToRead = 2 --
   SET @ReturnValue =  @ReturnValue + ' OR '

  IF @NextObjectToRead = 3 -- &
   SET @ReturnValue =  @ReturnValue + ' AND '
 END

IF @ObjectToRead = 8 -- 8 = *
 BEGIN
  SET @ReturnValue = @ReturnValue
 END

-- Nothing found?  => BREAK loop
IF @IndexToRead = LEN(@parFilter)
 BREAK

SET @ObjectRead = @ObjectToRead
SET @IndexRead = @IndexToRead
SET @IndexToRead = @NextIndexToRead

END -- LOOP  

IF @ReturnValue = ''
 BEGIN
  if charindex('@', @parFilter, 0) > 0
   if charindex('*', @parFilter, 0) > 0
    SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName +  ') LIKE ''' + lower(replace(replace(@parFilter, '*', '%'), '@', '')) + ''')'
   else
    SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName +  ') = ''' + lower(replace(@parFilter, '@', '')) + ''')'
  else
   if charindex('*', @parFilter, 0) > 0
    SET @ReturnValue = @ReturnValue + '(' + @parFieldName +  ' LIKE ''' + replace(@parFilter, '*', '%') + ''')'
   else
    SET @ReturnValue = @ReturnValue + '(' + @parFieldName +  ' = ''' + @parFilter + ''')'
 END

-- Return the result of the function
RETURN '(' + @ReturnValue + ')'
END