SQLite on Win 10

  1. ‹ Older
  2. 2 weeks ago

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

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

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

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

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

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

  8. Bob K

    Oct 10 Pre-Release Testers, Xojo Pro Kansas City

    @scott b I am the reason for the issue.

    Preach, Brother Scott!

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

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

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

  12. Alberto D

    Oct 10 Pre-Release Testers

    The links on that thread are not working for me.

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

  14. 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?

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

  16. Markus W

    Oct 11 #JeSuisHuman Europe (Germany)...

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

  17. 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
  18. Norman P

    Oct 11 Xojo Inc
    Edited 2 weeks ago by Norman P

    I wouldn't write a 1000 line long insert statement for starters
    It takes a ton of memory

    I would use transactions & commit every 1000 or so

    Something like

    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=""
        
        app.DB.SQLExecute("begin transaction") // <<<<<<<<<<
        
        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
            
            line = Input.ReadLine 'each line will be an arbitrary number of fields
            
            vRecordData=line.Split(tab) 'create an array of the data fields
            
            vSQLInsertBatch = ""
            
            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
                app.DB.SQLExecute(vSQLInsertBatch)  // <<<<<<<<<< insert this row but do not auto commit 
                
                vCounter=vCounter+1 'count to the upper bound of the array
              End If
            Next
            
            If mIsConnected And vCounter >= vBatchSize Then
              app.DB.SQLExecute("commit")  // <<<<<<<<<<
              vCounter = 0  // <<<<<<<<<<
              app.DB.SQLExecute("begin transaction") // <<<<<<<<<<
            End If
          End If
        Wend
        If mIsConnected And vCounter > 0 Then
          app.DB.SQLExecute("commit")
          vCounter = 0
        End If
        
      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
  19. Duane M

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

    Saw this last night and thought I'd "pop" it in. Not so fast. It appears to solve the speed issue but now it seems to have a logic issue regarding the "begin transaction" and "commit" statements. When submitting a set of more than 1000 records only 1001 get into the db. Doesn't matter how many it reads. Could be 2000 and only 1001 get in. Could be 3000 and only 1001 get in.

    I'm working on it. Thank you for the solution. BTW, this same set of records needs to go into MySQL on a server and that was why I was batching them in batches of 1000. That was a big speed improvement.

  20. Norman P

    Oct 12 Xojo Inc

    I wrote it in the editor for the forums so it may very well have a logic error

  21. last week

    Duane M

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

    I worked out the program logic issues on the Win 10 machine, brought the code back over to the Mac, and now this app is fast, very fast. on both platforms. Thanks so much for your help @Norman P and everyone else who had suggestions. A great learning experience!

or Sign Up to reply!