I have been doing some testing in v18.1 and I’ve encountered the following situation. (Windows 10)

SQL LOGIN("ODBC:MyDSN";"user";"password";*)
SQL SET OPTION(SQL charset;"UTF-16")
SQL SET PARAMETER($tTest;SQL param in)
SQL LOAD RECORD(SQL all records)
ASSERT($tResult=$tTest;"Failed")  //this fails because $tResult has 5 space chars (0x20) appended to the end

The assert fails because the result has 5 space chars added to the end. Testing with other strings reveals that the number of spaces appended is always equal to the number of characters in the original. E.g. “Hello world” has 11 space chars appended.

The same code works as expected in v17R6 (which is what we are currently running live).

The DSN points to a Microsoft SQLLocalDB instance via the SQL Native Client 11.0 driver. I’ve also tested it pointing to Microsoft SQL Server 2017 via the ODBC Driver 17 for SQL Server. Same result.

Removing the SET SQL OPTION doesn’t make any difference.

So far I haven’t found a solution apart from trimming, but obviously that isn’t practical.

Anyone encountered anything like this?


I was wrong about SET SQL OPTION not making a difference. To test it I commented the line out and ran the code again, thinking that would use the default charset of UTF-8 as per the documentation. However further tests show that the default is actually UTF-16.

Setting the charset to UTF-8 causes the result to come back as expected. But I don’t know if I want to go that way since the external database is using nvarchar & nchar.

SQL Server Profiler shows that the issue is in the statement sent by 4D.

-- 1st RPC
declare @p1 int
set @p1=1
exec sp_prepare @p1 output,N\@P1 nchar(10)',N'SELECT @P1',1
select @p1

-- 2nd RPC
exec sp_execute 1,N'Hello     '

-- 3rd RPC
exec sp_unprepare 1

The parameter has been defined using the number of bytes in the string (10) instead of the number of multi-byte characters (5). The value is then being padded with spaces to suit nchar(10).

I get the same behaviour using the <<Variable>> syntax instead of SQL SET PARAMETER. Also same result running compiled and using a process variable instead of a local variable.