Save DatabaseQuery into Map?

dige · **Posted:** Wed Jun 13, 2012 12:32 pm

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:

;
; ------------------------------------------------------------
;
;   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 · **Joined:** Mon Jun 09, 2003 8:30 am **Posts:** 158

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 · **Joined:** Mon Oct 10, 2011 2:26 pm **Posts:** 4

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:

;
; ------------------------------------------------------------
;
;   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 · **Posted:** Wed Jun 13, 2012 7:48 pm

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.

PureBasic Forum

Save DatabaseQuery into Map?

Who is online