• Welcome, Guest. Please login.
November 28, 2020, 08:08:28 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.

Topics - Bern Ertl

I will have need to retrieve the maximum value of a column (F1) from a table (T1).  How do you write the SQL statement for this?


I've got a table T1 that has fields F1, F2, F3, F4

I'm using the RowID as the primary key (in other words, I'm not defining any of my F# fields as primary keys).

I was to ensure that the pairing of (F2, F3) is unique in the table (and F2 and/or F3 can be NULL).  I'm assuming there is a way to enforce this in the table definition, but I'm not sure how. Ideas?
Is there a way to have one table include an index field to the RowIDs of another table and have the SQLite system enforce referential integrity (so that one a record in the "master" table is deleted, it checks if there are records in the secondary table referencing it)?  Or do I need to handle this with my own code?
Say I want to create a database file with 20 tables.  Is there any difference if I use 1 slExe function call with a very long string parameter of "CREATE TABLE ....; CREATE TABLE ...; etc." versus using 20 slExe calls with one "CREATE TABLE ..." per call?
The slPushDatabase and slPopDatabase functions can use an internal stack that is managed with a GLOBAL string array in the SQLitening DLL.  If there are two applications using the same SQLitening DLL, is the stack shared between the two apps, or does each reference to the DLL get it's own GLOBAL var space?

Also, if I have multiple databases open in remote mode (using the push/pop system), and the client app dies and drops the connection to the server, does the server close all them all?  Is it tracking all the open files too?

So I was debugging my code yesterday and learned a lesson which I thought worth sharing.  I spent 15 befuddled minutes staring at my code before figuring it out.  Maybe it will save someone else some grief if I share...

I was updating an Accounts table with connection handles for each user.  I was writing an Exit procedure (SQLiteningServerExits.DLL) to update this table and set the connection handle field to NULL when they were disconnected.  I check for error conditions at every step of the process.

The code was executing without error and yet not initializing the connection handle field:lResult = slmExe( lhDab, "UPDATE Accounts SET ConHndl = Null WHERE ConHndl = '" + PARSE$( rsData, $BS, 1) + "'")

Eventually, I realized that when I was setting the field, I was using:slExe slBuildInsertOrUpdate( "Accounts", gsConHandle, "ConHndl", "Username = '" + sUser + "'")

SQLitening/SQLite was storing the connection handle as a numeric/integer and I was specifying a string in the WHERE clause of the SET NULL statement.  SQLite processed the statement without error, but didn't find any matches.  Once I removed the single quotes from the WHERE ConHndl = statement, everything worked as expected.

It's going to take some getting used to SQLite's lack of data typing for column/field data.
If I create two separate applications and they both access the same SQLitening server, will there be any issues if an end user runs both programs at the same time?  Will each application get a distinct TcpFileNumber?  Would all network traffic be routed to the correct client application, or would it be possible for one app to receive data intended for the other application?
I've got a table that will store connection handles for users that log in to the application.  I'd like to be able to scan the table like so:

slSel "SELECT * FROM Accounts WHERE Username = '" + sUser + "' AND ConHndl = ''"

The ConHndl = '' is failing for a brand new table where the connection handle has never been stored (the value should be NULL according to the INSERT documentation).

How do I test for NULL?  Also, later when a record has had data stored in the ConHndl field, how can I reset the field to NULL (as opposed to an empty string)?
If I create a table like so:

slExe "CREATE TABLE IF NOT EXISTS Accounts (Username UNIQUE, Password, Access)"

Will SQLite enforce uniqueness on the username column - raising an error if I try to insert or update a duplicate value?
If I read a table into memory and then delete a record by it's RowID index, what happens to the RowIDs for remaining records in the table?  Do they remain the same, or get re-indexed?  Assuming they stay the same, will new records to that table re-use indexes from deleted records, or are the RowIDs always unique even if the record is deleted?
I'm not sure if this is feasible or not.

If I develop a client/server application that includes a user log-in system (ie. server maintains a database of valid user accounts and clients have to log in), is there an elegant way to enable logged in users to send messages to one another?  My main concern is allowing an admin for the system contact a logged in user when s/he is going to alter a system setting that might impact the work that user is doing.
If I select a set with one record/row and use slGetRow just one time to fetch the record, will slGetRow close the set, or does it need to be called a second time (returning %False) in order to close the set?

If I know there is only one record in the set, I assume it would be preferable to call slCloseSet after the slGetRow, correct?
I am designing an application that will need to access several different SQLite/SQLitening database files (remote mode) at various points.

I'm guessing it's only possible to have one database file open at a time unless I use slAttach, is this correct?  I'm not 100% clear on what slAttach does.

Would it be best practice to use slClose to close a database I recently opened and no longer need for immediate processing even if I need to re-open it again shortly, or would there be performance penalties with network traffic for opening and closing the database?

Does slDisconnect automatically close any open database, or does slClose need to be called explicitly?
I'd like to update two fields of the same record with one statement.  Right now, I'm using two SQL statements:

UPDATE table SET field1 = 'value1' WHERE RowID = 1

UPDATE table SET field2 = 'value2' WHERE RowID = 1

I'm guessing there is a better way to do this with just one SQL statement, right?
Fred, in the source for SQLiteningProcsA.BAS, you have a MACRO defined for slmOpen that calls sqlite3_busy_timeout() if there is an error with the sqlite3_open_v2() call.  I checked the sqlite function definition and it's not clear to me why you are calling this function in this case.  Why call it?
I want to build an application that uses several tables in an SQLite database using the client/server mode of SQLitening.  One of the tables in the database contains records that represent a calculation matrix.  Whenever any field on any record in this table is updated, I will execute a server side procedure to re-calculate the matrix and update all fields in the (entire) table.

My question is, how can I prevent user B from updating a field in the table right after user A has done so (and while the matrix is being recalculated)?

I'm thinking that I need to wrap the code in the matrix calculation procedure inside a critical section to ensure it only operates in serial and update the table fields by calling that function (instead of issuing a SQL command to update the table directly).

Is there a better way?
I'm working on a small program and exploring SQLitening/SQLite.  My program creates a local database with a single table and twelve columns, one of which LicenseID is specified as "Integer Primary Key Autoincrement".

I use the following to insert a record into the database (only one field is defined):

slExe slBuildInsertOrupdate("Licenses", BYCOPY zVal, BYCOPY zCol)

zCol is specifying a column other than the LicenseID column.

How can I easily determine what LicenseID is being assigned to the new record?
I realize that SQLitening/SQLite allows one to specify any filename extension, but I'd like to know if the file format generated by SQLite conforms to any published/recognized format/standard.  I see on the SQLite web site, some smaple code using the .db extension.  Is this a known format (ie. that might be accessible to 3rd party tools/utilities not using SQLite)?

Are there any other considerations with choosing a filename extension?
I've got a couple of Win XP computers connected to a cable modem router via a network switch.  There is no central server, but the computers can talk to each other and share printers/etc.  How can I test the SQLitening server (running on one machine and accessed by a client app on the other) with this set-up?  Is the computer's "workgroup" name the same as the host name required by slConnect?
I see the Entry1 and Entry2 functions declared in SQLiteningProcsA.BAS.  I also see the description for slRunProc defined in SQLitening.txt.

Im not sure I am understanding the explaination though.  Any chance someone could post a small sample showing how to call Entry1?