Database interactions

[JaxMusic]

Good day everyone.

I've been testing some Db functions using SQLite, but my questions would be applicable to any db option.

When using DatabaseQuery, are the records returned in a variable? How do you reference this going forward? For example, I'm wanting to create a procedure that takes the database and sql and returns the records. Then have that record set available for use by other procedures.

I see one potentially way to do that using an array to store the data but that seems kind of clunky to go through that data in the procedure to populate the array.

Assuming there is a record set type variable available in use for this, do you have to keep the database open to use?

Thanks in advance.

[spikey]

When using DatabaseQuery, are the records returned in a variable? How do you reference this going forward? For example, I'm wanting to create a procedure that takes the database and sql and returns the records. Then have that record set available for use by other procedures.

See https://www.purebasic.com/documentation ... se.pb.html from the manual, or viewtopic.php?f=13&t=73803 for a slightly more advanced example than in the manual.

When you call DatabaseQuery the library prepares a recordset in its own working memory which is not directly accessible to you. You can then use the NextDatabaseRow and PreviousDatabaseRow instructions to move through the recordset. When you do so the GetDatabase* instructions can be used to retrieve the current row values from the set.

I see one potentially way to do that using an array to store the data but that seems kind of clunky to go through that data in the procedure to populate the array.

What you do with this is up to you. In the second example I transfer the data straight into a visual control to display it. Storing record IDs as I go for easy reference later.

If I want more flexibility I might move it into a structured array or linked list so I can process it further. This might be appropriate in your case of having multiple procedures work on the same set.

Assuming there is a record set type variable available in use for this

There is no 'everything in one record set' type variable as you might find in a OOP language, but structured arrays, linked lists or maps, lend themselves to this type of storage depending on your needs. You can pass arrays, lists and maps into procedures as parameters and modify their contents inside the procedure.

Do you have to keep the database open to use?

Yes but you don't have to keep the connection open the whole time your application is running if you don't want to. If you only need to query occasionally it might make sense to open on demand and close afterwards. If you need to query often it might make more sense to keep the connection open all the time.

Bear in mind though that you will need to call FinishDatabaseQuery when you are done with a recordset and before you close a connection; otherwise you will leak memory.

[JaxMusic]

Thanks @Spikey. I had reviewed and tried a lot of that before posting here and you confirmed my suspicions. Not having a Recordset type that is referenced seems like a waste of resources but that’s the breaks. I can work with this “cursor” model but it makes things a bit more cluttered to my way of thinking.

[infratec]

As already said:
you can store the results in a linked list with a corresponding structure.
Then you have a local copy of your select results.
So you can close the database connection (if you want).
But the longest time is to connect to it. So if you have more selects, simply let the conection open.

[skywalk]

I think OP is referring to a direct recordset retrieval or entire sql dump into a PB data structure that matches.
Currently, we step through the returned result with GetDataBase() calls and assign them to each PB data structure element individually.
I remember reading of a SQLite external function that could retrieve arrays of results. But, it was easier for me to abstract with the current approach.

[TI-994A]

When using DatabaseQuery, are the records returned in a variable?

PureBasic is simply emulating the process structure of the underlying SQLite engine, which works on a similar query/fetch cycle. Assignments have to be done manually, according to the schemas and data types, which should be known beforehand. Here's a simple POC:

Code: Select all

UseSQLiteDatabase()

; custom structure according to database table structure
Structure people
  id.i
  name.s
  address.s
  telephone.s  
EndStructure

#sqLite = 0
#mainWin = 0
#dataList = 0

; create array to hold retrieved record-sets
Dim dataSet.people(0)

