• Welcome, Guest. Please login.
 
October 14, 2019, 03:50:13 am

News:

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 - Fred Meier

1
You must login to see download links!!!

Quote#====================<[ Version 1.6 July 22, 2012 ]>====================
1. Fixed bug in slBuildInsertOrUpdate where a value of only . or - or +
    was incorrectly considered to be numeric.

2. Changed SQLiteningServer to show the server version number in
    the Log whenever the service is started.

3. Changed SQLiteningServer to include more CriticalSections when doing
    read/write of flat files.

4. The ImHere thread in SQLiteningClient was not being stopped correctly
    causing memory errors.

5. The TCP socket was not being properly closed in SQLiteningClient causing
    memory leaks.
     
6. Fixed bug in SQLiteningServer that was not deleting the files in the Temp
    folder if the database was closed using slClose.

7. Fixed bug in SQLiteningServer that was creating unneeded files in the Temp
    folder for smaller select statements.

8. Added a MutEx synchronizing event in SQLiteningClient to prevent the ImHere
    message to be sent while sending/receiving a real message.

9. Fixed bug in SQLiteningClient that was not handling the timer going pas
    midnight in the ImHere routine

10. Fixed bug in SQLiteningServer that was using Word instead of Dword to store
    the timer value.

11. Added version info to the Dll's and Exe so you are able to determine the version
    by viewing the properties.

12. Added slGetStatus(5) which returns the elapsed seconds since last message sent
    to server. Will always return an empty string if running in local mode.

13. Changed SQLitening to close all open sets in slDisconnect.

14. Changed SQLitening to correctly report a TCP timeout as error -18.

15. Added the F ModChar to the slSel commands.  This can be used to change the size
    of the first Row Data Chunk (RDC) which normally defaults to half the size of
    MaxChunkSize which is set in the Config file.

16. Fixed the tool in QLiteningServerAdmin to kill an active connection. It was
    not working properly.

17. Added a new tool to SQLiteningServerAdmin which will display the version
    numbers of the currently running SQLiteningServer and SQLite.

18. Added a seventh flag to the slGetStatus -- 3 = Return SQLitening flag settings.
              7 = One if a SQLite library is loaded.
                      (Local = SQLite3.DLL, Remote = SQLiteningClient.DLL)

19. Added a :LogNote: file name to slPutFile. The FileData is written to the remote
    log as a Note entry.  The Note entry is also new.

20. Improved slGetTableColumnNames to allow for qualifying the table name with
    a database name.  Previously would only work with tables in the Main database.

21. Added the slIsDatabaseNameValid function which will returns %True if DatabaseName
    is valid(opened or attached).

22. Added the slIsTableNameValid function which returns %True if TableName is
    valid(exist, has been created).

23. Added the new MaxConnections entry to the SQLiteningServerCfg file. Controls the number
    of concurrent connections.

24. Added slGetStatus(6) which returns the SQLitening and SQLite version numbers.

25. Added two new SQLitening Error Codes. 
      -22 - The max concurrent connections (set in Cfg file) was exceeded.
      -23 - SQLitening.Dll, SQLiteningClient.Dll, and SQLiteningServer.Exe
            must all be the same file version number.  Note that this error
            was returned as -8 in earlier versions

26. Update SQLite3.Dll to the version 3.7.13 dated June 11, 2012.

27. Added several re-declares to SQLitening.Inc to allow you to use
     WString data types in PB. You must uncomment them to use.  You can
     also re-name them to anything you like.


Downloaded 14 times before modified
2
SQLite locks the database while a set is open/active, so SQLiteningServer
must close a set as soon as possible.  It does that by sending back the
first Row Data Chunk (RDC) and then writing the rest to disk.  A client
must not be allowed to keep a set open.  The current architecture works
very well for 'normal' sets.  It's only the 'huge' sets (millions of rows
with lots of columns) that could cause timeouts if the timeout default (30
seconds) is used.  I think the new F ModChar will assist you with these
'huge' sets. 

I have considered building the extra RDC's in a separate thread but SQLite
does not allow passing database handles with open sets to another thread
therefore some type of very complex inter-thread code would have to be
developed.  I don't want to bloat and complex SQLitening if it is not
needed. 

Do you think SQLiteningServer should handle 'huge' sets better?  Let me
hear from you. 
3
Scroll to the end of this thread for the latest version of SQLitening v1.54    You must login to see the download links!!!


SQLitening Version 1.50

                     *** Highlights ***
   1. Added slGetDatabaseAndFileNames which will return a list of database
      and file names that are currently opened and attached.

   2. Added slGetFieldDataTypes which will return the data type for each of
      the columns in all the selected rows returned by the passed select
      Statement.

   3. Added slSelBind which allows you to use binary data(Blobs and Unicode)
      in where clauses and it's use will also prevent SQL injection.

   4. Improved when a client process ends to prevent Half-Open connections.

  Below are the details of the changes:

Quote
#====================<[ Version 1.5 July 4, 2011 ]>====================
1. ADDED -- command/function called slGetDatabaseAndFileNames which will
    return a list of database and file names that are currently opened and
    attached.  The name entries are returned as a delimited text string which
    is $NUL separated.  Each entry contains two elements, a database name and
    a file name.  The two elements are separated by the vertical bar (|)
    character.  The first entry is always the Main database from the slOpen
    command.  The second entry will be the Temp database, but only if there
    are temporary tables currently created.  The rest of the entries, if any,
    will be the attached databases.  There are no file names assigned to
    temporary tables nor temporary databases. 

2. ADDED -- command/function called slGetFieldDataTypes which will return
    the data type for each of the columns in all the selected rows returned
    by the passed select Statement. The date types of each field for each row
    are returned as comma delimited strings.  Each row will return a stirng
    of numeric values, one value for each column. Will return an empty string
    if an error occurs or no row is selected.  The SQLite data types are:
            1=Integer, 2=Float, 3=Text, 4=Blob, 5=Null.
   If, for example, your select statment returned 3 rows each with 4 columns
   the returning string might be:  3314,3552,3315

