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: 12,089
Last edited:

scrat

Active Member
Licensed User
Longtime User
Hi,

How open a sqlite database in readonly mode ?

B4a expose OPEN_READWRITE and CREATE_IF_NECESSARY but not OPEN_READONLY

Thanks
 

scrat

Active Member
Licensed User
Longtime User
It's not for reading from assets folder

I try with this in line java

B4X:
#If Java
import android.database.sqlite.SQLiteDatabase;

public static  SQLiteDatabase openDBro (String P){
   return SQLiteDatabase.openDatabase(P,null,1);
}

#end if

but how to cast android.database.sqlite.SQLiteDatabase to anywheresoftware.b4a.sql.SQL
 

scrat

Active Member
Licensed User
Longtime User
thank you Erel. problem solved by adding a function intitializeReadOnly to your library
 

scrat

Active Member
Licensed User
Longtime User
Ok,
I did not know that it is allowed to share a B4A modified library.

Just 2 lines added to original Sql.jar
B4X:
public void InitializeReadOnly(String Dir, String FileName)
{
   this.db =   SQLiteDatabase.openDatabase(new File(Dir, FileName).toString(), null, 1 | 0x10);
}

Now it's possible to open a Sqllite database in readonly mode with InitializeReadOnly(Dir,FileName)
 

Attachments

  • Sql_RO.zip
    7.3 KB · Views: 615

Mahares

Expert
Licensed User
Longtime User
Now it's possible to open a Sqllite database in readonly mode with InitializeReadOnly(Dir,FileName)
This code returns an error:
B4X:
Sub Process_Globals
    Dim sql As SQLperso
End Sub
If sql.IsInitialized =False Then
    sql.InitializeReadOnly(File.DirRootExternal & "/test","test.db")
End If
B4A version: 5.80
Parsing code. (0.00s)
Compiling code. (0.02s)
Compiling layouts code. (0.00s)
Generating R file. (0.10s)
Compiling generated Java code. Error
B4A line: 36
sql.InitializeReadOnly(File.DirRootExternal & \
javac 1.7.0_80
src\b4a\example\main.java:369: error: cannot find symbol
Debug.DebugWarningEngine.CheckInitialize(_sql);_sql.InitializeReadOnly(anywheresoftware.b4a.keywords.Common.File.getDirRootExternal()+"/test","test.db"); ^
symbol: method InitializeReadOnly(String,String)
location: variable _sql of type SQL

EDIT 3/27/2016 10 PM GMT: Found the problem: Only works if the native SQL library by Erel is unchecked. Therefore, only SQLperso must be checked; otherwise error.
 
Last edited:

Mikonios

Active Member
Licensed User
Longtime User
When "BeginTransaction / EndTransaction" is used as the DB is blocked, record-level, table-level or database-level ??
 
Last edited:

mtechteam

Member
Licensed User
Longtime User
Near the top of this thread, this statement is made:
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.

Rather than in a activity, I have done so in a module. However, when I get a error (a catch in a try structure caused by an SQL error), it seems that if I am in a different activity that where I initialized this object, the MsgBox call (displaying the error) hangs. Is this something I should not be doing, or are there other things at play here.
 

mangojack

Expert
Licensed User
Longtime User
Near the top of this thread, this statement is made:
Usually you will want to declare the SQL object as a process global object.
Since that was written , the Starter service has been introduced. See Here .... Ideally this is where you should now declare / initialize SQL object.
Secondly .. if the message box is used for debug purposes only .. avoid and use Log(...) instead.

If you have further problems it might be wise to start a new thread in questions , with some code or uploading a demo project.
 

Ricardo Gonzalez Gaete

Member
Licensed User
Longtime User
Existen campos auto numéricos en SqlLite, y como se crean por código, por favor...
sql1.execnonquery("ALTER TABLE VTATERRENO ADD COLUMNS ID INTEGER PRIMARY KEY AUTOINCREMENT")
 

klaus

Expert
Licensed User
Longtime User
As LucaMs already said, you cannot add a new coumn with the INTEGER PRIMARY KEY AUTOINCREMENT definition, see here.
Instead of an INTEGER PRIMARY KEY AUTOINCREMENT column you can use the SQLite internal column rowid which does the same.
You may have a look at chapters 5 SQLite Database and more specific 5.1.3 INTEGER PRIMARY KEY rowid in the B4A User's Guide.
 

luciano deri

Active Member
Licensed User
Longtime User
Hello everyone. I have an java error when convert blob in img.
B4X:
sub WriteBlob
InputStream1 As InputStream
File.Exists(Main.PthIO,"logo.jpg") Then
    InputStream1 = File.OpenInput(Main.PthIO,"logo.jpg")
    Dim OutputStream1 As OutputStream
    OutputStream1.InitializeToBytesArray(1000)
    File.Copy2(InputStream1, OutputStream1)
    buffer = OutputStream1.ToBytesArray
End If
    If buffer.Length > 0 Then
        Main.dbSql.ExecNonQuery("UPDATE aziende set logo = '" & Array As Object(buffer) & "' WHERE  codice = '04'")
    End If

end sub
This is the record on db after write
B4X:
codice         ragosoc                            logo
04    AZIENDA VINICOLA    [Ljava.lang.Object;@21a715da
B4X:
sub ReadBlob
Dim img As Bitmap
       Dim Buffer() As Byte 'declare an empty byte array
        Buffer = DbCurAzie.Getblob("logo")
        Dim i As Long
        Try
            i = Buffer.Length
        Catch
            i = 0
        End Try
        If i > 0 Then
            Dim InputStream1 As InputStream
            InputStream1.InitializeFromBytesArray(Buffer, 0, Buffer.Length)
            Try
             img.Initialize2(InputStream1) ' in this point exit with the error
            Catch
                Log(LastException)
            End Try
            InputStream1.Close
        End If

end sub
This is the error
java.lang.RuntimeException: Error loading bitmap.
at anywheresoftware.b4a.objects.drawable.CanvasWrapper$BitmapWrapper.Initialize2(CanvasWrapper.java:521)
at java.lang.reflect.Method.invoke(Native Method)
at java.lang.reflect.Method.invoke(Method.java:372)
at anywheresoftware.b4a.shell.Shell.runVoidMethod(Shell.java:753)
......
 
Last edited:

luciano deri

Active Member
Licensed User
Longtime User
if i do this query command
B4X:
    Main.dbSql.ExecNonQuery("UPDATE aziende set logo = " & Array As Object(buffer) & " WHERE  codice = '04'")
brake with this error

android.database.sqlite.SQLiteException: unrecognized token: "[Ljava.lang.Object;@26a95a5 WHERE codice = '04'" (code 1): , while compiling: UPDATE aziende set logo = [Ljava.lang.Object;@26a95a5 WHERE codice = '04'

i don't understund how do update with ExecNonQuery2
B4X:
dbSql.ExecNonQuery2("UPDATE aziende set('logo', ?) WHERE codice = '04'", Array As Object(buffer))
???
 
Status
Not open for further replies.
Top