Android Code Snippet Uncommon But Useful SQLite Syntax Examples

Discussion in 'Code Snippets' started by Mahares, Jun 24, 2014.

  1. Mahares

    Mahares Well Known Member Licensed User

    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

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

    Attached Files:

  2. LucaMs

    LucaMs Expert Licensed User

    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.
     
  3. Mahares

    Mahares Well Known Member Licensed User

    @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.
     
  4. stevel05

    stevel05 Expert Licensed User

    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.
     
  5. LucaMs

    LucaMs Expert Licensed User

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

    stevel05 Expert Licensed User

    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.
     
  7. LucaMs

    LucaMs Expert Licensed User

     
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