B4A Library SQLExtended an extension to the SQL Library

keirS

Well-Known Member
Licensed User
SQLExtended wraps methods in android.android.database.sqlite that are not wrapped in the standard SQL library. Mainly the insert, update and delete convenience methods but also some additional functions as well. To make these methods work I also wrapped the ContentValues class which is called ColumnValues in the library.

The Reflection library is required to initialize the SQLExtended object as it gets the private DB field (SQLiteDatabase object) from the wrapper as can be seen in the code below.

B4X:
Sub Process_Globals
 
    Dim oSQL As SQL
    Dim oSQLE  As SQLExtended
End Sub

Sub Globals

End Sub

Sub Activity_Create(FirstTime As Boolean)
    'Do not forget to load the layout file created with the visual designer. For example:
    'Activity.LoadLayout("Layout1")
    Dim iRowId As Int
    Dim iUpdateCount As Int
    Dim iDeleteCount As Int
    Dim oCursor As Cursor
    File.Copy(File.DirAssets,"classicmodels.db",File.DirDefaultExternal,"classicmodels.db")
    oSQL.Initialize(File.DirDefaultExternal,"classicmodels.db",True)

    'Initalize SQLExtended object by using the reflection library to get the private db field of the SQL object.
    Dim R As Reflector
    R.Target = oSQL
    R.Target = R.GetField("db")
    oSQLE.Initialize(R.Target)


    'Setup ColoumnValues for insert
    Dim cvInsert As ColumnValues

    cvInsert.putString("productCode","S72_5678")
    cvInsert.putString("productName","HMS Belfast")
    cvInsert.putString("productLine","Ships")
    cvInsert.putString("productScale","1:700")
    cvInsert.putString("productVendor","Unimax Art Galleries")
    cvInsert.putString("productDescription","Famous WW2 Cruiser")
    cvInsert.putInt("quantityInStock",10)
    cvInsert.putDouble("buyPrice",20.95)
    cvInsert.putDouble("MSRP",31.50)

   'Setup ColioumnValues  for update
    Dim cvUpdate As ColumnValues
    cvUpdate.putDouble("buyPrice",30.95)
    cvUpdate.putDouble("MSRP",41.50)


    'Insert new row
    Log("Test Insert:")
    iRowId = oSQLE.insert("products",cvInsert)
    Log("Product " & cvInsert.getAsString("productCode") & " Inserted; Rowid = " & iRowId)

    'Query new row by it's rowid
    oCursor  = oSQL.ExecQuery2("SELECT *,rowid FROM products where rowid = ?",Array As String(iRowId))
    'Output record to log file
    CursorToLog(oCursor)

    Log("")
    Log("Test Update:")
    'unpdate new row
    iUpdateCount =  oSQLE.update("products",cvUpdate,"productCode = ?",Array As String("S72_5678"))
    Log("Updated " & iUpdateCount & " Row(s)")
    'Query new record by it's rowid
    oCursor  = oSQL.ExecQuery2("SELECT *,rowid FROM products where rowid = ?",Array As String(iRowId))
    'Output row to log file
    CursorToLog(oCursor)

    Log("")
    Log("Test Delete:")
    iDeleteCount  =  oSQLE.delete("products","productCode = ?",Array As String("S72_5678"))
    'delete the new record
    Log(iDeleteCount & " Row(s) Deleted")
  
    'Check row is deleted
    oCursor  = oSQL.ExecQuery2("SELECT *,rowid FROM products where rowid = ?",Array As String(iRowId))
    Log(oCursor.RowCount & " Row(s) selected for RowID " & iRowId)
  
    'Check Column/Value map functionality
     TestColumnValues
  
    'Check other functions
    Log(" ")
    Log("Check Other Functions:")
    Log("Set WAL Enabled: " & oSQLE.enableWriteAheadLogging)
    Log("Check WAL Enabled :" & oSQLE.isWriteAheadLoggingEnabled)
    oSQLE.disableWriteAheadLogging
    Log("Check WAL Disabled:"  & oSQLE.isWriteAheadLoggingEnabled)
    Log("Enalbe FK Constraints")
    oSQLE.ForeignKeyConstraintsEnabled = True
    Log("Disalbe FK Constraints")
    oSQLE.ForeignKeyConstraintsEnabled = False
