I thought it would be appropriate to give you a preview of the major changes coming in 1.3.
Depending on your feed back, SQLitening 1.3 should be available around Nov 1.
**** Named Locks ****
If you google "named locks" you will get many pages rerturned. Both Oracle and MySql support named locks.
Below is SQLitening proposed implementation for named locks:
'======================<[ Sel Rel Named Locks ]>=======================
Function slSetRelNamedLocks (byref rsLockNames as String, _
optional byval rsModChars as String, _
optional byval rsSelStatement as String, _
optional byval rlSelSetNumber as Long, _
optional byval rsSelModChars as String) as Long
' Will set or release named lock(s). This can be used to
' implement application locks or to simulate record locks. Names
' are locked on a server-wide basis. If a name has been locked by
' one client, the server blocks any request by another client for
' a lock with the same name. This allows clients that agree on a
' given lock name to use the name to perform cooperative named
' locking. But be aware that it also allows a client that is not
' among the set of cooperating clients to lock a name and thus
' prevent any of the cooperating clients from locking that name.
' One way to reduce the likelihood of this is to use lock names
' that are database-specific or application-specific. Named locks
' are only used in remote mode, ignored when running in local mode.
' Will also optionally do a Sel command but only if the lock request
' was successful.
' LockNames:
' This is a vertical tab ($VT) delimited list of names to set or release.
' Be sure these names uniquely identify the lock you want to set. If, for
' example, you know the RowID of a record you want to lock you would need
' to include some type of table ID within the name. The first character of
' each name must be one of the following:
' + (plus) = Set lock
' - (minus) = Release lock.
' All named locks owned by a connection are automatically released when
' the connection is no longer active.
' ModChars:
' Tn = Where n is the number of milliseconds to wait for a named
' lock to be set before returning as unsuccessful. If omitted
' or is zero then will return immediately if a named lock can
' not be set because it is owned by another connection.
' R = Release all named locks owned by this connection before setting
' or releasing any named locks in LockNames.
' SelStatement:
' If the SelStatement is not empty and locking was successful then
' will do the Sel command. The Statement should be Select or Pragma
' or any other that returns rows (Insert, Update, and Delete do not
' return rows).
' SelSetNumber:
' SelSetNumber can be omitted or be any value from 0 to 32767. If omitted
' then will use zero. If SelStatement is empty then this is ignored.
' SelSetNumber is used as an array index, no gaps is best, which will result
' in a smaller array. You can have as many unique sets open/active at same
' time as your memory will allow.
' SelModChars:
' This value is ignored it SelStatement is empty.
' D = Allow duplicate column names. Not recommended if using slFN or slFNX.
' because you will always get the first value returned. SQLite does
' not normally return qualified column names. SQLite will return C1
' twice if you Select T1.C1, T2.C1. So the solution is to alias one of
' them with the As clause as follows Select T1.C1, T2.C1 as C1Again.
' There is a Pragma called "full_column_names" which forces SQLite to
' return qualified names, but does not seem to work if you Select *.
' Read up on it and use if you like. I like using an alias because it
' is less code and more clear.
' Em = Return errors. This will override the global return errors flag.
' m is the optional message display modifier and can be:
' 0 = No message is displayed. This is the default.
' 1 = Display a warning message with OK button. Error is
' returned when OK pressed.
' 2 = Display a question message with OK and Cancel buttons.
' If they press OK, error is returned. If they press
' Cancel, will exit process.
' e = Do not return errors, display message and exit process. This
' will override the global return errors flag.
' Bn = Do a Begin Transaction before doing the Sel command. The type
' of Begin is controlled by the value of n as follows:
' 0 = Deferred. This is the default if n is omitted.
' 1 = Immediate.
' 2 = Exclusive.
' This allows for database locking and selecting in one trip to the server.
' CAUTION: If the Begin or the Select returns Busy then will restart with
' the Begin. Use Begin Immediate to prevent this or set ProcessMods
' to %gbfDoNotRetryIfBusy.
' R = Release all named locks owned by this connection after doing
' the Sel.
' ReturnCode:
' Zero if locking was successful. If locking failed then depending on
' ModChars and the global return errors flag, will either display error
' and exit or will return %SQLitening_LockTimeout. If locking failed then
' no locks are set nor are any released and no Sel is run. If locking was
' successful and there is an optional SelStatement then will return zero if
' processed OK. Else, depending on ModChars and the global return errors flag,
' will either display error and exit or will return the error number.
' Examples: (No error checking shown)
' slSetRelNamedLocks("+Cust1395" & $VT & +"Cust40928") -- Will set two named locks.
' slSetRelNamedLocks("+Cust1395" & $VT & "-Cust40928") -- Will set one named locks and release another.
' slSetRelNamedLocks("-Cust1395") -- Will release one named locks.
' slSetRelNamedLocks("+Cust1395", "T15000R", "Select * from Cust where Num=1395")
' Will first release all locks for this connection then set one named lock,
' waiting up to 15 seconds. If lock was set OK then will select the customer.
' slSetRelNamedLocks("", "R") -- Will release all locks for this connection.
' slSetRelNamedLocks("") -- Will do nothing.
**** Get Status ****
This new function will return different data depending on the passed request.
'===========================<[ Get Status ]>===========================
Function slGetStatus alias "slGetStatus" (byval rlRequest as Long)Export as String
' Returns the requested status which is normally a $NUL delimited list.
' If an error occurs then will return an empty string. You may call
' slGetError or slGetErrorNumber to determine the reason.
' The following requests are currently valid:
' 1 = Return all named locks. Will always return an empty string
' if running in local mode. Each named lock will be delimited
' by vertical tabs ($VT). Lock data will be delimited by
' backspaces ($BS) as follows:
' 1 = Tcp file number that owns the lock
' 2 = Lock value
' 3 = Status (0 = Locked, <>0 = Waiting)
' 4 = Time lock set (Milliseconds after midnight)
' 5 = User
' 6 = Computer
' 7 = IPAddress
' 8 = Connect Date-Time
' 2 = Return all connections. Will always return an empty string
' if running in local mode. Each connection will be delimited
' by vertical tabs ($VT). Connection data will be delimited by
' backspaces ($BS) as follows:
' 1 = Tcp file number
' 2 = User
' 3 = Computer
' 4 = IPAddress
' 5 = Connect Date-Time
' 3 = Return SQLitening flag settings. Will return a comma delimited
' list of one or zero for each flag as follows:
' 1 = One if AreRunningRemote is on.
' 2 = One if AreConnected is on.
' 3 = One if ReturnAllErrors is on.
' 4 = One if DisplayErrorMessage is on.
' 5 = One if DisplayErrorMessageQuestion is on.
' 6 = One if DoNotRetryIfBusy is on.
**** SQLiteningServerAdmin Tools ****
There is a new dialog available which allows for the following tools:
QuoteRefresh the Config Flags and FACT
List all Active Connectons
Kill one Active Connection
You already have the ability to "Refresh" but the other two are new.
**** Other Important Changes ****
QuoteChange SQLiteningServer.Exe to timeout a connection after 30 minutes of
no activity. You can do an empty string slExe if you need to keep a
connection active. Error %SQLitening_SendOrReceiveError will occur if
you attempt to use a connection that has timed out.
Added the following ModChars to slSel, slSelAry:
Bn = Do a Begin Transaction before doing the Sel command. The type
of Begin is controlled by the value of n as follows:
0 = Deferred. This is the default if n is omitted.
1 = Immediate.
2 = Exclusive.
This allows for database locking and selecting in one trip to the server.
Your feed back is welcome.
Hi Fred,
It all looks perfect to me. I especially like the approach to Named Locks (I learned something new... i didn't know the industry had such a term already in use). I think that the Named Locks approach is robust and efficient - especially since you can add locks, remove locks, and even retrieve a SELECT, all in one function call. Very cool.
One question: Is the connection timeout value hard coded, or will it be part of the server config file? I expect that many users will want to be able to use values different than 30 minutes. You know how picky users can be. :D
The timeout value will default to 30 minutes, changeable in the config file.