SQLitening Support Forum

I have upgraded the forum to us SSL encryption. You should see an secure padlock type of icon in your url address bar and the forum address starting with https://
If you encounter problems with the forum, please let me know. Thanks!

I finished moving the forum to a new web host. This forum is now served as a subdomain of PlanetSquires.
http://www.sqlitening.planetsquires.com/index.php

If you find any problems then please let me know. I think that you might have to re-upload any custom avatars that you were using. The attachments should be okay.

Quote#====================<[ Version 1.7 November 24, 2015 ]>====================
 1. Fixed code in SQLiteningServer.BAS : ProcessRequest() and the %reqConnect handler.
    Was not adding the required data (for bytes 7-?).
 2. Added new slKill function that allows a connection to be killed via program code.
 3. Updated the slExeBind documentation to correct the "CN" flag in slBuildBindDat to "CE".
 4. Updated code to correct error handling in slSetNamedLocks.
 5. Fix for issue with slDisconnect causing lock ups (added Reset thMutex in SQLiteDisconnect).

You must be a registered forum member and logged in to see the download links.

Quote
#====================<[ Version 1.6.5 December 6, 2012 ]>====================
1. Added the two new functions (slIsDatabaseNameValid and slIsTableNameValid) to
    the Special and Universial API's

2. Fixed bug when passing a database handle across threads in remote mode. 

3. Fixed bug in slExeBind that would sometimes store invalid integer or real numbers.

4. Fixed all array scan commands so they would not execute when the array is not dimensioned.

5. Fixed the return version routines in SQLiteningClient and SQLiteningServer. They would
    fail only if you are  doing multi connects within threads.

Read all about SQLite 4 in their wiki:  http://www.sqlite.org/src4/doc/trunk/www/index.wiki

From the SQLite mailing list:

http://www.mail-archive.com/sqlite-users%40sqlite.org/msg52055.html

You can view the contents of the book and comment on it before it gets published later this summer.

SQLitening Version 1.20 (changes)

====================<[ Version 1.2 June 19 2009 ]>=====================
1. Changed SQLitening.Dll to close the database when detaching and running local. This will process and remove any pending
journal file. This does not change the end result it just cleans up the journal file sooner.

2. Fixed Load/Unload problem with RunProc.

3. Added the slIsOpen function which will return %True if a database is open.

4. Added slPushSet and slPopSet. Push will save the data about an open set while pop will restore the data. This allows you
to reuse a set number with out first closing it.

5. Added a global flag (AreConnected) to improve the control of running remote or local and allowing for retrying to connect to server if first attempt(s) failed.

6. Fixed bug in server that would cause a crash if Close was called at wrong time.

7. Added bind blob and bind text examples to SQLiteningProcA.Bas.

8. Added another conditional compile Server Exit #4 which will fire for ever slOpen. This is the place to create SQLite custom function.

9. Added Exit #4 to SQLiteningServerExits.Bas and included as example of two SQLite customer functions (MyF0 and MyF2).

10.Added the "S" ModChar the both slGetFile and slPutFile.
S = Open file with Shared lock, default is both Read and Write locks.
This only applies to standard files, not Ini.

11.There will now be three supported API's. Basic, Special, and Universal. See doc in SQLitening.Txt. Support for SQLiteningVB is discontinued.

12.Included the zip file UserMods.Zip which contains user contributed additions and/or modifications to SQLitening soruce code.

13.Update SQLite3.Dll to version 3.6.15 dated 06-15-2009.


[attachment deleted by admin]

I want to publicly acknowledge and thank our friend Joe Byrne for transferring the SQLITENING.COM domain to me. That was very, very, cool of him to do so.

I will be eventually moving all Sqlitening stuff (including these forum posts) over to the new site once I get it organized and posted.

Big News!

A new version of SQLitening has been released.... Version 1.20. Please visit the download page for details about this release and the download link.

http://planetsquires.com/sqlite_client_server.htm

Thanks Fred!

