Android Question B4A SQLCipher IIF statement

[jotajota52]

Hi, I'm using SQLCipher lib 1.60, but I'm getting an SQLiteExcepcion: no such function: IIF
Is there an update to this lib that includes the iif function ?

 

[RB Smissaert]

Hi, I'm using SQLCipher lib 1.60, but I'm getting an SQLiteExcepcion: no such function: IIF
Is there an update to this lib that includes the iif function ?

Update to SQLCipher 3.25.2 and it will work.

RBS

 

[RB Smissaert]

Update to SQLCipher 3.25.2 and it will work.

RBS

Sorry, I added custom functions and this is one of them, so it changes the run sql from eg:

select iif(age >= 60, 'old', 'young') as A from patients_all

To:

select Case When age >= 60 Then 'old' Else 'young' End as A from patients_all

I think though SQLCipher 3.25.2 will handle iif, but will need to check my code
and see if this is converted.

RBS

 

[RB Smissaert]

Sorry, I added custom functions and this is one of them, so it changes the run sql from eg:

select iif(age >= 60, 'old', 'young') as A from patients_all

To:

select Case When age >= 60 Then 'old' Else 'young' End as A from patients_all

I think though SQLCipher 3.25.2 will handle iif, but will need to check my code
and see if this is converted.

RBS

Just checked and my SQL was indeed converted, so iif is one of my custom functions. Turning this off shows that SQLCipher 1.6 (I have this same version and I think it is still the latest version that can be used in B4A) doesn't handle iif.
There is a later version of SQLCipher that is likely to handle iff, but I couldn't get it to work in B4A.

Not sure if it could be applied in your app, but I have this conversion code, all in a Try Catch block:

            iFunctionConvertPos = strSQL.ToLowerCase.IndexOf("iif(")
            strError = "Error in function Iif"
        
            Do While iFunctionConvertPos > -1
                iFunctionConvertEndPos = GetPosOfClosingBracket(strSQL, iFunctionConvertPos)
                strFunctionToReplace = strSQL.SubString2(iFunctionConvertPos, iFunctionConvertEndPos + 1)
                iCommaPos = GetSQLFunctionCommaPos(strFunctionToReplace, 0)
                If iCommaPos = -1 Then Exit 'so leave SQL as it is and cause a handled error
                strArg1 = strFunctionToReplace.SubString2(4, iCommaPos)
                iComma2Pos = strFunctionToReplace.IndexOf2(",", iCommaPos + 1)
                If iComma2Pos = -1 Then Exit 'so leave SQL as it is and cause a handled error
                strArg2 = strFunctionToReplace.SubString2(iCommaPos + 1, iComma2Pos).Trim
                strArg3 = strFunctionToReplace.SubString2(iComma2Pos + 1, strFunctionToReplace.Length - 1).Trim
                strSQL = strSQL.Replace(strFunctionToReplace, _
                "Case When " & strArg1 & " Then " & strArg2 & " Else " & strArg3 & " End")
                iFunctionConvertPos = strSQL.ToLowerCase.IndexOf("iif(")
            Loop

Sub GetPosOfClosingBracket(strString As String, iStartPos As Int) As Int
    
    Dim i As Int
    Dim iOpenCount As Int
    
    For i = iStartPos To strString.Length
        If strString.CharAt(i) = "(" Then
            iOpenCount = iOpenCount + 1
        Else
            If strString.CharAt(i) = ")" Then
                iOpenCount = iOpenCount - 1
                If iOpenCount = 0 Then
                    Return i
                End If
            End If
        End If
    Next
    
    Return -1
    
End Sub

RBS

 

[RB Smissaert]

Hi, I'm using SQLCipher lib 1.60, but I'm getting an SQLiteExcepcion: no such function: IIF
Is there an update to this lib that includes the iif function ?

You can download the latest SQLCipher aar file here:

Maven Central Repository Search

Official search by the maintainers of Maven Central Repository

search.maven.org

But I was unable to alter the .xml and .jar files to make it work.
Maybe somebody could tell us if it can be done.

RBS

 

[DonManfred]

Hi, I'm using SQLCipher lib 1.60, but I'm getting an SQLiteExcepcion: no such function: IIF
Is there an update to this lib that includes the iif function ?

You are using which B4A Version? iif was added in V11

 

[RB Smissaert]

You are using which B4A Version? iif is added in V11

We are talking about SQL in SQLCipher, not B4A.

RBS

 

[Mahares]

Is there an update to this lib that includes the iif function ?

SQLCipher current B4A library is 1.60 It is based on: android-database-sqlcipher-4.0.0.aar which is based on SQLite 3.25.0
However, the iif() function was introduced in SQLite 3.32. 0, which was released on 22 May 2020. Therefore, you cannot use it, until the B4A library is upgraded to use 4.4 or 4.5 aar.
In the meantime, as a good alternative, use Case When......End . If you get stuck, post your code or project.

 

[jotajota52]

Thank you all for the help!

George.