Android Tutorial SQL tutorial

Status
Not open for further replies.
Update 2018:

New video tutorial:


The source code is available here: https://www.b4x.com/android/forum/t...ming-video-tutorials.88787/page-2#post-577932

Async methods: [B4X] SQL with Wait For

The following tutorial is obsolete:

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).
B4X:
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:
B4X:
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.

B4X:
Sub FillSimpleData
    SQL1.ExecNonQuery("INSERT INTO table1 VALUES('abc', 1, 2)")
    SQL1.ExecNonQuery2("INSERT INTO table1 VALUES(?, ?, ?)", Array As Object("def", 3, 4))
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.

B4X:
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).

B4X:
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:
B4X:
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.
B4X:
Sub InsertBlob
    Dim Buffer() As Byte = File.ReadBytes(File.DirAssets, "smiley.gif")
    '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.

B4X:
Sub ReadBlob
    Dim Cursor1 As Cursor = SQL1.ExecQuery2("SELECT image FROM table2 WHERE name = ?", Array As String("smiley"))
    Cursor1.Position = 0
    Dim Buffer() As Byte = 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:
B4X:
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.
B4X:
For i = 1 To 10000
        sql1.AddNonQueryToBatch("INSERT INTO table1 VALUES (?)", Array As Object(Rnd(0, 100000)))
Next
sql1.ExecNonQueryBatch("SQL")
...
Sub SQL_NonQueryComplete (Success As Boolean)
    Log("NonQuery: " & Success)
    If Success = False Then Log(LastException)
End Sub
 

Attachments

  • SQL.zip
    8.1 KB · Views: 11,838
Last edited:

nemethv

Member
Licensed User
Longtime User
Hello,

I'm struggling with reading stuff from an sqlite db file that I've already created outside the app. The file doesn't need to be edited/modified/etc in the app.
Im trying to open the file, but the code SQL1.Initialize(File.DirAssets,"lineinfo.db",False) - fails, I get an errormessage on this when trying to do it saying "database not available". The file is in the files tab. SQL is enabled/ticked.

The overall aim would be to create a list with information extracted from the SQL file.
In theory, it would be something like:

B4X:
Sub Globals

   Dim LineList As ListView
   Dim SQL1 As SQL
   Dim Cursor1 As Cursor ' not sure what to do with this?
End Sub


Sub Activity_Create(FirstTime As Boolean)
   
   Activity.Color = Colors.white
   If SQL1.IsInitialized = False Then 
      SQL1.Initialize(File.DirAssets,"lineinfo.db",False) ' this one fails
   End If   
   
    
   LineList.Initialize("LineList")
   LineList.Visible=True:LineList.Clear
   
   LineList.AddSingleLine(SQL1.ExecQuerySingleResult("SELECT DISTINCT LineName FROM lineinfo")) 'dont think this works either, but you get the gist of what i need hopefully...
   
   
    Activity.AddView(LineList, 0, 0, 100%x, 100%y)
End Sub

At the end I'd like the list to return results like "Line A", "Line B"...
Any thoughts?

Thank you

ps. i might not reply until the end of the coming long weekend, but I'll do my best to do so.
 
Last edited:

nemethv

Member
Licensed User
Longtime User
You cannot open a database in the assets folder. This is a virtual read-only folder located inside the APK file. You will need to first copy the database to a writable location. You can use DBUtils for that task.

Thanks! Worked :)
 
Last edited:

zekigultekin

Member
Licensed User
Longtime User
How can add SQL library to my basic4android project

Dear Erel,
I want use SQL in my project. When I define Dim SQL1 as SQL then I cannot see SQL reference. ;)

How can I add SQL library reference to my project.

Best regards,
 

McJaegs

Member
Licensed User
Longtime User
So I have my database created, but where in my project folder do I need to place it in order for it to be accessed?

I am also trying to populate a spinner with unique values from one column of the database. What would be the code to do that? Right now I have this

Cursor1 = RadioSQL.ExecQuery("SELECT DISTINCT Genre From Chicago")

If CitySpinner.SelectedItem = "Chicago" Then
GenreSpinner.AddAll(Cursor1)
 
Last edited:

McJaegs

Member
Licensed User
Longtime User
I recommend you to use DBUtils: Basic4android Search: DBUtils

It will help you put the database in the correct folder and it also includes an ExecuteSpinner method.

Ok, thanks. I have that now, but now when I try to run my program I get the error: java.lang.ClassCastException:android.database.sqlite.SQLiteCursor

I'm not sure what is causing this.
 

nemethv

Member
Licensed User
Longtime User
Hi,