3. ADDED -- the slSelBind function. This new command allows you to use binary
    data(Blobs and Unicode) in where clauses and it's use will also prevent SQL
    injection.

4. IMPROVED -- the optional Where parameter in slBuildInsertOrUpdate
    as follows: If the Where value is omitted or is an empty string ("")
    then will build an Insert statement else will build an Update statement.
    When building an Update statement and the Where value is not "*" then a
    Where clause will be appended as " Where " and then the Where value. If
    the Where value is "*" then the Update statement will not have a Where
    clause (Caution: All records in table will be updated!).

5. IMPROVED -- When a client process ends it normally notifies the server
    and the connection is closed.  For unknown reasons this does not happen
    sometimes, resulting in a Half-Open connection.  To assure this is
    trapped, SQLiteningClient was changed to insure a message is sent at
    least every two minutes. If no real message has been sent then a "ImHere"
    message will be sent.  Also changed SQLiteningServer to close a connection
    if no message has been received for three minutes.  This type of
    close will be logged as "WentAway".

6. CHANGED -- the name of ZLib.Dll to SQLiteningZLib.Dll to avoid conflicts
    with other application that also use ZLib, but a different one.  There
    are two freely available, ZLib1.Dll and ZLibWApi.Dll which are normally
    renamed to just ZLib.Dll.  ZLibWApi.Dll has all the functions of ZLib.Dll
    plus the ability to create and read .Zip files.  ZLibWApi.Dll uses standard
    calling conventions while ZLib1.Dll uses the C calling convention therefore
    they are not interchangable.  ZLibWApi.Dll, renamed to SQLiteningZLib.Dll,
    is the one used with SQLiteing.  The prior ZLib.Dll distributed by SQLitening
    should be deleted from your running folders.

7. FIXED -- SQLiteningServerAdmin to ensure that the SQLiteningServer.Cfg
    file exists before attempting to run a tool.

8. FIXED -- bug in SQLitening.  The slGetTables would not retrun the table
    names for the Temp database.

9. FIXED -- bug in SQLiteningServer.  All databases were automatically closed
    when a connection ends.  For speed reasons, no check was made to determine
    if a database was still open.  If a database was explicit closed with slClose
    and the server was under stress (hi use) then an error could occur when
    it was closed the second time.  Changed to check if a database is still
    open before closing when connection ends. 

10.FIXED -- slGetStatus request 1 to properly return the time the lock was set.   

11.FIXED -- the 'f' ModChar in slOpen as follows:     
         f  = Do not enable foreign key support. 
              If 'f' is passed then will send: PRAGMA foreign_keys=Off
              If 'f' is not passed then will send: PRAGMA foreign_keys=On

12.FIXED --  bug in SQLiteningServer. It would sometimes show the connetion
    timed out when it actually was dropped.

13.FIXED --  bug in slSelStr. It would not allow you to use chr$(0) as either
    of the delimiters.

14.REMOVED -- slsGetInsertID but only from the Special API, it will remain
    in the other APIs.  It was not returning the correct values in VB.
    Any of the APIs can use the "Select last_insert_rowid()" which is a
    core SQLite function.

15.DISALLOWED -- slPushSet and slPopSet in Remote mode.  If it was used in
    Remote mode the results were erroneous.

18. Update SQLite3.Dll to the version 3.7.7.1 dated June 29, 2011.



[attachment deleted by admin]
4
Here is a preview of the major changes coming in 1.5.  Depending on your
feed back, SQLitening 1.5 should be available first part of July.

--- Improved the optional Where parameter in slBuildInsertOrUpdate
    as follows: If the Where value is omitted or is an empty string ("")
    then will build an Insert statement else will build an Update statement.
    When building an Update statement and the Where value is not "*" then a
    Where clause will be appended as " Where " and then the Where value. If
    the Where value is "*" then the Update statement will not have a Where
    clause (Caution: All records in table will be updated!).

--- When a client process ends it normally notifies the server and the
    connection is closed.  For unknown reasons this does not happen
    sometimes, resulting in a Half-Open connection.  To assure this is
    trapped, SQLiteningClient was changed to insure a message is sent at
    least every two minutes. If no real message has been sent then a "ImHere"
    message will be sent.  Also changed SQLiteningServer to close a connection
    if no message has been received for three minutes.  This type of
    close will be logged as "WentAway".

--- Add a new command/function called slGetDatabaseAndFileNames.
    Returns a list of database and file names that are currently opened and
    attached.  The name entries are returned as a delimited text string which
    is $NUL separated.  Each entry contains two elements, a database name and
    a file name.  The two elements are separated by the vertical bar (|)
    character.  The first entry is always the Main database from the slOpen
    command.  The second entry will be the Temp database, but only if there
    are temporary tables currently created.  The rest of the entries, if any,
    will be the attached databases.  There are no file names assigned to
    temporary tables nor temporary databases. 

--- Add a new command/function called slGetFieldDataTypes.
    Returns the data type for each of the columns in the selected row.
    The SQLite data types are: 1=Integer, 2=Float, 3=Text, 4=Blob, 5=Null.

--- Changed the name of ZLib.Dll to SQLiteningZLib.Dll to avoid conflicts with
    other application that also use ZLib, but a different one.  There are two
    freely available, ZLib1.Dll and ZLibWApi.Dll which are normally renamed to
    just ZLib.Dll.  ZLibWApi.Dll has all the functions of ZLib.Dll plus the
    ability to create and read .Zip files.  ZLibWApi.Dll uses standard calling
    conventions while ZLib1.Dll uses the C calling convention therefore they
    are not interchangeable.  ZLibWApi.Dll, renamed to SQLiteningZLib.Dll, is
    the one used with SQLiteing. 

-- Added the slSelBind function in order to avoid SQL injection and to improve
   Unicode processing.
5
Tutorials and Training / SQLite Help File
December 06, 2010, 12:20:23 pm
Here is downloadable SQLite HTML Help file (.Chm). Thanks to Serge Igitov (Dagdamor).  See his message below.

