• Welcome, Guest. Please login.
 
August 20, 2019, 08:02:07 pm

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

721
The only global is gs, but it isn't used with %MSGBOX=0.
I tried critical section with gs in an earlier version with the same result.
I will put it back in, thank you and run some more tests.
722
Added a few more options.
Updated with a critical section (which was tried before)


#INCLUDE "win32api.inc"
#INCLUDE "\sql\inc\sqlitening.inc"

GLOBAL gCS AS CRITICAL_SECTION
GLOBAL gs  AS STRING
%TimesToRerun    = 1    'might try with a single thread and run many times
%MaxThreads      = 50   'threads to start

'$Server          = "192.168.1.2"  '$NUL for local access
$Server          = "255.255.255.255"
%PortNumber      = 51234

%MaxAttempts     = 10  'retry attempts if busy
%ConnectAttempts = 5    'retry attempts if busy

$FileToGet       = "RemoteFileUploadDownloadExample.txt"
%DisplayMsg      = 0  'show final results in a MSGBOX
$RsModChars      = "S"

%SleepTime       =10  'Milliseconds to wait before/after executing "sl" statements
%BEEP            = 1  'beep upon completion

FUNCTION PBMAIN()

LOCAL hThread AS DWORD, ThreadNumber, RunAgain AS LONG
InitializeCriticalSection gCS

FOR RunAgain = 1 TO %TimesToRerun
  slSetProcessMods "E0"
  FOR ThreadNumber = 1 TO %MAXTHREADS
    THREAD CREATE MyThread(ThreadNumber) TO hThread
    SLEEP 50
    THREAD CLOSE hThread TO hThread
  NEXT
  DO UNTIL THREADCOUNT = 1:SLEEP 1000:LOOP
  IF %DisplayMsg THEN ? gs
  IF %BEEP THEN BEEP
NEXT
? "Done, threadcount" + STR$(THREADCOUNT)
DeleteCriticalSection gCS
END FUNCTION

THREAD FUNCTION MyThread(BYVAL ThreadNumber AS DWORD) AS LONG

  LOCAL sDataReturned, rsModChars AS STRING, attempt,result AS LONG

  DO
    INCR Attempt
    result = slConnect($Server, %portnumber)
    IF result = 0 THEN EXIT DO
    IF attempt = %ConnectAttempts THEN EXIT DO
    SLEEP 100
  LOOP
  IF result THEN ? "Unable to connect in thread" + STR$(ThreadNumber):EXIT FUNCTION

  Attempt = 0
  DO
    INCR Attempt
    SLEEP %SleepTime
    result = slGetFile($FileToGet,sDataReturned,$rsModChars)  'S= Shared  default read/write locked
    SLEEP %SleepTime
    IF result = 0 THEN
       Logit FORMAT$(LEN(sDataReturned),"#,") + " bytes received in thread" + STR$(ThreadNumber):EXIT DO
    ELSE
      BEEP
      SLEEP 500
    END IF
    IF Attempt = %MaxAttempts THEN LogIt "Unable to get " + $FileToGet + " on thread" + STR$(ThreadNumber):EXIT DO
  LOOP UNTIL result = 0
  SLEEP %SleepTime
  slDisconnect
  SLEEP %SleepTime
END FUNCTION

SUB Logit(s AS STRING)
  EnterCriticalSection gCS
  IF %DisplayMsg THEN  gs = gs + s + $CR
  LeaveCriticalSection gCS
END SUB
723
Fred,

Sent you an IP and port number if you would like to test with a remote server.

This brute force testing is only being done to try and produce an error to eliminate them in the future.
I haven't tried reading and writing portions of a file, yet. 
I also tried adding rsModChars = "S" to get rid of read/write lock with same results.

On a GPF these appear in the SQLiteningServer.Log:

111003000330 Eror Could not Send -- Device I/O error
111003000330 Eror Could not Send -- Device I/O error
111003000330 Eror Could not Send -- Device I/O error
111003000330 Eror Could not Send -- Device I/O error
111003000330 Eror Could not Send -- Device I/O error
111003000330 Eror Could not Send -- Device I/O error
111003000330 Dcon #26877 Dropped
111003000330 Dcon #26875 Dropped
111003000330 Dcon #26874 Dropped
111003000330 Dcon #26878 Dropped
111003000330 Dcon #26876 Dropped
111003000330 Dcon #26873 Dropped

The server has been extremely reliable.
724
If someone can see an error in this code, please report it.
I removed the GUI code to try and isolate the problem.
It is always  Exception CODE: c0000005 in the DLL that crashes.


#INCLUDE "\sql\inc\sqlitening.inc"

GLOBAL gs  AS STRING
%PortNumber      = 51234
%MaxThreads      = 20
%MaxAttempts     = 100
%ConnectAttempts = 1
$Server          = "192.168.1.2"  '$NUL for local access
$FileToGet       = "RemoteFileUploadDownloadExample.txt"
%DisplayMsg      = 1

