Prepared Statements

Thread view:

Lawrence - 20 Mar 2007 18:56 GMT

Hi

I want to use prepared statements to execute SQL commands, I
understand how they work and have this basic code so far:

pstmtU = con.prepareStatement("INSERT INTO ?(?) Values(?)");

However as this will be used throughout different tables, there may be
more or less parameters in the Values() and also be of different types
(String, Integer etc) How can I get around this without creating lots
of prepared statements with varying amount of parameters?

Ideally there would be a way to use a statement within a statement,
but im not sure if this is possible.

Many thanks

Lawrence

Reply to this Message

usenetuser@hotmail.co.uk - 20 Mar 2007 19:06 GMT

> Hi
>
[quoted text clipped - 14 lines]
>
> Lawrence

You don't understand how they work. You can only use substitution at
the values level (generally). e.g. You can prepare 'INSERT INTO
tablex (fielda, fieldb) VALUES (?, ?)'. You can't substitute the
field names or table name.

You must prepare a statement for each distinct, er, statement.

Reply to this Message

Lawrence - 20 Mar 2007 20:51 GMT

> > Hi
>
[quoted text clipped - 21 lines]
>
> You must prepare a statement for each distinct, er, statement.

Oh ok, thanks for that ^_^

Reply to this Message

brian.vanheesch@gmail.com - 20 Mar 2007 21:24 GMT

PreparedStatement instruct the database to create a miniature stored
procedure. The first time the prepared statement is executed it is
scanned by the database and assigned an execution plan (or showplan).
Each subsequent execution reuses the same execution plan for
performance gains. DBA gurus can go into more detail on keeping the
indexes up to date, yadda yadda...

I never (/rarely) use anything other than PreparedSatements.

Reply to this Message

Mark Rafn - 20 Mar 2007 23:25 GMT

>I want to use prepared statements to execute SQL commands,

Good. They generally perform better, and get you out of all sorts of quoting
hell.

>pstmtU = con.prepareStatement("INSERT INTO ?(?) Values(?)");

Not allowed. Bind variables can only take the place of literals in SQL. You
can't use a bind variable for table or column names. The reason for this is
also the reason for the perf gain: the DB (usually) parses and develops the
execution plan for the statement at prepare time (and uses a cached plan if
you later prepare something with identical text). If the statement doesn't
include enough data to form an execution plan, it can't be prepared.

>However as this will be used throughout different tables, there may be
>more or less parameters in the Values() and also be of different types
>(String, Integer etc) How can I get around this without creating lots
>of prepared statements with varying amount of parameters?

You can't. You'll need to prepare a different statement for each distinct
statement. Only if two statements are identical except for literal values can
you use the same prepared statement for them.

>Ideally there would be a way to use a statement within a statement,
>but im not sure if this is possible.

I'm not sure what that means. You can use subqueries, and bind variables for
literals within them...
--
Mark Rafn dagon@dagon.net <http://www.dagon.net/>

Reply to this Message

Prepared Statements

Free Magazines