QuoteHello.

Would you like to have a downloadable version of the SQLite Documentation?
I've created one in Windows HTML Help (.chm) format. It seems to be useful,
single-file (no need to zip/unzip it), compact and easy to navigate/search.
I've uploaded a copy to my server:

http://www.phpc.ru/files/sqlite/sqlite_docs_3_7_3.chm

This one is based on the "sqlite_docs_3_7_3.zip" file.
Feel free to use it if you like. Any comments/suggestions are welcome.
And thank you for the SQLite :)

Regards,
Serge Igitov (Dagdamor)
www.phpc.ru


6
Tutorials and Training / SQLitening API's
July 29, 2010, 02:37:09 pm
SQLitening comes with three different API's as follows:
   Basic     ---  Dll name is SQLitening.Dll
             ---  Include name is SQLitening.Inc
             ---  All routines begin with sl.
   Special   ---  Dll name is SQLiteningS.Dll
             ---  Include name is SQLiteningS.Inc
             ---  All routines begin with sls.
   Universal ---  Dll name is SQLiteningU.Dll
             ---  Include name is SQLiteningU.Inc
             ---  All routines begin with slu.

All DLL's are written in PowerBASIC and use the SDECL (aka STDCALL)
convention for parameter passing.  SDECL specifies that the declared
procedure uses the "Standard Calling Convention" as defined by Microsoft.
When calling an SDECL procedure, parameters are passed on the stack from
right to left and the stackl is automatically cleaned before execution
returns to the calling code.

PowerBASIC allocates strings using the Win32 OLE string engine.
This allows you to pass strings from your program to DLLs, or
API calls that support OLE strings.


   Which API or DLL should you use?

Basic:: Always try to use the Basic API.  If you language supports passing
OLE strings both ByVal and ByRef then you should be able to use the Basic
API.  OLE strings are allocated using the Win32 OLE string engine.  There
are some routines in the Basic API that will probably only work with
PowerBASIC.  slSelAry is one that passes an array and therefore may only
work in PowerBASIC.  You may need to modify the include file
(SQLitening.Inc) to support your language

Special:: If your language supports OLE string passing but only ByRef
(Visual Basic) then the Special API will probable work for you.  The
language must also support optional parameter passing.  The slSelAry is
not available in this API.  You will need to modify the include file
(SQLiteningS.Inc) to support your language This DLL is a front-end to the
Basic API.

Universal:: If your language does not support OLE strings then the Special
API will probable work for you.  This API will work for any language that
can call a standard Dll.  The parameter passing is patterned after the
Windows API.  The slSelAry is not available in this API.  You will need to
modify the include file (SQLiteningU.Inc) to support your language This
DLL is a front-end to the Basic API.  Documentation about parameter passing
is located in the include file.
7
Tutorials and Training / ZLIB Wrapper Routines
July 11, 2010, 01:49:40 pm
Attached are wrapper routines to create and retreive compressed data files in the standard Zip format using ZLib.Dll.
8
SQLitening Version 1.40

                     *** Highlights ***
   1. SQLitening.Dll is now thread-safe.

   2. Added slCopyDatebase.  This new function will allow you to make copies/backups
      of a database while it is active without locking it.

   3. Added slSelStr.  This new function returns selected rows as a delimited text string.
      I really like this feature for small volume selects.

   4. Enhanced slExeBind to be able to Insert or Update multiple records. This feature
      can greatly reduce the time to affect multiple records like bulk loads.

  Below are the details of the changes:
Quote#===================<[ Version 1.4  July 12, 2010 ]>===================
1. SQLitening.Dll is now thread-safe.  You can now have multiple threads
    accessing your SQLite database in both local and remote mode.  This
    will allow you to have multiple connections per client to the server,
    one for each thread.  This can greatly increase response time for
    situations where you can take advantage of multiple threads.  A new
    example program (ExampleD.Bas) is included using multiple theads.
    This new feature also allows database Procs to use SQLitening.Dll
    rather than have to call SQlite direct (makes coding Procs much easier). 
    There is a new Proc (SQLiteningProcB.Bas) included using this new feature.

2. Added slCopyDatebase.  This new function will allow you to make copies/backups
    of a database.  Normally you make copies/backups by locking the database and then
    copying the database by using an external tool like FileCopy.  This method works well
    but has the following shortcomings:
      -- Any database clients wishing to write to the database file while a backup is
         being created must wait until the shared lock is relinquished.
      -- It cannot be used to copy data to or from :memory: nor Temp databases.
      -- If a power failure or operating system failure occurs while copying the resulting
         copied database may be corrupt.
    The slCopyDatabase function uses the new SQLite OnlineBackup API which was created
    to address these shortcomings. slCopyDatabase allows the contents of one database to be
    copied into another database, overwriting the original contents of the target database.
    The copy operation may be done incrementally, in which case the source database does not
    need to be locked for the duration of the copy, only for the brief periods of time when
    it is actually being read from. This allows other database users to continue uninterrupted
    while the copy is made.  See http://www.sqlite.org/backup.html for more info about the
    OnlineBackup API.

