Bug 2894

Didn't mean to close this one.  I'm not sure what is causing it; it would be 
great if someone with Sybase would debug this.  My only suggestion would be to 
try other JDBC drivers.

This does not seem a Sybase bug to me. It will happen with all databases
make a difference between setting an integer or string parameter.

Details:

If you make a prepared query like
 'select * from orders where orderno = ?',

internally a java object of type java.sql.PreparedStatement will be
generated. Before executing this statement and creating a resultset,
all parameters must be set. But this has to be done according to their
types. In this case, something like

 st.setInt(1,nameOfIntegerVariable);

has to be done. There are a lot of methods for other types, see
documentation of java.sql.PreparedStatement for details.

The taglib implementation has just one variant. It can be found in
method doEndTag() of class
org.apache.taglibs.dbtags.preparedstatement.SetColumnTag:

public class SetColumnTag extends BaseSetterBodyTag {

  public int doEndTag() throws JspTagException {

    PreparedStatement statement = getPreparedStatement();

    try {
      String string = null;

      if (_attributeName == null) {
        string = getBodyContent().getString();
      } else {
        string = (String) getAttribute(_attributeName);
      }

      statement.setString(_position,string);    // <--- here it is!

    } catch (SQLException e) {
      throw new JspTagException(e.toString());
    }
    return EVAL_PAGE;
  }

}

So the taglib will always try to do a setString() method call and you will
get an error from the database system, which expects another type.

So what to do? The taglib cannot automatically determine which method to
call. So isn't the best to extend the library with several tags for several
types?

<sql:setIntColumn>
   (doing a
      statement.setString(_position,Integer.parseInt(string));
   )

<sql:setFloatColumn>
<sql:setStringColumn>
  (same as current <sql:setColumn>, which should still be supported for
   backwards compatibility)
...


Btw. I would like names like setIntParam, setFloatParam etc. more.

Hope this helps a bit.

Hmmm, after thinking a bit about my first suggestion, I think another
modification would be better.

What about adding an attribute 'paramType' containing the type of
the parameter? Usage would be like this:

  <sql:setColumn position="1" paramType="integer"> 22 </sql:setColumn>

If the attribute is not required and String is default paramType, it would
be totally backwards compatible. This way we do not blow up the 
library with dozens of new tags. 

What has to be done in detail?

1) Add an attribute 'paramType' to tag setColumn:

   <tag>
    <name>setColumn</name>
    
<tagclass>org.apache.taglibs.dbtags.preparedstatement.SetColumnTag</tagclass>
...
<!-- next 4 lines new -->   
    <attribute>
      <name>paramType</name>
      <required>no</required>
      <rtexprvalue>no</rtexprvalue>
    </attribute>
<!-- end of changes -->
...
  </tag>

2) Insert into class BaseSetterBodyTag new Attribute paramType
   with setter method:

  public class BaseSetterBodyTag extends BodyTagSupport {
  ...
  protected  String _paramType = null;
  ...
  public void release() {
    ...
    _paramType = null;
  }
  ...
  public void setParamType(String paramType) {
      this._paramType = paramType;
  }
}

3) Modify SetColumnTag to examine this attribute. Be aware of 
   possible new Exceptions: 

  public int doEndTag() throws JspTagException {

    PreparedStatement statement = getPreparedStatement();

    try {
      String string = null;

      if (_attributeName == null) {
        string = getBodyContent().getString();
      } else {
        string = (String) getAttribute(_attributeName);
      }

      if ("INTEGER".equalsIgnoreCase(_paramType)) {
          statement.setInt(_position,Integer.parseInt(string));
      } else if ("FLOAT".equalsIgnoreCase(_paramType)) {
          statement.setFloat(_position,Float.parseFloat(string));

                  // more types can and should be easily added
      } else {
          statement.setString(_position,string);   // default
      }

    } catch (SQLException e) {
      throw new JspTagException(e.toString());
    } catch (NumberFormatException e) {
       throw new JspTagException(e.toString());
    }
    return EVAL_PAGE;
  }

What do you think?

Many database drivers will automatically convert to the correct datatype when 
setString is called.  It appears that some drivers do not fit this pattern.  In 
order to work around this, we would have to obtain meta data about the columns 
in the insert (perhaps one of the MetaData objects).  This would be a good bug 
fix candidate for an upcoming point release.

It will not be possible to find a general way to select the 
'correct' setXXX() method by looking at the database meta data
like column types, because the parameters might also occur in
expressions. I will give you an example of a query with different
results depending on the chosen setXXX() method:

...
Connection con = DriverManager.getConnection(jdbcURL, username, password);

// create testtab with one column and three rows:

Statement st= con.createStatement();
st.executeUpdate("create table tttt (col1 integer)");
st.executeUpdate("insert into tttt values (4)");
st.executeUpdate("insert into tttt values (8)");
st.executeUpdate("insert into tttt values (16)");

// prepare statement with parameters:

PreparedStatement prSt = con.prepareStatement(
     "select * from tttt where col1 > float4( char( ? + ? )) ");

// bind using setString() and retrieve results:

prSt.setString(1,"1");
prSt.setString(2,"2");
ResultSet rs1 = prSt.executeQuery();
while (rs1.next()) {
 System.out.println("Result using setString(): " +    rs1.getInt(1));
}
rs1.close();

// bind using setInt() and retrieve results:
prSt.setInt(1,1);
prSt.setInt(2,2);
rs1 = prSt.executeQuery();
while (rs1.next()) {
  System.out.println("Result using setInt(): " +    rs1.getInt(1));
}
rs1.close();

st.executeUpdate("drop table tttt");

The Result of this program is:

Result using setString(): 16
Result using setInt(): 4
Result using setInt(): 8
Result using setInt(): 16

because
 - float4( char( "1" + "2" )) = 12
 - float4( char( 1 + 2 )) = 3

So I would leave it up to the programmer or page designer
to specify the column type and would still suggest using
a new attribute. 

DBTags is officially a legacy/deprecated taglib, so we won't fix this bug.