SQLite on Win 10

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.

Try Shared Preferences or Documents folder :slight_smile:

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

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.

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.).

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.

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.

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

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.

Preach, Brother Scott!

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

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

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

Lazy bumchen :wink:

The links on that thread are not working for me.

This one goes somewhere:
http://www.rdS.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.

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?

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

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

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