Database Question

Databases
1

I’ve created and populated a SQLite database using RazorSQL. Now I want to access it via a simple Xojo app. The manual gives the following example:

[code]Dim dbFile As FolderItem
dbFile = GetOpenFolderItem("") ( I use a FolderItemDialog instead, because I have to search for the file and don’t want to hard-code a path. )

If dbFile <> Nil Then
Dim db As New RealSQLDatabase
db.DatabaseFile = dbFile
If db.Connect Then
MsgBox("Connected to " + dbFile.Name)
Else
MsgBox("Error: " + db.ErrorMessage)
End If
End If[/code]

Unfortunatly, there IS no database property db.DatabaseFile. I’ve tried several versions and I get a connection, but I can’t query the database. Obviously, the connection is to something else, but it is not null.

OK, apparently the language reference is out of date, so how do I connect to an existing SQLite database?

Edit (Paul): Added [code] block

2

You are running out of scope. Put a property in your project called db as sqlitedatabase then change:

Dim db As New RealSQLDatabase

to

db = New SQLiteDatabase

Then you will be able to query against the db elsewhere in your project.

If you want this all to remain in your method then just get the Dim statement out of the If Then block as when the block finishes so does the scope.

3

Add just two Functions in your App. Replace the Line with my SpecialFolder FileItem with your own File Dialog or filename.
Why I am using a 2nd function named “CreateDatabaseProvider”? Because I can switch between Database technologies (Sqllite, MySql, MSSQL) easily without changing all my queries.

Use the function .SQL to send your queries and recieve matching recordsets.

[code]Function SQL(CustomSQL as String) As RecordSet

// Prüfen auf eigene SQL Befehle
if CustomSQL <> “” then

#pragma BackgroundTasks false

dim db as Database
dim rs as RecordSet

db = app.CreateDatabaseProvider

try
  
  If db.Connect then
    
    rs = db.SQLSelect(CustomSQL)
    
  else
    
    rs = nil
    
  end if
  
catch err as NilObjectException
  
Finally
  
end try

return rs

#pragma BackgroundTasks true

end if
End Function
[/code]

[code]Function CreateDatabaseProvider() As Database

#pragma BackgroundTasks false

dim db as Database
dim dbSQLLite as new SQLiteDatabase
dim FI as new FolderItem

FI = SpecialFolder.ApplicationData.Child(“jakobssystems”).Child(app.LOCAL_DBNAME)

  if FI <> nil then
    
    if FI.Exists then
      dbSQLLite.DatabaseFile = FI
      dbSQLLite.MultiUser = true
    end if
    
  end if
  
  db = dbSQLLite

catch err as NilObjectException

Finally

end try

#pragma BackgroundTasks true

return db

End Function
[/code]

4

So that I can get it updated, what page has this out-of-date information?