• Welcome, Guest. Please login.
 
May 26, 2019, 04:31:11 am

News:

Welcome to the SQLitening support forums!


Show posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.

Messages - cj

31
QuoteHow can I connect my router to the my wireless internet connection? My router don't have a usb port and there is no way to connect my wireless internet with a cable.


Wireless computers connect to router using built-in or USB wireless network adapters
The modem connects to the internet and router plugs into the modem.
Some modems and routers are a single device.  Hopefully you have a cable modem connected to a cable line into the building.

You already plugged in the wireless adapter and no wires are needed

In the router software:  started by http://192.168.1.1  netgear routers are http://192.168.0.1
You may have to get into router software with an ethernet cable the first time since you don't have wifi working.

STEP A
Find wireless settings
1 Enable SSID Broadcast
2 Name (SSID)  Enter name everybody will see with wireless to logon
3 Passphrase    Enter secret logon password


Step B

   TPLink on port forwarding:
https://www.tp-link.com/us/faq-134.html

Name:               Anything you want, example  My Wireless Server
Extemal port:      51234
Internal port:      51234
Internal Server IP 169.254.107.158


Step C (Optional, but suggested)
   Address reservation so IP address can't change
   This assigns the MAC address of the network card to an IP address so the same IP address is always reserve when Windows boots.
https://www.tp-link.com/ae/faq-187.html

MAC Address:  It should appear so you don't have to manually type it in
Reserved IP: Address 169.254.107.158


Connect
In Windows wireless machines:
Click the logon icon on the taskbar
Select network by name
Enter passphrase

Can you see your wireless network name  in the list on any machine or a cell phone?  9/23/18  11:12 AM CST
------------------------------------------------------------------------------------------------------------------
SQLitening server on port 51234 may have to be manually started after Windows begins because wireless drivers may load last.

If you have a shortcut to  SQLiteningServer.exe be sure it uses Run as Administrator.

SQLiteningServer.Cfg
Hosts = 169.254.107.158
Port   = 51234

If using Windows Firewall    SQLiteningServer.exe must be in "Allow an app through Windows Firewall"
32
Welcome.  Be sure to encrypt and hmac over the internet unless you figured out how to use certificates with SQLitening.
33
FreeMyIP.Com is just an easy way to assign your remote ip address to a name so others can find you when it changes.
If you have a static IP address it can be used without FreeMyIP.com
34
Suggest using slBuildBindDat(sData$,"T") to return text in expected order.
CAST AS TEXT to the rescue if bind "B" or no bind parameter was used to insert any record like slbuildbinddat(sData$)

create table T1(C1)
slExeBind "Insert into T1 values(?)",slBuildBindDat("1")     'binary bind
slExeBind "Insert into T1 values(?)",slBuildBindDat("2")     'binary bind
slExeBind "Insert into T1 values(?)",slBuildBindDat("3","B") 'binary bind
slExeBind "Insert into T1 values(?)",slBuildBindDat("11")    'binary bind
Results:
11,2,3,1  Select c1 from t1 order by c1                     binary order is not wanted
1,11,2,3  Select c1 from t1 order by (c1 CAST AS TEXT)      CAST into correct text order
'-----------------------------------------------------------------------------------------------------------------------

THREADED tsResult AS STRING                                     
#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG  'BindWithCast.bas 9/18/18 CJ
slOpen ":memory:"
slexe "create table T1(C1)"
slExeBind "Insert into T1 values(?)",slBuildBindDat("1")       'binary since "T" was not used
slExeBind "Insert into T1 values(?)",slBuildBindDat("2","T")   'text
slExeBind "Insert into T1 values(?)",slBuildBindDat("3","T")   'text
slExeBind "Insert into T1 values(?)",slBuildBindDat("11","T")  'text
GetData "Select C1 from T1 order by C1"
GetData "Select C1 from T1 order by CAST(C1 AS TEXT)"
GetData "Select C1 from T1 order by CAST(C1 AS INTEGER)"
? tsResult,%MB_SYSTEMMODAL,"CAST - Mixed Binary and Text Data"
END FUNCTION

FUNCTION GetData (sql AS STRING) AS STRING
DIM  sArray() AS STRING
slselary sql,sArray(),"Q9c"
tsResult+= sql + $CR + JOIN$(sArray(),",") + $CR + $CR
END FUNCTION
35
Writing any text to a SQLite column is easy even if the data has embedded quotes.
slExeBind "Insert into Table1 values(?)",slBuildBindDat(Text$,"T")
----------------------------------------------------------------------------------------------------       

This enables logging any statements you want and disallowing statements that fail or didn't modify the table.
Statements that fail or didn't modify the log could be placed into another log.