3. Added slSelStr.  This new function returns selected rows as a delimited text string. 
    Each field and record is delimited by a single character.  The default field delimiter
    is $BS while the default record delimiter is $VT.  These defaults can be changed. This
    is easier to use than slSelAry and can be very handy for small amounts of returning data.
    Is excellent for testing the occurrence of a condition and processing Pragma returns.
    Lets say you wanted to know if there were any rows that had an
    'X' in column F1 of table T1:
          if len(slSelStr(Select 1 from T1 where F1='X' Limit 1) then
               ' if the above is true then you have at least 1 with 'X'
          end if
    This will display the page size -- ? slSelStr("Pragma page_size")

4. Updated Example C to include examples of the new slCopyDatabase and slSelStr. 
    Added a new Example D to demo multi-threading.

5. Enhanced slExeBind to be able to Insert or Update multiple records.  In
    prior releases slExeBind was only needed to work with blobs or text values
    that contained nulls.  Now it can be used to greatly improve Insert or
    Update speed.  My tests show it can improve them by as much as 45%.  Check
    out ExampleB.Bas for a sample that will Insert 50,000 records two
    different ways in either local or remote mode.  One way is to use
    slExeBind while the other slower way is to us slExe and stack the SQL
    insert statements.  Also note in this example the use of an array and the
    Join$ command to greatly improve concatenation speed. 

6. Added ModChars i,I,D,Z to slBuildBindDat which will allow for the
    building of dats to bind Integer, Integer64, Double, and Null values. 

7. Added the ability in slGetFile to pass a get position and a get length.
    Using a position and length is useful when you only want to get a
    portion of the file.  This also may be needed for very large files
    even when you want to get the whole file.

8. Added the ability in slPutFile to pass a put position. Using a
    position is useful when you only want to put a portion of the file. 
    This also may be needed for very large files even when you want to
    put the whole file.  Removed the create file in remote mode
    restriction.  Added D and T ModChars which allow you to delete a
    file and control when the file is truncated.

9. Changed slSel so that when no set is passed (defaults to zero) or
    the passed set number is zero it will first close that set. This
    will prevent error -14 (%SQLitening_InvalidSetNumber).

10. Changed slOpen to automatically enable SQLite foreign key support.
    SQLite disables this feature by default (for backwards compatibility),
    so must be enabled separately for each database connection. Also
    added the f ModChar to allow you to not enable foreign key support.

11. Added the C ModChar to slSel.  This will first close the passed set
    number.  This will prevent error -14 (%SQLitening_InvalidSetNumber)
    but should be used with caution.

12. Changed slConnect to check that the client and server versions are
    same.  If not then %SQLitening_AccessDenied is returned.

13. Changed slConnect so that when a second connect request is made it
    will just return if the current connection is still active.  Before
    it would just return without checking if still active.

14. Added a new request to slGetStatus.  Request 4 will check if the
    current remote connection is active and return either "Yes" or "No"
    This is like a 'ping' which will cause a trip to serve.

15. Add a quiet mode parm to SQLiteningServerAdmin so it can be run
    from a script without any user messages to answer. Pass the Q command
    line parm proceeded with a / or - (OS standard).  The Q must be followed
    by a numerical value for the action requested as follows:
         1 = Install
         2 = Start --- will first Install if required
         3 = Install and Start
         4 = Stop
         8 = Uninstall --- will first Stop if required
        12 = Stop and Uninstall
        16 = Reload Config
    This program will return the following codes if in Quiet mode:
        0 = All OK
        1 = Can't perform request, service in wrong status
        2 = Request failed.
        9 = Service in unknown or invalid status

16. Fixed bug in slSel when running in local mode and using the "B" ModChar.

17. Added a new sample proc called SQLiteningProcsB.Bas.  This proc uses
    SQLitening for all processsing.

18. Update SQLite3.Dll to the version 3.6.23.1 dated March 30 2010.



[attachment deleted by admin]
9
Here is a preview of the major changes coming in 1.4.  Depending on your
feed back, SQLitening 1.4 should be available first part of July.


--- SQLitening.Dll will be thread-safe.  You can now have multiple threads
    accessing your SQLite database in both local and remote mode.  This
    will allow you to have multiple connections per client to the server,
    one for each thread. This also allows database Procs to use SQLitening.Dll
    rather than have to call SQlite direct (makes coding Procs much easier). 

--- Added slCopyDatebase.  This new function will allow you to make copies/backups
    of a database.  Normally you make copies/backups by locking the database and then
    copying the database by using an external tool like FileCopy.  This method works well
    but has the following shortcomings:
      -- Any database clients wishing to write to the database file while a backup is
         being created must wait until the shared lock is relinquished.
      -- It cannot be used to copy data to or from :memory: nor Temp databases.
      -- If a power failure or operating system failure occurs while copying the resulting
         copied database may be corrupt.

--- Added slSelStr.  This new function returns selected rows as a delimited text string. 
    Each field and record is delimited by a single character.

--- Added the ability in slGetFile to pass a get position and a get length.
    Using a position and length is useful when you only want to get a
    portion of the file.  This also may be needed for very large files
    even when you want to get the whole file.

--- Added the ability in slPutFile to pass a put position. Using a
    position is useful when you only want to put a portion of the file. 
    This also may be needed for very large files even when you want to
    put the whole file.

--- Changed slSel so that when no set is passed (defaults to zero) or
    the passed set number is zero it will first close that set. This
    will prevent error -14 (%SQLitening_InvalidSetNumber).

--- Changed slOpen to automatically enable SQLite foreign key support.
    SQLite disables this feature by default (for backwards compatibility),
    so must be enabled separately for each database connection. Also
    added the f ModChar to allow you to not enable foreign key support.

--- Added a new request to slGetStatus.  Request 4 will check if the
    current remote connection is active and return either "Yes" or "No"
10
We will use the following two tables as examples:
   slExe "Create table T1(F1,F2,F3)   
   slExe "Create table T2(F1,F2)

If you join these two tables as follows:
   slSel "Select T1.F1, T2.F1 from T1, T2"
You will receive error -13 Invalid column name or number.

You are requesting F1 twice.  Qualifying if with the table name
makes SQLite happy but presents a problem for SQLitening as follows. 

SQLitening asks SQLite for the column names after the Select statement is
prepared.  SQLite by default only returns the unqualified names(no table
qualifier), therefore returning F1 twice.  SQLitening will raise an error
because later when you ask for the value of F1 using slFN/slFNX it would
not know which one to return. 

There are several ways to handle this situation.

1.  Add the "D" ModChar to the slSel which will allow duplicate column
names.  You would have to use slF/slFX.  Do no use slFN/slFNX because you
will always get the first value returned. 

2.  Set the Pragma call "full_column_names=1".  This Pragma will have SQLite
return qualified column names.  You can now use slFN/slFNX with the
qualified column names.  Note that this does not work with Select *. 

3.  Assign a different column name to the duplicate ones by using the 'As'
phrase as follows:
      slSel "Select T1.F1 as X1, T2.F1 as X2 from T1, T2"
Now you would use X1 and X2 in you slFN/slFNX statements.  I like this one
the best but any of the three will work. 

Debug Aid:
If you get error -13 and don't know which column is duplicated then add
the "D" ModChar to your slSel and follow that with slGetColumnName.  This
will display all the column names SQLite is returning

   slSel "Select T1.F1, T2.F1 from T1, T2"
   ?slGetColumnName

11
I find the way SQLite handles data typing to be a nice feature.  I
normally create columns with no data type, which will result in an
affinity of none.  The point of this post is that a good understanding of
storage class and affinities will assist you in understanding and
designing SQLite databases. 

The following was taken from http://www.sqlite.org/datatype3.html

Most SQL database engines (every SQL database engine other than SQLite, as
far as we know) uses static typing.  With static typing, the datatype of a
value is determined by its container - the particular column the value is
stored in. 

SQLite uses a more general dynamic type system.  In SQLite, the datatype
of a value is associated with the value itself, not with the container in
which it is stored.  The dynamic type system of SQLite is backwards
compatible with the more common static type systems of other database
engines in the sense that SQL statement that work on statically typed
databases should would the same way in SQLite.  However, the dynamic
typing in SQLite allowed it to do things which are not possible in
traditional statically typed databases. 

                 ****** Storage Classes ******

Each value stored in an SQLite database (or manipulated by the database
engine) has one of the following storage classes:
   NULL. The value is a NULL value.
   INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the value.
   REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
   TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16-LE).
   BLOB. The value is a blob of data, stored exactly as it was input.

Any column in a version 3 database, except an INTEGER PRIMARY KEY column,
may be used to store any type of value. 

Storage classes are initially assigned as follows:
Values specified as literals as part of SQL statements are assigned
storage class TEXT if they are enclosed by single or double quotes,
INTEGER if the literal is specified as an unquoted number with no decimal
point or exponent, REAL if the literal is an unquoted number with a
decimal point or exponent and NULL if the value is a NULL.  Literals with
storage class BLOB are specified using the X'ABCD' notation. 

                    ****** Column Affinity ******

The type of a value is associated with the value itself, not with the
column or variable in which the value is stored.  (This is sometimes
called manifest typing or duck typing.) All other SQL databases engines
that we are aware of use the more restrictive system of static typing
where the type is associated with the container, not the value.  To look
at it another way, SQLite provides dynamic datatypes such as one finds in
"script" programming languages such as Awk, Perl, Tcl, Python, and Ruby,
whereas other SQL database engines provide only compile-time fixed, static
typing such as found in Pascal, C++, and Java. 

In order to maximize compatibility between SQLite and other database
engines, SQLite support the concept of "type affinity" on columns.  The
type affinity of a column is the recommended type for data stored in that
column.  The key here is that the type is recommended, not required.  Any
column can still store any type of data, in theory.  It is just that some
columns, given the choice, will prefer to use one storage class over
another.  The preferred storage class for a column is called its
"affinity". 

Each column in an SQLite 3 database is assigned one of the following type affinities:
TEXT
NUMERIC
INTEGER
REAL
NONE

A column with TEXT affinity stores all data using storage classes NULL,
TEXT or BLOB.  If numerical data is inserted into a column with TEXT
affinity it is converted to text form before being stored. 

A column with NUMERIC affinity may contain values using all five storage
classes.  When text data is inserted into a NUMERIC column, an attempt is
made to convert it to an integer or real number before it is stored.  If
the conversion is successful (meaning that the conversion occurs without
loss of information), then the value is stored using the INTEGER or REAL
storage class.  If the conversion cannot be performed without loss of
information then the value is stored using the TEXT storage class.  No
attempt is made to convert NULL or blob values. 

A column that uses INTEGER affinity behaves in the same way as a column
with NUMERIC affinity, except that if a real value with no fractional
component and a magnitude that is less than or equal to the largest
possible integer (or text value that converts to such) is inserted it is
converted to an integer and stored using the INTEGER storage class. 

A column with REAL affinity behaves like a column with NUMERIC affinity
except that it forces integer values into floating point representation. 
(As an internal optimization, small floating point values with no
fractional component are stored on disk as integers in order to take up
less space and are converted back into floating point as the value is read
out.)

A column with affinity NONE does not prefer one storage class over
another.  No attempt is made to coerce data from one storage class into
another.  The data is stored on disk exactly as specified. 

The type affinity of a column is determined by the declared type of the
column, according to the following rules:

If the datatype contains the string "INT" then it is assigned INTEGER affinity.

If the datatype of the column contains any of the strings "CHAR", "CLOB",
or "TEXT" then that column has TEXT affinity.  Notice that the type
VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity. 

If the datatype for a column contains the string "BLOB" or if no datatype
is specified then the column has affinity NONE. 

If the datatype for a column contains any of the strings "REAL", "FLOA",
or "DOUB" then the column has REAL affinity

Otherwise, the affinity is NUMERIC.

If a table is created using a "CREATE TABLE <table> AS SELECT..."
statement, then all columns have no datatype specified and they are given
no affinity. 
12
Tutorials and Training / Error Handling
December 01, 2009, 03:37:40 pm
Many of SQLitening commands are functions which return zero if they worked
OK or non zero if they failed.  If they failed in SQLite then the return
value will be positive;  if it failed in SQLitening the value  will be negative. 
The error codes are listed at the end of this post. 

Most of the errors are severe -- that is neither your code nor your user
can fix the problem and try again.  Therefore the default SQLitening
action for error handling is to display a message and then exit the
process by calling the WinAPI ExitProcess.  This is the same API that is
called when your program ends it Main function.  There is the following
comment in the MS documentation which is just saying that your application
program will not be able to do any shutdown processing when SQLitening
does the ExitProcess. 
QuoteCalling ExitProcess in a DLL can lead to unexpected application or system
errors.  Be sure to call ExitProcess from a DLL only if you know which
applications or system components will load the DLL and that it is safe to
call ExitProcess in this context. 

