SQLite exception when create table

Databases
1

Why this string (sql) generate an error when execute is with db.ExecuteSQL?

sql = “?CREATE TABLE filelist (” +_
"filename TEXT, " +_
"nativepath TEXT, " +_
"size Real, " +_
"isfolder INTEGER DEFAULT 0, " +_
"isbundle INTEGER DEFAULT 0, " +_
"isapp INTEGER DEFAULT 0, " +_
“isvisible INTEGER DEFAULT 0);”

When I execute this line directly in the database, it runs without any error.

[i]Code:
lafDB = new SQLiteDatabase
lafDB.DatabaseFile = SpecialFolder.Desktop.Child(“lafDB.sqlite”)
Try
lafDB.CreateDatabase
Catch error as IOException
MessageBox("The database file could not be created: " + error.Message)
End Try

var sql as string

sql = “?CREATE TABLE filelist (” +_
"filename TEXT, " +_
"nativepath TEXT, " +_
"size Real, " +_
"isfolder INTEGER DEFAULT 0, " +_
"isbundle INTEGER DEFAULT 0, " +_
"isapp INTEGER DEFAULT 0, " +_
“isvisible INTEGER DEFAULT 0);”

Try
lafDB.Connect
lafDB.ExecuteSQL(sql)
MessageBox("‘filelist’ table created successfully.")
Catch error as DatabaseException
MessageBox("lafDB Error in Modul: CreateDB: " + error.Message)
End Try[/i]

I runs without any error when I replace sql in one line with:
sql = “CREATE TABLE filelist (filename TEXT, nativepath TEXT, size REAL, isfolder INTEGER DEFAULT 0, isbundle INTEGER DEFAULT 0, isapp INTEGER DEFAULT 0, isvisible INTEGER DEFAULT 0);”

2

That’s because you have somehow managed to get some “hidden characters” in your String “sql”.
Look at it’s binary/hex representation (LengthB is 179) -> it starts with “EF BB BF”, only then “43 52” (CREATE).
Just replace (rewrite completely) this line of code:

sql = "CREATE TABLE filelist (" +_

You then have a String “sql” with LengthB 176 (no more 179), starting with “43 52”.

Btw: You notice that in the Code Editor:
Place Insert-Cursor in between “C|REATE”, then use “Arrow-Left”. See how you have to do multiple “left”'s to get from “C” to the quote? There are your “hidden characters” :wink:

You have somehow inserted a Byte Order Mark (BOM) for UTF-8 in your code… and SQLite doesn’t like that -> that’s why you get the “Syntax error near CREATE” :wink:

Since there is no obvious error before/in/after CREATE, there has to be some “hidden” error… at least the error message has been helpful enough :wink:

3

You can select your code, right click, and from the menu select “Clean invisible ascii characters”. Several db managers add an invisible character at the beginning of what you copy.

4

After clean the string, it looks good.

sql = “•CREATE TABLE filelist (” +_

This sql string was copied from the Language Reference.

Thank you for your help.

5

Ha, cool - I’ve never noticed that one before :wink: Thanks for mentioning this!
Let’s go and zap the Gremlins :slight_smile: