CODEWITHSUNDEEP
sqlsqlite
wolfram77
wolfram77

Reputation: 3221

Which is better, [Table Exists Check + Select] or [Select + Error Handling]?

There exists several tables in an SQLite database. It is possible for data to be requested (from client) from a table which does not exist. If the table does exist, then desired data from table is sent as an array of rows, and if table does not exist, an empty array is to be returned.

There are 2 ways (i can think of):

Which way would be better (or faster)?

Upvotes: 0

Views: 126

Answers (2)

André Stannek
André Stannek

Reputation: 7863

It kind of depends on what your default case is. If the table will exist in most of the cases use the second option. As @CristianAbelleira pointed out in the comments, this will also reduce load on the database because it's one less transaction.

If on the other hand you expect the table to not exist most of the time, implement some kind of check before the actual select. This can be done via your first option but I also like the idea from Daves answer.

Upvotes: 3

DaveM
DaveM

Reputation: 734

I would run with the third idea.

When you connect to the dB get a quick list of the table names (works as a good double check that the connection is working OK) store these in a list somewhere, then as the user creates their query give them a drop down list with these table names in it (or somehow double check the entered name is in that list).

Only downside here is if the user creates a new table during the session.

Otherwise, if the user is doing a free form SQL statement just let the server throw our the error and then deal with it with a sensible error message.

The dB engine is likely more efficient at this than your 'check table exists' code. Unless of course you are generating a request building graphic, in which case the above scenario will probably work best (provided you don't have hundreds of tables).

Dave.

Upvotes: 2

Related Questions