#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG  'errorlog3.bas 9/14/18 CJ
slopen "junk.db3","C"
slexe "drop table if exists log"
slexe "drop table if exists t1"
slexe "create table if not exists log(statements)"
slExecute "create table if not exists t1(First,Last)"
slExecute "Insert into t1 values('one','one')"
slExecute "Insert into t1 values('two','two')"
slExecute "Insert into t1 values('three','three')"
slExecute "Insert into t1 values('four','four')"
slExecute "Update T1 Set First = 'Heidi'"
slExecute "Update T1 Set Last  = 'Klum'"
slExecute "Delete from t1 where rowid > 2"

  ? "Log" + $CR + GetData("select * from log") + $CR + $CR +   "Data File"  + $CR + GetData("select rowid,* from t1")
END FUNCTION

FUNCTION slExecute(sql AS STRING) AS LONG
LOCAL ChangeCount AS LONG
ChangeCount = slGetChangeCount("T") 'system change count

IF slexe(sql,"E0") THEN      'invalid statement, do something different with it
   FUNCTION = slGetErrorNumber
   ? slGetError+ $CR + sql,,"Statement Failed Log Not Updated"
   EXIT FUNCTION
END IF

ChangeCount = slGetChangeCount("T") - ChangeCount

LOCAL s AS STRING
s = LEFT$(LTRIM$(LCASE$(sql)),7) 'get rid of leading spaces, lower case
SELECT CASE s
  CASE "insert ":? sql,,USING$("INSERT changes #",changecount)
  CASE "update ":? sql,,USING$("UPDATE changes #",changecount)
  CASE "delete ":? sql,,USING$("DELETE changes #",changecount)
  CASE ELSE     :? sql,,USING$("Other changes  #",ChangeCount)
END SELECT

'decide what to log
IF ChangeCount > 0 THEN  'modify to taste
  slexebind "insert into log values(?)",slBuildBindDat(sql,"T"),"E0"
END IF

END FUNCTION

FUNCTION GetData(sql AS STRING) AS STRING
LOCAL sArray() AS STRING
IF slSelAry(sql,sArray(),"Q9E0c") THEN
  ? slGetError,%MB_SYSTEMMODAL,"Viewer"
ELSEIF UBOUND(sArray)>0 THEN
  FUNCTION = JOIN$(sArray(),$CR)
END IF
END FUNCTION



#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG  'errorlog2.bas 9/13/18 CJ
slopen "junk.db3","C"
slexe "drop table if exists log"
slexe "drop table if exists t1"
slexe "create table if not exists log(statements)"
slExecute "create table if not exists t1(First,Last)"
slExecute "Insert into t1 values('one','one')"
slExecute "Insert into t1 values('two','two')"
slExecute "Insert into t1 values('three','three')"
slExecute "Insert into t1 values('four','four')"
slExecute "Update T1 Set First = 'Heidi'"
slExecute "Update T1 Set Last  = 'Klum'"
slExecute "Delete from t1 where rowid > 2"

  ? "Log" + $CR + GetData("select * from log") + $CR + $CR +   "Data File"  + $CR + GetData("select rowid,* from t1")
END FUNCTION

FUNCTION slExecute(sql AS STRING) AS LONG
LOCAL ChangeCount AS LONG
ChangeCount = slGetChangeCount("T") 'system change count

IF slexe(sql,"E0") THEN      'invalid statement, do something different with it
   FUNCTION = slGetErrorNumber
   ? slGetError+ $CR + sql,,"Statement Failed Log Not Updated"
   EXIT FUNCTION
END IF

ChangeCount = slGetChangeCount("T") - ChangeCount

LOCAL s AS STRING
s = LEFT$(LTRIM$(LCASE$(sql)),7) 'get rid of leading spaces, lower case
SELECT CASE s
  CASE "insert ":? sql,,USING$("INSERT changes #",changecount)
  CASE "update ":? sql,,USING$("UPDATE changes #",changecount)
  CASE "delete ":? sql,,USING$("DELETE changes #",changecount)
  CASE ELSE     :? sql,,USING$("Other changes  #",ChangeCount)
END SELECT

'decide what to log
IF ChangeCount > 0 THEN  'modify to taste
  slexebind "insert into log values(?)",slBuildBindDat(sql,"T"),"E0"
END IF

END FUNCTION

FUNCTION GetData(sql AS STRING) AS STRING
LOCAL sArray() AS STRING
IF slSelAry(sql,sArray(),"Q9E0c") THEN
  ? slGetError,%MB_SYSTEMMODAL,"Viewer"
ELSEIF UBOUND(sArray)>0 THEN
  FUNCTION = JOIN$(sArray(),$CR)
END IF
END FUNCTION



36
You've got Questions? We've got Answers! / Re: Audit Trail
September 14, 2018, 01:00:32 pm
In a real application, processing errors is required so this design might be more practical.
Other statements that might be important to log are  "create", "drop", "begin", "end"  and others?

