• Welcome, Guest. Please login.
 
May 11, 2021, 08:04:30 PM

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 - Jean-Pierre LEROY

1
Dear all,

Is-it possible to setup the error logging Callback provided by SQLite when we use SQLitening ?

More info here:

https://www.sqlite.org/errlog.html

Thanks,
Jean-Pierre
2
Dear all,

For the first time a user got this message The above request was unsuccessful because the database is currently locked. Want to try again?

I'm using two SQLite databases for distinct applications on one server; using my administrator account I ran the Admin Tools to list all the active connections and only one user was still connected to the locked database : user nlefeuvre TCP File Number  6169

So I tried to kill this connection 6169 but without any success; this connection is always there!

Is there anything that could prevent the connection to be killed ? any ideas ?

I knwo I can stop the SQLitening services ... but the problem is that I have other users connected to the other database.

Regards,
Jean-Pierre
3
slSelArray is very powerful function to retrieve many records and later display them using a virtual ListView or a grid.

By example, for a specific application, I'm able to retrieve and display thousands of records (30 000) in less than 10 seconds.

Currently slSelArray fills the Array by column then row: Array(Column, Row).

In order to use Array Scan or Array Sort I need the Array in the reverse order, i.e. Array(Row, Column).

Your help will be appreciated.

Thanks.
Jean-Pierre

4
Dear all,

I know the slGetStatus(2) function which returns all connections to the SQLitening server.

I would like to know if someone has created a new function to retrieve the connections to a specific database ?

What I need is the list of users which are currently connected to the database.

Thanks,
Jean-Pierre

5
Dear all,

You'll find two screen-shot from an application that use SQLitening with success.

On a tab called "Summary" we display information about:

- the physical server
- the database engine
- the database file
- the database management system (SQLitening).

Everyday we do :

- a copy of the database at 7:15 pm using this command:

lResult = slCopyDatabase(gDataBase+".sav","E")


- a vacuum at 9:00 pm using this command:

slExe "Vacuum"