FUNCTION PBMAIN()
  LOCAL hThread AS DWORD, ThreadNumber AS LONG
  slSetProcessMods "E0"
  FOR ThreadNumber = 1 TO %MAXTHREADS
    THREAD CREATE MyThread(ThreadNumber) TO hThread
    SLEEP 50
    THREAD CLOSE hThread TO hThread
  NEXT
  DO UNTIL THREADCOUNT = 1:SLEEP 1000:LOOP
  IF %DisplayMsg THEN ? gs
  BEEP
END FUNCTION

THREAD FUNCTION MyThread(BYVAL ThreadNumber AS DWORD) AS LONG
  LOCAL sDataReturned AS STRING, attempt,result AS LONG
  DO
    INCR Attempt
    result = slConnect($Server, %portnumber)
    IF result = 0 THEN EXIT DO
    IF attempt = %ConnectAttempts THEN EXIT DO
  LOOP
  IF result THEN ? "Unable to connect in thread" + STR$(ThreadNumber):EXIT FUNCTION

  Attempt = 0
  DO
    INCR Attempt
    SLEEP 50
    result = slGetFile($FileToGet,sDataReturned)
    SLEEP 50
    IF result = 0 THEN Logit FORMAT$(LEN(sDataReturned),"#,") + " bytes received in thread" + STR$(ThreadNumber):EXIT DO
    IF Attempt = %MaxAttempts THEN LogIt "Unable to get " + $FileToGet + " on thread" + STR$(ThreadNumber):EXIT DO
  LOOP UNTIL result = 0
  SLEEP 50
  slDisconnect
  SLEEP 50
END FUNCTION

SUB Logit(s AS STRING)
  IF %DisplayMsg THEN gs = gs + s + $CR
END SUB

  Problem signature:
  Problem EVENT NAME:   APPCRASH
  Application NAME:   Test1.EXE
  Application Version:  0.0.0.0
  Application Timestamp:  00003039
  Fault Module NAME:  SQLiteningClient.Dll
  Fault Module Version:   0.0.0.0
  Fault Module Timestamp:   00003039
  Exception CODE:   c0000005
  Exception Offset:   000010d0
  OS Version:   6.1.7601.2.1.0.768.3
  Locale ID:  1033
  Additional Information 1:   0a9e
  Additional Information 2:   0a9e372d3b4ad19135b953a78882e789
  Additional Information 3:   0a9e
  Additional Information 4:   0a9e372d3b4ad19135b953a78882e789
725
slGetFile also works in a threaded environment where each thread must issue a  slConnect.
slDisconnect may be used after a thread finishes. 

Any file to be transferred must have its name in the [FACT] section of ..\bin\SqliteningServer.cfg.
The physical file on the server must be in ..\bin or underneath on the server.
726
With text files it is easy to just let another program handle the data rather than loading into a control.
I just used the filename already in the FACT which is nice for passing data back and forth.


FUNCTION GetFile AS LONG
  LOCAL result AS LONG, sFileName, sData, rsModChars, NewFile AS STRING
  sFileName = "RemoteFileUploadDownloadExample.txt"
  result = slGetFile(sFileName,sData,rsModChars)
  IF result THEN EXIT FUNCTION
  NewFile = EXE.PATH$ + sFileName
  result = FREEFILE
  OPEN  NewFile FOR OUTPUT AS #result
  IF ERR THEN ? "error" + STR$(ERRCLEAR) + " opening for output " + NewFile:EXIT FUNCTION
  PRINT #result, sData
  IF ERR THEN ? "error" + STR$(ERRCLEAR) + " writing " + NewFile:CLOSE #result:EXIT FUNCTION
  FLUSH #result
  CLOSE #result
  ShellExecute(ghDlg, "Open", NewFile + CHR$(0), $NUL, $NUL, %SW_SHOWNORMAL)
END FUNCTION
727
I have seen where the windows firewall doesn't ask to add SQLiteningServer.exe and
the server will work for awhile and then be blocked.

Here are useful commands that could be shelled to from your program or typed at a command prompt. 
I created a batch file named firewall.bat below to test them.

This is a lot easier than having users do this:
Click start, control panel, Windows firewall, Allow a program or feature through Windows Firewall, Allow another program, Browse, Find and click on SqliteningServer.exe,, click add, click ok.


@cls
rem Allow a program or feature through Windows Firewall http://support.microsoft.com/kb/947709

netsh advfirewall set currentprofile state off
netsh advfirewall set currentprofile state on
netsh advfirewall firewall delete rule name="SQLiteningServer.Exe"
netsh advfirewall firewall delete rule name="Open Port 51234" protocol=TCP localport=51234
netsh advfirewall firewall add rule name=   "SQLiteningServer.Exe" dir=in action=allow program="SQLiteningServer.exe" enable=yes
netsh advfirewall firewall add rule name=   "Open Port 51234" dir=in action=allow protocol=TCP localport=51234







