SQLitening Support Forum

Please login or register.

Login with username, password and session length
Advanced search  

News:

Welcome to the SQLitening support forums!

Author Topic: Audit trail and slInsert (Version 2)  (Read 149 times)

cj

  • Master Geek
  • ****
  • Posts: 688
    • View Profile
Audit trail and slInsert (Version 2)
« on: September 12, 2018, 04:26:32 PM »

'Encapsulated 3 functions to include logging
'slSel    ----> slSe
'slExe    ----> slEx
'slSelAry ----> slSelAr

'slInsert added for simple text inserting

#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG 'BindAndLog.Bas  9/12/18 CJ
 LOCAL sTemp() AS STRING
 REDIM sCol(1 TO 2) AS STRING 'columns in table
 KILL   "junk.db3":ERRCLEAR
 slOpen "junk.db3","C"
 slexe  "create table if not exists trantable(statements)"
 slex   "create table if not exists t1(c1,c2)"

 'slInsert - insert data without quoting strings
 sCol(1)="c1 binding"
 sCol(2)="c2 binding"
 slInsert "t1",sCol() 'tablename$,datacols$()

 slex     "insert into t1 values('c1 no bind','c2 no bind')"
 slse     "select sqlite_version()",0,"E0":slGetRow':? slf(1),,"SQLite Version"
 slex     "drop table if exists FimTable1234"

 slSelAr "select rowid,* from t1",sTemp(),"Q9"
 ? "T1 Table" + $CR + JOIN$(sTemp(),$CR) + $CR + $CR + "Log" + $CR +_
 Viewer("select rowid,* from trantable" ,"Q9"),,"Both tables"

END FUNCTION
'-------------------------------------------------------------------------------------
FUNCTION slInsert(sTable AS STRING,sCol() AS STRING) AS STRING
 LOCAL x AS LONG, sInsert,sBind,sQuestionMarks,sLog AS STRING
 FOR x = 1 TO UBOUND(sCol)
  sBind+=slBuildBindDat(sCol(x),"T")
 NEXT
 sQuestionMarks = LEFT$("(" + REPEAT$(UBOUND(sCol),"?,"),-1) + ")"  '(?,?,?)
 slexeBind "Insert into " + sTable + " values " + sQuestionMarks,sBind
 IF slGetChangeCount = 0 THEN ? "Insert error",%MB_SYSTEMMODAL,"slInsert"
 sLog = LEFT$(DATE$,5) + " " + TIME$ + " " + "Insert into " + sTable + " values (" + JOIN$(sCol(),",") + ")"
 slexebind "insert into trantable values(?)",slBuildBindDat(sLog,"T")
END FUNCTION
'-------------------------------------------------------------------------------------
FUNCTION Viewer(sql AS STRING,sModChars AS STRING) AS STRING
 LOCAL sArray() AS STRING
 FUNCTION = slSelAr(sql,sArray(),sModChars)
END FUNCTION
'-------------------------------------------------------------------------------------
'(BYREF rsStatement AS STRING, BYREF wsaColsAndRows() AS STRING, OPTIONAL BYVAL rsModChars AS STRING) AS LONG
FUNCTION slSelAr(rsStatement AS STRING, wsaColsAndRows() AS STRING,OPT rsModChars AS STRING) AS STRING
 LOCAL rsModChars2 AS STRING
 LOCAL sLog AS STRING
 LOCAL wsaColsAndRows() AS STRING
 IF ISFALSE(ISMISSING(rsModChars)) THEN rsModChars2 = rsModChars
 slSelAry rsStatement,wsaColsAndRows(),rsModChars2
 FUNCTION = JOIN$(wsaColsAndRows(),$CR)
 sLog = LEFT$(DATE$,5) + " " + TIME$ + " " + rsStatement
 slexebind "insert into trantable values(?)",slBuildBindDat(sLog,"T")
END FUNCTION
'-------------------------------------------------------------------------------------
'BYREF rsStatement AS STRING, OPTIONAL BYVAL rsModChars AS STRING) AS LONG
FUNCTION slex(sql AS STRING,OPT rsModChars AS STRING) AS LONG
 LOCAL rsModChars2 AS STRING
 LOCAL sLog AS STRING
 IF ISFALSE(ISMISSING(rsModChars)) THEN rsModChars2 = rsModChars
 slexe sql,rsModChars2
 sLog = LEFT$(DATE$,5) + " " + TIME$ + " " + sql
 slexebind "insert into trantable values(?)",slBuildBindDat(sLog,"T")
END FUNCTION
'-------------------------------------------------------------------------------------
'(BYREF rsStatement AS STRING, OPTIONAL BYVAL rlSetNumber AS LONG, OPTIONAL BYVAL rsModChars AS STRING) AS LONG
FUNCTION slse(rsStatement AS STRING,OPT rlSetNumber AS LONG,rsModChars AS STRING) AS LONG
 LOCAL rlSetNumber2 AS LONG
 LOCAL sLog,rsModChars2  AS STRING
 IF ISFALSE(ISMISSING(rlSetNumber)) THEN rlSetNumber2= rlSetNumber
 IF ISFALSE(ISMISSING(rsModChars))  THEN rsModChars2 = rsModChars
 slSel rsStatement ,rlSetNumber2,rsModChars2
 sLog = LEFT$(DATE$,5) + " " + TIME$ + " " + rsStatement
 slexebind "insert into trantable values(?)",slBuildBindDat(sLog,"T")
END FUNCTION



Didn't like previous version.
This version captures errors in log and doesn't need any other functions

#INCLUDE "sqlitening.inc"
$E1 = "...................................................." + $CR
$E2 = "...................................................." + $CR

FUNCTION PBMAIN () AS LONG  'errorlog.bas 9/13/18 CJ

 LOCAL sql AS STRING
 slSetProcessMods "E1"  'any error is captured into the log

 KILL "junk.db3":ERRCLEAR
 slopen "junk.db3","C"

 sql = "create table if not exists trantable(statement)" :slexe sql:logit(sql)
 sql = "select * from trantable"                         :slsel sql:logit(sql)
 sql = "drop table if exists HeidiKlum"                  :slexe sql:logit(sql)
 sql = "create table if not exists trantable(statement)" :slexe sql:logit(sql)
 sql = "create table wrong"                              :slexe sql:logit(sql)
 sql = "drop table if exists Table1"                     :slexe sql:logit(sql)
 sql = "select rowid      from trantable"                :slsel sql:logit(sql)
 sql = "select statement  from trantable"                :slsel sql:logit(sql)
 sql = "JIBBERISH"                                       :slexe sql:logit(sql)
 sql = "select *          from trantable"                :slsel sql:logit(sql)
 sql = "select * from xyz"                               :slsel sql:logit(sql)
 sql = "select * from trantable"                         :slsel sql:logit(sql)
 sql = "select 'Have'||' a'||' good'||' day'"            :slsel sql:logit(sql)

 viewall

END FUNCTION

SUB ViewAll
 LOCAL sArray() AS STRING
 IF slSelAry("select statement from trantable",sArray(),"Q9E0") THEN
   ? slGetError,%MB_SYSTEMMODAL,"Viewer"
 ELSEIF UBOUND(sArray) < 1 THEN
  ? "No data",%MB_SYSTEMMODAL,"Viewer"
 ELSE
  ? JOIN$(sArray(),$CR),%MB_SYSTEMMODAL,"Viewer"
 END IF
END SUB