This is the way I code all my programs, allowing SQLitening to handle all
errors.  There are two main advantages in letting SQLitening handle the
errors: 1) You don't have to write code to handle them and 2) You know
that all errors will be caught and stopped (VERY IMPORTANT--I have
reviewed many snippets of code where errors are being returned but there
is no code to catch them). 


If you have a good reason for not wanting SQLitening to handle the errors
then you can request to have the errors returned to your program for
processing.  There are three levels you can choose from:

   1. You can have some of the commands return the error and let SQLitening
   handle the other commands.  This is done by adding the "E" ModChar to the
   commands you want errors returned.

   2. You can change the default error processing to return errors by doing the
   slSetProcessMods "E" command.  Now all commands will return errors.  If there
   is a command that you want SQLitening to handle the error then add the "e"
   ModChar to that command.
   
   3. You can re-compile SQLitening, setting the conditional compile flag
   %ReturnAllErrors to %True.  This will change the default error processing to
   return all errors, just like 2 above, but without having to call slSetProcessMods
   each time.  Note: This will set the default to E0, if you want E1 or E2 then
   you would have to be add that code to SQLitening.



Once you decide to not use the default error processing then you must decide to
use E0(same as plan E) or E1 or E2. 
   E0 = No message is displayed.  Error is returned immediately. This is the default.
         Note that E and E0 are the same.
   E1 = Display a warning message with OK button.  Error is returned when OK pressed.
   E2 = Display a question message with OK and Cancel buttons. If they press OK,
         error is returned.  If they press Cancel, will exit process.
   

Error Codes

=====================<[ SQLitening Error Codes ]>=====================
%SQLitening_NoOpenDatabase          =  -7 - No open database
%SQLitening_AccessDenied            =  -8 - Access denied
                                             -- Connect was able to do Tcp Open but was unable to "talk"
                                                to SQLitening service. Probable caused by some type of
                                                firewall setting or an exit has denied connection.
                                             -- Open, Attach, GetFile, or PutFile password check failed or
                                                the file is protected.
%SQLitening_FileDoesNotExist        =  -9 - File does not exist
%SQLitening_FileOpenGetPutError     = -10 - File error doing open, get, or put
%SQLitening_LockTimeout             = -11 - Timeout occurred trying to lock an object or database
%SQLitening_NochangedRows           = -12 - No changed rows
%SQLitening_InvalidColumnNameNumber = -13 - Invalid column name or number
%SQLitening_InvalidSetNumber        = -14 - Invalid set number
%SQLitening_AttachIsInvalid         = -15 - Can not use SQL Attach
%SQLitening_CanNotConnect           = -16 - Can not connect
                                             -- Tcp Open failed.
%SQLitening_InvalidKeyOrNotFound    = -17 - Invalid key or key not found
                                             -- Ini GetFile section or entity not found.
                                             -- Ini PutFile WritePrivateProfileString winapi failed.
                                             -- Encrypt key must be set and must be 16, 24, or 32 bytes.
%SQLitening_SendOrReceiveError      = -18 - Error sending or receiving message"
                                             -- Prior connect failed, server went away, TCP time-out, or message length error
%SQLitening_InvalidStringOrRequest  = -19 - Invalid string or request
                                             -- Uncompressing string which is not compressed.
                                             -- String is too big to compress.
                                             -- BindDat is invalid.
                                             -- RunProc is invalid library or entry.
%SQLitening_ErrorAtServer           = -20 - An error occured at server -- check server log.

=======================<[ SQLite Error Codes ]>=======================
     0 = Successful result
     1 = SQL error or missing database
     2 = Internal logic error in SQLite
     3 = Access permission denied
     4 = Callback routine requested an abort
     5 = The database file is locked
     6 = A table in the database is locked
     7 = A malloc() failed
     8 = Attempt to write a readonly database
     9 = Operation terminated by sqlite3_interrupt()
    10 = Some kind of disk I/O error occurred
    11 = The database disk image is malformed
    12 = NOT USED. Table or record not found
    13 = Insertion failed because database is full
    14 = Unable to open the database file
    15 = NOT USED. Database lock protocol error
    16 = Database is empty
    17 = The database schema changed
    18 = String or BLOB exceeds size limit
    19 = Abort due to constraint violation
         ' Normally caused by trying to add a non unique key
         ' to an index during an Insert or Update.
    20 = Data type mismatch
    21 = Library used incorrectly
    22 = Uses OS features not supported on host
    23 = Authorization denied
    24 = Auxiliary database format error
    25 = 2nd parameter to sqlite3_bind out of range
    26 = File opened that is not a database file
   100 = sqlite_step() has another row ready
   101 = sqlite_step() has finished executing
13
SQLitening Version 1.30

                     *** Highlights ***
   1. Support for named locks with new command slSelRelNamedLocks.

   2. Server will "timeout" a connection after minutes of no activity.

   3. slSel can also begin a transaction.

   4. New command slGetStatus.
   
   5. New dialog in server admin to list and kill connections.


  Below are the details of the changes:
Quote#=================<[ Version 1.3  November 1, 2009 ]>==================
1. Removed the call to sqlite3_thread_cleanup in SQLiteningServer.Bas.
    It had become obsolete.

2. Added calls to sqlite3_initialize at server start and sqlite3_shutdown
    at server stop in SQLiteningServer.Bas.  These are new SQLite functions.

3. Added call to sqlite3_shutdown in SQLitening.Bas when process detaches.

4. Change SQLitening.Dll so all sets are automatically closed when slClose
    is called.

5. Change SQLiteningServer.Exe to timeout a connection after a number of
    minutes of no activity.  The default number is 30.  You can change it
    in the config file.  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.

6. Added the SystemTemp database to SQLiteningServer.Exe to be able to store
    stuff like the named locks and connection data.