Refer to this thread: http://planetsquires.com/support/index.php?topic=2690.0

Hi Everyone,

Fred sent over a small bug fix to the server. I have reuploaded the SQLitening 5 package if any of you wish to download again.

SQLitening just keeps getting better and better. Today, Fred sent me a new package with many new goodies included. I have not tested the changes yet but I expect that it will perform flawlessly.  Below is the list of changes per Fred's email to me.

You can download from:  http://www.planetsquires.com/files/sqlitening_002.zip

- From Fred's email -
Attached is release version 2 of SQLitening.  It includes many minor changes as well as field level encryption and compression.   The changes are:

1. Eliminated error -13 which was used for Invalid parms.  The current code can no longer raise this error.

2. Eliminated the slEscapeSpecialChars routine.  It did only one PB replace command which replaced all ' with ''.

3. Added slIsSetOpen routine.  This was a forum request.

4. Enhanced slExeBind so it can bind multiply fields in one call.  It can also encrypt and/or compress a field.
   
5. Enhanced slFX and slFNX to be able to get fields and decrypt and/or uncompress.

6. Added the K ModChar to slSetProcessMods which will set/unset the cryption key.

7. There is a new Dll called SQLiteningAuxRuts.Dll.  This is were the Rijndael encryption code resides as well as the calls to Zlib for compression.  I added a Dll so there would be no additional memory usage for those that do not use encryption nor compression.  This aux dll resides only with the client -- server is not concerned.  Note that the encryption/compression occurs at the field level, not the row or column level.  So if you encrypt/compress with slExeBind then you must also decrypt/uncompress with either slFX or slFNX.  I used Rijndael because it is much faster and smaller then BlowFish and was selected by the NIST as Advanced Encryption Standard.  Whatever that means but it impressed me.

8. Added two new options to the General section of the config file:
      CreateDatabaseAllowed = Yes or No  ---  Controls the creation of new databases.  If Yes then clients are
                                              allowed to create new databases on the server.  If omitted then
                                              will default to No
      TrimLogManually = Yes or No  ---  Controls the trimming of the log when it becomes larg e (> 600K).  If Yes
                                        then no automatic trimming will occur.  If no then will automatically
                                        trim 100K from front of log when it becomes large. If omitted then will
                                        default to No

9. Enhanced the Adim refresh so it will now refresh the config flags as well as the FACT

10. Included a second simple example program which does encryption and compression.
   
11. Enhanced almost all of the routine comments.  The updated comments are changed in both SQLitening.Bas and SQLitening.Txt.  This area should settle down in future.

- end of Fred's email-

Note: The SQLitening CHM help file has not yet been updated to reflect the changes.

Good luck!

Here it is! The very release for the entire new code base of SQLite Client/Server. The new name is "SQLitening".

The package contains a little bit of documentation. Not much, but enough to get you started. Be willing to invest a few minutes into browsing the documentation and the example program. It shows you how to connect to the server, open a database, and select records based on an SQL query. The api also allows you to easily deal with local SQLite3 databases in addition to databases available on the server.

An incredible amount of gratitude goes out to Fred Meier for creating the code base and sharing it with all of us.

Download (775K):  http://www.planetsquires.com/files/sqlitening_001.zip

Good luck!

Hi Everyone,

Consider this post a tease for something really exciting that is coming for SQLite Client/Server. Here are the highlights:

- Our programming friend, Fred Meier, has generously donated an entire new code base that handles local, and client/server access to the sqlite3 DLL. The code base will be made available very soon to all users. It is public domain in the same vein as the SQLite Client/Server.

- The name of the project will change from SQLite Client/Server to "SQLitening".

- Informal timings indicate that for most tasks the new code base performs from 40% to 80% faster than the existing SQLite Client/Server code.

- Full error handling with text messages.  Error requests can either exit the process or just return %False, depending on passed ModChar. 

- Server makes sure the SQLite Dll is thread safe and allows multi threading of SQLite commands. 

