SQLite on Win 10

  1. 2 weeks ago

    Duane M

    Oct 9 Pre-Release Testers, Xojo Pro Boston, MA

    I develop on a Mac and have a client/server app I'm working on. It works fine on Mac but basically not at all on Win 10. I've narrowed it down to the SQLite database. I'm importing/inserting a tab delimited text file. The database file and an enclosing folder are created at runtime. On the Mac I started by creating both where the app lived. On Windows that meant it they got created in the Debug folder. Thinking the Debug folder was the problem I changed both to "special" folders. On the Mac that's "/Users/UserName/Library/Application Support" and on Windows that's " \Users\UserName\AppData\Roaming\". Mac works fine, Win 10 it's erratic.

    The folder and file does get created. Sometimes, yes sometimes, it actually works. The importing of the data in the SQLite db that is. That would be a set of 100 records. Normally I test with an import of 3000 records and that never works on Win 10.

    I'm stumped.

  2. Matthew C

    Oct 9 Pre-Release Testers Roanoke, VA

    Try Shared Preferences or Documents folder :-)

  3. Duane M

    Oct 9 Pre-Release Testers, Xojo Pro Boston, MA

    Documents folder is not better. I'll try Shared Preferences next. Overall it's so slow it's functionally useless.

  4. Duane M

    Oct 9 Pre-Release Testers, Xojo Pro Boston, MA

    No good with Shared Preferences.

    It seems to me to be bogging down in reading the lines of tab delimited text. The text is UTF-8 and I changed to Windows CRLF for line endings.

    It's a lot faster, a lot, to insert the same data into MySQL on a remote server over the internet.

  5. Emile S

    Oct 9 Europe (France, Strasbourg)
    Edited 2 weeks ago by Emile S

    I created a folder in Documents, store there a bunch of data including a .sqlite file without problem (current Windows 10).

    The db allows Delete Record, Update Record, Add New Record… No trouble at all.

    Nota: there is long time that I stopped importing / exporting from / to Text...

    This even works on Linux Mint (same location).

    BTW: did you set a breakpoint in the import text part and follow the program execution (debug) ?

    Edit: In a brand new project, I even write / read some txt files and some .sqlite files in Documents\My_App_Folder\
    I had troubles on the first run, because I was wrong in my initialisation processand worked fine once I checked in the debugger what happens (why it worked on El Capital and crashed on Windows 10.).

  6. Bob K

    Oct 10 Pre-Release Testers, Xojo Pro Kansas City

    FWIW, I've done a ton of apps that work in Windows 10 that have no issues with SQLite.

    If you want to share a small sample project with us I'm sure someone will take a look at it.

  7. Duane M

    Oct 10 Pre-Release Testers, Xojo Pro Boston, MA

    Yes, I'm getting that lonely feeling where if no one else knows what I'm talking about then I've got a local problem.

  8. Bob K

    Oct 10 Pre-Release Testers, Xojo Pro Kansas City

    @Duane M Yes, I'm getting that lonely feeling where if no one else knows what I'm talking about then I've got a local problem.

    It happens sometimes to all of us. *Usually* it's solved by breaking it down into a smaller project and focusing just on the problem at hand.. The problem with your project is that it's probably big and there are a bunch of things going on. If it works in your small, sample project, then you know, for sure, that it works and that it's you.

    I can't tell you how many times I've been pissed off at some !*^$ Xojo bug and when I create a small example project for Feedback that I find the issue.

  9. Duane M

    Oct 10 Pre-Release Testers, Xojo Pro Boston, MA

    I'll put together a test project. It has to be something I'm doing in Xojo on Windows.

  10. scott b

    Oct 10 Pre-Release Testers, Xojo Pro local coffee shop

    @Bob K I can't tell you how many times I've been pissed off at some !*^$ Xojo bug and when I create a small example project for Feedback that I find the issue.

    this has happened to all of us at least once… for me it happens at least once per release (of Xojo). And mass majority of time I am the reason for the issue.

  11. Bob K

    Oct 10 Pre-Release Testers, Xojo Pro Kansas City

    @scott b I am the reason for the issue.

    Preach, Brother Scott!

  12. Markus W

    Oct 10 #JeSuisHuman Europe (Germany)...

    @Duane M It seems to me to be bogging down in reading the lines of tab delimited text. The text is UTF-8 and I changed to Windows CRLF for line endings.

    Don't read it in line by line. Read it in in one go and split the resulting text into lines.

  13. Dave S

    Oct 10 San Diego, California USA

    elsewhere on this forum I posted some code based on other code that Norman wrote a while back....
    this was a very fast CSV importer..... one that is used in the TADPOLE SQL Manager

  14. Markus W

    Oct 10 #JeSuisHuman Europe (Germany)...
    Edited 2 weeks ago by Markus W

    @Dave S elsewhere on this forum I posted some code based on other code that Norman wrote a while back....

    https://forum.xojo.com/40860-csv-parser/0

    Lazy bumchen ;)

  15. Alberto D

    Oct 10 Pre-Release Testers

    The links on that thread are not working for me.

  16. Emile S

    Oct 10 Europe (France, Strasbourg)

    This one goes somewhere:
    http://www.rdsisemore.com/CSVImporter.xojo_xml_project.zip

    Norman link is broken, but this is OK:
    http://www.great-white-software.com/Welcome.html

    check there for the code you need/want.

    Good luck.

  17. Duane M

    Oct 10 Pre-Release Testers, Xojo Pro Boston, MA
    Edited 2 weeks ago by Duane M

    @Dave S this was a very fast CSV importer

    That's being used.

    Users get spreadsheets. Can't count on them being the same columns. Can't count on users to prepare them properly. The CSV Importer cleans it up and brings it into a listbox. The listbox is exported to text. Then the text is used to create a database, table, and records. Fast on a Mac. Not so much on Windows.

    I'm working on a test app but I've got to get the Mac app delivered. Maybe I'll just tell them to buy Macs?

  18. Duane M

    Oct 10 Pre-Release Testers, Xojo Pro Boston, MA

    @Markus W Don't read it in line by line. Read it in in one go and split the resulting text into lines.

    I've eliminated this as the problem. It's not the reading of the text file.

  19. Markus W

    Oct 11 #JeSuisHuman Europe (Germany)...

    Hmmm, Dave's link does not contain the CSV Importer.

  20. Duane M

    Oct 11 Pre-Release Testers, Xojo Pro Boston, MA

    I took the example SQLite project included with Xojo and modified it for the purpose of debugging this issue. It comes down to one line on Win 10: app.DB.SQLExecute(vSQLInsertBatch) 'insert the batch

    I insert rows of data in batches of 1000 records. It's almost instantaneous on 2 Macs and on Windows using DB Browser for SQLite and submitting the exact same batch code it is instantaneous. Executing that line of code takes forever in Windows using Xojo.

    Right now I only have one Windows machine. The Win 10 machine where I'm trying to run this.

    I'll post the code but I think what I need is for someone to run the project with the supplied test data. I'm open to any suggestions, not sure of the next steps.

    Thanks.

    The database is created in one button and the table this data goes into in another. Then this code runs and you have to use the supplied data. I use 3 test files each has a different number of records. 100, 1500, and 3000 records. In practice I need to handle files of 20-30,000 records.

    If Not IsConnected Then
      MsgBox("Create the database and create the table first.")
      'Return False
    End If
    
    Dim tab, CR as String
    tab=Encodings.ASCII.Chr(9)
    CR=Encodings.ASCII.Chr(13)
    
    Dim input as TextInputStream
    Dim file As FolderItem
    file = GetOpenFolderItem("")
    
    If file <> Nil Then
      
      Try
        input = TextInputStream.Open(file)
        input.Encoding = Encodings.UTF8
      Catch e As IOException
        MsgBox("Cannot open file: " + Str(e.ErrorNumber) + " " + e.Message)
        Return
      End Try
      
      If input <> Nil Then
        //   ******************************************************************************************   //
        //   ******************************   BEGIN SQLITE TABLE BUILD   ******************************   //
        //   ******************************************************************************************   //
        
        Dim line, vSQLCreateTable, vSQLRowData, vSQLInsertBatch As String
        Dim vCounter As Integer = 1
        Dim i, vLineNumber, vBatchSize, vBatchCounter As Integer
        
        vBatchSize=1000 'set a size limit for the batch insert.
        vLineNumber=1
        
        line = input.ReadLine
        vSQLInsertBatch=""
        
        While Not input.EOF
          If vLineNumber=1 Then
            'In practice the first line is the table fields. But that is created in a separate button for this debuggin.
            If mIsConnected Then
              vLineNumber=vLineNumber+1
            End If
          Else
            //  ADD THE INDIVIDUAL RECORDS IN BATCHES OF 1000 OR LESS
            
            Dim vRecordData(), vSQLInsertRecord As String
            
            // ADD 1000 TO THE UPPER LIMIT OF THE LINE COUNT
            vSQLInsertBatch=""
            
            For vBatchCounter=1 to vBatchSize
              vSQLInsertRecord=""
              vSQLRowData=""
              If Not input.EOF Then
                line = input.ReadLine 'each line will be an arbitrary number of fields
                
                vRecordData=line.Split(tab) 'create an array of the data fields
                
                For i=0 to UBound(vRecordData)
                  if i<>UBound(vRecordData)then
                    vSQLRowData=vSQLRowData+""""+vRecordData(i)+""", "
                  Else
                    vSQLRowData=vSQLRowData+""""+vRecordData(i)+""", "+""+Str(vCounter)+");"
                    vSQLInsertRecord="INSERT INTO `C1CID` VALUES ("+vSQLRowData+""
                    vSQLInsertBatch=vSQLInsertBatch+vSQLInsertRecord
                    
                    vCounter=vCounter+1 'count to the upper bound of the array
                  End If
                Next
              Else
                Exit 'reached EOF short of full batch
              End If
            Next
            
            If mIsConnected Then
              app.DB.SQLExecute(vSQLInsertBatch) 'insert the batch
            End If
          End If
        Wend
      End If
    Else
      MsgBox("Connection failed!")
    End If
    
    If App.DB.Error Then
      AddDataStatusLabel.Text = "DB Error: " + App.DB.ErrorMessage
    Else
      AddDataStatusLabel.Text = "Data added."
      'Return False
    End If
  21. Newer ›

or Sign Up to reply!