7. Fixed a bug in SQLiteningServer.Exe that was sending back the wrong
    return value if the slSel was huge.

8  Changed the error handling on the following ruts to be consistent with other
    ruts that returned string.
             slGetColumnName
             slGetTableColumnNames
             slGetTableNames

9. Added rut slSetRelNamedLocks which 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 advisory 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.  They are ignored when running
    in local mode.  Will also optionally do a Sel command but only if the lock
    request was successful.

10. Added the following ModChars to slSel, slSelAry, and to the SelStatemnt of
    slSetRelNameLocks:
      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.
      R =  Release all named locks owned by this connection.
     
11. Added the following ModChars to slExe:
      R =  Release all named locks owned by this connection.

12. Added rut slGetStatus which returns the requested status which is normally
    a delimited list of returning data.  The following requests are currently
    valid:
         1 = Return all named locks.
         2 = Return all connections.
         3 = Return SQLitening flag settings.

13. Added the server name suffix to SQLiteningServerAdmin.Bas messages and
    changed the log to also show the suffix. Having a unique suffix allows
    multiple services (servers) to be running on same computer.

14. Added a new dialog to SQLiteningServerAdmin which allows for the following tools:
            Refresh the Config Flags and FACT
            List all Active Connectons
            Kill one Active Connection
    You already had the ability to "Refresh" but the other two are new.

15. Added two new items to the config file.
      MaxChunkSize = Number K  ---  Controls the size of Row Data Chunks which are returned from Select statements
                                    The value is in K so the actual size is * 1000.  Default is 500.

      ConnectionTimeOut = Number Minutes --- Control the number of minutes the server will wait to receive a message
                                             from an active connection.  Default is 30.


[attachment deleted by admin]
14
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.



15
Tutorials and Training / Server Trips and RDC
October 15, 2009, 09:02:26 am
SQLiteningServer.Exe always returns data from select statements in Row Data
Chunks(RDC).  A RDC is buffered in SQLiteningClient.Exe and consists of as
many rows/columns that will fit in MaxChunkSize.  MaxChunkSize is set in
the Config file and will default to 500K.  The size of the first RDC is
either MaxChunkSize/2 or is set with the F ModChar.  A RDC is formatted as
follows: Each row is preceded by a Long length and each column is preceded
by a Byte length.  The length values do not include the length field
itself.  If the row length is larger than a Long then a zero length row is
returned (should never happen).  If the column length will not fit in a
Byte(> 253) then the Byte contains 255 and it is followed by a Dword
length.  If the column is NULL then its length will be 254 rather than
zero. 

Let's say we have a table called Customer and it has 10 columns called
Col1 thru Col10 and each is loaded with exactly 1000 bytes of data.  So
each row is 10K bytes long (not a very good design but will work well for
our example).  Let's assume there are 500K customers in our table so now
we can determine the number server trips for different Select examples. 
Note that the overhead for the length fields is being ignored and default
MaxChunkSize is used and no F ModChar is assumed. 

Select * from Customer would require 11 trips(10K * 500K / 500K + the 250K first RDC)
Select Col1, Col2, Col3 from Customer would require 4 trips(3K * 500K / 500K + the 250K first RDC)
Select * from Customer Where ID=1234 would require 1 trip(10K * 1 is less than the 250K first RDC)
Select Col1, Col2 from Customer Where ID=1234 would require 1 trip(2K * 1 is less than the 250K first RDC)

I think you get the idea -- compute the approx number of bytes you are
electing.  If greater then the first RDC size then divide the remaining by
MaxChunkSize to determine the number of trips.  Of course all trips are
not equal, smaller RDC's will always be faster.  The default MaxChunkSize
size is not based on any study, it seems like a good starting size.  The
default first RDC size is smaller so it gets returned faster. 

In summary, only select what you must have and if you keep the size to one
RDC you will make only one trip to the server. 
16
Change of plans.  Rather than have just the two API's (Basic and
Universal) I have decided to also support a third (Special).  This will
allow languages like Visual Basic, which support OLE strings but not ByVal
parameter passing, to use strings.  Therefore....... 

Version 1.2 of SQLitening will have three different API's as follows:
   Basic     ---  Dll name is SQLitening.Dll
               ---  Include name is SQLitening.Inc
               ---  All routines begin with sl
   Special   ---  Dll name is SQLiteningS.Dll
               ---  Include name is SQLiteningS.Inc
               ---  All routines begin with sls
   Universal ---  Dll name is SQLiteningU.Dll
               ---  Include name is SQLiteningU.Inc
               ---  All routines begin with slu

All DLL's are written in PowerBASIC and use the SDECL (aka STDCALL)
convention for parameter passing.  SDECL specifies that the declared
procedure uses the "Standard Calling Convention" as defined by Microsoft. 
When calling an SDECL procedure, parameters are passed on the stack from
right to left and the stackl is automatically cleaned before execution
returns to the calling code. 

PowerBASIC allocates strings using the Win32 OLE string engine. 
This allows you to pass strings from your program to DLLs, or
API calls that support OLE strings.


   **** Which API or DLL should you use? ***

Basic:: Always try to use the Basic API.  If your language supports passing
OLE strings both ByVal and ByRef then you should be able to use the Basic
API.  OLE strings are allocated using the Win32 OLE string engine.  There
are some routines in the Basic API that will probably only work with
PowerBASIC.  slSelAry is one that passes an array and therefore may only
work in PowerBASIC.  You may need to modify the include file
(SQLitening.Inc) to support your language

Special:: If your language supports OLE string passing but only ByRef
(Visual Basic) then the Special API will probable work for you.  The
language must also support optional parameter passing.  You will need to
modify the include file (SQLiteningS.Inc) to support your language This
DLL is a front-end to the Basic API. 

