• Welcome, Guest. Please login.
 
May 26, 2019, 12:54:06 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

691
1) If BYVAL is removed from calls with strings then VB6 could use the same calls as PowerBASIC.
Example:

FUNCTION slConnect ALIAS "slConnect" (OPTIONAL rsServerTemp AS STRING, _
                                      OPTIONAL BYVAL rlPort AS LONG, _
                                      OPTIONAL rsModChars AS STRING, _
                                      OPTIONAL wsOutData AS STRING)Export AS LONG
    LOCAL rsServer AS STRING
    rsServer = rsServerTemp         
END FUNCTION


Trival, but while testing with VB6 found this:
If a connection fails
result = slGetFile(sFile,sData,"E1") will terminate program
One solution is to just use slSetProcessMods "E1"

Passing a nul or "" instead of using OPTIONAL would not be a problem for me.
I actually prefer it because then the options are always right there.

With the modication to Sqlitening.Bas in slConnect above this works fine with VB6.
This eliminates the need for SQLiteningS.dll  with this little VB6 test program.

Declare Function slGetFile Lib "SQLitening.Dll" (ByRef rsFileName As String, ByRef wsFileData As String, Optional ByRef rsModChars As String) As Long
Declare Function slConnect Lib "SQLitening.Dll" (Optional rsServer As String, Optional ByVal rlPort As Long, Optional ByRef rsModChars As String, Optional ByRef wsDataOut As String) As Long
Declare Sub slDisconnect Lib "SQLitening.Dll" ()
Sub Main()
  result& = slConnect("192.168.1.2", 51234)
  result& = slGetFile("RemoteFileUploadDownloadExample.txt", sData$)
  slDisconnect
  MsgBox Str$(Len(sData$)) + " bytes"
End Sub


A more complete VB6 example handling all errors without terminating in environment
Requires SQLitening.DLL and SQLiteningClient.DLL


Option Explicit '#DIM ALL in PowerBASIC
'Sqlitening declares
Declare Sub slSetProcessMods Lib "SQLitening.Dll" (ByRef rsModChars As String)
Declare Function slGetFile Lib "SQLitening.Dll" (ByRef rsFileName As String, ByRef wsFileData As String, Optional ByRef rsModChars As String) As Long
Declare Function slConnect Lib "SQLitening.Dll" (Optional rsServer As String, Optional ByVal rlPort As Long, Optional ByRef rsModChars As String, Optional ByRef wsDataOut As String) As Long
Declare Sub slDisconnect Lib "SQLitening.Dll" ()
Declare Function slGetError Lib "SQLitening.Dll" () As String
Declare Sub apiSleep Lib "KERNEL32.DLL" Alias "Sleep" (ByVal Milliseconds As Long)
'Constants to download
Const Ip As String = "192.168.1.2"  'Change 1 of 2
Const PortNumber As Long = 51234    'Change 2 of 2
Const sFile      As String = "RemoteFileUploadDownloadExample.txt"
'Main entry point
Sub Main()
  ReadFileOnServer
End Sub
Sub ReadFileOnServer()
  On Error Resume Next                 'an invalid might be returned by chdir
  Dim sData As String, result As Long  'define variables
  ChDir App.Path                       'useful if program in a temp folder or using VB6 environment
  slSetProcessMods "E0"                'E0=do not allow ExitProcess to terminate VB6 environment
  result = slConnect(Ip, PortNumber)   'attempt connection on local ip,  0=success
  If result = 0 Then                   'success
    result = slGetFile(sFile, sData$)  'attempt getting file, 0=success
    If result = 0 Then                 'success
      MsgBox Format$(Len(sData), "##,###,###,### bytes returned.") 'display bytes returned
    Else                               '
      MsgBox slGetError                'error, unable to get file
    End If                              '
    slDisconnect                       'disconnect if we connected
  Else                                 '
    MsgBox slGetError                  'error, unable to connect
  End If
End Sub








692
Works great!
Running many processes with many threads and unable to cause a problem.
693
Just installed 1.53.
694
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.
695
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
696
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.
697
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
698
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.
699
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
700
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







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





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


703
Exactly what I needed to get going.

Thank you!
704
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
705
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?