- Server will close all open databases when a connection ends. If a client forgets to issue the close or the client
process abnormally ends for any reason, the database file remains "in use/locked" until the server is shut down.  Not good. The new code base addresses this problem.

- The server runs as a service.  This is required so it can run without a user being logged in.  The service implementation in the new code base is extrememly well done and makes it a breeze to install/uninstall and start/stop the service.

- Server has a Log.  It records major events and will shrink when too large. 

- The local and client/server support binding of text, BLOB, and null.

- The new code base "hides" the database handle returned from the Open making the code easier to handle.

- The new code base implements a File Access Control Table (FACT). It allows you to assign one or more passwords to a file.  Multiple passwords allows you to change the password from one to another over time.  Two special passwords of * and ! are allowed.  ! for protected files and * for no checking. If a file is not in the FACT then access is allowed with no checking. Admin now has ability to refresh the FACT. 

As you can see, the new code base is extremely robust and implements many more features than my original concept. I am sure that everyone will be very pleased with the new code when it is posted. Documentation is currently being worked on and will need to be completed before the first upload of the code is made available.

Here is a sneak peak of how simple the code is:


   ' Connect -- omit this statement to run local in local mode.
   ' If you are connected to a server then you can run databases in local mode
   ' by using the slSetProcessMods function.
   slConnect "LocalHost"

   ' Open
   slOpen "sample.db3"

   ' Select record set
   slSel "Select * from Parts;"

   ' Process records
   do while slGetRow
     ' slF() retrieves column data based on field number.
     ' slFN() retrieves column data based on field name.
      sRow = slF(1) & ", " & slF(2) & "," & slFN("Product")
   loop

   slClose

   slDisconnect



The new code base implements over 30 functions that make working with SQLite3 incredibly easy.

Both code bases will co-exist for a short time. The long term plan is to phase out my code and go with the new code base as soon as possible.

Stay tuned....

Here is the download link to the latest version (0011): http://www.planetsquires.com/files/sql3_cs_0011.zip

Version 0011 (2008-04-20)
- Updated Sqlite3.dll to version 3.5.7
- Added error checking that will allow the client to be notified when the server unexpectantly shuts down before the client specifically requests to disconnect. An %ERR_DISCONNECTED error is returned.

- New format for the sql3server.ini file.

Example:

[config]
port        = default
ipaddress   = localhost
'ipaddress   = 192.168.0.100
'ipaddress   = www.planetsquires.com
busytimeout =
idletimeout =
 
[database]
databasename = sample.db3
security     = PSS234|sqlrocks
security     = paulsq|mypassword
security     = mark123|secret
 
[database]
databasename = \pharmacy\drugs.db3
security     = todd34|nfldpass
 
[database]
databasename = \accounting\gltrans.db3
security     = jennifer99|mypassword


During sql3_Connect, all usernames and passwords are checked regardless of what database it relates to. If a match occurs then the connection succeeds. During sql3_Use, the database to be used is matched against the related usernames and passwords for that database. If matched, then the use succeeds. Usernames and passwords are case sensitive. Database names are case insensitive.

I have been thinking about the server and how to set up a configuration for it. I plan to have the server controlled via the simple sql3server.ini text file.

Below is where I'm to right now. It has a section for the basic configuration (allows for multiple IP addresses) and multiple "Database" sections. Each Database section specifies the database name and a list of one or more "Security" attributes (basically, user id's and passwords) of clients that can access the database. The database can reside in the root directory with the server or in a subdirectory off of the server root.

Anyone see any issues with this or can offer any suggestions for improvement? Remember, the goal of this is to KEEP IT SIMPLE. 


 
[config]
port        = default
ipaddress   = localhost
ipaddress   = 192.168.0.100
ipaddress   = www.planetsquires.com
busytimeout =
idletimeout =
 
[database]
databasename = sample.db3
security     = PSS234|sqlrocks
security     = paulsq|mypassword
security     = mark123|secret
 
