Android Code Snippet Uncommon But Useful SQLite Syntax Examples

Description: These code snippets illustrate the use of some uncommon SQLite syntax, but can be useful when needed. Although they also give examples for some very common syntax, the focus is on the uncommon ones such as; ATTACH DATABASE, DETACH DATABASE, CREATE VIEW, LIKE, GLOB (not BLOB), CAST. I invite everyone to add more posts that illustrate additional uncommon syntax to this thread.
See attached complete functional project.

Summary:
1. Create 1st database and table, insert a few record
2. Create 2nd database and table, insert a few records, then close
3. Attach 2nd database and query tables from both databases
4. Compare LIKE to GLOB operators
5. Create a view (virtual table)
6. Detach 2nd database

Tags: SQLite, Attach , Detach, Glob, Create view. Insert or ignore, Cast
Dependencies: SQL library

    Dim SQL1, SQL2 As SQL   'in Process_Globals

    Dim DBFilePath As String = File.DirRootExternal   
    Dim txt As String  
    If SQL1.IsInitialized =False Then
            SQL1.Initialize(DBFilePath,"DB1",True)
    End If
    txt="CREATE TABLE IF NOT EXISTS  DB1Table (COUNTRY TEXT PRIMARY KEY,POPULATION TEXT)"
    SQL1.ExecNonQuery(txt)  'Create 1st table in 1st database
    txt="INSERT INTO DB1Table  VALUES(?,?)"
    SQL1.ExecNonQuery2(txt, Array As String("China","1330044000"))
    txt="INSERT INTO DB1Table  VALUES(?,?)"
    SQL1.ExecNonQuery2(txt, Array As String("India","1173108018"))
    txt="INSERT INTO DB1Table  VALUES(?,?)"
    SQL1.ExecNonQuery2(txt, Array As String("USA","310232863"))
   
    If SQL2.IsInitialized =False Then
            SQL2.Initialize(DBFilePath,"DB2",True)
    End If
    txt="CREATE TABLE IF NOT EXISTS  DB2Table (COUNTRY TEXT PRIMARY KEY,CAPITAL TEXT)"
    SQL2.ExecNonQuery(txt)  'Create 2nd table in 2nd database
    txt="INSERT INTO DB2Table  VALUES(?,?)"
    SQL2.ExecNonQuery2(txt, Array As String("China","Beijing"))
    txt="INSERT INTO DB2Table  VALUES(?,?)"
    SQL2.ExecNonQuery2(txt, Array As String("India","New DelHi"))
    txt="INSERT INTO DB2Table  VALUES(?,?)"
    SQL2.ExecNonQuery2(txt, Array As String("USA","Washington"))
    SQL2.Close
   
    txt="ATTACH DATABASE '" & File.Combine(DBFilePath,"DB2")  & "' AS D2"
    SQL1.ExecNonQuery(txt)    'attach to the 2nd database
   
    'Below combine data from both main database table and attached database table
    txt="SELECT T1.COUNTRY, T2.CAPITAL , T1.POPULATION FROM DB1Table T1 " _
    & " INNER JOIN D2.DB2Table T2 ON T1.COUNTRY=T2.COUNTRY"
    Cursor1=SQL1.ExecQuery(txt)
    Cursor1.Position=1
    Log(Cursor1.GetString("COUNTRY") & "  " & Cursor1.GetString("CAPITAL") & "  " _
    & Cursor1.GetString("POPULATION") )   'displays India data
   
    'Below to use CAST
    txt="SELECT T1.COUNTRY, T2.CAPITAL , CAST(T1.POPULATION AS INTEGER) AS POP FROM DB1Table T1 " _
    & " INNER JOIN D2.DB2Table T2 ON T1.COUNTRY=T2.COUNTRY ORDER BY POP DESC"
    Cursor1=SQL1.ExecQuery(txt)
    For i=0 To Cursor1.RowCount-1
        Cursor1.Position=i
        Log("Sort Descending: " & Cursor1.GetString("COUNTRY") & "  " & Cursor1.GetString("CAPITAL") _
        & "  " & Cursor1.GetString("POP") ) 
    Next 
   
    'Below the use of LIKE where search is not case sensitive. Displays Washington and New DelHi
    txt="SELECT T2.COUNTRY, T2.CAPITAL  FROM D2.DB2Table T2 " _
    & " WHERE T2.CAPITAL LIKE '%hi%'"
    Cursor1=SQL1.ExecQuery(txt)
    For i=0 To Cursor1.RowCount-1
        Cursor1.Position=i
        Log("Using LIKE: " & Cursor1.GetString("COUNTRY") & "  " & Cursor1.GetString("CAPITAL")) 
    Next
   
    'Below the use of GLOB similar to LIKE, but search is case sensitive. Dipslays only Washington
    txt="SELECT T2.COUNTRY, T2.CAPITAL  FROM D2.DB2Table T2 " _
    & " WHERE T2.CAPITAL GLOB '*hi*'"
    Cursor1=SQL1.ExecQuery(txt)
    For i=0 To Cursor1.RowCount-1
        Cursor1.Position=i
        Log("Using GLOB: " & Cursor1.GetString("COUNTRY") & "  " & Cursor1.GetString("CAPITAL"))
    Next
   
    'Below code to create a view:
    txt="CREATE VIEW viewT1 AS SELECT T1.COUNTRY, round(T1.POPULATION/1000000,0)" _
    & " AS MILLIONS FROM DB1Table T1 "
    SQL1.ExecNonQuery(txt)
   
    'Below display view data
    txt="SELECT * FROM viewT1 WHERE COUNTRY = 'China'"
    Cursor1=SQL1.ExecQuery(txt)
    Cursor1.Position=0
    Log(Cursor1.GetString("COUNTRY")  & "  " & Cursor1.GetString("MILLIONS") )   'displays China data population in millionsd
       
    txt="DETACH DATABASE D2"
    SQL1.ExecNonQuery(txt)    'Detach the 2nd database
   
    Cursor1.Close
    SQL1.Close

 

[LucaMs]

I liked you post, but there are many "uncommon" commands, we can't post them here.

It is better to go directly to the official website.

 

[Mahares]

@LucaMs: The official SQLite site provides good documentation, but not good real practical examples. That is what I am after here: easy to understand and apply examples.

 

[stevel05]

Although it's not specifically B4a code, I think it's a good place to post them. They will be useful for beginners and advanced users alike. I've certainly struggled through the official documentation (for SQLite and other repositories) in the past, my view is that anything that makes it easier to create a B4a app that does what you want is fair game for the code snippets forum.

 

[LucaMs]

Yes, but I meant to say that there are so many examples we could do that is probably better to wait for questions.

 

[stevel05]

The code snippets forum came out of a desire to share utilities and coding tricks with the community, the added benefit is that answers can be found without the questions having to be asked. If I am coding and come across something I think will be useful, I'll post it in the code snippets while I remember it, otherwise when I see a question that it will answer, It'll take me more time to find it again and remember what I did and why.

 

[LucaMs]

The code snippets forum came out of a desire to share utilities and coding tricks with the community,

I know, I was there

If I am coding and come across something I think will be useful, I'll post it in the code snippets while I remember it,

This is the right idea, otherwise you should create an entire manual for SQLite.