SQLitening Support Forum

https://forum.powerbasic.com/forum/user-to-user-discussions/powerbasic-for-windows/763455-running-a-task-scheduler-shortcut

Would it be better to use task scheduler in case your program isn't running?

https://www.windowscentral.com/how-create-automated-task-using-task-scheduler-windows-10

Rearranged the code into a couple of functions.
Not sure when you want to automatically call a function.
Using a timer, at startup, by a click?

#COMPILE EXE
#DIM ALL
#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN AS LONG
 CreateRecords
 ViewRecords
END FUNCTION

FUNCTION CreateRecords AS LONG
 LOCAL x AS LONG
 LOCAL s$
 DIM sarray$()
 LOCAL Errorcode&
 LOCAL sRoomNo AS STRING
 LOCAL sRecordNo AS STRING

 sRoomNo = "501"
 slOpen ("GuestsProDB.db3","C")
 'Registration
  slExe BUILD$("Create Table If Not Exists tblRegistration(Surname TEXT COLLATE NOCASE, Othernames TEXT COLLATE NOCASE, Address TEXT,", _
  "MobileNo TEXT, CompanyName TEXT, Position TEXT, Nationality TEXT, ArrivalDate TEXT, ArrivalTime TEXT, NoOfNights TEXT, DepartureDate TEXT, DepartureTime TEXT,", _
  "StateArrivingFrom TEXT, StateGoingTo TEXT, Identification TEXT, IDNo TEXT, Sponsor TEXT, PurposeOfVisit TEXT, NoOfGuests TEXT, RoomNo TEXT, RoomRate TEXT,", _
  "RoomType TEXT, BillNo TEXT, StayOver)")

  slExe "Create Index If Not Exists Registrationndx1 ON tblRegistration(BillNo)"
  slExe "Create Index If Not Exists Registrationndx2 ON tblRegistration(Surname)"
  slExe "Create Index If Not Exists Registrationndx3 ON tblRegistration(Othernames)"

  slExe slBuildInsertOrUpdate("tblRoomRates", sRoomNo & $NUL & "Available", "RoomNo, Status", "RoomNo=" & sRoomNo)
        Errorcode& = slExe("Insert into tblRegistrationHistory Select * From tblRegistration Where RowID = '" + sRecordNo + "'","E")
        IF Errorcode& = 19 THEN
            MSGBOX "Bill No Already Sent And Is Still Existing In The Registeration History", %MB_TASKMODAL OR %MB_ICONINFORMATION, EXE.NAME$
            EXIT FUNCTION
        END IF

        slEXE "Delete From tblRegistration WHERE DepartureDate <= date('now') AND DepartureTime >= time('12')"
END FUNCTION

FUNCTION ViewRecords AS LONG
 LOCAL s,sArray() AS STRING
 s = "select DepartureDate,DepartureTime,"
 s+= " case"
 s+= "  when DepartureDate <= date('now') THEN 'greater'"
 s+= "  when DepartureDate = date('now') THEN 'less'"
 s+= "  WHEN DepartureTime > time('12') THEN 'Greater Than'"
 's$+= "  else            'equal'"
 s+= " end as NewColName"
 s+= " from tblRegistration"
 slselary s,sarray$(),"Q9"
 ? JOIN$(sarray$(),$CR)
END FUNCTION

Sorry, somebody else might have trigger logic.
Might be able to create a temporary table and create a trigger on it.

https://www.sqlitetutorial.net/sqlite-trigger/

A recordset could be created using a CASE statement
to test any conditions and write what to do for any
of those conditions.  When done, process the recordset.
CASE statements can test the same record multiple times
and can be nested.

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN AS LONG

 slopen ":memory:"
 slexe "create table t1(c1 integer)"
 FOR x& = -10 TO 10:slexe "insert into t1 values(" + STR$(x&) + ")":NEXT

 s$ = "select c1, "
 s$+= " case"
 s$+= "   when c1 > 0 THEN 'greater'"
 s$+= "   when c1 < 0 THEN 'less'"
 s$+= "  else             'equal'"
 s$+= " end as NewColName"
 s$+= " from t1"
 
 slselary s$,sarray$(),"Q9"
 ? JOIN$(sarray$(),$CR)

END FUNCTION

1. No.  How are col;umns related?
http://www.garybeene.com/sw/gbchartmaster.htm
https://forum.powerbasic.com/forum/user-to-user-discussions/powerbasic-for-windows/61086-gbchartmaster-discussion

Quote from: undefinedI find the way this is handled in SQLite to be one of the great features.
I normally create columns with no data type, which will result in an
affinity of none, by using the following create syntax:
Code Select
  Create Table T1 (C1, C2, C3)
I have found no advantage in assigning column affinity using the following:
Code Select
  Create Table T1 (C1 Integer, C2 Text, C3 Real)

Hi, Fredrick!

It goes against the above post #1, but had to say something.
Glad we are doing it the same way.
I haven't been assigning affinity or NOT NULL in my demos and will try to remember.

I am bringing back this very old subject because of problems not defining a column as TEXT.
Be careful not specifying INTEGER or TEXT in CREATE statements.

1. text and binary values are inserted into the column
2. 101 is not "101" so searching and sorting may not return expected value.

