Questions: Reading tables from SQLite DB into map

PlayboyOKC · Sep 25, 2011

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.

B4X:

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 · Sep 26, 2011

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?

B4X:

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 · Sep 26, 2011

Have you tried DBUtils: Basic4android Search: DBUtils ?

DBUtils.ExecuteMap or ExecuteMemoryTable should be helpful.

Djembefola · Sep 26, 2011

PlayboyOKC said:
this method causes more SQL queries...

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

B4X:

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 · Sep 27, 2011

Working!

Thanks Djembefola - that works perfect.

Ricky D · Jan 10, 2012

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 · Jan 10, 2012

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

B4X:

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

Ricky D · Jan 10, 2012

what if no parameters?

Erel,

How do execute a simple Select * From IDs

??

Ricky

Erel · Jan 10, 2012

B4X:

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

Ricky D · Jan 10, 2012

now another

Thanks. That now works.

How do I create public static classes?

regards, Ricky

Erel · Jan 10, 2012

Please start a new thread for this question.

aklisiewicz · Jun 12, 2013

Studying this thread and I'm confused as well !

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

B4X:

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 · Jun 12, 2013

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 · Jun 12, 2013

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 · Jun 13, 2013

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 · Jun 24, 2013

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

Theera · Jun 24, 2013

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

B4X:

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

Questions: Reading tables from SQLite DB into map

PlayboyOKC

New Member

PlayboyOKC

New Member

Erel

B4X founder

Djembefola

Active Member

PlayboyOKC

New Member

Ricky D

Well-Known Member

Erel

B4X founder

Ricky D

Well-Known Member

Erel

B4X founder

Ricky D

Well-Known Member

Erel

B4X founder

aklisiewicz

Active Member

Erel

B4X founder

aklisiewicz

Active Member

Erel

B4X founder

aklisiewicz

Active Member

Theera

Well-Known Member