Universal:: If your language does not support OLE strings then the Special
API will probable work for you.  This API will work for any language that
can call a standard Dll.  The parameter passing is patterned after the
Windows API.  You will need to modify the include file (SQLiteningU.Inc)
to support your language This DLL is a front-end to the Basic API. 
17
You've got Questions? We've got Answers! / Testers Needed
February 10, 2009, 01:54:09 pm
There has been requests for SQLitening to be used by compilers other than PB.
I have supported VB and .Net by writing a custom front-end for those compilers
which works OK but I do not want to have a custom front-end for each compiler. 

Attached is the first version of a "Universal" API for SQLitening.  This
could support any language that can call a standard Dll (including VB). 
The parameter passing is patterned after the Windows API. 
SQLiteningUniversal.Dll is just a front-end to SQLitening.Dll. 

The SQLiteningUniversal.Inc include file will need to be modified to
support specific language syntax. 

I have only coded the main routines for now.  Will do the rest (some will
not be possible like slSelAry) after receiving feed back.  All routines
start with "slu".  The coded routines are:

         sluConnect
         sluOpen
         sluExe
         sluSel
         sluGetRow
         sluFX
         sluFNX
         sluCloseSet
         sluGetColumnNumber

I was only able to test with PB but need some volunteers who can test with other compilers.
Your help will be appreciated.  Please post your findings here.  Thanks.
18
You've got Questions? We've got Answers! / Version 1.1
October 31, 2008, 12:01:39 pm
Just to keep you informed, I plan on releasing version 1.1 next week.
Highlights are:

    Deleted the slGetFieldType routine and replaced it with
    slIsFieldNull so local and remote mode are the same.

    Added the slGetHandle routine so you can call SQLite directly
    in local mode.

    Added the %ReturnAllErrors conditional compile equate.

    Added the slConvertDat function so the returned hex string can
    be used in a where clause for Blob data.

    Added the ability to have a user coded monitor process running
    along with the server process so that can code things like writing
    back up copies or do any other database or file maintenance using
    all the power of SQLitening in local mode. 

    Added the ability to have user coded procs within a Dll called
    SQLiteningProcs.Dll so that you can greatly improve response time
    by reducing messages to/from server.

    Added SQLiteningVB.Dll which is a bridge DLL for Visual Basic to
    handle the ByVal string problem.
19
I find the way this is handled in SQLite to be one of the great features. 
I normally create columns with no data type, which will result in an
affinity of none, by using the following create syntax:
   Create Table T1 (C1, C2, C3)I have found no advantage in assigning column affinity using the following:
   Create Table T1 (C1 Integer, C2 Text, C3 Real)

The below was taken from http://www.sqlite.org/datatype3.html   
1. Storage Classes
   Each value stored in an SQLite database has one of the following storage classes:
   
      NULL. The value is a NULL value.
      INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
   
      REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
   
      TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16-LE).
   
      BLOB. The value is a blob of data, stored exactly as it was input.
   
   Any column except an INTEGER PRIMARY KEY may be used to store any type of
   value.  The exception to this rule is 'Strict Affinity Mode'. 
   
   Storage classes are initially assigned as follows:
   
   Values specified as literals as part of SQL statements are assigned
   storage class TEXT if they are enclosed by single or double quotes,
   INTEGER if the literal is specified as an unquoted number with no decimal
   point or exponent, REAL if the literal is an unquoted number with a
   decimal point or exponent and NULL if the value is a NULL.  Literals with
   storage class BLOB are specified using the X'ABCD' notation. 
   
   Values supplied using the sqlite3_bind_* APIs are assigned the storage
   class that is requested.

2. Column Affinity

   The type of a value is associated with the value itself, not with the
   column or variable in which the value is stored.  (This is sometimes
   called manifest typing.) All other SQL databases engines that we are aware
   of use the more restrictive system of static typing where the type is
   associated with the container, not the value. 
   
   SQLite support the concept of "type affinity" on columns.  The
   type affinity of a column is the recommended type for data stored in that
   column.  The key here is that the type is recommended, not required.  Any
   column can still store any type of data, in theory.  It is just that some
   columns, given the choice, will prefer to use one storage class over
   another.  The preferred storage class for a column is called its
   "affinity". 
   
   Each column in SQLite is assigned one of the following type affinities:
   
      TEXT
      NUMERIC
      INTEGER
      NONE
   
   A column with TEXT affinity stores all data using storage classes NULL,
   TEXT or BLOB.  If numerical data is inserted into a column with TEXT
   affinity it is converted to text form before being stored. 
   
   A column with NUMERIC affinity may contain values using all five storage
   classes.  When text data is inserted into a NUMERIC column, an attempt is
   made to convert it to an integer or real number before it is stored.  If
   the conversion is successful, then the value is stored using the INTEGER
   or REAL storage class.  If the conversion cannot be performed the value is
   stored using the TEXT storage class.  No attempt is made to convert NULL
   or blob values. 
   
   A column that uses INTEGER affinity behaves in the same way as a column
   with NUMERIC affinity, except that if a real value with no floating point
   component (or text value that converts to such) is inserted it is
   converted to an integer and stored using the INTEGER storage class. 
   
   A column with affinity NONE does not prefer one storage class over
   another.  It makes no attempt to coerce data before it is inserted. 

3. Determination Of Column Affinity

   The type affinity of a column is determined by the declared type of the
   column when the table is created, according to the following rules:
   
   If the datatype contains the string "INT" then it is assigned INTEGER affinity.
   
   If the datatype of the column contains any of the strings "CHAR", "CLOB",
   or "TEXT" then that column has TEXT affinity.  Notice that the type
   VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity. 
   
   If the datatype for a column contains the string "BLOB" or if no datatype
   is specified then the column has affinity NONE. 
   
   Otherwise, the affinity is NUMERIC.

20
The next release, version 1.07, will contain a conditional compile equate called %ReturnAllErrors.  If SQLitening is compiled with the %ReturnAllErrors conditional compile set to 1 then the default upon start up is to return all errors.  If this is set to 0 and SQLitening is compiled then the default is to display error message and exit process.

The usage and meaing of the "E" and "e" ModChar remain the same, the conditional compile only controls what the global "return all errors flag" is at start up.

It's called software, we should all be able to have our way.  :)