[sqlite] Ideal way to check whether a table has a specific column

[Shaun Seckman (Firaxis)]

Hello all,

I just wanted to find out whether or not this is the
most ideal method for determining if a column exists in a table. My
current technique is to do execute "pragma table_info(tableName)" then
step through the results and perform a string comparison against the
"name" column until I hit a match or I've finished stepping through the
record set.

Is there a better way?

Shaun Seckman

Firaxis Games
Programmer

_______________________________________________
sqlite-users mailing list
sqlite...@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[David Bicking]

You could try to prepare a statement like "SELECT YourColumnName FROM YourTable;" If prepare returns an error, then likely the error message will say the column doesn't exist. If no error, it does exist. Such a simple query shouldn't take long to prepare. Probably less time then to grab the results of the pragma and test the strings therein.

David

--- On Tue, 11/3/09, Shaun Seckman (Firaxis) <Shaun....@firaxis.com> wrote:

[Igor Tandetnik]

Shaun Seckman (Firaxis)
<Shaun....@firaxis.com> wrote:

> I just wanted to find out whether or not this is the
> most ideal method for determining if a column exists in a table. My
> current technique is to do execute "pragma table_info(tableName)" then
> step through the results and perform a string comparison against the
> "name" column until I hit a match or I've finished stepping through
> the record set.
>
> Is there a better way?

I guess you could just prepare a statement "select mycolumn from mytable;" (you don't need to actually run it). If the column doesn't exist, prepare will fail.

Igor Tandetnik

[Adam DeVita]

Another way

SELECT name, sql FROM sqlite_master WHERE type='table' and name ='yourtable
name'

the field sql will give you the full table structure as a string. Parse for
your desired table name.

No statements fail.

--
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada