Prepared Statements

Discussion in 'Java' started by Lawrence, Mar 20, 2007.

  1. Lawrence

    Lawrence Guest

    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
     
    Lawrence, Mar 20, 2007
    #1
    1. Advertising

  2. Lawrence

    Guest

    On 20 Mar, 17:56, "Lawrence" <> 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.
     
    , Mar 20, 2007
    #2
    1. Advertising

  3. Lawrence

    Lawrence Guest

    On Mar 20, 6:06 pm, wrote:
    > On 20 Mar, 17:56, "Lawrence" <> 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 ^_^
     
    Lawrence, Mar 20, 2007
    #3
  4. Lawrence

    Guest

    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.
     
    , Mar 20, 2007
    #4
  5. Lawrence

    Mark Rafn Guest

    Lawrence <> 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.dagon.net/>
     
    Mark Rafn, Mar 20, 2007
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Neil Zanella
    Replies:
    8
    Views:
    1,193
    mfmehdi
    Oct 20, 2006
  2. Peter Davies
    Replies:
    2
    Views:
    6,053
    Peter Davies
    Jan 24, 2006
  3. Glenn Stauffer

    cx_Oracle & prepared statements

    Glenn Stauffer, Aug 16, 2003, in forum: Python
    Replies:
    0
    Views:
    847
    Glenn Stauffer
    Aug 16, 2003
  4. seth brundle
    Replies:
    5
    Views:
    1,074
    seth brundle
    May 26, 2007
  5. Daniel de Angelis Cordeiro

    RubyODBC error codes when using prepared statements

    Daniel de Angelis Cordeiro, Mar 2, 2011, in forum: Ruby
    Replies:
    1
    Views:
    194
    Daniel de Angelis Cordeiro
    Mar 2, 2011
Loading...

Share This Page