OK, I’m going to try again to use xojo. Last time I got as far as being able to create as sqlite database. This is my code to do so. It works fine on the open handler of window1.[code]
Dim dbFile As FolderItem
dbFile=SpecialFolder.Documents.Child(“Apts.SQLite”)
Dim db As New SQLiteDatabase
db.DatabaseFile=dbFile
If db.CreateDatabaseFile Then
MsgBox(“Database Ready”)
Else
MsgBox(“No Database Connection”)
End If
[/code]
Now I want to create a table called ‘Places’ with this code on the active handler of pushbutton1 with this code
database.SQLExecute("CREATE TABLE Places (PlaceID INTEGER PRIMARY KEY, CompName TEXT)")
if Table Places exist
then
MsgBox ("Table Exists")
Else
MsgBox("No Such Table")
end if
end
I have tried many variations of this. None of them work. It says ‘This item does not exist’ on lines 1 and 2 on SQLExecute and Table and ‘Syntax error’ on lines 3 and 5 on then and else.
Does anyone have any helpful suggestions. All are appreciated.
Make sure you connect to the database, then execute your create statement, test for errors and then either Commit if no errors or Rollback if there were errors…
Also, when your code exits the Open event of the window all knowledge of dbFile is lost since it was dimmed in the Open event. You should make dbFile a property of the window if the database only needs to be know in that window. If there will be other windows that need access to the database then make dbFile a property in a Module.
In the first block of code, you create a local variable, db, that stores a connection to your database, but that variable goes out of scope as soon as the Open event ends. (The variable goes “out of scope”.)
Instead, create a property on the Window itself of type SQLiteDatabase. Let’s say you call that property DB. In the Open event, replace Dim db As New SQLiteDatabase with DB = new SQLiteDatabase. Be sure to call Connect in the Open event too.
Then in the pushbutton, you can access the database from the property DB.
[quote=98336:@Sandy Bassett]if Table Places exist
then
MsgBox ("Table Exists")
Else
MsgBox("No Such Table")
end if
end[/quote]
I’ll also point out that “if Table Places exist” is not valid Xojo syntax.
For an example of how to create, connect to and add data to a SQLite database, check out the example included with Xojo:
Examples/Database/SQLite/SQLiteExample
add
if db.error then
msgBox db.errorMessage
return
end
if you want a list of all tables in your DB
dim s as String
dim file as FolderItem = SpecialFolder.Documents.Child("Apts.SQLite")
Dim db as new SQLiteDatabase
if file <> nil and file.Exists then
db.DatabaseFile = file
if db.Connect then
Dim tables As RecordSet
tables = db.TableSchema
If tables <> Nil Then
for i as integer = 0 to tables.RecordCount-1
s = s + chr(13) + "Tablename (" _
+ str(i) + ") = " + tables.IdxField(1).StringValue
tables.MoveNext
next
tables.Close
MsgBox s
End If
end if
end if
Here’s something I wrote and use frequently… the top portion being in the constructor, which creates the tables on a database if it doesn’t already exist:
EDIT: rdlSql, in this example, is a global property type of sqliteDatabase in a module
[code]const SQL_FILENAME = “myDbFile.sql”
dim sqlFields as string
rdlSql = new sqliteDatabase
rdlSql.databaseFile = getFolderItem(app.executableFile.parent.nativePath + SQL_FILENAME)
rdlSql.encryptionKey = “myPassword”
if not rdlSql.databaseFile.exists then
sqlFields = "tblMyTable (" + _
"myKey INTEGER PRIMARY KEY AUTOINCREMENT," + _
"myData TEXT)"
if not constructTable(sqlFields) then 'unable to write table information to database file
// - HANDLE ERROR HERE
else
// - TABLE CREATED SUCCESSFULLY; HANDLE THAT HERE
end if
end if
[/code]
[code]Private Function constructTable(targetContent as string) As boolean
if rdlSql.databaseFile.exists then 'database file exists
if rdlSql.connect then 'able to connect to existing database
rdlSql.sqlExecute(“BEGIN TRANSACTION”)
rdlSql.sqlExecute("CREATE TABLE " + targetContent)
if rdlSql.error then 'error occured constructing tables
rdlSql.rollBack
rdlSql.close
return false
else 'commit changes; everything constructed successfully
rdlSql.commit
rdlSql.close
return true
end if
else 'unable to connect to existing database
rdlSql.close
return false
end if
else 'database does not exist
if rdlSql.createDatabaseFile then 'database file successfully created
if rdlSql.connect then 'able to connect to new database
rdlSql.sqlExecute(“BEING TRANSACTION”)
rdlSql.sqlExecute("CREATE TABLE " + targetContent)
if rdlSql.error then 'error occured constructing tables
rdlSql.rollBack
rdlSql.close
return false
else 'commit changes; everything constructed successfully
rdlSql.commit
rdlSql.close
return true
end if
else 'unable to connect to new database
rdlSql.close
return false
end if
else 'unable to create new database
rdlSql.close
return false
end if
end if
End Function[/code]
Testing a table with a small fake SELECT could do the job of detecting a table existence as requested.
Usage: If tableExists(db, “My_TABLE”) Then MsgBox “My_TABLE exists!”
Function tableExists(db As Database, table as String) As Boolean
If not db.Connect then
raise new RuntimeException // failed to connect
End
Dim SQLtest As String
if db IsA MSSQLServerDatabase then
SQLtest = "SELECT TOP 1 "OK" as tableExist FROM "+table
ElseIf db IsA OracleDatabase Then
SQLtest = "SELECT "OK" as tableExist FROM "+table+" WHERE ROWNUM<=1"
Else
SQLtest = "SELECT "OK" as tableExist FROM "+table+" LIMIT 1"
End
Try
Return db.SQLSelect(SQLtest).FieldCount > 0 // returned something
Finally
Return False // Exception, SELECT failed
End
End Function
For safely testing if a table exists:
[code]Function DoesTableExist(Extends Source As SQLiteDatabase, TableName As String) As Boolean
Dim Statement As SQLitePreparedStatement = Source.Prepare(“SELECT name FROM sqlite_master WHERE type=‘table’ AND name=?;”)
Statement.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
Dim RS As RecordSet = Statement.SQLSelect(TableName)
If RS = Nil Then
Return False
End If
Return RS.RecordCount > 0
End Function[/code]
Or if you want to do so in a case-insensitive manner:
[code]Function DoesTableExist(Source As SQLiteDatabase, TableName As String) As Boolean
Dim Statement As SQLitePreparedStatement = Source.Prepare(“SELECT name FROM sqlite_master WHERE type=‘table’ AND LOWER(name)=?;”)
Statement.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
Dim RS As RecordSet = Statement.SQLSelect(Lowercase(TableName))
If RS = Nil Then
Return False
End If
Return RS.RecordCount > 0
End Function[/code]
I would have simply replaced my example, but even after a refresh I can’t edit my post.
Just a note about the tableExists() I did. It tries to be DB agnostic, not just SQLite.
Kind of, it still has special cases for different database types. The same could be done using the database’s “right” way for table detection. Or a handful of extension methods could be written, one for each database type. Considering the question was about SQLite, I figured a SQLite detection routine would be best. I’ve got more for other engine types 
“It tries to be DB agnostic.” Should work in SQLite, PostgreSQL, MySQL, Oracle and MSSQL. Enough for me.
It’s done THAT way as a design choice: A short generic way. 
database.SQLExecute("CREATE TABLE IF NOT EXISTS Places (PlaceID INTEGER PRIMARY KEY, CompName TEXT)")
if database.error then
msgBox database.errorMessage
End If
If you just want to create a table if it does not already exist then use the “IF NOT EXISTS” argument.
[quote=103947:@Rick Araujo]“It tries to be DB agnostic.” Should work in SQLite, PostgreSQL, MySQL, Oracle and MSSQL. Enough for me.
It’s done THAT way as a design choice: A short generic way. ;)[/quote]
Oh don’t worry, I’m not trying to be critical. I saw one code snippet actually in use that tested for a table by doing SELECT * FROM table…
Not worrying, but your argument was asking for more explanations.