Logging errors should be considered for security.
I removed the slGetChangeCount check because some statements like "begin" don't modify, but might be needed to see what is happening.

Again, this does not log statements using  binding like:  slexebind "insert into t1 values(?,,?,?) ",slBuildBindDat(sql,"T")

#INCLUDE "sqlitening.inc"
%KillDatabase=1

SUB LogIt(sql AS STRING)
slexebind "insert into log values(?)",slBuildBindDat(sql,"T"),"E0"
END SUB

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

IF %KillDatabase THEN KILL "junk.db3":ERRCLEAR

LOCAL result AS LONG
LOCAL sql    AS STRING

slopen "junk.db3","C"
slexe "create table if not exists log(statements)"

sql    = "drop table if exists t1"
result = slExecute(sql)

sql    = "create table if not exists t1(First,Last)"
result = slExecute(sql)

sql    = "Insert into t1 values('John','Doe')
result = slExecute(sql)

sql    = "Update t999 set First = 'Fredrick' where Last = 'Doe'"
result = slExecute(sql)

sql    = "Delete from t1 where First = 'Fredrick'"
result = slExecute(sql)

sql    = "Insert into t1 values('Heidi','Klum')
result = slExecute(sql)


? "Log" + $CR + GetData("select * from log") + $CR + $CR +_
   "t1"  + $CR + GetData("select * from t1")

END FUNCTION

FUNCTION slExecute(sql AS STRING) AS LONG
  slexe(sql,"E0")
  logit sql
END FUNCTION

FUNCTION GetData(sql AS STRING) AS STRING
LOCAL sArray() AS STRING
IF slSelAry(sql,sArray(),"Q9E0c") THEN
  ? slGetError,%MB_ICONERROR,FUNCNAME$
ELSEIF UBOUND(sArray) < 1 THEN
  ? sql + $CR + "No data",%MB_SYSTEMMODAL,FUNCNAME$
ELSE
  FUNCTION = JOIN$(sArray(),$CR)
END IF
END FUNCTION
37
You've got Questions? We've got Answers! / Re: Audit Trail
September 14, 2018, 11:34:20 am
If the SQL statements don't use binding.
If binding is used wiill take some thought.

#INCLUDE "sqlitening.inc"
%KillDatabase=1

SUB LogIt(sql AS STRING)
slexebind "insert into log values(?)",slBuildBindDat(sql,"T")
END SUB

FUNCTION PBMAIN () AS LONG  'errorlog2.bas 9/14/18
IF %KillDatabase THEN KILL "junk.db3":ERRCLEAR

LOCAL sql AS STRING

slopen "junk.db3","C"

slexe "create table if not exists log(statements)"
slexe "create table if not exists t1(First,Last)"

sql = "Insert into T1 values('John','Doe')"                :slexe sql:Logit sql
sql = "Update t1 set First = 'Fredrick' where Last = 'Doe'":slexe sql:logit sql
sql = "Delete from t1 where Last = 'Doe'"                  :slexe sql:Logit sql

? GetData("select * from LOG"),,"Log"

END FUNCTION

FUNCTION GetData(sql AS STRING) AS STRING
LOCAL sArray() AS STRING
IF slSelAry(sql,sArray(),"Q9E0c") THEN
  ? slGetError,%MB_ICONERROR,FUNCNAME$
ELSEIF UBOUND(sArray) < 1 THEN
  ? sql + $CR + "No data",%MB_SYSTEMMODAL,FUNCNAME$
ELSE
  FUNCTION = JOIN$(sArray(),$CR)
END IF
END FUNCTION

38
General Board / Re: Audit trail and slInsert (Version 2)
September 13, 2018, 10:46:18 pm

#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
39
You've got Questions? We've got Answers! / Re: Audit Trail
September 13, 2018, 10:40:56 pm
Screenshot showing logging of all SQL statements and any errors.
40
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
41
General Board / Audit trail and slInsert (Version 2)
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



42
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











43
You might also look at List all Active Connections using SQLiteningServerAdmin.exe to see what is connected.

I increased MaxConnections in SQLiteningServer.Cfg while applications are running and
clicked Refresh the data and FACT using SQLiteningServerAdmin.exe.  It works as expected with multiple stations.

if you can't find the error, I would guess MaxMaxConnections in SQLiteningServer.Cfg got changed
and the FACT was not refreshed.  I bet it works, now?






44
Quote
Now the above code only Inserts a new record. Supposing I want to Update (Replace) a record?
« Last Edit: Today at 03:02:38 AM by Fredrick


The purpose of Upsert is to update the other columns if the key is found without changing the key or rowid

This demo shows using Upsert where rowid stays the same if the key is found
vs
Replace Into where rowid will change if the key is found

