"set char_convert on with no_error" in PreparedSta

[etienno]

Hi,

I am using the jdbcTemplate.batchUpdate() method on a Sybase database and because I am reading from a unrealiable data source, I have some bad data (outside unicode 16bit) to insert in the DB like "âæ?5B±*".

The Sybase manual say that I need to set some properties of the database session. I can do this in executing "set char_convert on with no_error" on the PreparedStament.

http://manuals.sybase.com/onlinebook...28381;pt=14594

So, basically It seems that I should do:

preparedStatement.execute("set char_convert on with no_error");

But when I insert this code in the place I guess it should be, I get this error:

org.springframework.jdbc.UncategorizedSQLException: (executing PreparedStatementCallback [org.springframework.jdbc.core.JdbcTemplate$SimplePreparedStatementCreator@70fe3f63]): encountered SQLException [JZ0S3: The inherited method execute(String) cannot be used in this subclass.]; nested exception is java.sql.SQLException: JZ0S3: The inherited method execute(String) cannot be used in this subclass.

The code I use is this one. I am following the example of Spring in Action, page 148 (Listing 4.8 ):

	public void updateBatchRecord(String sql,  final List objList){

		BatchPreparedStatementSetter setter = null;
		
		setter = new BatchPreparedStatementSetter(){
			public int getBatchSize(){
				return objList.size();
			}
			public void setValues(PreparedStatement ps, int index) throws SQLException {
				ps.execute&#40;"set char_convert off"&#41;; <----- EXCEPTION HERE
				Iterator it = &#40;&#40;List&#41;objList.get&#40;index&#41;&#41;.iterator&#40;&#41;;
				int i=0;
				while&#40;it.hasNext&#40;&#41;&#41;&#123;
					i++;
					Object obj = &#40;Object&#41;it.next&#40;&#41;;
					ps.setObject&#40;i, obj&#41;;
				&#125;
			&#125;
		&#125;;

// Finally, I call the Spring DAO and do a simple
//->JdbcTemplate jdbcTemplate = new 	JdbcTemplate&#40;dataSource&#41;;
//->jdbcTemplate.batchUpdate&#40;sql,setter&#41;;
SpringUtil.getSybaseDataAccess&#40;&#41;.updateBatchUpdate&#40;sql,setter&#41;;
	&#125;

So does anyone know how to execute a query on the PreparedStatement passed in the setValues() or elsewhere? I just need to execute "set char_convert on with no_error"before I update data in the database using the batchUpdate..


Any idea?

Thanks,

Etienne.

[trisberg]

Sounds like you need to create a separate statement. Try replacing ps.execute() with the following code snippet:

              Statement st = ps.getConnection().createStatement();
              st.execute("set char_convert off");
              st.close();

[etienno]

Thanks, but it didn't work either. It seems that the statement I got from the connection is not the same used by the batch update. I now get the classic exception of "cannot convert char...".

Here's the code I put as you proposed me :

	/**
	 * ObjList is a List (list of Updates) of List (list of values of an update)
	 * @param vo
	 */
	public void updateBatchRecord(String sql,  final List objList){

		BatchPreparedStatementSetter setter = null;
		
		setter = new BatchPreparedStatementSetter(){
			public int getBatchSize(){
				return objList.size();
			}
			public void setValues(PreparedStatement ps, int index) throws SQLException {
				  Statement st = ps.getConnection().createStatement();
	              st.execute("set char_convert off");
	              st.close();
				  logger.debug("statment created");
				Iterator it = ((List)objList.get(index)).iterator();
				int i=0;
				while(it.hasNext()){
					i++;
					Object obj = (Object)it.next();
					ps.setObject(i, obj);
				}
			}
		};		SpringUtil.getSybaseDataAccess().updateBatchUpdate(sql,setter);
	}

	
	public void processRow(ResultSet rs) throws SQLException {
		List values = new ArrayList();
		int cols = rs.getMetaData().getColumnCount();

		for&#40;int i=0; i<cols; i++&#41;&#123;
			values.add&#40;rs.getObject&#40;i+1&#41;&#41;;
		&#125;
		objList.add&#40;values&#41;;

		// BATCH_NO == 100 for testing
		if&#40;objList.size&#40;&#41;>=BATCH_NO&#41;&#123;
			String sql = "INSERT INTO "+tableName+" VALUES ";
			String sqlValue = "&#40;";
			for&#40;int i=0; i<cols-1; i++&#41;&#123;
				sqlValue += "?,";
			&#125;
			sqlValue += "?&#41;";

// should write 100 batch to go
			logger.debug&#40;"start batch "+objList.size&#40;&#41;&#41;;
			updateBatchRecord&#40;sql+sqlValue, objList&#41;;
// reset the objList Array
			objList = new ArrayList&#40;&#41;;
// should write 0 batch to go
			logger.debug&#40;"end batch "+objList.size&#40;&#41;&#41;;
		&#125;
	&#125;

When I run this code I get this error as the setting "set char_convert off" is not effective :

org.springframework.jdbc.UncategorizedSQLException: (executing PreparedStatementCallback [org.springframework.jdbc.core.JdbcTemplate$SimplePreparedStatementCreator@5d3b1765]): encountered SQLException [JZ0BE: BatchUpdateException: Error occurred while executing batch statement: Error converting characters into server's character set. Some character(s) could not be converted.
]; nested exception is com.sybase.jdbc2.jdbc.SybBatchUpdateException: JZ0BE: BatchUpdateException: Error occurred while executing batch statement: Error converting characters into server's character set. Some character(s) could not be converted.

com.sybase.jdbc2.jdbc.SybBatchUpdateException: JZ0BE: BatchUpdateException: Error occurred while executing batch statement: Error converting characters into server's character set. Some character(s) could not be converted.

	at com.sybase.jdbc2.jdbc.ErrorMessage.raiseBatchUpdateException(ErrorMessage.java:698)
	at com.sybase.jdbc2.jdbc.SybStatement.batchLoop(SybStatement.java:1330)
	at com.sybase.jdbc2.jdbc.SybStatement.sendBatch(SybStatement.java:1139)
	at com.sybase.jdbc2.jdbc.SybStatement.executeBatch(SybStatement.java:1106)
	at com.sybase.jdbc2.jdbc.SybPreparedStatement.executeBatch(SybPreparedStatement.java(Compiled Code))
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1722)
	at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:743)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:450)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:469)
	at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:734)

Thanks for your suggestion!


Etienne.

[trisberg]

Let's try something else then. How would you solve this using plain JDBC code? If you can get a straight JDBC program to work, then we should be able to get a Spring version to work as well.