Database example using DBUtils

[Tom Law]

A newbie here who could use some help. I am trying to design a database (based on SQLite and using DBUtils). The interface was no problem and creating the SQLite datafile was a breeze but as a VBNet user I am having some difficulty with the Basic4Android terminology.

Could someone point me towards a database example that thoroughly demonstrates the use of DBUtils.?

I am currently trying to find information on the 'ExecuteMap' function. (Would I be correct in assuming that a 'Map' is akin to a Datarow and that a ListofMaps represents a Dataset) or am I barking up the wrong tree?

Any help would be most appreciated

Tom

 

[Erel]

I guess that you saw the example which comes with DBUtils module.
ExecuteMap is demonstrated in Sub FillGradesTable:

Sub FillGradesTable
   'Building this table is a little bit more complicated.
   'We will create 20 tests and for each test we will give each student a grade.
   'We need to first get the list of possible student IDs.
   Dim Table As List
   Table = DBUtils.ExecuteMemoryTable(SQL, "SELECT Id FROM Students", Null, 0)
   'Table is a list of arrays. Each array holds a single item.
   Dim Cols() As String
   Dim ListOfMaps As List
   ListOfMaps.Initialize
   For test = 1 To 20
      For student = 0 To Table.Size - 1
         Dim m As Map
         m.Initialize
         Cols = Table.Get(student)
         m.Put("Id", Cols(0))
         m.Put("Test", "Test #" & test)
         m.Put("Grade", Rnd(0, 101)) 'The upper value is exclusive
         ListOfMaps.Add(m)
      Next
   Next
   DBUtils.InsertMaps(SQL, "Grades", ListOfMaps)
End Sub

A Map is exactly like .Net Hashtable or Dictionary. In this case the keys are the column names and the values are the values that should be assigned.

 

[Tom Law]

Hi Erel,

Yes thanks for that, it does provide a little help however for someone coming into Basic4Android it is somewhat limited.

I have been struggling to come to terms with DBUtils as I believe that it probably represents the best way to approach database creation in this medium however the lack of real life examples (that I have so far managed to find) make it difficult to progress. No doubt I will get there in the end, but it would be so much easier with a decent size comprehensive project as an example (i.e. one that uses all of the functions available).

Tom

 

[Erel]

DBUtils is not always the best way. It contains a set of methods for common tasks. The advantage of code modules is that you can also look into the code, learn from it and also modify it as you need.

 

[Mashiane]

DbUtils Map Error

Hi Erel

I am using your DbUtils module but get an error when I am running my application on Samsung SIII, below is my code.

' Procedure to read the site details from the Sites table
Sub SiteRead(sSiteID As String)
If sSiteID = "" Then Return
' check if a site exists, if so display record
Dim m As Map
m = DbUtils.ExecuteMap(SQLite, "SELECT SiteId, SiteName, SiteMobileNo, LRation FROM Sites WHERE SiteId = ?", _
Array As String(sSiteID))
If m = Null Then
' the site is not found
'edtSiteName.Text = ""
'edtCellNo.Text=""
'edtLRatio.Text = ""
Else
' put the details in the site screen
' the map uses lowercase, so change fields to lowercase
edtSiteName.Text = m.Get("sitename")
edtCellNo.Text= m.Get("sitemobileno")
edtLRatio.Text = m.Get("lration")
End If
End Sub

The error starts on the line with...
edtSiteName.Text = m.Get("sitename")
and says java.lang.RuntimeException: Object should first be initialized (Map).

I have compared my example to your student scores example and everything looks fine. Can you please advise as this works perfectly on the IDE.

Thanks a lot.

 

[JakeBullet70]

Your code looks good.
I would check your database, I am thinking what you have in the IDE is not what is on the device.

 

[Erel]

DBUtils.ExecuteMap returns an uninitialized Map when no results are found.

You should check:

If m.IsInitialized Then ...

 

[Mashiane]

Thanks Guys. I have another question regarding indexes. I have created this method.

' create an index on a table.
Sub Table_CreateIndex(TableName As String, FieldNames As String)
Dim spFields() As String = Regex.Split(",", FieldNames)
For i = 0 To spFields.Length -1
Dim field, sQry As String
field = spFields(i).trim
If field.Length > 0 Then
sQry = "CREATE INDEX IF NOT EXIST " & TableName & "_" & field & " ON [" & TableName & "] (" & field & ")"
Log("CreateIndex: " & sQry)
'SQLite.ExecNonQuery(sQry)
End If
Next
End Sub

...

but for some reason I get a null exception error when I run it, thus having commented the ExecNonQuery line. Can someone help please.

 

[Erel]

Please use [ code ] [ /code ] tags (without spaces) when posting code.

Which line raises this error?

 

[Mashiane]

Ok, thanks for the advice, was not aware. Its the SQLite.ExecNonQuery(sQry) line.