I'm trying to simplify a piece of code that i've been using.
The code I'm doing now is:
B4X:
LocalLineCount = SQL1.ExecQuerySingleResult("SELECT count(DISTINCT LineName) FROM lineinfo Where StationName = '"&whatever&"')
   For i = 1 To LocalLineCount
      LocalLineList.AddSingleLine(SQL1.ExecQuerySingleResult("SELECT DISTINCT LineName FROM lineinfo WHERE LocalLineID = " & i ))
   Next

I was wondering if there is any way to replace the last part (&i) with something auto-incremental because at the moment I have to put zillions of various IDs into my data tables rather than just actually somehow saying, "select the next line after the one you've already selected".
In other terms, what I'm trying to achieve is that there are certain stations on the underground network that serve more than one line. Rather than having to assign an ID for each line on each station and then call the LocalLineID as above, I want to avoid and automate that process somehow. Is it possible?
Thanks
 
Last edited:

poseidon

Member
Licensed User
Longtime User
re

I couldnt understand exactly... but you can make it one (?)

LocalLineCount = SQL1.ExecQuerySingleResult("SELECT count(DISTINCT LineName) FROM lineinfo Where StationName = '"&whatever& "' and
LocalLineID = " & i)

or something like :

LocalLineCount = SQL1.ExecQuerySingleResult("SELECT count(DISTINCT LineName) FROM lineinfo Where StationName = '"&whatever& "' and
LocalLineID > " & i)

(?)
 

nemethv

Member
Licensed User
Longtime User
I couldnt understand exactly... but you can make it one (?)

LocalLineCount = SQL1.ExecQuerySingleResult("SELECT count(DISTINCT LineName) FROM lineinfo Where StationName = '"&whatever& "' and
LocalLineID = " & i)

or something like :

LocalLineCount = SQL1.ExecQuerySingleResult("SELECT count(DISTINCT LineName) FROM lineinfo Where StationName = '"&whatever& "' and
LocalLineID > " & i)

(?)

I'm hoping avoid having having to use IDs in general. What I'd like is that if we know that the distinct count of stations is say 7 and then in a command I say select distinct stations, then I'd get 7 names. Then I'd want to return the first, second, nth. (rather having a prefixed ID value)
 

poseidon

Member
Licensed User
Longtime User
B4X:
LocalLineCount = SQL1.ExecQuerySingleResult("SELECT count(DISTINCT LineName) FROM lineinfo Where StationName = '"&whatever&"')


'forget this    For i = 1 To LocalLineCount
       Cursor=SQL1.ExecQuery("SELECT DISTINCT LineName FROM lineinfo WHERE (LocalLineID > 0 and  LocalLineID <" & LocalLineCount+1 & ")")
'forget this    Next

^so only 2SQL will be executed but then you have to make a loop to add at listview

for i=0 to  Cursor.Count-1
 LocalLineList.AddSingleLine(Coursor.GetString2(0))
next


for sure is more faster.


Generic Cursor Sample :
B4X:
   Dim SQLReader As Cursor
   Dim i As Int
    SQLReader = SQL1.ExecQuery("SELECT id,Code, Name FROM CONTACTS order by Name")

    For i = 0 To SQLReader.RowCount - 1
        SQLReader.Position = i
      
      lstv.AddTwoLines2(SQLReader.GetString("Code"),SQLReader.GetString("Name"),SQLReader.GetString("id"))
    Next
    SQLReader.Close
 
Last edited:

nemethv

Member
Licensed User
Longtime User
Found it

B4X:
For a = 0 To LineCount -1
   CurrentLineName = SQL1.ExecQuerySingleResult("SELECT DISTINCT LineName FROM LineInfo ORDER BY LineName LIMIT 1 OFFSET " & a)
Next
 

ltrebuchet

Member
Licensed User
Longtime User
Dir.Assets

My DBFile is included within the Files of my project, but it is not found File.DirAssets.
What did I miss?
Thank You
 

jeeradate

Member
Licensed User
Longtime User
Question relate to Cursor read data

From the code of the example as below

B4X:
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
The code "Cursor1 = SQL1.ExecQuery("SELECT col1, col2, col3 FROM table1")" read all the records in the table and store in "Cursor1" or read nothing
or read one record with this code "Cursor1.Position = i"

Would be kind to advice me?
:sign0104:
 

klaus

Expert
Licensed User
Longtime User
Cursor1 = SQL1.ExecQuery("SELECT col1, col2, col3 FROM table1")
Reads col1, col2 and col3 from all records in the database and stores them in the Cursor1 object.

Cursor1.RowCount
Holds the number of records (rows).

Cursor1.Position = i
Sets the index of the record (row), the first index = 0.

Cursor1.GetString("col1")
Gets the value of column col1 for the given row in Cursor1.Position = i.

Did you have a look at chapter 3 SQLite Database in the User's Guide ?

Best regards.
 

jeeradate

Member
Licensed User
Longtime User
Thank you for your kind response.

Both Guide is very helpful for me.
Thank you for your hard work of making these Guide.
:sign0098:
 
Status
Not open for further replies.
Top