SQLitening Support Forum

It allows for more concurrency. Errors like "Database currently locked" are less likely.

See this link:
http://books.google.de/books?id=v5OYlkt6uKYC&pg=PA154&lpg=PA154&dq=BEGIN+IMMEDIATE&source=bl&ots=hbX-lt3JRE&sig=QCRFn_ZSOWKwwBqw84vg6n9oxMQ&hl=de&sa=X&ei=zzu_UfKKLMvTsgbV0oFA&ved=0CI8BEOgBMAk#v=onepage&q=BEGIN%20IMMEDIATE&f=false

Also:
http://www.sqlite.org/lang_transaction.html

Rolf

I had really meant '100,000.25' which in Germany would be '1000.000,25'.
Tricky sometimes, I often fall into that trap.

Rolf

It seems that the problem occurs whith values < 1,0 and if you use the comma for decimal separation. If you use the dot for separation SQLite will give you the desired result.
(See picture done with SQLiteExpert)

Problem: SQLite will handle a value with a leading "0" followed by a comma as a string and not as a numeric variable.
Solution: Always use "." as decimal separator. (The comma "," is usually used as 1000-separator in English (1,000,000,25). This is exactly contrary to the German use.)

See also here:
http://www.sqlite.org/datatype3.html

Rolf

Looks like this happens only with values that are smaller than 1,00

QuoteNote:
While testing notice that slGetTableNames only works if no database is passed.
Maybe should start a separate thread or look at source code.

The slGetTableNames function works as expected, it is the sample in the help file that is wrong or at least not clear. (My fault - sorry!)

If you have just one database file opened you do not need to provide a database name for the function will use the default database. If you have attached one or more database files only then you will need to pass the database name as parameter, but only for the attached database files. See code example below:


#COMPILE EXE
#DIM ALL
#INCLUDE "..\modules\SQLitening.inc"

FUNCTION PBMAIN () AS LONG
   LOCAL x AS LONG
   LOCAL t AS STRING
   LOCAL r AS LONG

   'Create an empty database that serves as container for attached tables (no tables)
   x = slOpen("master.db","C0")
   slClose

   'Create databases to attach
   x = slOpen("data1.db","C0")
   IF x = 0 THEN
      slExe "CREATE TABLE Customers(CustID,Name,Adress,City,Zip,Phone)","E0"
      slExe "CREATE TABLE Orders(CustID,OrderNum,OrderDate)","E0"
   END IF
   slClose

   'And another database
   x = slOpen("data2.db","C0")
   IF x = 0 THEN
      slExe "CREATE TABLE Table1(Col1,Col2,Col3)","E0"
      slExe "CREATE TABLE Table2(Column1,Column2)","E0"
      slExe "CREATE TABLE Table3(Col1,Col2)","E0"
   END IF
   slClose

   'Open master.de and attach databases
   x = slOpen("master.db")
   slAttach "data1.db","data1"
   slAttach "data2.db","data2"

   t = slGetTableNames("data1")
   REPLACE CHR$(0) WITH $CRLF IN t
   MSGBOX t,0,"Tables in Data1.db"
   t = slGetTableNames("data2")
   REPLACE CHR$(0) WITH $CRLF IN t
   MSGBOX t,0,"Tables in Data2.db"
   slClose

   r = MSGBOX("Delete files?",%MB_OKCANCEL,"Exiting...")
   IF r = %IDOK THEN
      KILL "master.db"
      KILL "data1.db"
      KILL "data2.db"
   END IF
END FUNCTION


I will change the help documentation accordingly.

Rolf

The App keyword is part of an implementation of FireFly, similar to the one in VB. Below is the text from the older FF2 help file. I think it was just forgotten in the FF3 help file.

Quote
FireFly allows access to many internal data items via the global APP variable.

Type APP_Type
      Comments        As Asciiz * %MAX_PATH   ' Comments         (Project Properties)
      CompanyName     As Asciiz * %MAX_PATH   ' Company Name     (Project Properties)
      EXEName         As Asciiz * %MAX_PATH   ' EXE name of program 
      FileDescription As Asciiz * %MAX_PATH   ' File Description (Project Properties)
      hInstance       As Long                 ' Instance handle of the program
      Path            As Asciiz * %MAX_PATH   ' Current Path to the EXE (with trailing backslash)
      ProductName     As Asciiz * %MAX_PATH   ' Product Name     (Project Properties)     
      LegalCopyright  As Asciiz * %MAX_PATH   ' Legal Copyright  (Project Properties) 
      LegalTrademarks As Asciiz * %MAX_PATH   ' Legal Trademarks (Project Properties)
      ProductMajor    As Long                 ' Product Major number     (Project Properties)
      ProductMinor    As Long                 ' Product Minor number     (Project Properties)
      ProductRevision As Long                 ' Product Revision number     (Project Properties)
      ProductBuild    As Long                 ' Product Build number     (Project Properties)
      FileMajor       As Long                 ' File Major number     (Project Properties)
      FileMinor       As Long                 ' File Minor number     (Project Properties)
      FileRevision    As Long                 ' File Revision number     (Project Properties)
      FileBuild       As Long                 ' File Build number     (Project Properties)