728
Are deferred transactions as reliable as exclusive transactions?
Do exclusive transactions execute faster since nobody can access the databases? 
Could a deferred transaction cause a different result than an exclusive transaction?

A shared lock is changed to a reserved lock before writing and only one reserved lock is allowed.
http://www.sqlite.org/lockingv3.html

It is mentioned that exclusive locks are released as quickly as possible to maintain concurrency.




http://www.sqlite.org/atomiccommit.html

7.2 Exclusive Access Mode
SQLite version 3.3.14 adds the concept of "Exclusive Access Mode". In exclusive access mode, SQLite retains the exclusive database lock at the conclusion of each transaction. This prevents other processes for accessing the database, but in many deployments only a single process is using a database so this is not a serious problem. The advantage of exclusive access mode is that disk I/O can be reduced in three ways:
1.
It is not necessary to increment the change counter in the database header for transactions after the first transaction. This will often save a write of page one to both the rollback journal and the main database file.

2.
No other processes can change the database so there is never a need to check the change counter and clear the user-space cache at the beginning of a transaction.

3.
Each transaction can be committed by overwriting the rollback journal header with zeros rather than deleting the journal file. This avoids having to modify the directory entry for the journal file and it avoids having to deallocate disk sectors associated with the journal. Furthermore, the next transaction will overwrite existing journal file content rather than append new content and on most systems overwriting is much faster than appending.


The third optimization, zeroing the journal file header rather than deleting the rollback journal file, does not depend on holding an exclusive lock at all times. This optimization can be set independently of exclusive lock mode using the journal_mode pragma as described in section 7.6 below.



9.1 Broken Locking Implementations
But if you must use a network filesystem to store SQLite database files, consider using a secondary locking mechanism to prevent simultaneous writes to the same database even if the native filesystem locking mechanism malfunctions.





729
Thank you very much..
I will convert the demos to PBFORMS and post when done.
I really like using an array or tab delimited file to feed into a grid.


730
Exactly what I needed to get going.

Thank you!
731
What would be the correct sql statement be to browse forward or backward on any key?
Something like this?
SELECT * FROM TABLE$  WHERE Key$ < CurrentKey$  LIMIT Number$
SELECT *   FROM TABLE$  WHERE Key$ > CurrentKey$ LIMIT Number$

Not sure about getting lowest and highest  key

Something like this?
FUNCTION  GetRec(Table$, Key$, Direction$, Number&) AS STRING
   SELECT statement
   FUNCTION  = RecordSet$
END FUNCTION

Is there a common sub or function to return the recordset in this manner?

Anyone have an example using PBFORMS?
This would really help me get going on converting to sql.

Would it be possible to write a single function that does about everything needed to browse
without having to write sql statements?   Just pass the parameters instead?


Thank you
732
Rolf,
Sorry, the recordsets would not be CONSECUTIVE with the order by and where in my example.
How to do with only a SELECT statement?


733
How about WHERE and ORDER BY in the select statement using the recordset column number output by number.
Using the SQLitening sample database here just demonstrating the syntax

slSel "SELECT PRICE FROM PARTS WHERE PRICE > 9 ORDER BY 1 DESC LIMIT 10"
slSel "SELECT * FROM PARTS WHERE PRICE > 9 ORDER BY 4 DESC LIMIT 10"
                                                                       
734

'UpDown.bas   Works great!!
#DIM ALL
#COMPILE EXE "..\Bin\UpDown.exe"
#INCLUDE "..\Inc\SQLitening.Inc"
FUNCTION PBMAIN AS LONG
  LOCAL sServer, sFileName,sWrite,sRead, sResults AS STRING
  LOCAL PortNumber, counter, result AS LONG
  slSetProcessMods "E0"
  sServer = "192.168.1.3"
  PortNumber = 51234
  result = slConnect(sServer, PortNumber)
  IF result THEN ? "Connect error"+ STR$(Result),%MB_ICONERROR,EXE.FULL$:EXIT FUNCTION
  FOR counter = 1 TO 10
    sFileName = "TEST1A.DAT"  'TEST1A.DAT in [FACT section]
    IF counter = 4 THEN sFileName = "" 'create an error
    sWrite = SPACE$(counter * 100000)
    result = slPutFile(sFileName, sWrite,"C")
    IF result THEN  sResults = sResults + "Put error" + STR$(result)+ $CR
    result = slGetFile(sFileName, sRead)
    IF result THEN  sResults = sResults + "Get error" + STR$(result) + $CR
    sResults = sResults + FORMAT$(LEN(sRead),"#,") + " bytes" + $CR
  NEXT
  slDisconnect
  ? sResults,,EXE.FULL$
END FUNCTION