[MODULE] sqlite

microdevweb · Post by **microdevweb** » Thu Mar 05, 2020 1:44 pm

Hello all,

Here is a small module that I use regularly for using the sqlite database.

Benefits:

make easier the sqlite usage
can write a log file (if sqlite::LOG_NAME is fill) only for error if sqlite::logOnlyIfError = #True or all update request if not #true

; ----------------------------------------------------
; AUTHOR  : microdevWeb
; MODULE  : sqlite
; EXAMPLE :
; ----------------------------------------------------
XIncludeFile "include/sqlite.pbi"
EnableExplicit
Macro sqlError()
  MessageRequester("SQL ERROR",req+Chr(10)+db\getError(),#PB_MessageRequester_Error)
  End
EndMacro
sqlite::FILE_PATH = "data"
sqlite::FILE_NAME = "myDase.db"
sqlite::LOG_PATH = "log"
sqlite::LOG_NAME = "sqll.log"

Procedure createDatabase()
  Protected db.sqlite::obj = sqlite::new(),
            req.s = "CREATE TABLE IF NOT EXISTS  person ("+
                    "id INTEGER PRIMARY KEY AUTOINCREMENT,"+
                    "name TEXT,"+
                    "age INTEGER)"
  
  If Not db\open():sqlError():EndIf
  If Not db\update(req):sqlError():EndIf
  db\close()
EndProcedure

Procedure fillDatabase()
  Protected db.sqlite::obj = sqlite::new(),
            req.s = "INSERT INTO person (name,age) VALUES (?,?)",
            name.s,age.s
  If Not db\open():sqlError():EndIf
  Restore D_NAME
  Repeat
    Read.s name
    If name = "-1":Break:EndIf
    Read.s age
    db\setString(0,name)
    db\setLong(1,Val(age))
    If Not db\update(req):sqlError():EndIf
    
  ForEver
  db\close()
EndProcedure

Procedure readDatabase()
  Protected db.sqlite::obj = sqlite::new(),
            req.s = "SELECT * FROM person"
  If Not db\open():sqlError():EndIf
  If Not db\query(req):sqlError():EndIf
  While db\nextRow()
    Debug db\getString(db\getIdColumn("name"))+" : "+Str(db\getLong(db\getIdColumn("age")))
  Wend
  db\close()
EndProcedure

createDatabase()
fillDatabase()
readDatabase()


; for make some records into  db
DataSection
  D_NAME: 
  Data.s "Pierre","54","Jacque","18","Olivier","30","Marcel","34","Jhon","47","George","64","Philippes","28","Fred","84","Alain","16","-1"

EndDataSection

Exemple de log

Code: Select all

2020-03-05  12 : 46 : 34 UPDATE : CREATE TABLE IF NOT EXISTS  person (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,age INTEGER) 
2020-03-05  12 : 46 : 34 SET_STRING : Pierre 
2020-03-05  12 : 46 : 34 SET_LONG : 54 
2020-03-05  12 : 46 : 34 UPDATE : INSERT INTO person (name,age) VALUES (?,?) 
2020-03-05  12 : 46 : 34 SET_STRING : Jacque 
2020-03-05  12 : 46 : 34 SET_LONG : 18 
2020-03-05  12 : 46 : 35 UPDATE : INSERT INTO person (name,age) VALUES (?,?) 
2020-03-05  12 : 46 : 35 SET_STRING : Olivier 
2020-03-05  12 : 46 : 35 SET_LONG : 30 
2020-03-05  12 : 46 : 35 UPDATE : INSERT INTO person (name,age) VALUES (?,?) 
2020-03-05  12 : 46 : 35 SET_STRING : Marcel 
2020-03-05  12 : 46 : 35 SET_LONG : 34 
2020-03-05  12 : 46 : 35 UPDATE : INSERT INTO person (name,age) VALUES (?,?) 
2020-03-05  12 : 46 : 35 SET_STRING : Jhon 
2020-03-05  12 : 46 : 35 SET_LONG : 47 
2020-03-05  12 : 46 : 35 UPDATE : INSERT INTO person (name,age) VALUES (?,?) 
2020-03-05  12 : 46 : 35 SET_STRING : George 
2020-03-05  12 : 46 : 35 SET_LONG : 64 
2020-03-05  12 : 46 : 35 UPDATE : INSERT INTO person (name,age) VALUES (?,?) 
2020-03-05  12 : 46 : 35 SET_STRING : Philippes 
2020-03-05  12 : 46 : 35 SET_LONG : 28 
2020-03-05  12 : 46 : 35 UPDATE : INSERT INTO person (name,age) VALUES (?,?) 
2020-03-05  12 : 46 : 35 SET_STRING : Fred 
2020-03-05  12 : 46 : 35 SET_LONG : 84 
2020-03-05  12 : 46 : 35 UPDATE : INSERT INTO person (name,age) VALUES (?,?) 
2020-03-05  12 : 46 : 35 SET_STRING : Alain 
2020-03-05  12 : 46 : 35 SET_LONG : 16 
2020-03-05  12 : 46 : 36 UPDATE : INSERT INTO person (name,age) VALUES (?,?)