Questions: Reading tables from SQLite DB into map

[PlayboyOKC]

I need to read these settings in from a SQLite table that I created... one, I can't figure out what I am doing wrong, or what is the proper way to read in all the values in a table... two, this is kinda practice for later on in my program. If I can figure th's out, I can really get moving.

I have tried tons of combinations/variations, maps, lists, etc. Can someone please show me the correct way to read this table into map variables.

I have heavily commented the code for clarification and log results.

THANK YOU, and sorry in advance... not a "COMPLETE NOOB", even though I feel like it at this point.

Sub Read_tblSettings
   'This is the values I used to fill the SQL table with earlier...
   'listSettingName.AddAll(Array As String("varDebugState", "Test"))
   'listSettingValue.AddAll(Array As String("1", "Test Text"))
   '
   'SQL1 Table tblSettings overview:
   'Cols:      SettingName      |   Value
   'Row 1:      varDebugState   |   1
   'Row 1:      Test         |   Test Text

   'Test a webview - This works, so I know the table is filled correctly...
   '   webviewSettings.Visible = True
   '   webviewSettings.LoadHtml(DBUtils.ExecuteHtml(SQL1, _
   '      "SELECT SettingName, Value FROM tblSettings", _
   '      Null, 0, True))

   Dim listSettingName As List
   Dim listSettingValue As List
   listSettingName.Initialize
   listSettingValue.Initialize

   'This reads out correctly
   listSettingName.AddAll(Array As String(DBUtils.ExecuteMemoryTable(SQL1, "SELECT SettingName FROM tblSettings", Null, 0)))
   listSettingValue.AddAll(Array As String(DBUtils.ExecuteMemoryTable(SQL1, "SELECT Value FROM tblSettings", Null, 0)))
   Log("Read_tblSettings - listSettingName: " & CRLF & listSettingName)
      'Log shows...
      'Read_tblSettings - listSettingName: 
      '(ArrayList) [(ArrayList) [[Ljava.lang.String;@40b352d8, [Ljava.lang.String;@40b35520]]
   Log("Read_tblSettings - listSettingValue: " & CRLF & listSettingValue)
      'Log shows...
      'Read_tblSettings - listSettingValue: 
      '(ArrayList) [(ArrayList) [[Ljava.lang.String;@40b36b88, [Ljava.lang.String;@40b36c70]]
    
   'Dim listsize As Int
   'listsize = listSettingName.Size
   For   i = 0 To listSettingName.Size-1
      Log("Read_tblSettings - Setting Name and Value: " & CRLF & listSettingName.Get(i) & " - " & listSettingValue.Get(i))
      'Log shows...
      'Read_tblSettings - Setting Name AND Value: 
      '(ArrayList) [[Ljava.lang.String;@40b352d8, [Ljava.lang.String;@40b35520] - (ArrayList) [[Ljava.lang.String;@40b36b88, [Ljava.lang.String;@40b36c70]

      'mapSettings is a Process Global that is already initialized      
      mapSettings.Put(listSettingName.Get(i), listSettingValue.Get(i))
   Next
   'This returns null for varDebugState ? ! ? ! ? !
   Log("mapSettings.Get('varDebugState') now: " & mapSettings.Get("varDebugState"))
End Sub

 

[PlayboyOKC]

Solved? with...

Fixed with this... not sure if this solution is actually proper though (fixing the above coding may be a better solution)... this method causes more SQL queries, possibly slowing the device/results on larger tables/databases, correct?

Sub Read_tblSettings
   Dim listsize As Int
   Dim Cursor As Cursor
   listsize = SQL1.ExecQuerySingleResult("SELECT count(*) FROM tblSettings")
   Cursor = SQL1.ExecQuery("SELECT SettingName, Value FROM tblSettings")
   For i = 0 To listsize-1
      Cursor.Position = i
      mapSettings.Put(Cursor.GetString("SettingName"), Cursor.GetString("Value"))
   Next
End Sub

 

[Erel]

Have you tried DBUtils: Basic4android Search: DBUtils ?

DBUtils.ExecuteMap or ExecuteMemoryTable should be helpful.

 

[Djembefola]

this method causes more SQL queries...

You don't need the first query. You can use cur.Rowcount instead.

Sub Read_tblSettings
  Dim cur As Cursor
  cur = SQL1.ExecQuery("SELECT SettingName, Value FROM tblSettings")
  For i = 0 To cur.RowCount-1
    cur.Position = i
    mapSettings.Put(cur.GetString("SettingName"), cur.GetString("Value"))
  Next
End Sub

 

[PlayboyOKC]

Working!

Thanks Djembefola - that works perfect.

 

[Ricky D]

ExecuteMap

Erel, I don't understand the third parameter of the ExecuteMap sub in dbUtils.

I haven't seen a proper example.

Please help.

I'd just like to get all IDs out of my IDs table

regards, Ricky

 

[Erel]

The third parameter is useful for parameterized queries.
This example if from the DBUtils example:

   m = DBUtils.ExecuteMap(SQL, "SELECT Grade FROM Grades WHERE id = ? AND test = ?", _
      Array As String(spnrStudentId.SelectedItem, Value))

 

[Ricky D]

what if no parameters?

Erel,

How do execute a simple Select * From IDs

??

Ricky

 

[Erel]

m = DBUtils.ExecteMap(SQL, "SELECT * FROM ids", Null)

 

[Ricky D]

now another

Thanks. That now works.

How do I create public static classes?

regards, Ricky

 

[Erel]

Please start a new thread for this question.

 

[aklisiewicz]

Studying this thread and I'm confused as well !

The third parameter is useful for parameterized queries.
This example if from the DBUtils example:

    m = DBUtils.ExecuteMap(SQL, "SELECT Grade FROM Grades WHERE id = ? AND test = ?", _
        Array As String(spnrStudentId.SelectedItem, Value))

Erel,
can you explain
what is: id = ?
what is: , _

I do not see any info about this :-(
Since DBUtils is used so often it should be much better documented.

Art

 

[Erel]

1. This is a parmeterized query. See the SQL tutorial: SQL tutorial

2. This is a line continuation character. It allows you to break a long line to multiple lines.

 

[aklisiewicz]

I have seen SQL tutorial but still some parameters are not clear (simply because they are not mentioned in the manual or elsewhere. I would appreciate if you can point me to some place where I can find details. For example line like this:

Table = DBUtils.ExecuteMemoryTable(SQL1, Query1, Null, 0)

I was not able to find any information what are the last 2 parameters

----------------------------------------------------------------------

I went through Manual and didn't see anywhere information about breaking the line - it would be god to mention this somewhere so people will not waste time pulling their hair out, trying to figure it out.

So to break down the line I put ",_" or just "_" ?
How does it relate to "Word Wrap" setting in IDE. Another words why would I use this method if I can turn on WordWrap?

Thanks - Arthur

 

[Erel]

Please do not post duplicate questions.

So to break down the line I put ",_" or just "_" ?

Just an underscore.

This is useful if you are not using word wrap mode.

 

[aklisiewicz]

thanks it is clear now, but I would strongly suggest to place this information somewhere in the manual.
Arthur

 

[Olaan]

Hi all,
I think that you forget the important line.

Sub Read_tblSettings
  Dim cur As Cursor
  mapSettings.initialize '<== You forget it
  cur = SQL1.ExecQuery("SELECT SettingName, Value FROM tblSettings")
  For i = 0 To cur.RowCount-1
    cur.Position = i
    mapSettings.Put(cur.GetString("SettingName"), cur.GetString("Value"))
  Next
End Sub