(SQLite) Was there a returned error when trying to create an existing TABLE?

Databases
1

I saw in an old project (written using Real Studio, in 2011-2012) a check against db.ErrorMessage saying something like:



If db.ErrorMessage = ""table Photo_ID already exists" Then…

(or it comes from my SQLite for dummies paper book; I do not have it handly)

But, I checked that using Xojo 2015r1 (where the project has this old code) in the debugger, and there is no error (Error 0, no Error string). *

I wasted hourless time with that Legacy code, until I removed it.

  • The only place I can think to, where get this at the original writting time was in the debugger. I recall using it widely to understand how SQLite was working, then.
2

NEVER trust in a informational message as a source of specific data. A new version of the underlying engine could just change the message and what you did just breaks. (As Xojo got broken because they extracted macOS version from a string that Apple said “don’t try it” and a patch info put there recently caused havoc). This message is used for information only. Error codes, if defined and documented for some use, could be used instead. BUT…

I think you are just trying to achieve something just covered using proper SQL. SO…

What do you want to achieve?

  1. Just to known if some table exists, or…
  2. Create a table if such table does not exists,
  3. Something else
3

I have a method for this:

Function tableExists (dbh As SQLiteDatabase, tablename As String) As Boolean

Var  sql As String

sql = "select 1 from " + tablename

Try
  Call dbh.SelectSQL (sql)
  Return True
Catch e as DatabaseException
  Return False
end try

End Sub

Seems to work - can this be improved?

4

If he wants just avoid creation clashes, it can be directly done in SQL, e.g:

CREATE TABLE IF NOT EXISTS my_table (
	id int PRIMARY KEY,
   	some_text text
);
5

Remember: legacy code. It worked long time ago, and somewhere in time, this changed and stopped working.

I was only curious to know if someone recall that. I will check my SQL for Dummies (a 20 years old edition) when back home in some hours.

CREATE TABLE IF NOT EXISTS

IF NOT EXISTS was not in my SQL for Dummies printed book. At one moment in time (since 2012), I found a pdf of an earlier edition and discovered that.

6

Of course. But I want to avoid table creation if it’s there already. What I’ll be doing instead, if the table already exists, is updating it.

7

That’s why I said:

8

Apparently, the problem is solved.

Thank you !