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:
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.
Tags: Pretty much every one of the 12 function names featured in the first part series.
Dependency: SQL Library 1.30
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:
B4X:
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.
B4X:
'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