Android Code Snippet Exploring SQLite Core Functions, Part One

Description:

The following link http://www.sqlite.org/lang_corefunc.html explains the use of all the core functions. This first part snippet demonstrates an example of each of 12 core functions. I hope to publish a couple of more snippets regarding the rest of the core functions that are available in SQLite by default . Below, I have an explanation, example SQL statement and result for each of the 12 functions using a SQLite table with the following definition:

Dim DBTableName As String  = "tblColors"
MyQuery="CREATE TABLE IF NOT EXISTS " & DBTableName & " (COLOR_NAME TEXT, COLOR_RGB TEXT, COLOR_B4A INTEGER)"
  SQL1.ExecNonQuery(MyQuery)

Some of the functions were introduced in later versions of SQLite. For instance, INSTR() was introduced in version 3.7.15. On my Galaxy Tab A, the version is: 3.8.6.1. All the 12 functions worked without any problems and are all supported. Some of the functions have a widespread use, but others are used very sparingly. As a bonus to illustrating the use of the core functions, I have included examples of the use of Smart String Literal.

'ABS(X): Below calculates the absolute value:
    MyQuery="SELECT abs(COLOR_B4A) FROM " & DBTableName & " WHERE COLOR_NAME='cornflowerblue'" 'shows 10185235
    Log(SQL1.ExecQuerySingleResult(MyQuery) )

    'changes(): below returns the number of database rows that were changed, inserted or deleted by the most 
    'recently completed INSERT, DELETE, or UPDATE:
    MyQuery="SELECT changes() FROM " & DBTableName '
    Log(SQL1.ExecQuerySingleResult(MyQuery) )

    'CHAR(X!,X2,..): The char(X1,X2,...,XN) function returns a string composed of characters having the unicode 
    'code point values of integers X1 through X. Shows the lower case o. For 98 the letter is b. Reverse of unicode(X) 
    'function. :char(98,111) returns: bo. Supported in later versions. Would not work with SQLite 3.7.11:
    MyQuery="SELECT char(98,111) FROM " & DBTableName 
    Log(SQL1.ExecQuerySingleResult(MyQuery) )

    'coalesce((X,Y): below similar to the Ifnull function. 
    'returns the first non null value. If no non NULL values then returns NULL: COLOR_RGB is null, therefore returns mysterious
    MyQuery="SELECT coalesce(COLOR_RGB,'mysterious') FROM " & DBTableName & " WHERE COLOR_NAME='TestColor3'" '
    Log(SQL1.ExecQuerySingleResult(MyQuery) )
   
    'GLOB(X,Y): The glob(X,Y) function is equivalent to the expression "Y GLOB X". Returns 1 if both are exatly
    'the same including matching their case. Otherwise returns 0. cornFlowerblue in Y returns 0, cornflowerblue returns 1:
    'Note the letter F, capitalized in one, but not the other: cornFlowerblue is not same as cornflowerblue:
    MyQuery="SELECT Glob(COLOR_NAME, 'cornFlowerblue') FROM " & DBTableName & " WHERE COLOR_NAME='cornflowerblue'" '
    Log(SQL1.ExecQuerySingleResult(MyQuery) )

    'HEX(X): below function interprets its argument as a BLOB and returns a string which is the upper-case hexadecimal
    ' rendering of the content of that blob.
    MyQuery="SELECT hex(COLOR_NAME) FROM " & DBTableName & " WHERE COLOR_NAME='orange'" '
    Log(SQL1.ExecQuerySingleResult(MyQuery) )

    'ifnull(X,Y): below if the COLOR_RGB column contains a null value, the returned value is the word mysterious; otherwise:returns
    ' a copy of itself which is the first non-NULL argument:
    MyQuery="SELECT ifnull(COLOR_RGB,'mysterious') FROM " & DBTableName & " WHERE COLOR_NAME='TestColor3'" '
    Log(SQL1.ExecQuerySingleResult(MyQuery) )
   
    'instr(X,Y): Below The instr(X,Y) function finds the first occurrence of string Y within string X. The word blue 
    'starts at the 11th character in cornflowerblue. Works with SQLite 3.7.15 and higher.
    MyQuery="SELECT instr(COLOR_NAME,'blue') AS partial FROM " & DBTableName & " WHERE COLOR_NAME='cornflowerblue'" 'shows 11
    Cursor1=SQL1.ExecQuery(MyQuery) 
    Cursor1.Position=0 
    Log(Cursor1.GetString("partial"))

    'last_insert_rowid():  below shows the ROWID of record inserted before database is closed:
    MyQuery="SELECT last_insert_rowid() FROM " & DBTableName 
    Log(SQL1.ExecQuerySingleResult(MyQuery) )

    'LENGTH(), UPPER(), LOWER(): Below: length, Uppercase and lowercase:
    MyQuery=$"SELECT 
    length(COLOR_NAME) AS MyLen, UPPER(COLOR_NAME) AS U, LOWER(COLOR_NAME) AS L 
    FROM  ${DBTableName} 
    WHERE COLOR_NAME='cornflowerblue'"$
    Cursor1=SQL1.ExecQuery(MyQuery) 
    Cursor1.Position=0 
    Log($"${Cursor1.GetInt("MyLen")} ${Cursor1.GetString("U")}   ${Cursor1.GetString("L")} "$)

Tags: Pretty much every one of the 12 function names featured in the first part series.

Dependency: SQL Library 1.30