Connect to multiple SQLite databases

Databases
1

I just tried to connect to two SQLite-databases, both opening in the App.open. One is called App.db, the other App.settingsDB (which is used for some specific large settings). Initially both databases get created. However, when I try to do an insert record into settingsDB, the program obviously tries to insert it in the first database.

Did I miss something and is just one db connection at a time allowed?

2

show the code you used…

You can also ATTACH the databases, which in some cases makes this much easier to deal with

but multiple database instances are allowed… but usually only one connection PER database (not always)

3

Hi,
If you want to work with several SQLite databases at once… open one of them and use the Attach method to “attach” additional ones to the “main”.

You assign an alias to the attached database, so you can refer to it by that alias during code operations.

Javier

4

It is a huge project so I cannot show all of the code, but here is the open statement of the app:

App.open------

/Open or create database
Dim f As FolderItem
f=GetFolderItem("OADownloader.db")

if f.Exists then
  db = New SQLiteDatabase
  db.DatabaseFile = f
  If db.Connect Then
    // proceed with database operations here..
  Else
    MsgBox("The database couldn't be opened. Error: " + db.ErrorMessage)
  End If
else
  CreateDatabase(f)
end if

//Open or create settingsDB
Dim g As FolderItem
g = GetFolderItem("settings.db")
if g.Exists then
  settingsDB = new SQLiteDatabase
  settingsDB.DatabaseFile = f
  if settingsDB.Connect then
    //proceed with database operations here
  else
    MsgBox("Could not open the settings file. Error: " + settingsDB.ErrorMessage)
  end if
else
  createSettings(g)
end if

The createX is just to initiate the databases. Both are created (either at start or because the files already exist) and working.

Here is the part where it goes wrong. The table request was done to confirm that the wrong database is being used.

ImportLoader.importThread.Run ------

//loop through the rows
if myexcelFile <> nil then
  Dim sheet as ExcelReader.Worksheet
  Dim row as ExcelReader.Row
  Dim cell as ExcelReader.Cell
  Dim reader as ExcelReader.Workbook = ExcelReader.Workbook.Open(myexcelFile)
  
  if reader <> nil then
    if reader.WorksheetCount > 0 then
      sheet = reader.Worksheet(0)
      
      for i As Integer = 1 to sheet.PopulatedRowCount-1
        Dim drow As DatabaseRecord = new DatabaseRecord
        
        //we always have the same fields
        for j As Integer = 0 to myHeaders.Ubound
          if sheet.cell(myHeaders(j).pos,i) <> nil then
            if myHeaders(j).columnName = "ISSN" then
              Dim issnVal As String = sheet.cell(myHeaders(j).pos,i).value
              Dim issns(-1) As String
              issns = issnVal.Split("&")
              if issns.Ubound > 0 then
                drow.Column("ISSN1") = trim(issns(0))
                drow.Column("ISSN2") = trim(issns(1))
              else
                issns = issnVal.Split(";")
                if issns.Ubound > 0 then
                  drow.Column("ISSN1") = trim(issns(0))
                  drow.Column("ISSN2") = trim(issns(1))
                else
                  drow.Column("ISSN1") = trim(issns(0))
                end if
              end if
            else
              drow.Column(myHeaders(j).columnName) = sheet.cell(myHeaders(j).pos,i).value.trim
            end if
          end if
        next j
        
        
        
        App.settingsDB.InsertRecord("journals", drow)
        if App.settingsDB.error then
          System.DebugLog(App.settingsDB.ErrorMessage)
          Dim sql As String = "SELECT name FROM sqlite_master WHERE type='table'"
          Dim rs As RecordSet = App.settingsDB.SQLSelect(sql)
          while rs <> nil and not rs.eof
            System.DebugLog("table " + rs.Field("name").StringValue)
            rs.MoveNext
          wend
        end if
        progressValue = i
      next i
      
    end if
  end if
end if
5

sorry too hard to read … please edit and insert code tags

6

Apologies, updated it with code tags.

7 

//Open or create settingsDB
Dim g As FolderItem
g = GetFolderItem("settings.db")
if g.Exists then
  settingsDB = new SQLiteDatabase
  settingsDB.DatabaseFile = f  <------ SHOULDN'T THAT BE "G"
  if settingsDB.Connect then
8

My colleagues were right, I am definitely still too tired to spot tiny mistakes like that :blush: (recovering from pneumonia and they send me home to work on this app :expressionless: )

Thanks for helping !

Next time I will try the attach feature.

9

LOL… understood… irony is I’m drugged out with Norco at the moment, waiting for a kidney stone to pass…

10

Get well soon! Maybe we should start a coding for the sick clinic :wink:

11

Copy / Paste / Modify is also a trap:
I do not understand why a db was not modified (yesterday) until I open it with a db tool and noticed two tables in the DB file… instead of two db files with one (different) table each !

Hard to find bug like yours. I forgot to modify a single FolderItem name and the bug was here !