Garbage characters in RecordSet (MSSQL)

Hi!

I’m running into a strange problem when using MSSQLServerDatabase (on a server running SQL Server 2012 (11.0.2100)). When running some queries, the Record Set starts including garbage characters which are not shown when running the same query in the SQL Server Management Studio or when creating a native Windows application running the same query. Has anyone ever come across this?

Calling Code (Part of class)

[code]dim output as Variant = nil

dim database as MSSQLServerDatabase = App.detachDatabaseInstance

dim SQLQuery as String = “SELECT” + " " + aKey + " " + “FROM” + " " + self.pTableName + " " + “WHERE id =” + " " + STR(self.id) + " " + “AND” + " " + aKey + " " + “%NULLQUALIFIER%”

https://connect.microsoft.com/SQLServer/feedback/details/467300/problem-with-varchar-max-via-sql-native-client
’ There’s a bug as explained by the link above in SQL NativeClient that will respond in an unacceptable way to XOJO if a column data type is subject to MAX. This will cause the Xojo application to crash.
’ Avoiding this issue by not returning NULL values at all and replying with an empty record set, which will lead this method to return nil also, which is exactly what we need.
select case aValueType
case ValueTypeNullableObject
SQLQuery = SQLQuery.replace("%NULLQUALIFIER%", “is not NULL”)
case ValueTypeInteger
SQLQuery = SQLQuery.replace("%NULLQUALIFIER%", “<> 0”)
end select

dim rc as RecordSet = database.SQLSelect(SQLQuery) <-- debugging this also contains the garbage values

if rc <> nil then
while not rc.EOF
output = rc.field(aKey).value

if aKey = "requestedChanges" then
  Print SQLQuery
  dim test as MemoryBlock = rc.field(aKey).StringValue
  print test <-- garbage
end if

if output <> nil then
  'output = ConvertEncoding(output, Encodings.WindowsANSI)
end if

rc.MoveNext

wend

rc.close[/code]

Table Setup

[id] [int] IDENTITY(1,1) NOT NULL, [operationType] [int] NOT NULL, [affectedTable] [nvarchar](100) NOT NULL, [previousState] xml NULL, [requestedChanges] xml NOT NULL, [requestedBy] [nvarchar](100) NOT NULL, [requestedOn] [datetime] NOT NULL, [belongsTo] [nvarchar](200) NOT NULL, [destinationChanges] xml NULL, [requestReceipt] xml NULL, [requestStatus] [int] NOT NULL, [assignedTo] [nvarchar](100) NULL, [lastModifiedBy] [nvarchar](200) NOT NULL, [lastModifiedDate] [datetime] NULL, [createdDate] [datetime] NOT NULL, [responseObjects] [varbinary](max) NULL

Record Set Output

???<inserted id="28" DBMarca="1234561" DBSalutation="Mr" DBFirstName="Valentin" DBLastName="Rozescu" DBSex="m" DBFirstEmployment="2012-01-01" DBBirthday="1970-01-01" DBAddress="123 Voluntari Street" DBFunction="Consultant" DBDepartment="IT" DBGrade="White" DBLocation="HO" DBSignature="D"/>?ú???????=x????¿??ß??ß????»????«?ßú??ª??????????µ??????á½????Ñ?????»???????????á??»????»??¿É????í????ß?É????ì???½???µú«???????á??½¡?º¿µÉ?????ó?ª??½????????É???Å?üí?????¬?????????????????º????Å?????¥????????ñ???¬???????ü??????ü?¿??ü¿?ª????????íì??¡?ìì??óµ??????ñ??ª???????????«?????????É?¿á?ª??ñµ????«??????????????????ñßÅ??ñŵ??????«?????É?µ?????µÉ½???߬???¿???????ñ«????ü??ú??¼??ñ???????????????????ñ¬???µ?»?ª??¥

The results of a database query have no encoding defined. ConvertEncoding is the wrong function, use DefineEncoding instead. ConvertEncoding assumes that the encoding has already been defined. That is false in the case of a database query. Use DefineEncoding to, well, define the (expected) encoding of the data.

You need to tell Xojo which encoding is used.

Try rc.field(aKey).StringValue.DefineEncoding(Encodings.XXX)

Replace XXX with the proper encoding.

Thank you all for your answers. I changed the vartype from XML to nvarchar(MAX) and set the encoding to SystemDefault. Now all is well!

This may go wrong on other Desktop OS Setups. You should be better use the Enconding the Database is using, not the System default of the Desktop OS.