• Welcome, Guest. Please login.
October 25, 2021, 08:24:39 PM


Welcome to the SQLitening support forums!

Show posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.

Messages - Fred Meier

General Board / Re: Is Fred OK?
February 19, 2013, 06:53:08 PM
Sorry to tell you Fred passed away in January 2013
Starting with version 1.4, July 2010, you can use SQLitening rather than calling SQLite direct in database Procs.
Below are Release Notes from that release:
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.
This would get the third lowest age   Select Age from MyTable Order by Age Limit 1 Offset 2

This would do the same thing   Select Age from MyTable Order by Age Limit 2,1

From SQLite doc:
QuoteThe LIMIT clause is used to place an upper bound on the number of rows
returned by a SELECT statement.  Any scalar expression may be used in the
LIMIT clause, so long as it evaluates to an integer or a value that can be
losslessly converted to an integer.  If the expression evaluates to a NULL
value or any other value that cannot be losslessly converted to an
integer, an error is returned.  If the LIMIT expression evaluates to a
negative value, then there is no upper bound on the number of rows
returned.  Otherwise, the SELECT returns the first N rows of its result
set only, where N is the value that the LIMIT expression evaluates to. 
Or, if the SELECT statement would return less than N rows without a LIMIT
clause, then the entire result set is returned. 

The expression attached to the optional OFFSET clause that may follow a
LIMIT clause must also evaluate to an integer, or a value that can be
losslessly converted to an integer.  If an expression has an OFFSET
clause, then the first M rows are omitted from the result set returned by
the SELECT statement and the next N rows are returned, where M and N are
the values that the OFFSET and LIMIT clauses evaluate to, respectively. 
Or, if the SELECT would return less than M+N rows if it did not have a
LIMIT clause, then the first M rows are skipped and the remaining rows (if
any) are returned.  If the OFFSET clause evaluates to a negative value,
the results are the same as if it had evaluated to zero. 

Instead of a separate OFFSET clause, the LIMIT clause may specify two
scalar expressions separated by a comma.  In this case, the first
expression is used as the OFFSET expression and the second as the LIMIT
expression.  This is counter-intuitive, as when using the OFFSET clause
the second of the two expressions is the OFFSET and the first the LIMIT. 
This is intentional - it maximizes compatibility with other SQL database
The resulting data type depends on the ModChar used in slBuildBindData, default will be Blob.

QuoteFunction slBuildBindDat alias "slBuildBindDat" (rsData as String, _
                                                optional byval rsModChars as String)Export as String
'   Returns a BindDat entry which is a specially formatted string required
'   by slExeBind.  Data contains the value you want converted into a BindDat.
'   A BindDat(s) is required to be passed to slExeBind.  The returned data
'   may also be compressed and/or encrypted.  If an error occurs then the
'   return value will be an empty string.  Use slGetError or slGetErrorNumber
'   to determine the error.
'   ModChars:
'      B = Bind as Blob.  This is the default.
'      C = Compress the data. Only use with Blob or Text.
'      N = Encrypt the data. Only use with Blob or Text. Requires a crypt key to
'          be set using slSetProcessMods.
'      T = Bind as Text.  Default is to bind as Blob.
'      i = Bind as Integer 32 bit (must be the only ModChar).  Default is to bind as Blob.
'      I = Bind as Integer 64 bit (must be the only ModChar).  Default is to bind as Blob.
'      D = Bind as Double (must be the only ModChar).  Default is to bind as Blob.
'      Z = Bind as Null (must be the only ModChar).  Default is to bind as Blob.
'      Note: If both C and N then will first compress and then encrypt.
How to Register for this Forum / Re: Hello everyone.
January 04, 2013, 11:38:44 AM
QuoteIn SQLiteningServer.Cfg the Hosts line must be IP address # and not hosts or a name on my system.
slSetProcessMods "E0"  'See different values.  This handles your own errors, result is optional.
These are not "required".  I never handle my own errors.  I just let SQLitening do it's thing, much less work.

Bern's answerer is correct but I have a question --- why are you updating the row immediately after inserting, why not just insert all, including the image, using a single slExeBind?

QuoteIs it best practice to save Images in a separate table?
All depends on how often they are accessed. If not as often as the other fields then I would consider using another file and doing slAttach only when the image is needed.

QuoteHow do you insert a $NUL into F1 to automatically increment the rowid?
slExeBind slBuildInsertOrUpdate("T1", "NULL" & $Nul & "?" & $Nul & "?"), s
1. You can try changing the declare from Quad to Long in SQLitening.Inc and test if you get the correct answer.
Declare Function slGetInsertID lib "SQLitening.Dll" alias "slGetInsertID" () as Long

2. If it supports the Currency type then you can change to that and multiply X 10000.

3. The only place Quad is used is on slGetInsertID, use the last_insert_rowid() SQLite core function instead.

) After inserting a client how do you know the ClientNumber just inserted so multiple patients can be added using it?
   Use slGetInsertID.
2) How would you create a unique patient number based upon the client number?
   Use the RowID assingned by SQLite.
QuoteFred's joins and binds are much faster than thisl
If anyone can show me how to use Freds' method to JOIN Bind instead of this it would be appreciated
The below code is an example of concatenating strings by adding eact string to an array and then using the Join$ command to build the final string.
  Local x as Long
  Local s as String
  Dim lsaA(1000) as String
  for x = 0 TO 1000
     lsaA(x) = "This is a string # " & format$(x)
  s = join$(lsaA(), "")

This was the best(fastest) way to concatenate large string before StringBuilder was available.
StringBuilder seems to be just as fast but don't know which uses the more resources.
Int is same as Integer execpt when adding the Primary Key clause.  Then Integer Primary Key is acutally the RowID and therfore is never Null.
Yes, my test also show that slGetChangCount adds to the count when slExe is done regardless of Transaction processing.
As you stated, slBuildBindDat is realy simple if you don't do compress and/or encrypt.  All it does is returns a stirng containing a double word with the data length + 1 followed by a "B" followed by the data.
Have you tried "Select * from Table Order by ColumnB || ColumnA"
Fixed the return version routines in SQLiteningClient and SQLiteningServer.
They would fail only if you are  doing multi connects within threads.

            Tis attachment only contains the changed modules!
I experienced the problem in LocalHost remote mode and working on solution.

There was flaw in the irGetDllFileVersion routine in SQLiteningClient when using threads.
For unknown reason the iif$ PB command would fail on not first calls.
I also changed the irGetMyExeFileVersion routine in SQLiteningServer.

Version 1.60.5 has been posted