CODEWITHSUNDEEP
perlsqliteprepared-statementdbi
ceving
ceving

Reputation: 23824

How to prepare a pragma statement in Sqlite using Perl?

I tried to prepare a pragma statement:

$sth = $dbh->prepare (q{pragma table_info(?)})

but I get the following error:

DBD::SQLite::db prepare failed: near "?"

What is wrong with the prepare string?

Upvotes: 0

Views: 1352

Answers (3)

RayCW
RayCW

Reputation: 184

You asked "Is it a general limitation of prepare, that it can be used only for values and not for identifiers? Or is prepare in my example not aware of the context?"

You can only bind (prepare) values, not table names or column names. This is true for all relatonial databases afaik.

Using preparing (binding) of parameters makes it possible for sqlite to reuse execution plans, so it makes sqlite much faster.

Upvotes: 0

ikegami
ikegami

Reputation: 385789

You may prepare PRAGMA statements just as you are doing, but your statement has a syntax error.

When given a string, ? is equivalent to a string literal. So

SELECT * FROM Table WHERE field = ?

means

SELECT * FROM Table WHERE field = 'test'

and

PRAGMA table_info(?)

means

PRAGMA table_info('test')

The problem is that you're suppose to give

PRAGMA table_info(test)

As such, you need to use

$dbh->prepare("PRAGMA table_info(".$dbh->quote_identifier('test').")")

Upvotes: 3

Matt Jacob
Matt Jacob

Reputation: 6553

From the DBI docs:

With most drivers, placeholders can't be used for any element of a statement that would prevent the database server from validating the statement and creating a query execution plan for it.

So, I can only assume that the placeholder in this situation is preventing the database server (or self-contained database engine in this case) from validating the statement.

Even if the statement could be prepared, according to the SQLite PRAGMA docs, it looks like table_info is expecting an unquoted table name parameter. DBD::SQLite wants to quote all bind values by default, so that would cause a separate issue anyway.

Upvotes: 5

Related Questions