I am new in ScyllaDB. For my project I want to update one column(pin table) from another table (client). So at first I select col1, customerID from client table, then for each row: add row into batch statement. If batchStatement is reached at 100 statement, then executed and new builder is instantiated.
I didn’t find out why I am getting this error:
java.lang.IllegalStateException: Batch statement cannot contain more than 65535 statements.
at com.datastax.oss.driver.api.core.cql.BatchStatementBuilder.addStatement(BatchStatementBuilder.java:84)
public void updateBillingPassword(CassandraConnector connector) {
try {
Select select = selectFrom(connector.getDatabase(), "Client")
.column("col1")
.column("customerid");
SimpleStatement selectStatement = select.build().setTimeout(Duration.ofHours(5));
ResultSet resultSet = connector.execute(selectStatement);
PreparedStatement preparedStatement = connector.getSession().prepare(
"Update Pin set pinCol=:value where pinNO=:clcustomerid"
);
BatchStatementBuilder batchStatementBuilder = BatchStatement.builder(DefaultBatchType.UNLOGGED);
int count=0, batchSize = 100;
Iterator<Row> iterator = resultSet.iterator();
while(iterator.hasNext()){
Row row = iterator.next();
String col1 = row.getString("col1");
String clcustomerid = row.getString("customerid");
if (col1 != null && clcustomerid!=null) {
count++;
batchStatementBuilder = batchStatementBuilder.addStatement(preparedStatement.bind(col1, clcustomerid));
try{
if(count%batchSize==0 || !iterator.hasNext()){
LOGGER.info("Executing batch statement...");
System.out.println("Executing batch statement...");
connector.getSession().execute(batchStatementBuilder.build());
LOGGER.info("Executed batch statement.");
System.out.println("Executed batch statement.");
batchStatementBuilder = BatchStatement.builder(DefaultBatchType.UNLOGGED);
}
} catch (Exception e){
LOGGER.error("",e);
}
}
}
} catch (Exception e) {
// Handle exceptions
LOGGER.error("",e);
e.printStackTrace();
}
}