There is nothing wrong using 2 steps since it is your program and this is an embedded database system.

I find nothing wrong with this:
1. Search by a  key/subject
2. If found UPDATE using rowid else INSERT

Note:
The key/subject can also be changed using Upsert so the rowid won't change.
But, if the new key (in the ON Conflict) exists an error will occur.
I don't think Upsert is meant to be used to change the key if it could cause a CONFLICT of the CONFLICT.
Example:
sql = "INSERT INTO t1 VALUES(" +_    'UPSERT
         $SQ  + sSubject + $SQ + ","     +_
         $SQ  + sData    + $SQ + ")"     +_
         "ON CONFLICT(c1_key) DO UPDATE SET c1_key = 'NewKey', c2_data = '" + sData + "'"

We can do anything in PowerBASIC after checking for an error using IF or CASE.
I like the Upsert statement, but will probably stick with searching for a key and acting upon the search result.
Others may HAVE to use all sql statements.  Upsert may not be the best solution.

In this demo program set %Upsert=0 to test REPLACE INTO instead of Upsert method.


'Method1. Upsert retains the rowid if an insert conflict and updates columns using ON CONFLICT
'Method2. Replace Into deletes existing row if found and inserts a new row

'Method3. Two step method may give you more options in my opinion (no example in this program)
'   Search for key/sSubject
'   If found THEN
'     UPDATE Table Set ... Where rowid=found
'   ELSE
'     Insert Into Table ...
'   End If
'
' -------------------------------------------------------------------
'   Set %Upsert=1 to test usng Upsert method
'   Set %Upsert=0 to test using Replace Into (which many do not like)
'--------------------------------------------------------------------

'  Notice rowid stays the same with Upsert if the key is found
'  Notice rowid changes if the key is found with Replace Into

#DIM ALL
#COMPILE EXE 'upsert2.bas
#INCLUDE "sqlitening.inc"
%Upsert = 0 '0=Use Replace Into which may do not like  '1=Use Upsert method to retain rowid
FUNCTION PBMAIN () AS LONG
LOCAL sql,sSubject,sData,sTitle AS STRING
slOpen "junk.db3","C"
slexe "drop table if exists t1"
slexe "create table if not exists t1(c1_Key unique, c2_Data)"
sSubject = "Chemistry"
sTitle = IIF$(%Upsert,"UPSERT METHOD (rowid's stay the same)","REPLACE INTO (rowid's change)")
DO
  sSubject = INPUTBOX$("Please enter subject",sTitle,sSubject)
  IF LEN(sSubject) = 0 THEN EXIT DO
  sData    = "New time is " + TIME$
  IF %Upsert THEN
  sql = "INSERT INTO t1 VALUES(" +_    'UPSERT
         $SQ  + sSubject + $SQ + ","     +_
         $SQ  + sData    + $SQ + ")"     +_
         "ON CONFLICT(c1_key) DO UPDATE SET c2_data = '" + sData + "'"
  ELSE
  sql = "REPLACE INTO t1 VALUES(" +_   'REPLACE INTO
         $SQ  + sSubject + $SQ + ","     +_
         $SQ  + sData    + $SQ + ")"
  END IF

  slexe sql
  IF slGetChangeCount = 0 THEN ? "Serious Error"
  REDIM sArray(0) AS STRING
  slSelAry "select rowid,* from t1",sArray(),"Q9c"
  ?  sql + STRING$(2,$CR) + JOIN$(sArray(),$CR),%MB_SYSTEMMODAL, sTitle
LOOP
? "If you don't like Upsert or Replace Into, see method 3",,"Have a good day"
END FUNCTION
45
>slEXE "insert into t1 values(" + sSubject  + ") on conflict(c1) do nothing"
  slEXE "insert into t1 values('" + sSubject + "') on conflict(c1) do nothing"

Attempt 20 inserts
#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG
LOCAL x,duplicates AS LONG
slOpen "junk.db3","C"
slexe "drop table if exists t1"
slexe "create table if not exists t1(c1 unique)"

FOR x = 1 TO 10
  slEXE "insert into t1 values('Maths')","E0"                       'ignore error
  IF slGetChangeCount = 0 THEN INCR duplicates

  slEXE "insert into t1 values('Maths') on conflict(c1) do nothing" 'or upsert
  IF slGetChangeCount = 0 THEN INCR duplicates
NEXT

Viewer "select rowid,* from t1",duplicates

END FUNCTION

SUB Viewer(sql AS STRING,duplicates AS LONG)
LOCAL sArray() AS STRING
slSelAry sql,sArray(),"Q9"
? sql + $CR + JOIN$(sArray(),$CR),%MB_SYSTEMMODAL,"Duplicates" + STR$(duplicates)
END SUB