Reading a SQLite record

[radsoft]

Hello everyone,

I've just started learning about SQLite. I've written PureBasic code that successfully reads an SQLite file from disk and displays the records in a ListIconGadget with the record ID in the first column followed by other fields in additional columns. That part works well but I'm needing help with the next stage of reading and displaying record fields.

My ultimate intention is that I click on a row in the ListIconGadget and then click a button that will open a window and display the field entries for that record.

My record reading code so far is:

Code: Select all

rowNumber = GetGadgetState(ligFrmMain) ;Determine the row number in the ListIconGadget 
readRecordIDStr = GetGadgetItemText(ligFrmMain, rowNumber, 0) ;Read the Record ID which is stored on the first column

OpenDatabase(sqliteNo, "myData.db", "", "") ;Open the database file

SetDatabaseString(sqliteNo, 0, readRecordIDStr) ;Set the record ID for data binding in the query
query.s = "SELECT * FROM myTable where id = ?" ;Make the query string

DatabaseQuery(sqliteNo, query)

Debug GetDatabaseString(sqliteNo, 2)
CloseDatabase(sqliteNo)

The code above gives the correct ListIconGadget row number and determines the record ID from the first column but Debug GetDatabaseString(sqliteNo, 2) does not print the contents of that field. This is just one of many attempts to read the record from the file so I'm wondering if someone could help me please.

Thanks in advance

[infratec]

Simple Example:

Code: Select all

EnableExplicit

Define.i Event, Exit, i
Define SQL$


UseSQLiteDatabase()

If OpenDatabase(0, ":memory:", "", "", #PB_Database_SQLite)
  
  If DatabaseUpdate(0, "CREATE TABLE Test (Key text, Value text)")
    
    OpenWindow(0, 0, 0, 400, 300, "SQLite Test", #PB_Window_MinimizeGadget|#PB_Window_ScreenCentered)
    ListIconGadget(0, 10, 10, 380, 280, "ID", 50, #PB_ListIcon_FullRowSelect)
    AddGadgetColumn(0, 1, "Key", 100)
    AddGadgetColumn(0, 2, "Value", 100)
    
    For i = 1 To 10
      DatabaseUpdate(0, "INSERT INTO Test VALUES ('Key" + Str(i) + "', 'Value" + Str(i) + "')")
    Next i
    
    If DatabaseQuery(0, "SELECT rowid, * FROM Test")
      While NextDatabaseRow(0)
        AddGadgetItem(0, -1, GetDatabaseString(0, 0) + #LF$ + GetDatabaseString(0, 1) + #LF$ + GetDatabaseString(0, 2))
      Wend
      FinishDatabaseQuery(0)
    EndIf
    
    Repeat
      
      Event = WaitWindowEvent()
      Select Event
        Case #PB_Event_CloseWindow
          Exit = #True
          
        Case #PB_Event_Gadget
          Select EventGadget()
            Case 0
              If EventType() = #PB_EventType_LeftDoubleClick
                SQL$ = "SELECT Key, Value FROM Test WHERE rowid=" + GetGadgetItemText(0, GetGadgetState(0), 0)
                If DatabaseQuery(0, SQL$)
                  If NextDatabaseRow(0)
                    MessageRequester("Record " + Str(GetGadgetState(0) + 1), GetDatabaseString(0, 0) + " " + GetDatabaseString(0, 1))
                  EndIf
                  FinishDatabaseQuery(0)
                EndIf
              EndIf
          EndSelect
      EndSelect
      
    Until Exit
    
  EndIf
  
  CloseDatabase(0)
EndIf

[radsoft]

Hello infratec and thank you. I struggled with this for ages and you've just solved it. Much appreciated!

[mk-soft]

I had once written a function to support this.
Just have a look at how something like this works.
Link: ShowDatabaseRows (Items)

[radsoft]

Thank you mk-soft, I appreciate your help.