#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2001
    Posts
    68
    Rep Power
    14

    Unhappy


    I am strictly a java programmer that connects to an oracle database to do basic query jobs. I was reading in an Oracle tuning book that it would be much more efficient for me to use bind variables in my queries. All of my queries are written in JAVA and passed to the database through either a bean or JDBC connection and all queries are exactly the same with the exception of the variables in the where clause (i.e. where customer= 77447). I read that my query should read (i.e. where customer = :custNO) or something. How do I tell or pass what the custNO is from my java program to the oracle database? Does anyone have experience in this? I would need to somehow set the bind variable in JAVA and pass it the same way i pass the rest of the query, but i have no idea what to call it or how to pass it.... Sorry for the long question, I just want to make sure my problem is clear.... thanks to whoever has thoughts on this.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 1999
    Location
    Columbia, SC
    Posts
    5
    Rep Power
    0
    What you need is called a 'prepared statement'. This way you can have a generic SQL statement and pass a parameter to it. For example...

    // Load driver
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

    // Get connection
    Connection con = DriverManager.getConnection("jdbc:odbc:yourdatabase", "", "");

    // Setup PreparedStatement
    PreparedStatement ps = con.prepareStatement("SELECT firstname,lastname FROM customers WHERE customer = ?");

    // Setup ResultSet
    ResultSet rs;

    // Now this is where we setup the parameter to be passed to the statement

    ps.setInt(77447); // Of course this could be some other variable

    // Now get the results
    rs = ps.executeQuery();

    This is basically it. The "?" in the statement is the variable you pass in. You can have several question marks in the statement, but you must set their value in the order they appear in the statement. Also the value does not have to be an Int but it can be String,Float,Blob,etc. I hope this helps.

    Cheers

IMN logo majestic logo threadwatch logo seochat tools logo