SQLitening Support Forum

Yes, any file type can be insert into a blob column, but it may not be the best choice.
PDF files can be saved in a database, but client or I copy them "as is" into a folder for each client.
The client can click on them or do anything without having to save to a database and extract.
Chrome browser displays PDF files very well or SHELL "filename.pdf" to use default viewer for type "pdf".
Giving the client the option to open pdf folder allows them to select/display with a click.

#COMPILE EXE "SaveAnyFile"  'Save as "SaveAnyfile.Bas"
#INCLUDE "sqlitening.inc"
   
FUNCTION PBMAIN () AS LONG
 LOCAL sData AS STRING
 LOCAL sFileName AS STRING

 slOpen "junk.db3","C"
 slexe  "create table if not exists Table1(MyFile)"

 sFileName = "SaveAnyFile.bas"
 IF GetFileFromDisk(sFileName,sData) THEN EXIT FUNCTION
 slExeBind "insert into Table1 values(?)",slbuildbinddat(sData,"B")
 IF slGetChangeCount = 0 THEN ? "insert error":EXIT FUNCTION

 slsel "select rowid,Myfile from Table1"
 DO WHILE slGetRow
   ? CHR$("Rowid=",slfn("rowid"),$CR,$CR,slfn("MyFile"))
 LOOP
END FUNCTION

FUNCTION GetFileFromDisk(sFileName AS STRING,sData AS STRING) AS LONG
 sData = ""
 IF ISFILE(sFileName) = 0 THEN FUNCTION = 53:EXIT FUNCTION
 LOCAL hfile AS LONG
 hfile = FREEFILE
 OPEN sFileName FOR BINARY AS hFile
 IF ERR THEN FUNCTION = ERR:BEEP:EXIT FUNCTION
 GET$ hFile,LOF(hFile),sData
 IF ERR THEN FUNCTION = ERR
 CLOSE hFile
END FUNCTION

FUNCTION GetLockNames AS STRING
 'connect and open database before call 
 LOCAL s1,s2 AS STRING, x AS LONG
 s1 = slGetStatus(1)
 IF LEN(s1) = 0 THEN EXIT FUNCTION
 REPLACE $VT WITH "," IN s1
 REPLACE $BS WITH "," IN s1
 FOR x = 2 TO PARSECOUNT(s1) STEP 8
  s2 = s2 + PARSE$(s1,x) + ","
 NEXT
 FUNCTION = LEFT$(s2,-1)
END FUNCTION

Though I have not had a problem, I am removing this change in case
ATTACH or push the databases handles may need slOpen.  Better safe than sorry.

FUNCTION slOpen ALIAS "slOpen  ...
  IF thDab THEN EXIT FUNCTION  '<--- add  line in SQLitening.Bas and compile

Fredrick,
I don't think it is a bug so I doubt it. That is up to Paul Squires.
Options:
1. Use slClose before slOpen.
2. Use IF ISFALSE(slIsOpen) THEN slOpen.
3. Don't call slOpen more than once without first closing.
4. Write a function or macro that replaces slOpen.
5  Add line IF thDab THEN EXIT FUNCTION like I did.

Fim,
Thank you for reporting this 4/1/2020 at:
https://sqlitening.planetsquires.com/index.php?topic=9756.msg26645;topicseen#msg26645

FUNCTION slOpen ALIAS "slOpen  ...
  IF thDab THEN EXIT FUNCTION  '<--- add  line in SQLitening.Bas and compile

Rather than test every program ever written, make above 1-line change to SQLitening.Bas
This is not an error in SQLitening, but a programming error not checking if database was open.
Calling slOpen will now just return if database is open and not create new handle and leak previous handle.

Test new SQLitening.dll to be sure duplicate handles are not created using slOpen
#INCLUDE "sqlitening.inc"  'test new sqlitening.dll with 3 open methods

SUB Test(sdatabase AS STRING)
 LOCAL h AS LONG
 slOpen sdatabase
 h = slgethandle
 slOpen sdatabase
 IF h <> slGetHandle THEN ? "Memory leak"
END SUB

FUNCTION PBMAIN () AS LONG
 Test "temp.tmp"  'test database file
 Test ":memory:"  'test in-memory database
 Test ""          'test temp database
 ? "Done"
END FUNCTION
Also tested over the internet and using threads.

Hi, Bern.

I can't think of any use.  The docs mention making direct calls to SQLite.
I only use slOpen in other threads.
.
I never realized a new global or threaded file handle was created with each call to slOpen.
I always thought SQlitening ignored a call to an already open database.

I will be modifying some programs by mostly using IF ISFALSE(slIsOpen) Then slOpen ...

Quote from: undefinedslGetHandle ([rsModChars String, rlSetNumber Long]) Dword

Returns the requested handle.  ModChars will determine which handle is returned.  The database handle may be used to call SQLite directly or can be passed to a different thread to be used in slOpen.  The set handle may only be used to call SQLite directly and then only if in local mode. A %SQLitening_InvalidStringOrRequest error will occur if you try to get a set handle in remote mode.


