• Welcome to SQLitening Support Forum.
 

News:

Welcome to the SQLitening support forums!

Main Menu
Menu

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.

Show posts Menu

Messages - cj

#1
General Board / slBuildBindDat Replacements
March 18, 2024, 03:33:47 PM
Optimizing insert statements using binding discovered PowerBASIC could run of out stack space around 256K using CHR$ statement.

FUNCTION binds(s AS STRING) AS STRING
 LOCAL sb AS ISTRINGBUILDERA
 sb = CLASS "StringBuilderA"
 sb.add MKDWD$(LEN(s)+1)
 sb.add "T"
 sb.add s
 FUNCTION = sb.string
END FUNCTION

FUNCTION bindb(s AS STRING) AS STRING
 LOCAL sb AS ISTRINGBUILDERA
 sb = CLASS "StringBuilderA"
 sb.add MKDWD$(LEN(s)+1)
 sb.add "B"
 sb.add s
 FUNCTION = sb.string
END FUNCTION

FUNCTION PBMAIN AS LONG
 LOCAL s,sbind AS STRING
 s = STRING$(300000,"A")
 sbind = binds(s)
 sbind = bindb(s)
 'sbind = CHR$( MKDWD$(LEN(s)+1), "T", s ) 'crash around 258729 bytes
 ? "Have a crash-free day"
END FUNCTION         

#3
slSetRelNamedLocks  does not work without SQLitening server and has some problems.
These functions fix that problem and are easy to use.

#INCLUDE ONCE "sqlitening.inc"

FUNCTION PBMAIN AS LONG

slconnect "192.168.0.2"
slOpen    ":memory:"
slexe    "create table t1(c1)"

IF slLock  ("A") THEN IF sllock  ("Z") THEN ? "Locked A,Z"
? slGetLockNames
IF slUnlock("A") THEN IF slUnlock("Z") THEN ? "Unlock A,Z"

END FUNCTION
'=========================================================
FUNCTION slGetLockNames 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 = s2
END FUNCTION
'=========================================================
FUNCTION slIsLock(sId AS STRING) AS LONG
LOCAL Found  AS LONG 'Return 1 on success
LOCAL s      AS STRING
s = slGetLockNames
IF INSTR(s,sID + ",") THEN
  FUNCTION = 1
END IF
END FUNCTION
'=========================================================
FUNCTION slLock(sId AS STRING) AS LONG
LOCAL Found  AS LONG 'Return 1 on success
LOCAL s      AS STRING
s = slGetLockNames
IF INSTR(s,sID + ",") THEN Found = 1
IF Found = 0 THEN    'not found
  slSetRelNamedLocks "+" + sID,"T10000 E0"
  IF slGetErrorNumber = 0 THEN FUNCTION = 1
END IF
END FUNCTION
'=========================================================
FUNCTION slUnlock(sId AS STRING) AS LONG
LOCAL Found  AS LONG  'Return 1 on success
LOCAL s      AS STRING
s = slGetLockNames
IF INSTR(s,sID + ",") THEN Found = 1
IF Found = 1 THEN    'must be found
  slSetRelNamedLocks "-" + sID,"T10000 E0"
  IF slGetErrorNumber = 0 THEN FUNCTION = 1
END IF
END FUNCTION'

#4
Are you using slConnect (which is required?)

IF slUnLock("fim") THEN
   sllock("fim")
END IF






#5
It might be easier except I can't get any return code other than 0
If I call with "+a" multiple times no error.  If I call with "-a" multiple times no error.
Needs "+" or "-" to avoid error 19

At least this works:
If slLock("something") THEN ? "Locked":slUnlock("something")

What am I doing wrong?

Note:  Made these modifications to helper functions:
Added "E0"
If slGetErrorNumber = 0 THEN FUNCTION = 1

I don't really need these functions because when in a record I redisplay the screen every 5-seconds
if anyone changed the current data.  I do this by keeping current buffer in a string and compare
to the new buffer every 5-seconds.  If they don't match, I display new data and display a message.

#INCLUDE ONCE "sqlitening.inc" 'slLock.bas

FUNCTION PBMAIN AS LONG

 LOCAL x,ecode AS LONG
 slconnect "192.168.0.2",5802 '<--- requires sqlitening server
 slOpen    "Junk.db3","C"
 slexe    "drop table if exists t1"
 slexe    "create table if not exists t1(c1 text)
 slexe    "insert into t1 values('apples'),('peaches'),('pumpkin pie')"
 FOR x = 1 TO 2
  ecode = slSetRelNamedLocks("+a", "T0", "", 0, "E2")
  IF slGetErrorNumber THEN ? slGetError,,"sql error":EXIT FUNCTION
  ? slGetLockNames,,"Ecode" + STR$(ecode)
 NEXT
END FUNCTION
'=========================================================
FUNCTION slGetLockNames 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 = s2
END FUNCTION
'=========================================================
FUNCTION slIsLock(sId AS STRING) AS LONG
 LOCAL Found  AS LONG 'Return 1 on success
 LOCAL s      AS STRING
 s = slGetLockNames
 IF INSTR(s,sID + ",") THEN
  FUNCTION = 1
 END IF