If column is UNIQUE:
insert 101 and '101' and two values are insert if no datatype
insert 101 and '101' and second value produces duplicate error if datatype was specified

Bottom line:
Specifying (or not specifing) data type in CREATE statements is very important

CREATE statement without specifying column type and 2 records are inserted
create table t1(key1 primary key) without rowid
insert into t1 values('101') 'insert success
insert into t1 values(101)   'insert sucess

CREATE statement specifying column as TEXT and 1 record is insert and second produces duplicate error
create table t1(key1 TEXT primary key) without rowid"
insert into t1 values('101') 'insert success
insert into t1 values(101)   'duplicate

This example demonstrates inserting 2-values into a unique column
The first loop creates table without a datatype and 2-values are insert
The second loop creates table with a datatype and 1-value is insert

#INCLUDE "sqlitening.inc"
FUNCTION PBMAIN () AS LONG
 slSetProcessMods "E1"
 LOCAL x AS LONG, sArray() AS STRING
 FOR x = 1 TO 2
  slopen "junk.db3","C"
  slexe "drop table if exists t1"

  IF x =1 THEN
   slexe "create table if not exists t1(key1 primary key) without rowid"
  ELSE
   slexe "create table if not exists t1(key1 TEXT primary key) without rowid"
  END IF

  slexe "insert into t1 values('101')"
  slexe "insert into t1 values(101)"
  slselary "select * from t1",sArray()
  ? JOIN$(sArray(),$CR),,"create table if not exists t1(key1 primary key) without rowid"
  slClose
 NEXT
END FUNCTION

'Test multiple clients without multiple computers or SQLitening server

#INCLUDE "sqlitening.inc"  'fredrick9.bas

FUNCTION PBMAIN AS LONG

 LOCAL x,hthread AS LONG

 'Simulate 3 client workstations
 FOR x = 1 TO 3
  THREAD CREATE WorkStation(x) TO hThread
  THREAD CLOSE hThread  TO hThread
 NEXT
 DO:SLEEP 100:LOOP UNTIL THREADCOUNT=1
END FUNCTION

 ViewAll

'===========================================================
THREAD FUNCTION WorkStation(BYVAL x AS LONG)
 Helper x
END FUNCTION

SUB Helper(x AS LONG) THREADSAFE
 slopen "hello.db3","C"   '<--- would be in a shared folder on network
 slexe  "create table if not exists t1(column1)"
 slexe  USING$("insert into t1 values('write from thread#')",x)
 slclose
END SUB


'===========================================================
FUNCTION ViewAll AS STRING
 LOCAL s() AS STRING
 slopen "hello.db3"   
 slselary "select * from t1",s(),"Q9c"
 ? JOIN$(s(),$CR),,"All users done"
END FUNCTION

True, just a normal local area network.
Share a folder on one computer, and map to that folder on the clients.
The SQLitening server/service would not be needed nor the SQLiteingServer.exe be distributed.
Clients would use something like this: slOpen "z:\test\database.db3","C"

Fredrick,
Download/extract SQLiteningServer.bas https://sqlitening.planetsquires.com/index.php?topic=9697.0

SUB LogIt() can be modified to whatever you want.
Compile and replace SQLiteningServer.exe
'===================================================================================

Bypassing the server is much faster and less to distribute.
I tested without using SQLiteningServer to a mapped drive without issue.

slOpen "z:\test\database.db3","C"
Always use "begin immediate" or "begin exclusive" instead of just "begin"

I've used the server for many years and thinking of swithing to simple networking.

Is the server really needed or better on a small LAN?

Instructions added to post #1

Definitely.  See the first post in the general forum.

The link is to a modified SQLiteningServer.bas that might already do what you want.
Compile it and copy  SqliteningServer.exe to whereever your current SQLiteningServer.exe is.

If you installed the modified SQLiteningServer.Bas
the log (SqliteningServer.log) would look like this:

07-21-2019 14:45:23  SELECT * FROM parts LIMIT 1
07-21-2019 14:45:55  SELECT * FROM parts LIMIT 2 ORDER BY PRICE

Also, if you run the filewatcher.exe program, every time someone
accesses the server you would see a scrolling window move up one
line to show you the last line in the log file (SqliteningServer.log)

Sounds like all you need to do is install the modification
and optionally run file watcher to see the changes to the log in real-time.

Quote from: undefinedI've noticed running without the server is much faster on a local network
and not sure it is even needed by most.

>> Really? How does that work?

REM slConnect

Fredrick,

I haven't used procs, but was thinking of making it easier to insert functions into the server.
I'm not convinced adding code to the server is harder than using a proc once a function is added to demonstrate it that can be duplicated and modified.

After the modification below, SQliteningServer.bas compiles with a click.

1. Did you download and install this:
https://sqlitening.planetsquires.com/index.php?topic=9697.msg26453;topicseen#msg26453

2. Is this to add a time stamp to
  Every insert (unlikely)
  Create a table logging all entries?
  Create a flat file text log on disk?

3. This is for the server, right?

I've noticed running without the server is much faster on a local network
and not sure it is even needed by most.

Downloaded 97-times.
Wonder if anyone uses it?

Might add a new function or two to see if it would
be easier and faster than using the Procs method.
The code compiles "as is" with the update above.