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

ChrShe

Member
Licensed User
Longtime User
I'm sorely unfamiliar with SQL Lite. Can we use an .mdf database file created in SQLExpress?

Thanks much!
 

sioconcept

Active Member
Licensed User
Longtime User
Hi,
I have a sqlite database encrypted, I would leave it as is (without decrypting them into a new file). The goal would be to decrypt the database and use it directly from memory for SQL queries. This may be read-only.
Is it possible to use the library through an object or variable without a file? My problem is that the database must not be copied if it is clear and I supose that hacking the tablet / phone that database can be copied ...
An idea?
 

Phil

Member
Licensed User
Longtime User
SQLite and MS Access Date conversion

I had a hard time finding relevant information about converting date fields to and from Access and SQLite format. I have finally come up with a couple of solutions. Not much but I thought some of you might find them useful.

‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
This sqlite query will convert all "YourDate" field data in an SQLite database from Access short date format (MM/DD/YYYY) to
SQLite format (YYYY-MM-DD) Make sure you backup the database first, just in case the results are not what you intended.

Query:
"UPDATE Table SET YourDate = substr(YourDate, 7) || "-" || substr(YourDate,1,2) || "-" || substr(YourDate, 4,2);"

‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’


This B4A subroutine will Convert a date string from Access short date format to SQLite date format and vice-versa

Sub ConvertDateStr (DatetoConvert As String) As String

Dim DateParsed(), OutputStr As String
If DatetoConvert.CharAt(2) = "/" Then 'it's in Access short date format
'convert date to SQLite format (YYYY-MM-DD) from Access short date format (MM/DD/YYYY)
DateParsed = Regex.Split("/", DatetoConvert)
OutputStr=DateParsed(2) & "-" & DateParsed(0) & "-" & DateParsed(1)
'date is now in YYYY-MM-DD format
Else If DatetoConvert.CharAt(4) = "-" Then 'it's in SQLite date format
'convert date from SQLite format (YYYY-MM-DD) to Access short date (MM/DD/YYYY)
DateParsed = Regex.Split("-", DatetoConvert)
OutputStr=DateParsed(1) & "/" & DateParsed(2) & "/" & DateParsed(0)
'date is now in MM/DD/YYYY format
Else
Msgbox("Cannot convert date.", "Format not Recognized")
Return
End If

Return OutputStr

End Sub

‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
Usage:
'to convert an Access short date to SQLite format
Dim SqlDate As String
SqlDate = ConvertDateStr(AccessDate)
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
 

Phil

Member
Licensed User
Longtime User
Formatting Datedialog output

:signOops::sign0104:
Note:

If you use the DateDialog to set a date then try to use this sub to convert it you will need to format the Datedialog output first.

sDate= NumberFormat(Dd.Month,2,0) & "/" & NumberFormat(Dd.DayOfMonth,2,0) & "/" & Dd.Year

That reformats the string so that month and day are 2 digits. ie 01,02, etc

Example:

Dim sDate As String
Dim Dd As DateDialog
Dd.Year = DateTime.GetYear(DateTime.Now)
Dd.Month = DateTime.GetMonth(DateTime.Now)
Dd.DayOfMonth = DateTime.GetDayOfMonth(DateTime.Now)
ret = Dd.Show("Set date", "Choose Date", "OK", "Cancel", "", Null )

If ret = DialogResponse.CANCEL Then
Return
Else
sDate= NumberFormat(Dd.Month,2,0) & "/" & NumberFormat(Dd.DayOfMonth,2,0) & "/" & Dd.Year
end if
 

darso

New Member
Licensed User
Longtime User
The SQL library is shiped with Basic4Android it is in the Library folder of Basic4Android.

Best regards.
My shiped SQL Library is version 1.02 (and B4A ver 2.25 ). ExecQueryAsync or ExecNonQueryBatch cannot be used on that version.
 

Carcas

Member
Licensed User
Longtime User
Hi :)

I have this problem:

example SQL.zip runs properly but my app dont runs ......


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.DirInternal, "Prova1.db", True)
End If
CreateTables
End Sub
Sub CreateTables
SQL1.ExecNonQuery("DROP TABLE IF EXISTS Posizione")

SQL1.ExecNonQuery("CREATE TABLE table1 (Pos INTEGER , Orient INTEGER, col3 INTEGER)")

End Sub

i have this error......

