1. *** New version of B4J is available ***
    B4J v7.8
    Dismiss Notice

Android Code Snippet Exploring SQLite Core Functions, Part One

Discussion in 'Code Snippets' started by Mahares, Dec 7, 2016.

  1. Mahares

    Mahares Well Known Member Licensed User

    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:

    Code:
    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.

    Code:
    '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
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice