• Welcome, Guest. Please login.
 
April 20, 2019, 06:34:12 am

News:

Welcome to the SQLitening support forums!


Recent posts

Pages 1 ... 8 9 10
91
You've got Questions? We've got Answers! / Re: Audit Trail
Last post by Fredrick Ughimi - September 13, 2018, 06:13:55 pm
Thanks Bern and CJ for your response.

I wanted to create an audit trail that would track users actions like: Save, Update and Delete on a table.

Earlier on I was having issues replying to posts here. It's all good now.
92
You've got Questions? We've got Answers! / Re: Fredrick, Audit Trail (Ver...
Last post by cj - September 13, 2018, 01:10:12 pm
Didn't like previous version.
This version also captures error messages into 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 will be in 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(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
93
General Board / Audit trail and slInsert (Vers...
Last post by cj - September 12, 2018, 06:56: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



94
You've got Questions? We've got Answers! / Re: Audit Trail
Last post by Bern Ertl - September 12, 2018, 09:34:28 am
Yeah, I created a system that allows me to log every SQL statement being executed in a transaction table.  See here:

https://www.sqlitening.planetsquires.com/index.php?topic=3539.msg18054;topicseen#msg18054
95
You've got Questions? We've got Answers! / Audit Trail
Last post by Fredrick Ughimi - September 11, 2018, 02:51:33 pm
Hello,

Has anyone done Audit Trail using SQLitening? I searched but I did see anything of that sort here.

Writing SQLite Triggers would do?
96
General Board / Re: Forum has been upgraded to...
Last post by Paul Squires - September 06, 2018, 09:11:56 pm
I have added a redirect from http://sqlitening.planetsquires.com/support/index.php that seems to work.
97
General Board / Re: Forum has been upgraded to...
Last post by Paul Squires - September 06, 2018, 08:54:33 pm
Thanks Bern, I will do that. I did try to do the redirect but it resulted in an endless loop due to the fact that the forum is in a subdomain on the main PlanetSquires site. I will try a few more options......
98
General Board / Re: Forum has been upgraded to...
Last post by Bern Ertl - September 06, 2018, 06:55:52 pm
I just went through this for a few vBulletin forums that I own over the last three days.  Not fun.

I had http://sqlitening.planetsquires.com/support/index.php bookmarked and was getting all kinds of error messages for the last few times I tried to access the forum.  Might be a good idea to either put a simple "hey, we've moved to this URI" page there or set a redirect from that old address to the new index/home page.
99
General Board / Forum has been upgraded to SSL...
Last post by Paul Squires - September 01, 2018, 10:59:15 am
I have upgraded the forum to us SSL encryption. You should see an secure padlock type of icon in your url address bar and the forum address starting with https://
If you encounter problems with the forum, please let me know. Thanks!
100
You've got Questions? We've got Answers! / Re: MaxConnections
Last post by cj - August 20, 2018, 03:54:08 pm
Like Bern has said, the log should show what happened.

SQLiteningServerAdmin.exe
Tools:
List all Active Connections will show connections even if they will not be active for about 4-minutes then they will go way.

SQliteningServer.Cfg set to MaxConnections = 5

SQLiteningServer.log
08-20-2018 13:30:12 Conn #70 SK 620 CJSQLitening192.168.0.2 (192.168.0.2 on port 50130)
08-20-2018 13:30:15 Conn #71 SK 620 CJSQLitening192.168.0.2 (192.168.0.2 on port 50131)
08-20-2018 13:30:18 Conn #72 SK 620 CJSQLitening192.168.0.2 (192.168.0.2 on port 50133)
08-20-2018 13:30:20 Conn #73 SK 620 CJSQLitening192.168.0.2 (192.168.0.2 on port 50134)
08-20-2018 13:30:23 Conn #74 SK 620 CJSQLitening192.168.0.2 (192.168.0.2 on port 50135)
08-20-2018 13:30:28 Conn #75 SK 620 CJSQLitening192.168.0.2 (192.168.0.2 on port 50136)
08-20-2018 13:30:29 Conn #76 SK 620 CJSQLitening192.168.0.2 (192.168.0.2 on port 50137)
08-20-2018 13:30:31 Conn #77 SK 620 CJSQLitening192.168.0.2 (192.168.0.2 on port 50138)
08-20-2018 13:30:52 Conn #78 SK 620 CJSQLitening192.168.0.2 (192.168.0.2 on port 50142)
08-20-2018 13:30:55 Conn #79 SK 620 CJSQLitening192.168.0.2 (192.168.0.2 on port 50144)

08-20-2018 13:34:23 Dcon #75 WentAway
08-20-2018 13:34:23 Dcon #76 WentAway
08-20-2018 13:34:26 Dcon #77 WentAway
08-20-2018 13:34:47 Dcon #78 WentAway
08-20-2018 13:34:49 Dcon #79 WentAway











Pages 1 ... 8 9 10