• Welcome, Guest. Please login.
August 12, 2020, 11:40:22 pm


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 - Frank W. Kelley

Thanks, Bern. That certainly will shorten the query and make future changes easier.

In searching the web, I noticed that some versions of SQL support the "WHERE (F1,F2,F3) NOT IN (csl)" construct, but SQLite apparently isn't one of them.
Is there a "simple" way to query multiple fields against a single comma separated list? I'm trying to avoid multiple "NOT IN" sections of a query. The ideal solution would be something like:

"SELECT Name FROM Accounts WHERE (F1,F2,F3) NOT IN (comma-separated-list)"

But SQLite doesn't appear to like this. I'm trying to avoid:

"SELECT Name FROM Accounts WHERE F1 NOT IN (comma-separated-list)
            AND F2 NOT IN (comma-separated-list)
            AND F3 NOT IN (comma-separated-list)"

Mostly because there will be up to seven "F" fields in the query.

Any suggestions are welcome.
SQLite/Lightning has been my go-to DB solution for so long now, I have difficulty remembering exactly what I used previously.

At the moment, I'm finishing up an application that mimics a radio station, allowing a user to program his own "station" on his PC (for those who have always felt they can do a better job of programming than the guys actually on the radio). Perhaps my 30 years in radio broadcasting had something to do with this idea. The program uses two MCI controls to allow overlapping of music. Each song can be edited to apply custom mix points and tempo-matching, just like on the radio. (By the way, I'm listening to "my" oldies station as I write this.)

The important point from this board's perspective is that under PBWin 10 and SQLite, the application can import song files (mp3 or wav), parsing the file names in the format "title_artist.wav", or just by title if there is no underscore or dash in the file name. Moreover, it also updates an existing database, recognizing when new songs have been added to the song source directory and deleting any that have been removed. And it keeps tabs on artists, so the listener can prevent the same artist repeating too often.

Oh, it does all this for a library of 500+ songs in 0.03274 seconds (time varies from run to run, but always under 0.04 seconds).

The power of PBWin's ARRAY command and SQLite makes it possible. Truly a remarkable combination.
Solved it via Google -- just need to parse the selected, comma separated list as follows:

The original contents of "list" was: "1628,173,179,194". This was parsed into a string variable "listorder" which uses WHEN/THEN. The expanded result looks like this:

"SELECT Title FROM Songs WHERE RecID IN ("1628,173,179,194") ORDER BY CASE RecID WHEN 1628 THEN 1 WHEN 173 THEN 2 WHEN 179 THEN 3 WHEN 194 THEN 4 END"

Works perfectly!
I have some code that selects records based on a collection of IDs, as in:
"SELECT Title FROM Songs WHERE RecID IN (" + list + ")"

where "list" is something like "37,202,18,9,706,5".

The goal is to have the records appear in the same order as they appear in the "list" variable. However, SQLite appears to default to having the records appear in ascending numerical order. Is there an "ORDER BY" clause that will preserve the record IDs in their original order? Every "ORDER BY" combination I've tried so far does not produce the desired results.
General Board / Re: slDisconnect corrected
November 18, 2015, 04:20:27 pm
Thanks, cj, for your work on this. It is appreciated!
General Board / Re: slDisconnect corrected
November 17, 2015, 07:53:19 pm
So, is this the final solution to this issue? (adding "RESET thMutex"?)

SUB SQLiteDisconnect ALIAS "SQLiteDisconnect" EXPORT
  @tlpKillImHere = 1
  RESET tlpKillImHere
  DoRequest %reqDisconnect, 0, 0, "", 0
  TCP CLOSE thSocket
  CloseHandle thMutex
  RESET thMutex
I originally went the pop/push route and still had problems. This solution is solid for my situation. I offer it for those who might experience the same problem and need a fix that works.
This is apparently a resolution to a problem discussed at length in this thread.

The program I'm working on allows users to switch between a local database and a server-hosted database. A selection window allows them to enter the server host's name or IP address, then test the connection. On the local machine (LocalHost) that is running the server, the code executed without fail every time. But when I ran it on a client machine, the switch between databases would freeze on the second call to slConnect.

The solution (after MANY hours of troubleshooting) was to remove all instances of slDisconnect (and slClose) in my switching code. To switch from one database to another, simply execute an slOpen (for the local DB) or slConnect followed by slOpen when switching to the server. The switching code now works flawlessly on all the test machines.

The clue was in an old post by Fred where he explained there is only a single handle used for the database. When you open a new handle the previous one is forgotten.

If you're experiencing a freeze problem when switching/opening databases, this could be the answer.
The issue does not involve timeout or any external setting. slSetRelNamedLocks simply ignores the rsSelModChars argument when a named lock conflict occurs.

Here is a revised version of the slSetRelNamedLocks routine (including comments) to facilitate copy/paste for those who would like to modify the DLL. Using this routine, setting rsSelModChars = "E0" correctly returns the error value to your calling routine without displaying the additional MSGBOX alert internally before doing so. Note that for simplicity, I did not test for "E" alone, so "E0" is required in this instance. New additions are in bold. (Also added the routine as a zip file.)