FUNCTION LogIt(s AS STRING) THREADSAFE AS LONG
 LOCAL sHeader AS STRING  'returns 1 on success
 LOCAL AnError AS LONG
 AnError = slGetErrorNumber 'set flag

 IF AnError THEN 'experimental, show last error in log
  slexebind "insert into trantable values(?)",slBuildBindDat($E1 + slGetError,"T"),"E0"
 END IF

 sHeader = LEFT$(DATE$,5) + " " + TIME$ + " " + s
 slexebind "insert into trantable values(?)",slBuildBindDat(s,"T"),"E0"
 IF slGetErrorNumber THEN
   ? slGetError + $CR + $CR + s,%MB_SYSTEMMODAL,"LogIt"
   EXIT FUNCTION
 END IF
 IF AnError THEN slexebind "insert into trantable values(?)",slBuildBindDat($E2,"T"),"E0"
 IF slGetChangeCount <> 1 THEN
   ? "Write to log failed",%MB_SYSTEMMODAL,"LogIt"
 END IF

END FUNCTION



« Last Edit: September 13, 2018, 11:01:04 AM by cj »
Logged
CJ

cj

  • Master Geek
  • ****
  • Posts: 688
    • View Profile
Re: Audit trail and slInsert (Version 2)
« Reply #1 on: September 13, 2018, 08:16:18 PM »

Code: [Select]
#INCLUDE "sqlitening.inc"
$E1 = "...................................................." + $CR
$E2 = "...................................................." + $CR

FUNCTION PBMAIN () AS LONG  'errorlog.bas 9/13/18 CJ

 LOCAL sql AS STRING
 slSetProcessMods "E1"  '[b]any error will be in the log [/b]

 KILL "junk.db3":ERRCLEAR
 slopen "junk.db3","C"

 sql = "create table if not exists trantable(statement)" :slexe sql:logit(sql)
 sql = "select * from trantable"                         :slsel sql:logit(sql)
 sql = "drop table if exists HeidiKlum"                  :slexe sql:logit(sql)
 sql = "create table if not exists trantable(statement)" :slexe sql:logit(sql)
 sql = "create table wrong"                              :slexe sql:logit(sql)
 sql = "drop table if exists Table1"                     :slexe sql:logit(sql)
 sql = "select rowid      from trantable"                :slsel sql:logit(sql)
 sql = "select statement  from trantable"                :slsel sql:logit(sql)
 sql = "JIBBERISH"                                       :slexe sql:logit(sql)
 sql = "select *          from trantable"                :slsel sql:logit(sql)
 sql = "select * from xyz"                               :slsel sql:logit(sql)
 sql = "select * from trantable"                         :slsel sql:logit(sql)
 sql = "select 'Have'||' a'||' good'||' day'"            :slsel sql:logit(sql)

 viewall

END FUNCTION

SUB ViewAll
 LOCAL sArray() AS STRING
 IF slSelAry("select statement from trantable",sArray(),"Q9E0") THEN
   ? slGetError,%MB_SYSTEMMODAL,"Viewer"
 ELSEIF UBOUND(sArray) < 1 THEN
  ? "No data",%MB_SYSTEMMODAL,"Viewer"
 ELSE
  ? JOIN$(sArray(),$CR),%MB_SYSTEMMODAL,"Viewer"
 END IF
END SUB

FUNCTION LogIt(s AS STRING) THREADSAFE AS LONG
 LOCAL sHeader AS STRING  'returns 1 on success
 LOCAL AnError AS LONG
 AnError = slGetErrorNumber 'set flag

 IF AnError THEN 'experimental, show last error in log
  slexebind "insert into trantable values(?)",slBuildBindDat($E1 + slGetError,"T"),"E0"
 END IF

 sHeader = LEFT$(DATE$,5) + " " + TIME$ + " " + s
 slexebind "insert into trantable values(?)",slBuildBindDat(sHeader,"T"),"E0"
 IF slGetErrorNumber THEN
   ? slGetError + $CR + $CR + s,%MB_SYSTEMMODAL,"LogIt"
   EXIT FUNCTION
 END IF
 IF AnError THEN slexebind "insert into trantable values(?)",slBuildBindDat($E2,"T"),"E0"
 IF slGetChangeCount <> 1 THEN
   ? "Write to log failed",%MB_SYSTEMMODAL,"LogIt"
 END IF

END FUNCTION
Logged
CJ