PureBasic Forums - English

Posted: **Wed Jan 11, 2006 2:49 am**

Yeah, I know I'm wrapping a wrapper but I like high level functions

Anyway, as stated, this is WIP and untested: I nearly ditched PBOSL SQLite lib until I got a better understanding from the source code (guys, DO something about the help file!). I was bothered about speed of accessing a result set since me wants everything (and I mean *everything*) done in 1/60th of a second

I may not get there but here's my first attack at a "fast" read. The proc reads/processes a result set as fast as poss and uses callbacks for each row and column in a way that may be optimised for specific need. Other code in due course.

NB: This is just the "fast" part plus the structures/vars it relies on. You'll either be familiar with SQLite... or not. Untested as yet (tomorrow)

Code: Select all

Structure sqlDatabaseT
  err.s

  dbn.s
  dbh.l
EndStructure

Structure sqlQueryT
  err.s

  *db.sqlDatabaseT
  q.s ; The query
  rs.SQLite3_Recordset
  
  cbc.l ; CallBack Count (should be at least one for new row/rec callback!)
  cbl.s ; CallBack List (using string of 4*cbc characters. Space as required and poke in callback addresses)
EndStructure

Dim sqlFastCB.l(10) ; Set to max number of columns expected

Procedure.l sqlRSFastRead(*q.sqlQueryT)
  Protected 

  *q\rs\sValue = ""
  *q\err = "OK"
  
  If *q\rs\Handle=0: *q\err="Null Recordset Handle": ProcedureReturn -1: EndIf

  If *q\rs\Rows=0 Or *q\rs\Cols=0: ProcedureReturn 0: EndIf ; Empty result set (not an error!)
  
  If *q\cbc=0: ProcedureReturn -2: EndIf ; Not even a row callback!

  cbla = @*q\cbl ; CallBack List Address
  rcb  = PeekL(cbla) ; Row CallBack
  ccc  = *q\cbc-1 ; Column Callback Count (doesn't have to be any! Maybe only row cb or first X cols)
  If ccc ; Extract column callback adresses
    For c=1 To ccc: sqlFastCB(c)=PeekL(cbla+c*4): Next
  EndIf

  roff = *q\rs\Cols * 4 ; Row Offset (for skipping rows or columns with no callback
  radd = *q\rs\Handle
  
  For r=1 To *q\rs\Rows
    CallFunctionFast(rcb, r, radd) ; New row/rec
    
    If ccc ; Column callbacks
      cadd=radd
      For c=1 To ccc: CallFunctionFast(sqlFastCB(c), c, cadd): cadd+4: Next
      radd+roff
    EndIf
  Next
  
  ProcedureReturn 0
EndProcedure

Posted: **Wed Jan 11, 2006 11:23 am**

Hmmm, as you can see I wrote that in the early hours... but working on it again now and will update later. In the mean time a question for Kiffi: In the proc below what is "ProcedureReturn @Recordset" returning? Should it be "*Recordset" and if so what's the point since it is already known outside the proc, and if there is no point then maybe it would be better to return *Recordset\Rows?

PS: I may not be fully awake yet so smack me down hard (with a wet kipper) if I'm not reading the code correctly

Code: Select all

ProcedureDLL.l SQLite3_GetRecordset(sSQLQuery.s, lDataBaseHandle.l, *Recordset.SQLite3_Recordset)
  
  If lDataBaseHandle = 0
    LastMessage = "Wrong Database-Handle"
    ProcedureReturn
  EndIf
  
  *Recordset\BOF        = #True
  *Recordset\EOF        = #True
  *Recordset\Handle     = 0
  *Recordset\Rows       = 0
  *Recordset\Cols       = 0
  *Recordset\CurrentPos = 0
  *Recordset\sValue     = ""
  
  hResult = CallCFunctionFast(sqlite3_get_table, lDataBaseHandle, sSQLQuery, @lResultsPtr, @lRows, @lCols, @ReturnValue)
  
  If hResult = #SQLITE3_OK
    
    *Recordset\Handle = lResultsPtr
    *Recordset\Rows = lRows
    *Recordset\Cols = lCols
    *Recordset\CurrentPos = 1
    *Recordset\BOF = #True
    
    If lRows > 0
      *Recordset\EOF = #False
    Else
      *Recordset\EOF = #True
    EndIf
    
    LastMessage = "OK"
    
    ProcedureReturn @Recordset
    
  Else
    
    CallCFunctionFast(sqlite3_errmsg, @ReturnValue)
    LastMessage = PeekS(ReturnValue)
    ProcedureReturn #False
    
  EndIf
  
EndProcedure

PureBasic Forums - English

WIP - SQLite Wrapper

WIP - SQLite Wrapper