On another tab called "Connections & Named locks" we display:
- on the upper part the all connections to the server (it could be useful to be able to filter only the connections to the database(s) used by an application but I don't know how to do that).
- on the bottom part all the named locks on the server.

PS: by convention for my applications, all the named locks are composed of three parts:
1. A prefix with 3 characters used to identify the application.
2. The name of the table on which the named lock is set.
3. The record ID

Example: +LIT:Tasks:22961

With the first part it is easy to filter and display only the named locks associated to the current application.

SQLitening linked with SQLite is very robust; we never had a problem on the server or on the client side. Also the support with this forum is excellent. We've upgraded our version of SQLitening a week ago and so far, no problem at all.

My objective was just to share with you our beautiful experience with SQLitening and SQLite.

I hope that will give others some ideas.

Regards,
Jean-Pierre
6
Dear all,

In the thread below Frank reported that the slSetRelNamedLocks function simply ignores the rsSelModChars argument when a named lock conflict occurs.

http://www.sqlitening.com/support/index.php?topic=9391.msg24774

This issue has been corrected in version 1.70 of SQLItening.

It appears to me that the slSetRelNamedLocks function ignores also the rsModChars argument when a named lock conflict occurs (see my screenshot) when I use this command :


slSetRelNamedLocks(+Equipments:AWGPRT141)


or with this code :


slSetRelNamedLocks(+Equipments:AWGPRT141,T0)


Your help will be greatly appreciated.

Regards,
Jean-Pierre
7
Dear all,

I started using slSetRelNamedLocks to avoid the "last save wins" scenario by locking the records with named locks.

When the function returns 0 I open a form allowing the user to UPDATE the record.
When the result of the function is <> 0, the record is locked by another user and I open a form allowing the user to VIEW only the record (no updates are allowed).

I discovered that with the same Tcp file number, slSetRelNamedLocks always 0 with the same lock name.

Example for the same user (i.e. same tcp file number) calling twice the function:

lSetRelNamedLocks("+Cust1395")   => Return 0  normal for the first call.
lSetRelNamedLocks("+Cust1395")   => Return 0  seems strange for the second call ?

What do you think ?
Is-there any workaround ?

Any help will be appreciated.

Regards,
Jean-Pierre
8
Dear all,

I would like to upgrade my production server from version 1.6 to the latest 1.7 version of SQLItening.

Just curious to know if there any side effects doing this upgrade.

Thanks
Jean-Pierre
9
Dear all,

Do you have any success using  sqlite3_create_function in order to redefine standard SQLite function ?

Here is my need:

"The lower(X) function returns a copy of string X with all ASCII characters converted to lower case. The default built-in lower() function works for ASCII characters only. To do case conversions on non-ASCII characters, load the ICU extension. "

I would like to create a new function that could be able to return a copy of string X not only for ASCII charcater, but for Unicode characters (at least for extended ASCII Codes from 128 to 255).

Do I have to do it on server side or and client side ?

Thanks for your help.
Regards,
Jean-Pierre





10
Dear all,

It is my first attempt to use slCopyDatabase to backup online my DataBase (which is about 800 Mega bytes).

I use this command :


lResult = slCopyDatabase("MyDb.sav","E")


It's seem quite a slow process (32 minutes to have a complete backup) on a powerful server; during the backup process I had about 10 permanent connections on this Database.

Do you have any experience changing the P or S parameters ?

Thanks,
Jean-Pierre
11
Dear all,

I use SQLitening in some FireFly projects with great success.

In my case some users are using laptops and other desktop PCs on a LAN. After lunch time (or when they go away from their laptop for a long period of time) they got -18 error message, when they came back and try using the application.

This message appears only on laptops, so I suspect that it is probably due to power saving options where the lan card is powered off.

Here is my question:

Is there a simple way to handle this -18 error message and try to reconnect automatically to the database when the laptop wakes on again and is connecting again to the network.

That could be nice to avoid end user to launch again the application.

Thanks for your help and / or shared experience on this topic.
Jean-Pierre
12
Dear all,

To rebuild my database I use this statement in my program

slExe "Vacuum"

I would like to know if there is a way to know when the "Vacuum" process is done and/or to wait "programmatically" for the end of the "vacuum" process.

Thank.
Jean-Pierre

13
Hi Fred,

I've just dowloaded Version 1.60 of SQLitening.

I made some tests with some of my projects.

Did you made any changes to the way SlPutFile works ?

I'm no longer able to extract files that was saved in BLOB field with this command in Local Mode.


slPutFile lTempFileNameS, lFileContent, "C"


SlGetFile works normally.
Any idea ?
Thanks. Jean-Pierre
14
Dear all,

I can retrieve the SQLite version with this code :


slSelStr("Select sqlite_version()","E")


Is there a similar function to retrieve the SQLitening version ?

Thanks,
Jean-Pierre
15
Dear all,

Today I tried to insert or update a record with a "RichText" string in a "Text" field and I get this error :

1 = unrecognized token:

Any ideas ?

Thanks,
Jean-Pierre
16
Fred,

I have a strange issue with large table (with millions of record).

In server mode, after reading a few thousands records (precisely 2873 in the example below) I get the error -14 (Invalid set number) on the SlGetRow() function; with the ZTRACE() function we can see than the first 2873 records are read very quickly (see below):

[code]
Record N
17
Dear all,

I'm working on a specific project for a customer.

I've a problem with a SQL statement that works in Local mode but doesn't work in Server mode; as you can see, the only difference is the fact that I specify DESC (descending) at the end of the statement on the second field used to sort the record.


SELECT CodeIsin, AnneeMois, DateCours, Ouverture, PlusHaut, PlusBas, Cloture, Volume FROM Mois"                                   ' => Works fine in both mode
SELECT CodeIsin, AnneeMois, DateCours, Ouverture, PlusHaut, PlusBas, Cloture, Volume FROM Mois ORDER BY CodeIsin, AnneeMois"      ' => Works fine in both mode
SELECT CodeIsin, AnneeMois, DateCours, Ouverture, PlusHaut, PlusBas, Cloture, Volume FROM Mois ORDER BY CodeIsin, AnneeMois DESC" ' => Works only in Local mode ?


Here is the trace of the program in LOCAL mode; the 3 SQL Statements work fine; the third one is a bit longer and that is a bit strange because there is a specific index on these two fields : (CodeIsin, AnneeMois DESC)

Local Mode,SQL Statement: SELECT CodeIsin, AnneeMois, DateCours, Ouverture, PlusHaut, PlusBas, Cloture, Volume FROM Mois
After the slSel() and before slGetRow()
After the last slGetRow(), records read=185650
Start at : 16:55:53
End      : 16:55:53
Computed in 0.37 secondes

Local Mode,SQL Statement: SELECT CodeIsin, AnneeMois, DateCours, Ouverture, PlusHaut, PlusBas, Cloture, Volume FROM Mois ORDER BY CodeIsin, AnneeMois
After the slSel() and before slGetRow()
After the last slGetRow(), records read=185650
Start at : 16:55:53
End      : 16:55:54
Computed in 0.50 secondes

Local Mode,SQL Statement: SELECT CodeIsin, AnneeMois, DateCours, Ouverture, PlusHaut, PlusBas, Cloture, Volume FROM Mois ORDER BY CodeIsin, AnneeMois DESC
After the slSel() and before slGetRow()
After the last slGetRow(), records read=185650
Start at : 16:55:54
End      : 16:55:59
Computed in 5.05 secondes


Here is the trace of the program in SERVER mode; only the first 2 SQL Statements work fine; I get error -18 "Error sending or receiving message" on the third SQL statement (with the keyword DESC at the end).

Server Mode,SQL Statement: SELECT CodeIsin, AnneeMois, DateCours, Ouverture, PlusHaut, PlusBas, Cloture, Volume FROM Mois
After the slSel() and before slGetRow()
After the last slGetRow(), records read=185650
Start at : 16:58:50
End      : 16:58:54
Computed in 4.30 secondes

Server Mode,SQL Statement: SELECT CodeIsin, AnneeMois, DateCours, Ouverture, PlusHaut, PlusBas, Cloture, Volume FROM Mois ORDER BY CodeIsin, AnneeMois
After the slSel() and before slGetRow()
After the last slGetRow(), records read=185650
Start at : 16:58:54
End      : 16:58:59
Computed in 4.82 secondes

Server Mode,SQL Statement: SELECT CodeIsin, AnneeMois, DateCours, Ouverture, PlusHaut, PlusBas, Cloture, Volume FROM Mois ORDER BY CodeIsin, AnneeMois DESC


Here is the test program; I can provide the database with the table with the185650 records.


#COMPILE EXE
#DIM ALL     

#Include "WIN32API.INC"   
#Include "SQLitening.Inc"
Declare Function zTrace Lib "zTrace.DLL" Alias "zTrace" (zMessage As Asciiz) As Long
%rSet_Mens = 1   

' SQLitening - Modes
%SQLitening_LOCAL_MODE  = 1
%SQLitening_SERVER_MODE = 2

Function ElapsedTime(ByVal pSecondeFin As Double, pSecondeDeb As Double) As Double
    If pSecondeDeb <= pSecondeFin Then
        ElapsedTime = pSecondeFin-pSecondeDeb
    Else
       ' 24 heures = 86400 secondes
       ElapsedTime = (86400-pSecondeDeb)+pSecondeFin       
    End If
End Function   

Function PBMain () As Long     
   
    Global gSQLiteningMode As Long     
    Global gTitle          As String : gTitle  = "SQLitening DESC issue"
    Local lDatabase        As String : lDatabase = "test.db3"
    Local lServerHostName  As String : lServerHostName =  "192.168.1.26"

    ' SQLite           
    Local lSql     As String
    Local lResult  As Long             
   
    ' Local or Server mode
    Select Case MessageBox(%HWND_DESKTOP, "Server Mode ?", ByCopy gTitle, _
                        %MB_YESNO Or %MB_ICONQUESTION Or %MB_DEFBUTTON1 Or %MB_APPLMODAL )                               
        Case %IDYES
            gSQLiteningMode = %SQLitening_SERVER_MODE                     
       
        Case %IDNO
            gSQLiteningMode = %SQLitening_LOCAL_MODE           
    End Select             
   
    ' Connect to the server -- omit this call to run in local mode
    ' or use the slSetProcessMods function to explicitly switch to
    ' local mode without dropping the server connection.  No server
    ' name will default to LocalHost. 
    If gSQLiteningMode = %SQLitening_SERVER_MODE Then slConnect lServerHostName, 51234   
           
    '----------------------------------
    ' open / create the SQLite Database
    '----------------------------------
    ' open the DataBase, create (C) if the DataBbase doesn't exist
    lResult = slOpen(lDatabase,"C")
    If lResult Then
        MessageBox(%HWND_DESKTOP," slOpen:"+slGetError+$CrLf, ByCopy gTitle, %MB_OK Or %MB_DEFBUTTON1 Or %MB_ICONERROR Or %MB_TASKMODAL)
    End If                     
       
    '--------------------                   
    ' create table 'Mois'
    '--------------------
    lSql = "CREATE TABLE IF NOT EXISTS Mois (" +_
           "CodeIsin  TEXT, " +_
           "AnneeMois TEXT, " +_
           "DateCours TEXT, " +_
           "Ouverture REAL, " +_
           "PlusHaut  REAL, " +_
           "PlusBas   REAL, " +_
           "Cloture   REAL, " +_
           "Volume    INT)  "
    lResult = slExe(lSql)
    If lResult Then
        MessageBox(%HWND_DESKTOP," slExe:"+slGetError+":"+lSql+$CrLf, ByCopy gTitle, %MB_OK Or %MB_DEFBUTTON1 Or %MB_ICONERROR Or %MB_TASKMODAL)
    End If
   
    '-------------------------------------                                                   
    ' create index 'MOISCodeIsinAnneeMois'
    '-------------------------------------
    lSql = "CREATE UNIQUE INDEX IF NOT EXISTS MOISCodeIsinAnneeMois ON Mois (CodeIsin, AnneeMois DESC)" 
    lResult = slExe(lSql)
    If lResult Then
        MessageBox(%HWND_DESKTOP," slExe:"+slGetError+":"+lSql+$CrLf, ByCopy gTitle, %MB_OK Or %MB_DEFBUTTON1 Or %MB_ICONERROR Or %MB_TASKMODAL)
    End If                       
   
    '==================================
    '============== TESTS =============
    '==================================
    Test("SELECT CodeIsin, AnneeMois, DateCours, Ouverture, PlusHaut, PlusBas, Cloture, Volume FROM Mois")                                   ' => Works fine in both mode
    Test("SELECT CodeIsin, AnneeMois, DateCours, Ouverture, PlusHaut, PlusBas, Cloture, Volume FROM Mois ORDER BY CodeIsin, AnneeMois")      ' => Works fine in both mode
    Test("SELECT CodeIsin, AnneeMois, DateCours, Ouverture, PlusHaut, PlusBas, Cloture, Volume FROM Mois ORDER BY CodeIsin, AnneeMois DESC") ' => Works only in Local mode ?
   
    ' end of the program
    MsgBox "Test finalized", %MB_ICONINFORMATION Or %MB_TASKMODAL
                 
End Function

Sub Test(pSql As String)

    ' date & time
    Local lHeureDeb    As String
    Local lHeureFin    As String
    Local lSecondeDeb  As Double
    Local lSecondeFin  As Double
   
    ' SQLite           
    Local lResult  As Long
    Local lCounter As Long   

    ' trace
    lSecondeDeb = Timer
    lHeureDeb   = Time$ 
    ZTRACE(" ")
    ZTRACE(IIf$(gSQLiteningMode = %SQLitening_SERVER_MODE, "Server Mode,","Local Mode,")+"SQL Statement: "+pSql)
    lResult = slSel(pSql, %rSet_Mens,"E")
    If lResult Then
        MessageBox(%HWND_DESKTOP," slSel:"+slGetError+$CrLf, ByCopy gTitle, %MB_OK Or %MB_DEFBUTTON1 Or %MB_ICONERROR Or %MB_TASKMODAL)   
    End If     
    ZTRACE("After the slSel() and before slGetRow()")   
   
    ' while we have some records
    Do While slGetRow(%rSet_Mens)   
        Incr lCounter                               
    Loop
   
    ' close the RecordSet
    slCloseSet(%rSet_Mens)
               
    ' trace
    lSecondeFin = Timer
    lHeureFin   = Time$                                                           
    ZTRACE("After the last slGetRow(), records read="+Format$(lCounter))
    ZTRACE("Start at : "      +lHeureDeb)   
    ZTRACE("End      : "      +lHeureFin)
    ZTRACE("Computed in " +Format$(ElapsedTime(lSecondeFin,lSecondeDeb),"0.00")+" secondes")

End Sub


Any ideas ?

Thanks,
Jean-Pierre
18
Hi Fred,

I would like to use the Haru Free PDF Library with one PB project in which I already use SQLitening.

For libHaru I use the headers made by Jose Roca on his WebSite (http://www.jose.it-berater.org/smfforum/index.php?topic=3058.0).

Both dll (libhpdf.dll and SQLiteningClient.dll) use the zlib.dll on the client side.

if I use the zlib.dll (72,5 Ko, 82 functions exported) that is provided with SQLitening version 1.40, my application gpf when I call libHaru functions.

If I use the zlib.dll (59 Ko, 51 functions exported) that is provided with libHaru 2.1.0, libHaru works and it seems that SQLitening is also working correctly ...

I have nevertheless a few questions ...

Q1. I don't use "encryption" or "compression" with SQLitening so why zlib.dll is needed for SQLiteningClient.dll ?
Q2. It is safe to use the zlib.dll file that comes with libHaru with SQLitening on the client side ? again it seems to work but it could be due to the fact that I don't use "encryption" or "compression" with SQLitening ?
Q3. Is-there any other workaround in order to be able to use SQLitening and libHaru library with there own version of zlib ?

Thank you for your help.
Jean-Pierre
19
This morning I try to update my database remotely and get an error message (I think it was -16 or -18); since this error message I'm no longer able to update or insert any new record in my database; I get this message :

"The above request was unsuccessful because the database is currently locked. Want to try again ?

If I answer "No" I get this message :
"SQLite or SQLitening returned the following unexpected message!"
5 = database is locked

I have two questions:

Q1. How can I unlock the database.
Q2. Is there a simple way to prevent this kind of event.

Thanks for your help.
Jean-Pierre
20
Dear all,

I'm using SQLitening in remote mode; I would like to display the size of the Database on the client application.

Is-there an easy way to do that ?

Thanks,
Jean-Pierre