Re: Dynamic SQL question.

From: Gerard M. Averill <e-mail.address_at_my.sig>
Date: Thu, 29 Apr 99 19:49:55 GMT
Message-ID: <7gacrn$8ig$1@news.doit.wisc.edu>

In article <7g8i0l$e82$1_at_nnrp1.dejanews.com>, odessa7_at_my-dejanews.com wrote:
>Greetings!
>
>I need to create a dynamic select statement and return a result set back to
>Java calling env.
>
>Suppose, I built my dynamic select as 'SELECT fname, lname FROM emp where ssn
>= :x'
>
>What do I do next? I heard I need to use DBMS_SQL package. How do I get a
>result set back from DBMS_SQL I can return a cursor/or array back to java
>caller? Any code sample is appreciated.
>

L.B,

You don't need to use DBMS_SQL to do what you want; this can be done simply using a prepared statement with a bind variable. Assuming you have a valid database connection object:

    try {
      PreparedStatement stmt = conn.prepareStatement("SELECT fname, lname FROM emp where ssn = ?");

      try {
        stmt.setString(1, "123456789"); // Bind SSN value (assumed string)
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
          String firstName = rs.getString(1); // Get column 1 as string
          String lastName = rs.getString(2); // Get column 2 as string

          < do something with values >
        }
      }
      finally {
        stmt.close(); // Close statement (and result set)
      }

    }
    catch (SQLException e) {
      < error handling >
    }

Note the use of a ? instead of the Oracle-like syntax for bind variables. Prepared statements with bind variables can be reused; that is, after preparing it once, new SSNs can be bound ( stmt.setString ) and the query re-executed ( stmt.executeQuery ) repeatedly.

Hope this helps.
Gerard

---

Gerard M. Averill, Researcher
CHSRA, University of Wisconsin - Madison GAverill<at>chsra<dot>wisc<dot>edu Received on Thu Apr 29 1999 - 14:49:55 CDT