SQLiteDatabase

Databases
1

Hi,

i never had any problems to create a Database. No i written a small application with a Setup-Method:

[h]SetupDatabase(f As FolderItem)[/h]

[code]Dim DB As SQLiteDatabase
DB = New SQLiteDatabase

DB.DatabaseFile = f
If DB.CreateDatabaseFile Then
//proceed with database operations…
DB.SQLExecute("CREATE TABLE " + tblFamilyEvents + “(” +_
“EventID INT PRIMARY KEY NOT NULL,” +_
“Type INT,” +_
“Date VarChar” +_
“)”)

DB.SQLExecute("CREATE TABLE " + tblPersonalEvents + “(” +_
“EventID INT PRIMARY KEY NOT NULL,” +_
“Type INT,” +_
“Date VarChar” +_
“)”)

DB.SQLExecute("CREATE TABLE " + tblPersonalAttributes + “(” +_
“AttributeID INT PRIMARY KEY NOT NULL,” +_
“Type INT,” +_
“Date VarChar” +_
“)”)
Else
MsgBox("Database not created. Error: " + db.ErrorMessage)
End If[/code]

I also made a Button-Click-Event:

[code]Dim f As FolderItem = GetSaveFolderItem("", “”)

If f <> Nil Then
SetupDatabase(f)

Dim dbr As New DatabaseRecord

dbr.IntegerColumn(“Type”) = 2
dbr.Column(“Date”) = “11 JAN 1934”

DB.InsertRecord(tblPersonalEvents, dbr)
End If[/code]

So the Database will create correctly but the Record will not inserted. Why?

Best regards

2

You are not providing a key value in your DatabaseRecord to Insert. Could it be that for tblPersonalEvents you do not have the EventID set to be an AUTOINCREMENT PRIMARY KEY?

You should check DB.Error and DB.ErrorMessage after the InsertRecord to see what SQLite says is going on.

3

There is also a scoping issue with your SetupDatabase method. You’re creating the db object locally (Dim db as…) which will be closed when you finish the method. You should either create a property or return the database object.

4

Declared it as an Property…just made the Code-Sample smaller :wink:

5

OK, found the “Error”, Xojo wants “Integer”, not “Int” within the Declaration.

Second Question. Later on, i wanna implement a Fulltext-Search via SQLite FTS4. Should i need to create everytime a virtual Table, if i load the DB-File or will it saved within the DB-File or do i need to create it new everytime?