Re: static/dynamic/embedded SQL distinctions

From: shakah <shakahshakah_at_gmail.com>
Date: 22 Aug 2005 12:54:44 -0700
Message-ID: <1124740484.080989.55060@f14g2000cwb.googlegroups.com>

jrefactors_at_hotmail.com wrote:
> I want to distinguish between static SQL, dynamic SQL, and embedded
> SQL, but couldn't find too much useful resources in the web.
>
> For example, if we put SQL statements (SELECT, INSERT, UPDATE, etc...)
> inside an application (e.g. Java application, VB application, etc...),
> do we consider those SQL statements as static SQL? or embedded SQL?
>
> How about dynamic SQL? any practical examples?
>
> Please advise. thanks!!

My take on that would be...

static SQL

---

Hard-coded SQL statements in code, e.g.:   java.sql.ResultSet rs = stmt.executeQuery(     "SELECT colA, colB FROM tableA"
    ) ;

  while(rs.next()) {
    System.out.println("colA: " + rs.getString(1)) ;
    System.out.println("colB: " + rs.getString(2)) ;

  }

dynamic SQL

---

Building the SQL string according to passed-in parameters, e.g.:   public PreparedStatement getStmt(Connection con, String sCity) {     PreparedStatement pstmt = con.prepareStatement(

        "SELECT colA, colB from tableA"
      + (null==sCity ? "" : " WHERE city=?")
      ) ;
    if(null!=sCity) {
      pstmt.setString(1, sCity) ;

    }
    return pstmt ;
  }

  java.sql.ResultSet rs = getStmt(con, "London").executeQuery() ;
  while(rs.next()) {
    System.out.println("colA: " + rs.getString(1)) ;
    System.out.println("colB: " + rs.getString(2)) ;

  }

As for embedded SQL, I've always thought of that as putting SQL statements in code (in a language-neutral way) and using a preprocessor of some sort to generate code in place, e.g what Pro*C does for Oracle:   http://www-db.stanford.edu/~ullman/fcdb/oracle/or-proc.html Received on Mon Aug 22 2005 - 14:54:44 CDT