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????¿??ß??ß????»????«?ßú??ª??????????µ??????á½????Ñ?????»???????????á??»????»??¿É????í????ß?É????ì???½???µú«???????á??½¡?º¿µÉ?????ó?ª??½????????É???Å?üí?????¬?????????????????º????Å?????¥????????ñ???¬???????ü??????ü?¿??ü¿?ª????????íì??¡?ìì??óµ??????ñ??ª???????????«?????????É?¿á?ª??ñµ????«??????????????????ñßÅ??ñŵ??????«?????É?µ?????µÉ½???߬???¿???????ñ«????ü??ú??¼??ñ???????????????????ñ¬???µ?»?ª??¥