End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub

Sub CursorToLog(oCursorToLog As Cursor)
Dim iRowCount As Int
Dim iColumnCount As Int
Log(" ")
For iRowCount = 0 To oCursorToLog.RowCount - 1
   oCursorToLog.Position = iRowCount
   For iColumnCount = 0 To oCursorToLog.ColumnCount -1
       Select oSQLE.getColumnType(oCursorToLog,iColumnCount)
      Case 0
         Log(oCursorToLog.GetColumnName(iColumnCount) & " : " & "NULL")
      Case 1
          Log(oCursorToLog.GetColumnName(iColumnCount) & " : " & oCursorToLog.GetInt2(iColumnCount))
      Case 2
           Log(oCursorToLog.GetColumnName(iColumnCount) & " : " & oCursorToLog.GetDouble2(iColumnCount))
      Case 3
           Log(oCursorToLog.GetColumnName(iColumnCount) & " : " & oCursorToLog.GetString2(iColumnCount))
      Case 4
           Log(oCursorToLog.GetColumnName(iColumnCount) & " : " & "BLOB")
  
      End Select
  
   Next
   Log("  ")
Next



End Sub

Sub TestColumnValues
Dim oColumnValues As ColumnValues
Dim sByteString As String
Dim icntr As Int

Log(" ")
Log("Test Column Values:")
oColumnValues.putBoolean("Boolean",True)
oColumnValues.putByte("Byte",127)
oColumnValues.putByteArray("ByteArray",Array As Byte(1,2,126,127))
oColumnValues.putDouble("Double",2222234.46)
oColumnValues.putFloat("Float",999999999999.99)
oColumnValues.putInt("Int",99)
oColumnValues.putLong("Long",999999999)
oColumnValues.putNull("Null")
oColumnValues.putShort("Short",137)
oColumnValues.putString("String","A String")

Log(" ")
Log("Test getMethods:")
Log(oColumnValues.getAsBoolean("Boolean"))
Log(oColumnValues.getAsByte("Byte"))
Dim aByteArray() As Byte
aByteArray = oColumnValues.getAsByteArray("ByteArray")

For icntr = 0 To aByteArray.Length - 1
If sByteString.Length = 0 Then
    sByteString = sByteString & aByteArray(icntr)
Else
    sByteString = sByteString & "," & aByteArray(icntr)
End If

Next


Log(sByteString)

Log(oColumnValues.getAsDouble("Double"))
Log(oColumnValues.getAsFloat("Float"))
Log(oColumnValues.getAsInt("Int"))
Log(oColumnValues.getAsLong("Long"))
Log(oColumnValues.getAsShort("Short"))
Log(oColumnValues.getAsString("String"))

Log("  ")
Log("Key/Value Pairs:")
For cntr = 0 To  oColumnValues.size - 1
Log(oColumnValues.getKeyAt(cntr) & ": " & oColumnValues.getValueAt(cntr))
Next


End Sub
To install the library download the zip and extract it. Copy the contents of the Library folder to your additional libraries folder.
 

Attachments

Last edited:

rboeck

Well-Known Member
Licensed User
Hi keirS,

today i had strange update problems in sqlite; when i updated a record and read back, the new version was ok; when i left the app and restarted, the previous state was there.
Now i tried the WAL logging feature and the problem is away. But i do not really understand what the problem really is...

Thanks for your work!
Reinhard
 

keirS

Well-Known Member
Licensed User
Sorry I missed your post. I think it maybe something to do with how the default SQLite Journal works. It will rollback some times if the user pauses the app because a transaction has not been ended. You need to code for this by deciding what to do if the user pauses or exits the app while in a transaction.

If you read the result of an update while in a transaction you will see the update because it is looking at a result from a buffered file. If for any reason that buffer cannot be committed to the DB file or the transaction cannot be committed then rollback journal is used to rollback the data to it's original state.

So how do you tell if your are currently in a transaction?

I have added the inTransaction method to the library which will return true if the process it is called from is in a transaction. I don't tend to use this much because I tend to put all my database related code in service modules.
 
Top