[database]
databasename = \pharmacy\drugs.db3
security     = todd34|nfldpass
 
[database]
databasename = \accounting\gltrans.db3
security     = jennifer99|mypassword
 


Hi Everyone,

Because SQLite3 Client/Server is a free, open source program, I really have no idea how many (if anyone) is actually using the program. I expect that people have downloaded it and play around a bit with it, but I really wonder if it is actually being used in real live applications.

I only develop this product as time permits so if people are not actively using it then the incentive to push develop is really not there (well, it will be there once FireFly 3 is finished because I have a real big need for this database program after that!).

If you feel comfortable posting your use of SQLite3 Client/Server then I would greatly appreciate it. The more people that post in this thread with an account of their use of the product, the greater incentive I have to speed up development.

A simple post describing the type of application you are developing and whether it is single user or multiuser (if multiuser, then how many active connections to the server do you anticipate).

Thanks!

Here is the download link to the latest version (0010): http://www.planetsquires.com/files/sql3_cs_0010.zip

Quote
Version 0010 (2008-03-14)
- Updated Sqlite3.dll to version 3.5.6
- Fixed bug where destroy recordset call was not being sent to the server.

I have spoken to a few people regarding the use of the SQLite3 Client/Server code and many lament that it is slow in client/server "remote" mode when sending a bulk number of individual INSERT or UPDATE commands to the server. The reason for this is that there is an overhead associated with the setup and TCP call to the server. A much better and significantly faster method is to create a dynamic string locally containing many individual SQL commands and send them all at one time to the server (wrapping the entire string of commands in a TRANSACTION).

Here is actual code from FireFly3's code snippet handler that inserts or updates code in the SQLite database. It iterates a linked list and builds a large SQL string composed of many individual SQL commands. After the loop ends, the SQL string is wrapped in a transaction.

(Note: This code uses a "local" database. If used in a client/server "remote" environment then you would need to issue an sql3_connect just after creating the session).


   sDatabase = GetDataStore & $CODESTORE_FILE
   If Dir$( sDatabase ) = "" Then Exit Function
   
   sql3_StartSession hSession, 0   ' do not use Unicode support

   errcode = sql3_Use( hSession, sDatabase, %SQL3_LOCAL )
   If errcode Then GoTo ExitOut


   pListItem = List_GetFirst( g.pListSnippets )
   Do Until pListItem = 0
       
      List_GetItemInfo pListItem, "", pSnippet
       
      If pSnippet Then
     
         If @pSnippet.IsDirty Then
            fChanges = %TRUE
             
            If @pSnippet.nRowID = 0 Then
               sSQL = sSQL & "INSERT INTO Snippets (name, code) VALUES ( '" & _
                             sql3_Fix(@pSnippet.@zName) & "', '" & _
                             sql3_Fix(@pSnippet.@zCode) & "' );"
            Else
               sSQL = sSQL & "UPDATE Snippets SET name = '" & sql3_Fix(@pSnippet.@zName) & "', " & _
                             "code = '" & sql3_Fix(@pSnippet.@zCode) & "' WHERE rowid = " & Format$(@pSnippet.nRowID) & ";"
            End If
           

         End If
         
      End If
       
      pListItem = List_GetNext( pListItem )
   Loop     
   
   
ExitOut:

   If fChanges Then
      sSQL = "BEGIN IMMEDIATE TRANSACTION; " & _
              sSQL & _
             "COMMIT TRANSACTION;"

      errcode = sql3_Exec( hSession, sSQL )
   End If
   
   
   If hSession Then
      sql3_EndSession hSession
   End If



I hope that this little tidbit of information helps you.

Here is the download link: http://www.planetsquires.com/files/sql3_cs_0009.zip

Quote
Version 0009 (2008-02-02)
- Updated logic for SELECT * queries again. If you want the RowID returned as part of the recordset then you must
specificially ask for it in your SELECT  statement. For example:  SELECT RowID, * FROM parts;
- Column names were being evaluated case sensitive rather than case insensitive.