Batch Update in ASA

[schiremath]

Hi Guys,
I am trying to insert records into ASA db using BatchPreparedStatementSetter but for some reason it is failing. the error is

[JZ0BE: BatchUpdateException: Error occurred while executing batch statement: ASA Error -143: Column '@p0' not found];
SQL was [INSERT INTO OP_POSITION_DETAILS_SHW ( KEY_POSITION,COMPANY_ID ) VALUES ( ?,? )] for task
[executing PreparedStatementCallback [org.springframework.jdbc.core.JdbcTemplate$SimpleP reparedStatementCreator@1490eb5]]

I am not able to figure out how that column name "@po" is constructed although I am specifying in my insert SQL.

Could any of you tell me please, whats happening and what's needs to be changed.

Your help / suggestion is much appreciated.

Regards
Sanjeev

[schiremath]

Hi Guys,
I found partial soultion to my problem.

The ASA version we are currently using 6.0.3 which does not support Batch Update and hence above exception as batch operation is parameterized. So, I fixed my code temporarily by inserting records individually. .
some where I read that spring will check with back-end/jdbc driver whether batch update support or not and if not it does a normal insert statement.
I can't remember where I read, but is this true, If so has any body tried with ASA 6.0 and above ( inclusive)?

Look forward for information if any body has any thing on this.


Regards
Sanjeev

[irbouho]

from JdbcTemplate.java(Current version in CVS, I did not check if it is in 1.1RC2)

public int[] batchUpdate(String sql, final BatchPreparedStatementSetter pss) throws DataAccessException {
  if (logger.isDebugEnabled()) {
    logger.debug("Executing SQL batch update [" + sql + "]");
  }
  return (int[]) execute(sql, new PreparedStatementCallback() {
    public Object doInPreparedStatement(PreparedStatement ps) throws SQLException {
      int batchSize = pss.getBatchSize();
      DatabaseMetaData dbmd = ps.getConnection().getMetaData();
      try {
        if (dbmd != null && dbmd.supportsBatchUpdates()) {
          for &#40;int i = 0; i < batchSize; i++&#41; &#123;
            pss.setValues&#40;ps, i&#41;;
            ps.addBatch&#40;&#41;;
          &#125;
          return ps.executeBatch&#40;&#41;;
        &#125;
        else &#123;
          int&#91;&#93; rowsAffected = new int&#91;batchSize&#93;;
          for &#40;int i = 0; i < batchSize; i++&#41; &#123;
            pss.setValues&#40;ps, i&#41;;
            rowsAffected&#91;i&#93; = ps.executeUpdate&#40;&#41;;
          &#125;
          return rowsAffected;
        &#125;
      &#125;
      finally &#123;
        if &#40;pss instanceof ParameterDisposer&#41; &#123;
          &#40;&#40;ParameterDisposer&#41; pss&#41;.cleanupParameters&#40;&#41;;
        &#125;
      &#125;
    &#125;
  &#125;&#41;;
&#125;

Spring checks with back-end/jdbc driver whether batch update is supported or not and if not it does a normal insert statement.
I do not know what ASA stands for but I think you can write a small test to check if your db / jdbc driver support batch updates.

[Keith Donald]

Just something to consider on Spring's JDBC batch update support:

In the 1.1 codebase there is a new SqlOperation that makes performing batch updates easier than using the batchUpdate method of JdbcTemplate with a PreparedStatementSetter. This operation essentially is a higher-level abstraction on top of JdbcTemplate. The result is some pretty clean code:

BatchSqlUpdate batchUpdater = new MyBatchSqlUpdate(getDataSource(), "insert into table values(?, ?, ?)");
batchUpdater.update(new Object[] { value1, value2, value3 };
batchUpdater.update(new Object[] { value4, value5, value6 };
batchUpdater.update(new Object[] { value7, value8, value9 };
// etc...
batchUpdater.flush();

[/code]

[trisberg]

Hi Guys,
I found partial soultion to my problem.

The ASA version we are currently using 6.0.3 which does not support Batch Update and hence above exception as batch operation is parameterized. So, I fixed my code temporarily by inserting records individually. .
some where I read that spring will check with back-end/jdbc driver whether batch update support or not and if not it does a normal insert statement.
I can't remember where I read, but is this true, If so has any body tried with ASA 6.0 and above ( inclusive)?

Look forward for information if any body has any thing on this.

I tried batch updates with Sybase ASA 9.0.1 which supports batch updates and everything worked fine. I added a DEBUG log statement to JdbcTemplate where we determine if batch updates are supported or not se we can more easily detect what is going on. This is in CVS and will be part of 1.1 final.

The JdbcTemplate does execute each statement individiually if batch updates are not supported. If this does not work, could you post your code.

[schiremath]

Hi All,
Thank you very much for all your replies.
When I tried Batch update using ASA 6.0 ( which also supports BachUpdate as per the documentation )
i get the following error message:

ASA Error -143: Column '@p0' not found]

I am still using spring 1.1 RC1 and not from CVS head.
Should I get it from CVS and build or can I
use the latest RC1 build.?

Also please note that I tried with jConnect 5.5 / 5.2 driver.
These drivers support parameterised update and hence I get the above error.


Thanks and Regards
Sanjeev

[trisberg]

I don't think this is a Spring issue. I would create a new database with a new table and try using that just to verify that the batch updates do work.

I did google for your error and there where a few similar looking issues. Here is one link that might help:
http://64.233.161.104/search?q=cache...mn+%40p0&hl=en

[schiremath]

Hi Thomas,
First of all thanks for your reply and link. After doing little investigation,
on ASA doc and google I found out that if using jConnection 5.5 /5.2 driver we can set "LITERAL_PARAMS"
parameter to enable literal params.
So, the connection url will be some thing like
"URL=jdbc:sybase:Tds:ip_address:2638/dbName1?LITERAL_PARAMS=true"

It works. Batch update works fine in ASA 6.0.3

I must agree with you that it is nothing to do with Spring.
Once again thanks for all your help.

Regards
Sanjeev