Android Tutorial SQL tutorial

Discussion in 'Tutorials & Examples' started by Erel, Dec 2, 2010.

  1. Erel

    Erel Administrator Staff Member Licensed User

    This tutorial covers the SQL library and its usage with Basic4android.
    There are many general SQL tutorials that cover the actual SQL language. If you are not familiar with SQL it is recommended to start with such a tutorial.
    SQL Introduction

    A new code module is available named DBUtils. It contains methods for common tasks which you can use and also learn from the code.

    Android uses SQLite which is an open source SQL implementation.
    Each implementation has some nuances. The following two links cover important information regarding SQLite.
    SQLite syntax: Query Language Understood by SQLite
    SQLite data types: Datatypes In SQLite Version 3

    SQL in Basic4android
    The first step is to add a reference to the SQL library. This is done by going to the Libraries tab and checking SQL.
    There are two types in this library.
    An SQL object gives you access to the database.
    The Cursor object allows you to process queries results.

    Usually you will want to declare the SQL object as a process global object. This way it will be kept alive when the activity is recreated.

    SQLite stores the database in a single file.
    When we initialize the SQL object we pass the path to a database file (which can be created if needed).
    Code:
    Sub Process_Globals
        
    Dim SQL1 As SQL
    End Sub

    Sub Globals

    End Sub

    Sub Activity_Create(FirstTime As Boolean)
        
    If FirstTime Then
            SQL1.Initialize(
    File.DirDefaultExternal, "test1.db"True)
        
    End If
        CreateTables
        FillSimpleData
        LogTable1
        InsertManyRows
        
    Log("Number of rows = " & SQL1.ExecQuerySingleResult("SELECT count(*) FROM table1"))
       
        InsertBlob 
    'stores an image in the database.
        ReadBlob 'load the image from the database and displays it.
    End Sub
    The SQL1 object will only be initialized once when the process starts.
    In our case we are creating it in the sd card. The last parameter (CreateIfNecessary) is True so the file will be created if it doesn't exist.

    There are three types of methods that execute SQL statements.
    ExecNonQuery - Executes a "writing" statement and doesn't return any result. This can be for example: INSERT, UPDATE or CREATE TABLE.

    ExecQuery - Executes a query statement and returns a Cursor object that is used to process the results.

    ExecQuerySingleResult - Executes a query statement and returns the value of the first column in the first row in the result set. This method is a shorthand for using ExecQuery and reading the value with a Cursor.

    We will analyze the example code:
    Code:
    Sub CreateTables
        SQL1.ExecNonQuery(
    "DROP TABLE IF EXISTS table1")
        SQL1.ExecNonQuery(
    "DROP TABLE IF EXISTS table2")
        SQL1.ExecNonQuery(
    "CREATE TABLE table1 (col1 TEXT , col2 INTEGER, col3 INTEGER)")
        SQL1.ExecNonQuery(
    "CREATE TABLE table2 (name TEXT, image BLOB)")
    End Sub
    The above code first deletes the two tables if they exist and then creates them again.

    Code:
    Sub FillSimpleData
        SQL1.ExecNonQuery(
    "INSERT INTO table1 VALUES('abc', 1, 2)")
        SQL1.ExecNonQuery2(
    "INSERT INTO table1 VALUES(?, ?, ?)"Array As Object("def"34))
    End Sub
    In this code we are adding two rows. SQL.ExecNonQuery2 receives two parameters. The first parameter is the statement which includes question marks. The question marks are then replaced with values from the second List parameter. The List can hold numbers, strings or arrays of bytes (blobs).
    Arrays are implicitly converted to lists so instead of creating a list we are using the Array keyword to create an array of objects.

    Code:
    Sub LogTable1
        
    Dim Cursor1 As Cursor
        Cursor1 = SQL1.ExecQuery(
    "SELECT col1, col2, col3 FROM table1")
        
    For i = 0 To Cursor1.RowCount - 1
            Cursor1.Position = i
            
    Log("************************")
            
    Log(Cursor1.GetString("col1"))
            
    Log(Cursor1.GetInt("col2"))
            
    Log(Cursor1.GetInt("col3"))
        
    Next
        Cursor1.Close
    End Sub
    This code uses a Cursor to log the two rows that were previously added.
    SQL.ExecQuery returns a Cursor object.
    Then we are using the For loop to iterate over all the results.
    Note that before reading values from the Cursor we are first setting its position (the current row).

    Code:
    Sub InsertManyRows
        SQL1.BeginTransaction
        
    Try
            
    For i = 1 To 500
                SQL1.ExecNonQuery2(
    "INSERT INTO table1 VALUES ('def', ?, ?)"Array As Object(i, i))
            
    Next
            SQL1.TransactionSuccessful
        
    Catch
            
    Log(LastException.Message)
        
    End Try
        SQL1.EndTransaction
    End Sub
    This code is an example of adding many rows. Internally a lock is acquired each time a "writing" operation is done.
    By explicitly creating a transaction the lock is acquired once.
    The above code took less than half a second to run on a real device.
    Without the BeginTransaction / EndTransaction block it took about 70 seconds.
    A transaction block can also be used to guarantee that a set of changes were successfully done. Either all changes are made or none are made.
    By calling SQL.TransactionSuccessful we are marking this transaction as a successful transaction. If you omit this line, all the 500 INSERTS will be ignored.
    It is very important to call EndTransaction eventually.
    Therefore the transaction block should usually look like:
    Code:
    SQL1.BeginTransaction
    Try
      
    'Execute the sql statements.
    SQL1.TransactionSuccessful
    Catch
    'the transaction will be cancelled
    End Try
    SQL1.EndTransaction
    Note that using transactions is only relevant when doing "writing" operations.

    Blobs
    The last two methods write an image file to the database and then read it and set it as the activity background.
    Code:
    Sub InsertBlob
        
    'convert the image file to a bytes array
        Dim InputStream1 As InputStream
        InputStream1 = 
    File.OpenInput(File.DirAssets, "smiley.gif")
        
    Dim OutputStream1 As OutputStream
        OutputStream1.InitializeToBytesArray(
    1000)
        
    File.Copy2(InputStream1, OutputStream1)
        
    Dim Buffer() As Byte 'declares an empty array
        Buffer = OutputStream1.ToBytesArray
       
        
    'write the image to the database
        SQL1.ExecNonQuery2("INSERT INTO table2 VALUES('smiley', ?)"Array As Object(Buffer))
    End Sub
    Here we are using a special type of OutputStream which writes to a dynamic bytes array.
    File.Copy2 copies all available data from the input stream into the output stream.
    Then the bytes array is written to the database.

    Code:
    Sub ReadBlob
        
    Dim Cursor1 As Cursor
        
    'Using ExecQuery2 is safer as it escapes special characters automatically.
        'In this case it doesn't really matter.
        Cursor1 = SQL1.ExecQuery2("SELECT image FROM table2 WHERE name = ?"Array As String("smiley"))
        Cursor1.Position = 
    0
        
    Dim Buffer() As Byte 'declare an empty byte array
        Buffer = Cursor1.GetBlob("image")
        
    Dim InputStream1 As InputStream
        InputStream1.InitializeFromBytesArray(Buffer, 
    0, Buffer.Length)
       
        
    Dim Bitmap1 As Bitmap
        Bitmap1.Initialize2(InputStream1)
        InputStream1.Close
        
    Activity.SetBackgroundImage(Bitmap1)
    End Sub
    Using a Cursor.GetBlob we fetch the previously stored image.
    Now we are using an input stream that reads from this array and load the image.

    Asynchronous queries
    SQL library v1.20 supports asynchronous select queries and asynchronous batch inserts.

    Asynchronous means that the task will be processed in the background and an event will be raised when the task completes. This is useful when you need to issue a slow query and keep your application responsive.

    The usage is quite simple:
    Code:
    sql1.ExecQueryAsync("SQL""SELECT * FROM table1"Null)
    ...
    Sub SQL_QueryComplete (Success As Boolean, Crsr As Cursor)
        
    If Success Then
            
    For i = 0 To Crsr.RowCount - 1
                Crsr.Position = i
                
    Log(Crsr.GetInt2(0))
            
    Next
        
    Else
            
    Log(LastException)
        
    End If
    End Sub
    The first parameter is the "event name". It determines which sub will handle the QueryComplete event.

    Batch inserts
    SQL.AddNonQueryToBatch / ExecNonQueryBatch allow you to asynchronously process a batch of non-query statements (such as INSERT statements).
    You should add the statements by calling AddNonQueryToBatch and eventually call ExecNonQueryBatch.
    The task will be processed in the background. The NonQueryComplete event will be raised after all the statements execute.
    Code:
    For i = 1 To 10000
            sql1.AddNonQueryToBatch(
    "INSERT INTO table1 VALUES (?)"Array As Object(Rnd(0100000)))
    Next
    sql1.ExecNonQueryBatch(
    "SQL")
    ...   
    Sub SQL_NonQueryComplete (Success As Boolean)
        
    Log("NonQuery: " & Success)
        
    If Success = False Then Log(LastException)
    End Sub
     

    Attached Files:

    • SQL.zip
      File size:
      8.1 KB
      Views:
      7,799
    Last edited: Nov 27, 2014
  2. nfordbscndrd

    nfordbscndrd Well-Known Member Licensed User

    Indexes

    I have written a data-intensive program in VB6 using databases/tables created in Access2007. The database has over a half-dozen tables totalling over a gig.

    The Archos 70 has a 250GB hard drive, so storing the databases should not be a problem, but each table has multiple indexes, most of them indexing 2+ fields combined. I have looked at the links you gave for SQLite, but cannot figure out if it offers the same ability of indexing multiple fields.

    Also, it was not clear to me if an index can be created and stored in advance of running the app, or if it is created each time the app is run, which seems like it would really be time consuming with such large files. I'm also concerned about running such an app with the A70 only having 256MB of memory.

    I downloaded a user's app (from the B4a site) which uses SQLite, but the demo version will not run it, so I can't experiment with SQLite. I don't have high hopes for getting my app onto the Archos 70, but this is my only need for Basic4android, so I wouldn't buy it without knowing if this will work.
     
  3. Erel

    Erel Administrator Staff Member Licensed User

    SQLite supports multiple columns in the index. See the CREATE INDEX command: SQLite Query Language: CREATE INDEX
    The index is stored in the database file. There is no need to recreate it each time.

    I cannot estimate the performance of such a database on your device.
     
  4. nfordbscndrd

    nfordbscndrd Well-Known Member Licensed User

    I am trying to run the following:

    Cursor = SQL1.ExecQuery("SELECT * FROM Words")

    I compile the app to a device, enter some text, click a button to execute this code, and get this error:

    android.database.sqlite.SQLiteException: no such table: Words: , while compiling:
    SELECT * FROM Words

    I did an If File.Exists... to make sure the database is really there. The only table in the db is "Words".

    If it helps, here is the Log:
    [​IMG]
     
  5. Erel

    Erel Administrator Staff Member Licensed User

    Are you sure that the Words table exists in the database?
    Can you upload the database (in a new thread preferably)?
     
  6. nfordbscndrd

    nfordbscndrd Well-Known Member Licensed User

    Yeah. Like I said, Words is the *only* table in the database.
    It's at http://www.hsv-life.com/AIC/wordsdb.zip

    I created it with SQLite DB Browser. You can see the Words table in this screen shot:
    [​IMG]
     
  7. Erel

    Erel Administrator Staff Member Licensed User

    You can issue the following query to list all the tables:
    Code:
    SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;
     
  8. nfordbscndrd

    nfordbscndrd Well-Known Member Licensed User

    When I insert that code and compile, I get an error message saying that "End Select" is missing.
    Also, where does this display the table names? Do I have to add some code to do that?
     
    Last edited: Jan 23, 2011
  9. nfordbscndrd

    nfordbscndrd Well-Known Member Licensed User

    After staring at this a dozen times, it dawned on me that the code you gave me needs to be in a Query, so I put in:

    Code:
    Cursor = SQL1.ExecQuery("Select name from sqlite_master _
                   WHERE  Type='table' ORDER BY name;")
    Cursor.Position = 0
    Msgbox(Cursor.GetString("name"), "table:")
    I got the response of "android_metadata".
    Ooo-kay...
     
  10. nfordbscndrd

    nfordbscndrd Well-Known Member Licensed User

  11. karmacomposer

    karmacomposer Member Licensed User

    Can we access mysql databases on a hosted server via internet commands in Basic4Android?

    I have a MASSIVE database project I have been working on for a client that uses PHP and MySql. If I could write a app for android that accesses and utilizes the data from the existing database, it would be fantastic.

    However, this database is protected and must remain on the protected server (since it has sensitive data and must be private for governmental reasons).

    How would I do this?

    Mike
     
  12. Erel

    Erel Administrator Staff Member Licensed User

    You can use the HTTP library to communicate with web services.
     
  13. karmacomposer

    karmacomposer Member Licensed User

    This is looking better and better.

    Mike
     
  14. markbarrett_1

    markbarrett_1 Member Licensed User

    Hi,

    I agree with Erel, using http services would be much better.

    In my humble opinion, use https and spend a little bit of money to get a signed certificate for your server from a certificate authority.

    Cheers.

    M
     
  15. asawyer13

    asawyer13 Member Licensed User

    Mark,
    Do you know of a simple step by step guide of what would be needed to setup an https webservice and get the certificate set up?
    I can write the php webservice, but I don't know how to set up the server, etc?
    Alan
     
  16. eps

    eps Well-Known Member Licensed User

    That's a great post!! Very helpful :)

    On my way to creating a SQLite DB and then manipulating the data - it's only going to be a piddly little DB, but it may grow and grow..
     
  17. markbarrett_1

    markbarrett_1 Member Licensed User

    Sorry for the delayed response, I've been otherwise occupied.

    What web server are you going to use? Both lighttpd and apache have heaps of examples (favourite search engine is your friend). Most of the questions resolve around using a self signed certificate, but how you configure your server doesn't change, its the way of getting your certificate that does if you self sign. Usually its a 5 or so line configuration in your httpd.conf which is essentially specifying your SSL key locations and which part of the wwwroot is to use this key information.

    The bit about getting your certificate signed depends on which certificate authority you are going to use (be it verisign, godaddy, thawte etc etc). Each of them have their own instructions and procedures. You probably just want to double check the certificate authority you choose is one of the ones trusted by the android libraries before you fork out your cash to get the key signed.

    Hope this is of some help.

    Cheers.

    M
     
  18. Erel

    Erel Administrator Staff Member Licensed User

  19. HankAllen

    HankAllen New Member Licensed User

    Unable to access "test1.db"

    When I try to run the SQL sample project, I get error says I'm unable to open the database file. This makes no sense because I am an admin user on this PC.
    Any help would be greatly appreciated.
     
  20. Erel

    Erel Administrator Staff Member Licensed User

    Do you get this error in the emulator?
     
Loading...