Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Java > Prepared Statements

Reply
Thread Tools

Prepared Statements

 
 
Lawrence
Guest
Posts: n/a
 
      03-20-2007
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 With Quote
 
 
 
 
usenetuser@hotmail.co.uk
Guest
Posts: n/a
 
      03-20-2007
On 20 Mar, 17:56, "Lawrence" <(E-Mail Removed)> wrote:
> 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.

 
Reply With Quote
 
 
 
 
Lawrence
Guest
Posts: n/a
 
      03-20-2007
On Mar 20, 6:06 pm, (E-Mail Removed) wrote:
> On 20 Mar, 17:56, "Lawrence" <(E-Mail Removed)> wrote:
>
>
>
> > 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.


Oh ok, thanks for that ^_^

 
Reply With Quote
 
brian.vanheesch@gmail.com
Guest
Posts: n/a
 
      03-20-2007
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 With Quote
 
Mark Rafn
Guest
Posts: n/a
 
      03-20-2007
Lawrence <(E-Mail Removed)> wrote:
>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 http://www.velocityreviews.com/forums/(E-Mail Removed) <http://www.dagon.net/>
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
RubyODBC error codes when using prepared statements Daniel de Angelis Cordeiro Ruby 1 03-02-2011 03:23 PM
jdbc mysql timeout error - prepared statements? seth brundle Java 5 05-26-2007 12:58 AM
component statements within architecture statements Neil Zanella VHDL 8 10-20-2006 09:05 AM
Prepared Statements and unbound variables Peter Davies Java 2 01-24-2006 01:45 PM
cx_Oracle & prepared statements Glenn Stauffer Python 0 08-16-2003 03:42 AM



Advertisments