Joel Brown's Weblog

Using Parameters in SQL Statements

When you wish to execute a select statement containing WHERE criteria that changes from request to request, there are two typical approaches:

• use input parameters within the statement. For example, set your rowset's command is "select a,b,c from mytable where xyzzy = ?".  Each "?" represents a single value.
  Then set the input parameter in your code:
  

  rowset.set Object(1, qvalue ) ; // parameter numbers start at 1.

  
  
• build the sql command at runtime

String command = "select a, b, c from mytable" ; // stash this constant value somewher
rowset.setCommand( 
    command + " where xyzzy = " + qValue.toString() ) ;

In general, use parameters whenever possible. Creator always executes the rowset's command by using a java.sql.PreparedStatement.  PreparedStatements are compiled (prepared) by the JDBC driver or database and accept input parameters so they can be reused with different data. This reuse improves performance of your application.

However, for any specific situation, you may need to build your command at runtime. If you do, keep in mind that the rowset's command should be valid sql and have the same columns that you plan to have at runtime. This allows the creator design time to determine column names for binding. At runtime, you can set the rowset's command property to just about anything - just make sure any bound columns are included in the query.

Input parameters do have shortcomings. Common issues are::

• runtime query performance. For example, the database's query optimizer may come up with a poor query plan when one uses "wumpus like ?" compared to "wumpus like 'big%'".
  
• IN comparisons. Until runtime, you may not know how many values will exists - "xyzzy IN ( ?,?)" or "xyzzy IN (?, ?, ?, ?)"

Most of this isn't specific to Creator, as we use plain old JDBC to prepare and execute the statements.

Q & A

Where do I call setObject()?

Before the rowset is executed or re-executed.  Exactly where is up to you and your application.

What object type do I pass to setObject()?

To be safe, use the java object type corresponding to the database's type - here's where you need to know a little bit about JDBC. 

How can I share the SQL between the rowset's command property if I'm building the actual statement at runtime?

Often you want the SQL's column list -select a,b,c form mytable - to be in the rowset's command.  At runtime, you'll append the WHERE clause.     The rowset's command property is initially set in the bean's constructor (open the code folded section called "Creator-managed Component Initialization" to see it).  So later in the constructor, save a copy of the command into a property.  You'll also need to create that property.

Posted at 11:57AM Oct 03, 2005 by jfbrown in Creator  |  Comments[2]