End Type


You can access any members of this Type variable from anywhere in your program.

For example, to display the current version of your program in an "About" box, simply do the following:

st$ = Format$(App.ProductMajor) & "." & Format$(App.ProductMinor) & "." & _
      Format$(App.ProductRevision) & "." & Format$(App.ProductBuild)
   
FF_Control_SetText HWND_FORM1_LABEL1, st$


The App variable is similar to the App variable similar to the one found in Visual Basic. An exception is that the PrevInstance member found in Visual Basic is implemented in FireFly as a separate function (i.e. FF_PrevInstance).

As you can see, many of the elements of the App variable come from the data that you set in the Project Properties dialog for your project.

BTW I think questions concerning FireFly should be better put into the FireFly forum, for many of the users of SQLitening they won't be very helpful.

Rolf

Hi Fredrick,

the code could be used anyway only if you are using FireFly3. Maybe you should try the demo.

Rolf

Hello Tony,

in FF3 you would use this code to assign a field value to a textbox:

FF_TextBox_SetText( HWND_FORM1_TEXT1, slF(1, 0 ) )

Attached you find a small FF3 project of a Data Browser example.


Rolf

Hi Tony,

I had talked to Fred about it, but he thought many of the SQLitening users are not FireFly users. So we left it out of the distribution and I published it only on the FireFly Forum.

Rolf

Hello Tony,

thanks for your work.

Since you are also using Paul's FireFly there is also an easier way to use SQLitening. I have create a function set for FireFly's Function Library. Like this you have the functions plus the essential information right at hand in FireFly. You can find the download here:

http://www.planetsquires.com/protect/forum/index.php?topic=2609.msg19813#msg19813

Notice: Though it was created originally for v1.40 it is fully compatible with v1.54.

In FireFly just copy the folder that is in the zip to the folling location in you FireFly folder:

\FF3\CodeStore\PowerBASIC


Rolf

Welcome Tony!

You certainly will enjoy it. And with just one or two lines of code you can turn your database program into a client/server application.

Rolf

@Paul:
Whenever you are ready to publish it let me know so that I can make the necessary changes to the documentation.

@Bern:
Thanks for this useful addition.

Rolf

With the yyyymmdd format it will work without problems.

Rolf

You might try a combination of GROUP BY and HAVING.

This piece of code should work and deliver the last record for a given period (I hope):

SELECT Customer, Idate, Inumber, Iamount FROM MyTableName
WHERE Idate <= '4/4/2013'
GROUP BY Customer
HAVING MAX(Idate)


It might be a good idea to save dates in yyyy-mm-dd format for this to work properly. For use with other date/time formats see the SQLite documentation here: http://www.sqlite.org/lang_datefunc.html
Rolf


PS:
I tried this functionality successfully on the sample.db3 that comes with SQLitening. This is the query that I used:

SELECT Manuf, Redref, Product, Pgroup, Price FROM Parts
WHERE Price <= 300
GROUP BY MANUF
HAVING max(Price)

QuoteI have tested Sqlite and found that it locks the whole database when one pc is doing an update.  If you have two pc doing updates to the same database the secound one fails.  If I have two pc that are doing 1000 updates each.  Does one of the pc's get an error or does it takes pc1 update and then pc2 and back to pc1 and etc... until all the updates are done?

The locking remains the same when using SQLitening. But SQLitening will keep trying to open the database until the timeout is reached. Only if a process keeps the file locked for a very long time you will receive errors.

What is the install process for the server?  Do you have to have the dll for each of the client and server on all pc's and server that are using the software.
Copy the server files to a directory on your server. Make necessary changes to the SQLiteningServer.Cfg file. Run SQLiteningServerAdmin. That is basically it. Check the helpfile under User Guide.

QuoteCan Sqlitening use the Sqlite database?

Yes - SQLitening is an extension to SQLite. It provides Client-/Server access to SQLite databases.

QuoteThe last thing what is the licensing?

SQLitening is Public Domain. You can use the compiled binaries and the source code in any way - private and commercial - you want to.

Rolf