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.
To install the library download the zip and extract it. Copy the contents of the Library folder to your additional libraries folder.
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 SubTo install the library download the zip and extract it. Copy the contents of the Library folder to your additional libraries folder.
Attachments
Last edited:
