Prepared Statements

[Lawrence]

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

 

[usenetuser]

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

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.

 

[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.

Oh ok, thanks for that ^_^

 

[brian.vanheesch]

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.

 

[Mark Rafn]

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...