• Welcome, Guest. Please login.
 
August 20, 2019, 07:53:18 pm

News:

Welcome to the SQLitening support forums!


Custom function for date time stamping

Started by Bern Ertl, December 31, 2012, 11:43:54 am

Previous topic - Next topic

Bern Ertl

Add this to a SQLiteningProcs[Letter] file:UNION Quad_FileTime
   Q AS QUAD
   F AS FILETIME
END UNION

FUNCTION qFormat( BYVAL q AS QUAD) AS STRING

   IF q > 100000&& THEN
       FUNCTION = FORMAT$( q \ 100000&&) + FORMAT$( q MOD 100000&&)
   ELSE
       FUNCTION = FORMAT$( q)
   END IF

END FUNCTION

SUB  sqlFnGetTimeStamp CDECL ( BYVAL hContext AS DWORD, BYVAL iArgs AS LONG, BYVAL pArgv AS DWORD PTR) EXPORT

   'Returns FORMAT$( FILETIME) = current server date/time

   LOCAL sResult AS STRING
   LOCAL ST as SYSTEMTIME
   LOCAL CurTime AS Quad_FileTime

   GetLocalTime ST
   SystemTimeToFileTime ST, CurTime.F

   sResult = qFormat( CurTime.q) + $NUL

   sqlite3_result_text hContext, BYVAL STRPTR( sResult), -1, %SQLITE_TRANSIENT

END SUB

FUNCTION ICF ALIAS "ICF" (BYVAL rhDab AS DWORD, BYVAL rlTcpFileNumber AS LONG, blParm1 AS LONG, blParm2 AS LONG, bsParm3 AS STRING, bsParm4 AS STRING) EXPORT AS LONG

   'ICF = Initialize Custom Functions

   'In = None

   'Out:  FUNCTION = 0 for success, <>0 if error
   '         blParm1 = where in execution path SQLite error occured (if any)
   '         bsParm3 = error message if error occurred

   LOCAL lResult AS LONG, zText AS ASCIIZ * 32

   zText = "ServerTimeStamp"
   lResult = sqlite3_create_function( rhDab, zText, 0, %SQLITE_UTF8, 0, CODEPTR( sqlFnGetTimeStamp), 0, 0)

   IF ISTRUE lResult THEN
      bsParm3 = "Failed to create ServerTimeStamp"
      blParm1 = 500  'Return "error line number"
      FUNCTION = lResult 'Set return code
      EXIT FUNCTION
   END IF

END FUNCTION


In your application:...
   'Register functions and keep them persistent ...
   lResult = slRunProc( "[letter]ICF", lReturn1, lReturn2, sBuffer1, sBuffer2, "Eu")
...
   'Application Code
...
   'Unload custom functions when no longer needed
   slRunProc "[letter]", lReturn1, lReturn2, sBuffer1, sBuffer2, "U"
...


and finally, in your SQL wherever date time stamping is required...CREATE TABLE IF NOT EXISTS TransactionLog (RowID INTEGER PRIMARY KEY, SQL_Statement, User, TimeStamp);
CREATE TRIGGER fki_TransactionLog_Update_TimeStamp AFTER INSERT ON TransactionLog FOR EACH ROW
   BEGIN
      UPDATE TransactionLog SET TimeStamp = ServerTimeStamp() WHERE RowID = NEW.RowID;
   END;

Fredrick Ughimi

Hello Bern,

This is exactly what I have been looking for.

What is the difference SQLiteningProcsA and SQLiteningProcsB?

Which should I used to implement your codes here.

Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

July 21, 2019, 11:15:22 am #2 Last Edit: July 21, 2019, 11:27:30 am by cj
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.

Fredrick Ughimi

July 21, 2019, 03:45:49 pm #3 Last Edit: July 21, 2019, 03:49:24 pm by Fredrick Ughimi
Hello CJ,

Thank you for your response.

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

I think I downloaded it. But I haven't installed it. I will now. How do you use it?

>>Is this to add a time stamp to
  Every insert (unlikely)

Yes. Every Insert, Update, Delete, etc

>>Create a table logging all entries?

Yes. Something like that.

>>Create a flat file text log on disk?

Yes.

>>3. This is for the server, right?

Yes.

>>I'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?

Kind regards.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

July 21, 2019, 05:21:04 pm #4 Last Edit: July 21, 2019, 06:34:07 pm by cj
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 Ughimi

Thanks CJ,

All done now. Would do some testing in the days to come.

>>REM slConnect

And it would still run on a LAN?
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

Definitely.  See the first post in the general forum.

Fredrick Ughimi

Hello CJ,

>>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

I guess it be great if the user or computer on the network is also shown in the log.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

Fredrick Ughimi

>>Definitely.  See the first post in the general forum.

That means no need for port number and the server computer name and ipadress don't matter?

sServer = "Server"
sPort = "Default"
slConnect(sServer, Val(sPort))
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

July 23, 2019, 10:52:22 am #9 Last Edit: July 23, 2019, 11:17:03 am by cj
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?

Fredrick Ughimi

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

How do we implement this in practice in relation to installation? I guess all systems on the network would be client computers with one of the clients computers hosting the database. That means no need to install the servers files where the database resides?

I really like to try this out.
Fredrick O. Ughimi<br /><br />fughimi@gmail.com<br />- Freedom lies in being bold -- Robert Frost, Poet

cj

July 24, 2019, 01:00:13 pm #11 Last Edit: July 24, 2019, 01:03:08 pm by cj
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"

cj

July 24, 2019, 01:29:38 pm #12 Last Edit: July 24, 2019, 06:55:57 pm by cj
'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