Garbage characters in RecordSet (MSSQL)

  1. 2 weeks ago

    Andrei L

    Oct 12 Pre-Release Testers, Xojo Pro Bucharest, Romania

    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)

    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

    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.

  2. Tim H

    Oct 12 Pre-Release Testers Answer Portland, OR USA

    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.

  3. Sascha S

    Oct 12 Pre-Release Testers, Xojo Pro Germany/W'haven

    @Andrei L rc.field(aKey).StringValue

    You need to tell Xojo which encoding is used.

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

    Replace XXX with the proper encoding.

  4. Andrei L

    Oct 12 Pre-Release Testers, Xojo Pro Bucharest, Romania

    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!

  5. Sascha S

    Oct 12 Pre-Release Testers, Xojo Pro Germany/W'haven

    @Andrei L SystemDefault

    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.

or Sign Up to reply!