'======================<[ Set Rel Named Locks ]>=======================
FUNCTION slSetRelNamedLocks ALIAS "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) EXPORT 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.
'      C  = Will first do a slCloseSet(rlSetNumber). This will prevent
'           error 14 (%SQLitening_InvalidSetNumber) but should be used
'           with caution. Omiting set number or passing set number of
'           zero will do the same thing.
'      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.

   LOCAL lsColumnNames AS STRING

   ' Must handle differently if running remote
   IF tuFlags.AreRunningRemote THEN

      ' Do pre select stuff if there is a slSel commnad
      IF LEN(rsSelStatement) THEN
         ' Close set if requested
         IF INSTR(rsSelModChars, "C") OR ISFALSE rlSelSetNumber THEN slCloseSet(rlSelSetNumber)
         ' Audit it
         IF ISFALSE irPreSelect(rsSelStatement, rlSelSetNumber) THEN ErrorRut
      END IF

      ' Call remote rut
      irGetRutAddress "SQLiteSetRelNamedLocks", lhRutAddr
      lNoMsg = (INSTR(rsSelModChars, "E0") > 0)
         CALL DWORD lhRutAddr USING UsingSetRelNamedLocks(thDab, rsLockNames, rsModChars, rsSelStatement, rlSelSetNumber, rsSelModChars, lsColumnNames) TO tlLastError
         IF ISFALSE tlLastError OR tlLastError = %SQLITE3_BUSY THEN EXIT DO
         IF tlLastError <> %SQLitening_LockTimeout OR ISTRUE tuDbFlags.Bits.DoNotRetryIfBusy OR ISTRUE(lNoMsg) THEN ErrorRut
         IF MSGBOX(LEFT$("Named Locks " & rsLockNames & ", " & rsSelStatement, 255) & STRING$(2, $CR) & _
              "The above request was unsuccessful because the named lock(s) is currently locked.  Want to try again?", %MB_ICONQUESTION + %MB_YESNO, $Title) = %IDNO THEN ErrorRut

      ' Do post processing for the Sel stuff
      IF LEN(rsSelStatement) THEN

         ' Check if had error
         IF tlLastError AND ISFALSE irCheckIfBusy("Named Locks " & rsSelStatement) THEN ErrorRut
         DO WHILE tlLastError
            CALL DWORD thCallSel USING UsingSel(thDab, rsSelStatement, rlSelSetNumber, rsSelModChars, lsColumnNames) TO tlLastError
            IF tlLastError AND ISFALSE irCheckIfBusy("Named Locks " & rsLockNames & " " & rsSelStatement) THEN GOTO ErrorRut
         LOOP WHILE tlLastError = %SQLITE3_BUSY

         ' Mark the set as active
         thaSets(rlSelSetNumber) = 1

          ' Do post select stuff
         IF LEN(rsSelStatement) THEN IF ISFALSE irPostSelect(lsColumnNames, rlSelSetNumber, rsSelModChars) THEN ErrorRut

      END IF


      ' Call slSel if they passed a statement
      IF LEN(rsSelStatement) THEN FUNCTION = slSel(rsSelStatement, rlSelSetNumber, rsSelModChars)


   ' Exit OK

   irHandleError "Named Locks " & rsLockNames & ",  " & rsSelStatement, rsModChars & rsSelModChars
   FUNCTION = tlLastError


Thanks for the response, Bern.

We were trying to avoid the "last save wins" scenario by locking the records with named locks. As it turns out, a check of the source code for the DLL revealed that the error setting is ignored when a lock conflict occurs. Adding a couple of lines of code and recompiling the DLL removes the SQLitening message and only the error code is returned, so all is good.
In switching an application from purely local to client/server, I've started switching commands from slSel to slSetRelNamedLocks to prevent problems when more than one user attempts to edit the same record. I've set error handling when making the call to "E0". My understanding is that slSetRelNamedLocks should return an error condition if the requested record is locked. It does this, but only after displaying an SQLitening message that the record is locked and asking if the user wants to retry.

Is there a way to suppress the SQLitening message in order for my code to handle the locking conflict? None of the docs cover this (beyond setting the error flag to "E" or "E0"), and there's no information on these forums.

Any help is sincerely appreciated.

cj, you supplied the answer in this thread: http://www.sqlitening.com/support/index.php?topic=9229.msg24258#msg24258

After the failure, I have to execute slSetProcessMods "L0" to restore local control. That's all it took -- all is working perfectly now!

Thanks for answering my question long before I asked it (and why I didn't search for posts on "slConnect" at the outset, I'll never know.)
If I log on using slConnect at program startup, the connection goes through fine using either the second machine's name or the router static IP.

If I log on to the local machine using the local database (which succeeds) and then execute the code posted earlier with an incorrect machine name for the remote server, the connection of course fails...however, when I attempt to reconnect to the local machine after the failure, the reconnect fails.

This doesn't appear to be a remote connection issue; it's a problem of reconnecting to the local machine's database when the remote connection fails due to an incorrect machine name or IP address. If the remote server machine's name or IP is correct, I can complete the remote connection test and then restore the local DB access with no errors. Executing slGetLastError after each call each step of the way apparently has no affect.
A program I'm developing was originally set up to run only locally, but now we're expanding to allow a networked version. After installing the server on a second machine, I am able to connect by using either the host name or the router static IP and everything works great.

The issue now is when the program is in the hands of users, we wanted to be able to allow them to test the network connection by entering either the host machine name or IP address. However, after testing I am unable to reconnect with the local machine.

IF LEN(remote$) THEN                            ' remote$ contains either machine name or IP address
   IF SLCONNECT(remote$, 0, "E0") = 0 THEN      ' Connected with no error
      ' Attempt to open the database
      errnum& = SLGETERRORNUMBER
      MSGBOX FORMAT$(errnum&)
   SLEEP 500
   SLOPEN gDataPath$ + $DBFILE                  ' Switch back to local file
   ' This produces an error -18 and program terminates

I've tried several combinations of SLCLOSE and SLDISCONNECT with no success. When the program first runs the identical SLOPEN command works perfectly. It is only after executing SLCONNECT that the SLOPEN command fails.

What am I missing?