SQL and ODBC : Could not convert SQL_WCHAR to varchar to UTF-8

michel.weber · Post by **michel.weber** » Sat Dec 05, 2009 10:20 am

We have a thesaurus stored in an SQLServer 2008 database.

I wanted to extract it and convert it to user aqiv filte for use with TEXIS/Appliance.

For some records i get the following error :

Line 28 in the script is the sql statement :
<table cellspacing="0">
<tr>
<td>ID</td>
<td>Descriptor</td>
<td>Non-Descriptor(s)</td>
</tr>
<local dsc="">
<sql PROVIDER="ODBC" ROW CONNECTSTR="Driver={SQL Server Native Client 10.0};Server=PDE790\SQL2008;Database=MYdb;Uid=MYuid;Pwd=MYpwd"
"SELECT TOP 100 [DESCID] ,[DESCRIPTOR] ,[NONDESCRIPTOR] FROM [AP_TO_DBREFERENCES].[dbo].[Search_Thesaurus]">
<if $dsc ne $DESCRIPTOR>
<if $dsc ne "">
</tr>
</if>
<$dsc=$DESCRIPTOR>
<tr>
<td><fmt %s $DESCID></td>
<td><fmt %s $dsc ></td>
</if>
<if $NONDESCRIPTOR ne "">
<td><fmt %s $NONDESCRIPTOR></td>
</if>
</sql>
</tr>
</table>

The information from the field is returned correctly, but I'm unable to see what causes the problem for these particular records.

here is an excerpt of the results which are printed inside the sql loop :
<tr>
<td>7</td>
<td>commerce des armes</td>
<td>trafic d'armes</td>

<td>vente d'armes</td>
</tr>
<tr>
<td>8</td>
<td>commerce Est-Ouest</td>


</tr>
<tr>
<td>9</td>
<td>commerce extérieur</td>
<td>organisation du commerce extérieur</td>
</tr>
<tr>

Both DESCRIPTOR and NONDESCRIPTOR ar nvarchar(255). I have tried casting them to varchar(255), then the errors disappear, but the result isn't utf-8 anymore.
I also thought for a moment it might be related to NULL values, but it's not.

Any suggestions?

Post by **John** » Mon Dec 07, 2009 2:51 pm

You are seeing the data come through as UTF-8? It sounds as if the translation is already happening somewhere, even though the ODBC is reporting it as WCHAR instead.

Post by **Kai** » Mon Dec 07, 2009 4:13 pm

It appears there may be an issue with zero-length (empty) WCHAR fields coming from ODBC, which may be the cause of your second example error (we still need to verify this).

Out of curiosity, do any of the rows that produce the `Could not convert' errors have hi-bit characters in those fields?

michel.weber · Post by **michel.weber** » Mon Dec 07, 2009 4:26 pm

Yes the output is utf-8.

That is what i expected with nvarchar, but maybe i'm wrong.

SQLServer uses UCS-2 encoding.

It seems to generate an error for 1/3 of the records but i can't see any pattern it happens with NULLs, with english words, and french words...
but ti never complains about the DESCRIPTOR filed

michel.weber · Post by **michel.weber** » Mon Dec 07, 2009 4:39 pm

Sorry our messages crossed each other.

It happens a lot with NULLs, but not all the time. See record 21 below.

I'm not sure wat a 'hi-bit' character is, but it definitely also happens with plain english words.
For example records 13 and 22 in the sample output












<!-- Record 7ID :

:Descriptor ::abattage d'animaux::nonDescriptor ::abattage de bétail:: -->

<!-- Record 8ID :

:Descriptor ::abattage d'animaux::nonDescriptor ::étourdissement d'animal:: -->

<!-- Record 9ID :

:Descriptor ::ABM Agreement::nonDescriptor :::: -->









<!-- Record 19ID :

:Descriptor ::absolute majority::nonDescriptor :::: -->