Android Question B4A SQLCipher IIF statement

jotajota52

Member
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

Well-Known Member
Licensed User
Longtime User
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
 
Upvote 0

RB Smissaert

Well-Known Member
Licensed User
Longtime User
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:

B4X:
            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
 
Upvote 0

RB Smissaert

Well-Known Member
Licensed User
Longtime User
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:
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
 
Upvote 0

DonManfred

Expert
Licensed User
Longtime User
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
 
Last edited:
Upvote 0

Mahares

Expert
Licensed User
Longtime User
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.
 
Upvote 0
Top