Save DatabaseQuery into Map?

[dige]

Hi folks, I am looking for a convenient and flexible way to access the result of a database queries.
Ideal would be an array as a return.

However, since PB neither return the numbers of columns ands rows,
the array should be dimensioned constantly dynamic. But I do not like that, even for performance reasons.
Now I have the idea to save ​​the data into a map and use as key the combination of row and column.

Are there any reasons that speak against this practice?

Code: Select all

;
; ------------------------------------------------------------
;
;   PureBasic - Database example file
;
;    (c) Fantaisie Software
;
; ------------------------------------------------------------
;

UseSQLiteDatabase()

Procedure CheckDatabaseUpdate(Database, Query$)
  Result = DatabaseUpdate(Database, Query$)
  If Result = 0
    Debug DatabaseError()
  EndIf
  
  ProcedureReturn Result
EndProcedure

DatabaseFile$ = GetTemporaryDirectory()+"/Database.sqlite"

Global NewMap SQLData.s()

If CreateFile(0, DatabaseFile$)
  CloseFile(0)
  
  If OpenDatabase(0, DatabaseFile$, "", "")
    
    CheckDatabaseUpdate(0, "CREATE TABLE food (name CHAR(50), weight INT)")
    
    CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('apple', '10')")
    CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('pear', '5')")
    CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('banana', '20')")
    
    If DatabaseQuery(0, "SELECT name, weight FROM food")
      n = 0
      While NextDatabaseRow(0)
        Debug "Add Row: " + Str(n) + " | " + GetDatabaseString(0, 0) + ";" + GetDatabaseString(0, 1)
        
        SQLData(Str(n) + ";0") = GetDatabaseString(0, 0)
        SQLData(Str(n) + ";1") = GetDatabaseString(0, 1)
        n + 1
      Wend
      FinishDatabaseQuery(0)
    EndIf
    
    CloseDatabase(0)
  Else
    Debug "Can't open database !"
  EndIf
Else
  Debug "Can't create the database file !"
EndIf

Debug "Row 0 = " + SQLData("0;0") + ";" + SQLData("0;1")
Debug "Row 1 = " + SQLData("1;0") + ";" +  SQLData("1;1")
Debug "Row 2 = " + SQLData("2;0") + ";" +  SQLData("2;1")

[bobobo]

if it works, it works.

but flexible ??
1. you lose the flexiblility of sql-queries

i'd preferr to copy the "normal" sqlite-db into a memory-sqlite-db to work with that.
that seems to be more flexibel.

[Electric Sheep]

If you don't mind importing a couple of SQLite functions then you can use sqlite3_get_table. You will then get your results and know the number of rows and columns, which you can put into an array.

Code: Select all

;
; ------------------------------------------------------------
;
;   PureBasic - Database example file
;
;    (c) Fantaisie Software
;
; ------------------------------------------------------------
;

UseSQLiteDatabase()

ImportC "sqlite3.lib"
  sqlite3_get_table(*db.i, zSQL.p-Utf8, *pazResult.i, *pnRow.i, *pnColumn.i, *pzErrmsg.i = 0)
  sqlite3_free_table(*db.i)
EndImport

#SQLITE_OK = 0

Procedure CheckDatabaseUpdate(Database, Query$)
   Result = DatabaseUpdate(Database, Query$)
   If Result = 0
      Debug DatabaseError()
   EndIf
   
   ProcedureReturn Result
EndProcedure

DatabaseFile$ = GetTemporaryDirectory()+"/Database.sqlite"

If CreateFile(0, DatabaseFile$)
   CloseFile(0)
   dbHandle.i = OpenDatabase(0, DatabaseFile$, "", "")
   dbHandle = PeekI(PeekI(dbHandle+SizeOf(integer)))
   
   If dbHandle
      CheckDatabaseUpdate(0, "CREATE TABLE food (name CHAR(50), weight INT)")

      CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('apple', '10')")
      CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('pear', '5')")
      CheckDatabaseUpdate(0, "INSERT INTO food (name, weight) VALUES ('banana', '20')")
      
      query.s = "SELECT name, weight FROM food"
      
      *res.i = 0
      nRows.i = 0
      nCols.i = 0
      
      sqlreturn = sqlite3_get_table(dbHandle, query, @*res, @nRows, @nCols, 0)
      
      If sqlreturn = #SQLITE_OK
        Dim SQLData$(nRows, nCols-1)
        
        For row = 0 To nRows
          For column = 1 To nCols
            *val.i = PeekI(*res + ((column - 1 + (row * nCols)) * SizeOf(integer)))
            SQLData$(row, column-1) = PeekS(*val, -1, #PB_UTF8)
          Next
        Next
        
        For row = 0 To nRows
          For column = 0 To nCols-1
            Debug Str(row) + " : " + Str(column) + " = " + SQLData$(row, column)
          Next
        Next
        
        sqlite3_free_table(*res)
      EndIf
      
      CloseDatabase(0)
   Else
      Debug "Can't open database !"
   EndIf
Else
   Debug "Can't create the database file !"
EndIf

[dige]

Yes, thats the Way I currently do it. But now I wanna
change to PBs generic DB Interface to have support
for ODBC and PostgreSQL.