• Welcome, Guest. Please login.
August 13, 2020, 12:01:59 am


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.

Topics - Frank W. Kelley

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.
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.
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.
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.
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?
Here is a revision of the current SQLightning help file in .CHM format. It features:

  • Revised structure to allow faster access to topics (fewer clicks)

  • New, modern interface (skin)

  • Links for all keywords throughout the file

  • Expanded cross reference

  • Correction of typos

  • Long sections on keywords broken up into smaller paragraphs (where possible) to improve comprehension

  • Open style typeface for easier reading

  • Consistent formatting of subheads, code examples, etc.

Any feedback is welcome.
I'm trying to determine how to find customers in my database who are no longer active. The DB consists of a table "Customer" with a primary index "CustID". A second table, "Schedule", has a member "Cust" that holds the value of the "CustID" to associate it with a particular customer. I need to be able to determine which customers no longer have any instance of their "CustID" in "Schedule.Cust".

I've tried several statements with no success, my latest being:

"SELECT CustID FROM Customer,Schedule WHERE (CustID = Cust) NOT IN Schedule"

which gives me a SQL syntax error.

Any assistance is appreciated.
I have a program that is currently using Tsunami that I would like to upgrade to SQLitening. The program uses a couple of UDT arrays to store data. I am looking for a way to avoid breaking the UDT into component parts for storage and then re-assembling everything when the data is read.

The ideal solution would be to store each entire UDT array as a single field and then read that field back into the UDT using PEEK$/POKE$. However, a test using this approach reveals that at least one $NUL appears in the resulting string, which would cause problems in SQLitening.

Is there a good solution for this? Initially, I thought of doing a REPLACE to substitute the backspace character for any $NULs then reverse the process when reading the data from the DB, but because integers and currency types are also in the UDT, this approach might end up corrupting the data stream.

Any thoughts?