END FUNCTION
'=========================================================
FUNCTION slLock(sId AS STRING) AS LONG
 LOCAL Found  AS LONG 'Return 1 on success
 LOCAL s      AS STRING
 s = slGetLockNames
 IF INSTR(s,sID + ",") THEN Found = 1
 IF Found = 0 THEN    'not found
  slSetRelNamedLocks "+" + sID,"T10000 E0"
  IF slGetErrorNumber = 0 THEN FUNCTION = 1
 END IF
END FUNCTION
'=========================================================
FUNCTION slUnLock(sId AS STRING) AS LONG
 LOCAL Found  AS LONG  'Return 1 on success
 LOCAL s      AS STRING
 s = slGetLockNames
 IF INSTR(s,sID + ",") THEN Found = 1
 IF Found = 1 THEN    'must be found
  slSetRelNamedLocks "-" + sID,"T10000 E0"
  IF slGetErrorNumber = 0 THEN FUNCTION = 1
 END IF
END FUNCTION'
#6
Fim,
I had to search and found using "namedlock"
I added 4 functions.  slIsLock should do what you want..

https://sqlitening.planetsquires.com/index.php?topic=9803.msg26798;topicseen#msg26798

FUNCTION PBMAIN AS LONG
 slconnect "192.168.0.2",12345
 slOpen ":memory:"
 IF slIsLock("99") THEN ? "Already locked"  ELSE ? "Not locked"
 IF slLock  ("99") THEN ? "lock success"    ELSE ? "lock failed"
 IF slUnlock("99") THEN ? "unlock success"  ELSE ? "unlock failed"
END FUNCTION





#7
Thanks Paul!
Testing code by running multiple times and no problem.
#INCLUDE ONCE "sqlitening.inc"

FUNCTION PBMAIN AS LONG
LOCAL x AS LONG
slSetProcessMods "E2"
FOR x = 1 TO 3
  slOpen  ":memory:"
  slAttach "sample.db3","paul"
  ? slSelStr("select price from parts limit 1") '3365
  slexe "DETACH DATABASE paul"
  ? slSelStr("select price from parts limit 1") 'no such table: parts
  slclose
NEXT
END FUNCTION
         
#8
I have read closing a SQLite database closes attached databases.
Searched forum and source code and can't find anything on detaching a database.
#9
#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN AS LONG

 LOCAL sArray() AS STRING

 slOpen    "old.db","C"  'old schema: firstname,lastname,id
 slExe     "create table if not exists T1(firstname text,lastname text, id integer primary key)"
 slExebind "insert into T1 values(?,?,null)",bindstr("Heidi") + bindstr("Klum")
 slExebind "insert into T1 values(?,?,null)",bindstr("Robert")+ bindstr("Hope")
 slExebind "insert into T1 values(?,?,null)",bindstr("Bugs")  + bindstr("Bunny")
 slClose

 slOpen    "new.db","C"
 slAttach  "old.db","old" 'new schema: id,name
 slExe     "create table if not exists T1(id integer primary key,name text)"
 slExe     "insert or replace into T1(id,name) select id,lastname||', '|| firstname from old.T1"

 slselary  "select firstname,lastname from old.t1 " +_
           "union all " +_
           "select id,name from t1",sArray(),"Q9c"
 MSGBOX    JOIN$(sArray(),$CRLF)

END FUNCTION

#10
General Board / Default first record
June 11, 2023, 02:54:55 PM
create table if not exists CounterTable(c1 integer,c2 text, c3 text);

insert into CounterTable (c1,c2,c3)              
 select 1,'cool','beans'                                
 where not exists (select * from CounterTable);


#11
SELECT * FROM WORDLIST WHERE LENGTH = 10  'missing column name

https://sqlite.org/forum/forumpost/fc1209eb59bcdbc0ec81e193561063707bcd36953d78a7c295521cd20277d674

QuoteThis is usually due to the operating system keeping recently used disk blocks in memory and/or SQLite doing the same with recently used pages from the database file.

#12
Did you receive an email alert  that I posted this message? :)

There are lots of settings by clicking down arrow to the right of your login name.
I have no idea of the default settings.
#13
Might consider adding an interpreter like PHP to your choice of server.
I use Abyss Web server which allows adding PHP as an extension.
Abyss also directly supports free TLS for life using LetsEncrypt.
It automatically updates itself each year once setup.  Everything is free!

I know people use SQLite as a backend with web pages so it might be a way to go.
I don't know why a private message would fail.  I'll try sending you one.

Note: I was not getting email notifications.  Apparently it is working for me now.
Also, if you are not receiving email alerts, there is a button to receive email alerts in upper right and lower right corner.
I think there is also some setting that may need to be set.
#14
Searched for VB.NET and .NET in upper right corner and found some hits like this.
https://sqlitening.planetsquires.com/index.php?topic=9622.msg25395#msg25395
Might send a private message to someone in any link found.

Sqlitening does no encryption over the internet and does not use certificates.
A middleware program between the client and server could do that.

SQLitening is a pure joy to work with so I hope you can use it.
I would help on the SQLitening side as I don't use any .NET.

---
Call SQLite using .net  (just placed here as a reference.)
https://www.skotechlearn.com/2021/06/connect-SQLite-in-VB-Net.html

There is https://sqlabs.com/cubesql  (the 2-user version is free according to there site.)