Newbie: SqlUpdate Spring 1.2.5 WSAD 5 WinXP Oracle 9

[xcs1726]

Hi
I am having difficulty with a port of Spring 1.2.5 to WSAD 5, Oracle 9, WinXP
The SqlUpdate method is misfunctioning.
It doesn't seem to be appending the VALUES clause to an SQL INSERT statement.

The code has been successfully tested using Eclipse 3.1 w/MySQL 4 & Tomcat 4 & 5
Abbreviated Code Snippet follows:
// imports ....

public class TmcTourReqAddQuery extends SqlUpdate{

private DataSource ds;
private TmcTourReq req;

protected final Log logger = LogFactory.getLog(getClass());

public TmcTourReqAddQuery(DataSource ds, TmcTourReq req){
this.ds = ds;
this.req = req;
}

public DataSource getDs() {
return ds;
}

public void setDs(DataSource ds) {
this.ds = ds;
}

public TmcTourReq getReq() {
return req;
}

public void setReq(TmcTourReq req) {
this.req = req;
}

public int addTmcTourReq(){
logger.info("TmcTourReqAddQuery.addTmcTourReq");
logger.info("TmcTourReqAddQuery.addTmcTourReq QUERY ds = "+ ds);
if(null == req) { logger.error("TmcTourReqAddQuery.addTmcTourReq: null Request");
}
if(null == req.getFirstName()){
logger.info("TmcTourReqAddQuery.addTmcTourReq: null first name");
} else {
logger.info ("TmcTourReqAddQuery.addTmcTourReq first Name = " + req.getFirstName());
}
SqlUpdate su =
new SqlUpdate(ds, "insert into Request set firstName =? , lastName = ?, ... /// and so on ... ");
su.declareParameter(new SqlParameter("firstName", Types.VARCHAR));
su.declareParameter(new SqlParameter("lastName", Types.VARCHAR));
// ... and so on ...);
su.compile();
Object[] oa = new Object[21];
oa[0] = new String(req.getFirstName());
oa[1] = new String(req.getLastName());
// ... and more ...
int count = su.update(oa);
logger.info("TmcTourReqAddQuery.addTmcTourReq: count = " + count);
return count;
}

}

The Exception from within WSAD console:

[1/17/06 11:24:08:554 EST] 64188f44 TmcTourReqAdd I com.csc.tmctours.db.queries.TmcTourReqAddQuery TmcTourReqAddQuery.addTmcTourReq
[1/17/06 11:24:08:554 EST] 64188f44 TmcTourReqAdd I com.csc.tmctours.db.queries.TmcTourReqAddQuery TmcTourReqAddQuery.addTmcTourReq QUERY ds = com.ibm.ws.rsadapter.jdbc.WSJdbcDataSource@75468f4 0
[1/17/06 11:24:08:554 EST] 64188f44 TmcTourReqAdd I com.csc.tmctours.db.queries.TmcTourReqAddQuery TmcTourReqAddQuery.addTmcTourReq first Name = Requestor First Name
[1/17/06 11:24:09:304 EST] 64188f44 WSRdbDataSour I DSRA8203I: Database product name : Oracle
[1/17/06 11:24:09:367 EST] 64188f44 WSRdbDataSour I DSRA8204I: Database product version : Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
JServer Release 9.2.0.6.0 - Production
[1/17/06 11:24:09:367 EST] 64188f44 WSRdbDataSour I DSRA8205I: JDBC driver name : Oracle JDBC driver
[1/17/06 11:24:09:367 EST] 64188f44 WSRdbDataSour I DSRA8206I: JDBC driver version : 9.2.0.6.0
[1/17/06 11:24:09:929 EST] 64188f44 XmlBeanDefini I org.springframework.beans.factory.xml.XmlBeanDefin itionReader Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
[1/17/06 11:24:09:992 EST] 64188f44 SQLErrorCodes I org.springframework.jdbc.support.SQLErrorCodesFact ory SQLErrorCodes loaded: [DB2, HSQL, MS-SQL, MySQL, Oracle, Informix, PostgreSQL, Sybase]
[1/17/06 11:24:10:007 EST] 64188f44 DispatcherSer E org.springframework.web.servlet.DispatcherServlet Could not complete request
[1/17/06 11:24:10:039 EST] 64188f44 DispatcherSer E org.springframework.web.servlet.DispatcherServlet TRAS0014I: The following exception was logged org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [insert into Request set firstName =? , lastName = ?, phone = ? , email = ? , hostFirstName = ? , hostLastName = ? , hostPhone = ? , hostEmail = ?, guestFirstName = ? , guestLastName = ? , guestCompanyName = ? , tourDate = ?, tourTime = ?, altDate = ? , altTime = ?, seniorLevelName = ? , seniorLevelTitle = ? , specialRequirements = ? , objectives = ?, message = ?, marketing = ?]; nested exception is java.sql.SQLException: ORA-00926: missing VALUES keyword

java.sql.SQLException: ORA-00926: missing VALUES keyword

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBE rror.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:2 89)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:589)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol .java:1957)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TT C7Protocol.java:1114)
at oracle.jdbc.driver.OracleStatement.executeNonQuery (OracleStatement.java:2181)
at oracle.jdbc.driver.OracleStatement.doExecuteOther( OracleStatement.java:2063)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTi meout(OracleStatement.java:2922)
at oracle.jdbc.driver.OraclePreparedStatement.execute Update(OraclePreparedStatement.java:644)
at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement. executeUpdate(WSJdbcPreparedStatement.java:469)
at org.springframework.jdbc.core.JdbcTemplate$2.doInP reparedStatement(JdbcTemplate.java:682)
at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:454)
at org.springframework.jdbc.core.JdbcTemplate.update( JdbcTemplate.java:676)
at org.springframework.jdbc.core.JdbcTemplate.update( JdbcTemplate.java:699)
at org.springframework.jdbc.object.SqlUpdate.update(S qlUpdate.java:158)
at com.csc.tmctours.db.queries.TmcTourReqAddQuery.add TmcTourReq(TmcTourReqAddQuery.java:104)

/// and more ..

[trisberg]

That's an invalid insert statement for Oracle - it should look something like this:

insert into Request (firstName, lastName, phone, email, hostFirstName,
 hostLastName, hostPhone, hostEmail, guestFirstName, guestLastName,
 guestCompanyName, tourDate, tourTime, altDate, altTime, seniorLevelName,
 seniorLevelTitle, specialRequirements, objectives, message, marketing)
 values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)