; create sample in-memory database
Procedure createSampleDatabase()
  Protected dbError = #False
  
  If OpenDatabase(#sqLite, ":memory:", "", "")       
    If DatabaseUpdate(#sqLite, "CREATE TABLE people (id INTEGER PRIMARY KEY, " +
                               "name CHAR(100), address CHAR(200), telephone CHAR(50))")  
      Restore people
      Dim queries.s(7)
      Define insertQuery.s = "INSERT INTO people (name, address, telephone) VALUES ("
      For i = 0 To 7
        Read.s name$
        Read.s address$
        Read.s telephone$
        queries(i) = insertQuery + "'" + name$ + "','" + address$ + "','" + telephone$ + "')"
      Next 
      
      DatabaseUpdate(#sqLite, "BEGIN")
      For i = 0 To 7
        If Not DatabaseUpdate(#sqLite, queries(i))
          AddGadgetItem(#dataList, -1, "error inserting data!")
          dbError = #True
          Break
        EndIf     
      Next i
      DatabaseUpdate(#sqLite, "COMMIT")      
      
    EndIf           
    
    ; in-memory database must remain open after creation
    ; CloseDatabase(#sqLite)  ; will be lost if closed
    
  EndIf
  
  If dbError
    AddGadgetItem(#dataList, -1, "error creating in-memory database!")
  EndIf
  
  ProcedureReturn Bool(dbError = #False)
EndProcedure

; read database table into structure array
Procedure readTable(tableName.s)
  Shared dataSet()  
  Protected tableCount, dbError = #True
  
  ; in-memory database must remain open after creation
  If IsDatabase(#sqLite)  ; instead of OpenDatabase(#sqLite...      
    If DatabaseQuery(#sqLite, "SELECT COUNT (*) FROM people")      
      NextDatabaseRow(#sqLite)      
      tableCount = GetDatabaseLong(#sqLite, 0)            
      FinishDatabaseQuery(#sqLite)

      ; re-size structure array according to table count
      ReDim dataSet(tableCount - 1)  
      
      If DatabaseQuery(#sqLite, "SELECT * FROM people")        
        While NextDatabaseRow(#sqLite)
          dataSet(i)\name = GetDatabaseString(#sqLite, 1) 
          dataSet(i)\address = GetDatabaseString(#sqLite, 2) 
          dataSet(i)\telephone = GetDatabaseString(#sqLite, 3) 
          i + 1
        Wend        
        FinishDatabaseQuery(#sqLite)      
        CloseDatabase(#sqLite) 
        dbError = #False
      EndIf            
      
    EndIf              
  EndIf
  
  If dbError
    AddGadgetItem(#dataList, -1, "error retrieving record!")
  EndIf
  
  ProcedureReturn Bool(dbError = #False)
EndProcedure

; display retrieved record-sets
Procedure displayTableRecords()
  Shared dataSet()
  
  For i = 0 To 7
    With dataSet(i)
      AddGadgetItem(0, -1, \name + ", " + \address + ", " + \telephone) 
    EndWith
  Next i
  
EndProcedure

wFlags = #PB_Window_SystemMenu | #PB_Window_ScreenCentered
OpenWindow(#mainWin, 0, 0, 500, 300, "Database Example", wFlags)
ListViewGadget(#dataList, 10, 10, 480, 280)

If createSampleDatabase()  
  If readTable("")  
    displayTableRecords()   
  EndIf
EndIf

While WaitWindowEvent() ! #PB_Event_CloseWindow : Wend

; sample data for database
DataSection
people:
  Data.s "Clark Kent", "1, DC Lane, Planet Krypton.", "5551234", 
         "Bruce Wayne", "2, DC Avenue, Planet Earth.", "5552345",
         "Diana Prince", "3, Paradise Island, Planet Earth.", "5553456",
         "David Banner", "4, Marvel Drive, Planet Earth.", "5554567",
         "Peter Parker", "5, Marvel Boulevard, Planet Earth.", "5555678",
         "Steve Rogers", "6, Marvel Heights, Planet Earth.", "5556789",
         "Peter Parker", "11, Marvel Boulevard, Planet Earth.", "5555678",
         "Steven Rogers", "6, Marvel Heights, Planet Earth.", "5556789"  
EndDataSection

[JaxMusic]

Thanks @TI-994A

I understand how it works. I was hoping for a recordset type object. Because there is not one, I will modify my procedures to use a custom data type, make it persist, and return the pointer from within the procedure. Just a bit more work but will solve the problem nicely.