SQLiteDatabase.dylib hard crash on SELECT *

  1. ‹ Older
  2. 4 months ago

    Aaron H

    Mar 14 Pre-Release Testers Europe (Germany)
    Edited 4 months ago

    @ChristianSchmitz And how would that affect the listbox?

    It's this that does not work:

    dim SomeValue as whatever = RecordSet.Field( "FiledName" ).whatever

    It always returns the same value, or nothing.

  3. Aaron H

    Mar 14 Pre-Release Testers Europe (Germany)
    Edited 4 months ago

    There are probably other issues ... the DOD listbox has its own DOD_RecordSet object which has a Xojo RecordSet object as a property. I can look inside that and see what's going on, but how it works is not my programming, it's Kem's, so maybe better to talk with him about it. All I know so far is that it doesn't work, in particular calling up values using .Field doesn't get the needed values.

  4. Christian S

    Mar 14 Pre-Release Testers, Xojo Pro, XDC Speakers Germany

    This is sample code from our examples:

    // use internal sqlite library
    call InternalSQLiteLibraryMBS.Use
    
    Dim db As New SQLDatabaseMBS
    
    // connect to database
    // in this example it is SQLite,
    // but can also be Sybase, Oracle, Informix, DB2, SQLServer, InterBase, MySQL, SQLBase and ODBC
    
    dim path as string
    
    if TargetMacOS then
    path = "/tmp/test.db" // put the database in the temporary folder
    else
    path = "test.db" // for Windows and Linux in the current folder the application is inside.
    end if
    
    db.DatabaseName = "sqlite:"+path
    
    if db.Connect then 
    
    'db.Option("AutoCache") = "true"
    
    Dim r As RecordSet = db.SQLSelect("Select fid, fvarchar20 from test_tbl")
    
    // fetch results row by row and print results
    while not r.EOF
    
    dim fid as integer = r.Field("fid").IntegerValue
    dim fvarchar20 as string = r.Field("fvarchar20").StringValue
    
    window1.Listbox1.AddRow str(fid)
    window1.Listbox1.cell(window1.Listbox1.LastIndex,1)=fvarchar20
    
    r.MoveNext
    wend
    end if

    Whether I use AutoCache or not, it doesn't matter and listbox shows same values in both cases. (and more than one row)

  5. Aaron H

    Mar 14 Pre-Release Testers Europe (Germany)
    Edited 4 months ago

    @ChristianSchmitz This is sample code from our examples: ...
    Whether I use AutoCache or not, it doesn't matter and listbox shows same values in both cases. (and more than one row)

    I believe you, but please try it with Kem's DOD Listbox and you'll see then it doesn't work. I don't know why and right now I don't have time to figure it out, but maybe Kem would have time.

    P.S. Thanks :)

  6. Jay M

    Mar 14 Pre-Release Testers, Xojo Pro NC, USA

    @ChristianSchmitz Whether I use AutoCache or not, it doesn't matter and listbox shows same values in both cases. (and more than one row)

    MoveNext always works as expected. The problem is with any of the other Move* commands. I just confirmed that even with AutoCache on, the other Move* commands do nothing.

  7. Jay M

    Mar 14 Pre-Release Testers, Xojo Pro NC, USA

    @Aaron H The MBS object works, BTW, loads without crashing.

    Aaron, have you noticed when using the MBS object that the app uses a lot more memory? My tests are showing at least 5x more memory than should be needed.

  8. Jay M

    Mar 14 Pre-Release Testers, Xojo Pro NC, USA

    @Jay M Aaron, have you noticed when using the MBS object that the app uses a lot more memory? My tests are showing at least 5x more memory than should be needed.

    Sorry, forgot to say this is with AutoCache turned on.

  9. Christian S

    Mar 14 Pre-Release Testers, Xojo Pro, XDC Speakers Germany

    @Jay M Aaron, have you noticed when using the MBS object that the app uses a lot more memory? My tests are showing at least 5x more memory than should be needed.

    How would you measure it?

    I can check move methods.

  10. Jay M

    Mar 14 Pre-Release Testers, Xojo Pro NC, USA

    @ChristianSchmitz How would you measure it?

    Using Activity Monitor and comparing to same query with Xojo's SQLite class. This is when loading a large recordset from a query. Only tried SQLite database, don't know about others.

  11. Christian S

    Mar 14 Pre-Release Testers, Xojo Pro, XDC Speakers Germany

    I found a bug. So a new plugin is available now for testing:
    Currently here

  12. Jay M

    Mar 14 Pre-Release Testers, Xojo Pro NC, USA

    @ChristianSchmitz I found a bug. So a new plugin is available now for testing:
    Currently here

    What was fixed? The move methods, the memory usage, or both?

  13. Christian S

    Mar 14 Pre-Release Testers, Xojo Pro, XDC Speakers Germany

    The Move methods. For memory usage I think that is fine so far.
    We may store text in different encoding (e.g. UTF-16 or UTF-32 instead of UTF-8) which may take more space temporarily.

  14. Norman P

    Mar 14 Pre-Release Testers, Xojo Pro www.great-white-software.com/b...

    Since Xojo primarily uses UTF-8 would it make sense to compile this plugin so that SQLite uses UTF-8 rather than leaving it as the default UTF-16 ?

  15. Christian S

    Mar 14 Pre-Release Testers, Xojo Pro, XDC Speakers Germany

    Well, SQLAPI defines the default encoding based on the function used to open the database.
    As UTF-16 API is used by the plugin cross-platform, we get UTF-16 as default encoding.

    Simply use pragma if you like to change it.
    Strings returned by the plugin are returned as UTF-8 independent of whether we use UTF-8, UTF-16 or UTF-32 to talk to the database.

  16. Norman P

    Mar 14 Pre-Release Testers, Xojo Pro www.great-white-software.com/b...

    Wouldn't that make things slower since you open UTF-16 then have to convert when reading and writing ?
    If you opened it UTF-8 there would not be any conversions required
    No ?

  17. Christian S

    Mar 14 Pre-Release Testers, Xojo Pro, XDC Speakers Germany

    As of today we use unicode API for SQLite. I can check if we could switch to UTF-8.
    But conversion between UTF-16 and UTF-8 is normally not a notable performance problem we see.

  18. Norman P

    Mar 14 Pre-Release Testers, Xojo Pro www.great-white-software.com/b...
    Edited 4 months ago

    Fair - was just a question that seemed like it needed to ba asked :)
    I'd guess the colume of data to be converted would have to be quite large before you'd notice any performance hit

  19. Jay M

    Mar 14 Pre-Release Testers, Xojo Pro NC, USA

    @ChristianSchmitz For memory usage I think that is fine so far.

    Not in Aaron's case. He's trying to load as much as possible per customer's requirements. Using 5 to 10 times the memory is not fine.

    @ChristianSchmitz Well, SQLAPI defines the default encoding based on the function used to open the database.
    As UTF-16 API is used by the plugin cross-platform, we get UTF-16 as default encoding.

    Simply use pragma if you like to change it.

    According to the docs that you referenced earlier , this setting only affects new databases created with that connection. Existing databases will use whatever encoding they are set to, and can't be changed.

    @ChristianSchmitz We may store text in different encoding (e.g. UTF-16 or UTF-32 instead of UTF-8) which may take more space temporarily.

    What do you mean by "temporary'? My tests show the recordset is using this space, so it will not go away unless the query is re-run.

    The db I am testing with was not created with the MBS plugin. I created it in an external tool. And this same database uses much less memory under the Xojo SQLIte class.

    @ChristianSchmitz But conversion between UTF-16 and UTF-8 is normally not a notable performance problem we see.

    I do not think the encoding is the cause of the excessive memory usage. It must be something else.

  20. Christian S

    Mar 14 Pre-Release Testers, Xojo Pro, XDC Speakers Germany

    You can change the encoding after opening the database the first time and change it. Changing later may not work.

    If you like, you can send me a test project to demonstrate. The cache is released when the recordset is closed. If that is not working, please let me know.

  21. Jay M

    Mar 14 Pre-Release Testers, Xojo Pro NC, USA

    @ChristianSchmitz You can change the encoding after opening the database the first time and change it. Changing later may not work.

    That's not what the SQLite docs say:

    It is not possible to change the text encoding of a database after it has been created and any attempt to do so will be silently ignored.
    
    Once an encoding has been set for a database, it cannot be changed.

    @ChristianSchmitz The cache is released when the recordset is closed.

    Yes, but that's not going to help. The fact that it consumes so much memory in the first place is the problem.

    @ChristianSchmitz If you like, you can send me a test project to demonstrate.

    I can do that, but in order to see the results you need a large db to test with, and I'm not going to try to send a file that large (>7 GB).

    Here are some numbers from my tests. The query selects 5 columns, and the entire table has an average row size of 323 bytes:

    Xojo SQLite
    100,000 rows causes entire app to use 55 MB
    1,000,000 rows causes entire app to use 400 MB
    10,000,000 rows causes entire app to use 3.7 GB
    19,400,000 rows causes entire app to use 6.98 GB
    
    MBS SQLite
    100,000 rows causes entire app to use 619 MB
    1,000,000 rows causes entire app to use 5.9 GB
    10,000,000 rows causes entire app to use 59 GB
    19,400,000 rows is unknown, I killed it after it got to 100 GB. Based on above it would probably been around 120 GB
  22. Newer ›

or Sign Up to reply!