Android - no such collation sequence: SQL_Latin1_General_CP1_CI_AS

Question

I'm developing an android application where I need to use the query below ,

SELECT distinct AttributeValue.AttributeValueName as InventoryId
    ,WorkFlowTransaction.WorkFlowTransId
    ,Client.ClientName
    ,Project.ProjectName
    ,Product.ProductName 
FROM WorkFlowTransaction 
JOIN AttributeValue 
    ON WorkFlowTransaction.WorkflowTransId = AttributeValue.WorkflowTransId 
JOIN Attribute 
    on Attribute.AttributeId = AttributeValue.AttributeId 
JOIN Product 
    on Product.ProductId = WorkFlowTransaction.ProductId 
JOIN Client 
    ON Client.ClientID=WorkFlowTransaction.ClientId 
JOIN Project 
    on Project.ProjectID=WorkFlowTransaction.ProjectId 
where AttributeValue.AttributeValueName 
    COLLATE SQL_Latin1_General_CP1_CI_AS not in (
                                 Select LocationId 
                                 from BarcodeDetails 
                                             ) 
    and Attribute.AttributeName = 'Inventory Id'
    and WorkFlowTransaction.ClientId = 2 
    and WorkFlowTransaction.ProjectId = '44' 
    and WorkFlowTransaction.ProductId = '47' 
    and Attribute.IsDeleted ='0' 
    and AttributeValue.IsDeleted ='0' 
    and WorkFlowTransaction.IsDeleted = '0'

This Query is working fine in SQL-Server .But no such collation sequence: SQL_Latin1_General_CP1_CI_AS exception thrown in android .
Please help me out giving your idea or solution .

and whats you expected? it is mssql(not sqlite) specific collation. — Selvin, Apr 17 '14 at 09:57

score 1 · Answer 1 · edited May 23 '17 at 12:28

To learn what the collation sequence is, see this question: What does 'COLLATE SQL_Latin1_General_CP1_CI_AS' do?

latin1 makes the server treat strings using charset latin 1, basically ascii

CI case insensitive comparisons so 'ABC' would equal 'abc'

AS accent sensitive, so 'ü' does not equal 'u'

There isn't a direct replacement for it in SQLite and Android SQLite does not expose the APIs required for installing your own custom collation sequences.

Depending on your data and requirements, you can possibly get close enough with plain COLLATE NOCASE which works with ASCII characters but not full Latin1 character set.

Android - no such collation sequence: SQL_Latin1_General_CP1_CI_AS

1 Answers1