Parsing code. 0.00
Compiling code. 0.05
Compiling layouts code. 0.00
Generating R file. 0.13
Compiling generated Java code. 1.01
Convert byte code - optimized dex. 0.69
Packaging files. 0.30
Copying libraries resources 0.00
Signing package file (debug key). 1.15
ZipAlign file. 0.12
Installing file to device. Error
pkg: /data/local/tmp/result_DEBUG.apk
Failure [INSTALL_FAILED_VERSION_DOWNGRADE]

Restarting ADB Server may solve this problem. 'I done

Tools - Restart ADB Server. Device serial: emulator-5554
 

aklisiewicz

Active Member
Licensed User
Longtime User
Cursor1.GetString("col1")

Gets the value of column col1 for the given row in Cursor1.Position = i.

Best regards.

Ok, so how would I read several columns?
I tried


Cursor1.Position = i
Cursor1.GetString("col1","Col2","Col3")

but it doesn't seem to work. I simply want to read one record with a given ID (or CursorPosition) and then display theta on the screen.
For some reason I'm finding such simple task very tedious and complicated to implement using B4A. While working with desktop Apps
the steps involved were: 1)reading the record at a given ID (usually Pk) 2)assigning value from the record Column to the screen variable 3)displaying screen variable in (either editable or read only screen field). I wonder how this co-relates in B4A. One thing I noticed is that all examples are (a)-very fragmented - that means it is hard to find (b)-have added unnecessary complexity. I understand that keeping a FileName, Path or other things as variables,arrays, maps etc. brings lots of flexibility, but at the same time it makes en example App more complex to study.
Here is an example. As a newbie I would like to see simple App which allows to Add/Change/Delete record, but I would like to see each activity done on a separate screen. For example I havea list of records, I select a record -> it opens a Form with record details. On that form I have a 2 buttons UPDATE and DELETE buttons which then either allow to update/delete record.
This is typical scenario for most of the data grid/forms. Then as more advanced App I would like to see this same App with the form which allows: store/update an image (as path), store/update an image as BLOB, store/update Map Location, store update MEMO() (TEXT) type fields.



. Then this same App can be brought to the next level to display record details (that would be Level2). Next this same App could have added Search capability (Level3), then perhaps Sorting capability (Level 4), then FormType activity which displays Parent/Child data (like DBUtils example does) (and that would be Level5).....etc.

This way user can study from the simplest scenario and have more and more features added. Right now many examples (although workable) fall into different context and different development styles, which makes all of us very confused. Those who came from VB might have a little easier, but there are people who are coming from different environments and languages with different concepts and work styles.
Despite I'm really grateful for all the documentation and example Apps (especially the Docs and tutorials from Klaus) I think it would be very beneficial to create on larger App and show hoe to implement from simple to very advanced features step by step.

Arthur
 
Last edited:

Geezer

Active Member
Licensed User
Longtime User
Ok, so how would I read several columns?
I tried


Cursor1.Position = i
Cursor1.GetString("col1","Col2","Col3")

B4X:
cursor1.position = I
val1 = cursor.getstring("col1")
val2 = cursor.getstring("col2")
val3 = cursor.getstring("col3")

or direct to the views

B4X:
label1.text = cursor.getstring("col1")
edittext1.text = cursor.getstring("col2")

and so on
 

aklisiewicz

Active Member
Licensed User
Longtime User
Excellent, that's what I was looking for.
Thanks a lot.

Now, how would I handle a this ?
database = SQlite
I need to display text field with multiple lines (I think it is a counterpart of MEMO() from MySQL) ?

My 2nd column is TEXT() type so when I try to assign values from database

B4X:
strDesc = Cur.GetString("col2")



I get an error:
src\B4A\UserInterfaceTabHost\issuedetails.java:475: inconvertible types
found : java.lang.String
required: android.widget.TextView
mostCurrent._strdesc.setObject((android.widget.TextView)(mostCurrent._cur.GetString("col2")));


Art
 
Last edited:

aklisiewicz

Active Member
Licensed User
Longtime User
yes, but when I do this I can compile with no errors, then when I run it get type mismatch Error

the column in SQLite is TEXT() type, not the CHAR() or VARCHAR()

Art
 

johnaaronrose

Active Member
Licensed User
Longtime User
SQL Query Prepared Statements

I seem to remember from my Oracle days that it had the facility for SQL Queries to use Prepared Statements (I think that they were called 'using parameterized values'). They had the advantage of preventing SQL Injection. Are they still available with B4A on SQLite databases?
 
Status
Not open for further replies.
Top