ModChars:
·    D = Return the open database handle. This is the default.
·    S = Return the open set handle for set number passed in SetNumber.


Returns zero if an error occurs and the global return errors flag is on.

This demonstrates how multiple global handles are incorrectly created (programming error) for the current database.
Notice the database cannot close correctly or be killed if this is done.  Permission denied error.
Calling slOpen many times without closing it will eventually produce an error 7.

GLOBAL gs AS STRING
#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG
slSetProcessMods "E2"
LOCAL x AS LONG
Logit "Open database only if not open"
FOR x = 1 TO 5
  IF ISFALSE(slIsOpen) THEN slopen "junk.tmp","C": Logit USING$("handle #",slGetHandle)
NEXT
slClose
KILL "junk.tmp"
Logit  ERROR$(ERR) + " killing junk.tmp"


[b]'Now show how to incorrectly use slOpen[/b]

Logit ""
Logit "Open database without testing if already open"
FOR x = 1 TO 5
  slopen "junk.tmp","C"
  Logit USING$("handle #",slGetHandle)
NEXT
slClose
KILL "junk.tmp"
LOGIT ERROR$(ERR) + " killing junk.tmp"

? gs

END FUNCTION

SUB LogIt(s AS STRING)
gs = gs + s + $CR
END SUB

Enables a column delimiter and a new row delimiter in new JOIN2$ function.
This makes it easy to display a string from a 2-dimensional array with a $CR  or other characters as a row delimiter.

Improved money macro to automatically display passed column name instead of printf statement.
SQlite "AS" is optional so just appended colname to the money macro.
Don't like leading 0 displaying on money columns so used ltrim to remove it.

Enjoy!

#DIM ALL
MACRO money(colname)= CHR$("ltrim(printf('%.2f',",colname,"*.01),'0')",colname)
#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN () AS LONG

 LOCAL sql,sColDel,sRowDel,rs() AS STRING
 slopen "sample.db3"
 sql = "select manuf,redref,"+ money("PRICE") + " from parts limit 20"
 slselary sql,rs()
 sColDel = "   "
 sRowDel = $CR
 ? JOIN2(rs(),sColDel,sRowDel),,USING$("Rows #_, Cols #",UBOUND(rs,2),UBOUND(rs))

END FUNCTION

FUNCTION JOIN2(rs() AS STRING,sColumnDelimiter AS STRING,sRowDelimiter AS STRING) AS STRING
 LOCAL sb AS ISTRINGBUILDERA
 sb = CLASS "STRINGBUILDERA"
 'sb.capacity = 1024*1000 'does well without capacity

 LOCAL c              AS LONG
 LOCAL LowCol         AS LONG
 LOCAL HighCol        AS LONG
 LOCAL HighCol_minus1 AS LONG

 LOCAL r              AS LONG
 LOCAL LowRow         AS LONG
 LOCAL HighRow        AS LONG

 LowCol = LBOUND(rs,1)
 HighCol= UBOUND(rs,1)
 HighCol_minus1 = HighCol-1
 LowRow = LBOUND(rs,2)
 HighRow= UBOUND(rs,2)

 FOR r = LowRow TO HighRow
  FOR c= LowCol TO HighCol_minus1
   sb.add  rs(c,r)
   sb.add sColumnDelimiter
  NEXT c
  sb.add rs(c,r)
  sb.add sRowDelimiter
 NEXT r
 FUNCTION = sb.string
END FUNCTION

Very interesting read:
https://proprivacy.com/vpn/comparison/nigeria-vpn

I have been successfully using SQLitening over a VPN between machines that share the exact same dedicated VPN server using the same user name and password.  This might be the best way to go if this is allowed.
I paid nothing extra and didn't need any dedicated IP address (other then share same exact server.)

I will finally have time to work on encrypting SQLitening on 12/3.

https://help.logmein.com/pkb_Home?q=share%20a%20port&l=en_US&c=products%3Almihamachi2&atype=FAQ__kav

It looks like you share a folder and use Windows mapping.
I only connected 2 machines and used chat.
I didn't use SQLite with it.
There are links to PDF files at the bottom of their home page.
Many use it so there must be better docs on the net, don't know where.

Windows 10 has a built-in VPN server, but I couldn't connect.
Some on the internet say there is a problem introduced in a Windows update.
https://pureinfotech.com/setup-vpn-server-windows-10/

I got Hamachi to connect, but didn't go any further.

I'll go back to encrypting the TCP data being transmit to the server.

Paid NordVPN connected with a couple machines using same username/password on one of their servers marked as allowing dedicated connections.  Might break terms of service or not work with more machines.

I will look into it later today.
NordVPN worked great.
I don't like the price of the dedicated server.
I will also add AES256 to SQLitening TCP within a few days.

I will do the same.

NordVPN has a feature called Internet Kill switch.
The SQLitening server can't be accessed while it is on.