0

I am using voltdb-5.0.2 and starting database as voltdb create --zookeeper portno

I have loaded a procedure from java class in voltdb.The procedure takes arguments - tableName , columnName and integer value .

This is the error on sqlcmd line - when I call the procedure as - exec CheckDeleteProcedure arg1(NewTable) arg2 arg3.

VOLTDB ERROR: USER ABORT Attempted to queue DML adhoc sql 'Delete from NewTable where id <=?;' from read only procedure at checkdel.CheckDeleteProcedure.run(CheckDeleteProcedure.java:56)

Arvind Kumar Dwivedi
  • 1
  • 1
  • Arvind, can you provide the contents of CheckDeleteProcedure.java? It's hard to tell the exact problem without that. – BenjaminBallard Mar 17 '15 at 16:49
  • public class CheckDeleteProcedure extends VoltProcedure { public long run(String tbl,String fieldName,int tstamp) { String query2 = "Delete from "+tbl+" where "+fieldName+" <=?;"; final SQLStmt deleteArg= new SQLStmt(query2); voltQueueSQLExperimental(query2,tstamp); long deletedCount = voltExecuteSQL()[0].asScalarLong(); return deletedCount; } } – Arvind Kumar Dwivedi Mar 18 '15 at 12:36
  • My requirement is to delete a table contents created through interactive DDL rather than application catalog .After loading the procedure from sqlcmd ,and on executing for a sample table I get this error. I tried the other way also like starting with the catalog. – Arvind Kumar Dwivedi Mar 18 '15 at 12:39

1 Answers1

0

Thanks for adding that detail.

The voltQueueExperimental() method is an experimental feature that is not supported.

The procedure was categorized as read-only at compile time because it does not contain any compile time constant SQLStmt objects that involve writes. At run-time, you are getting this error because a read-only procedure is not allowed to do any writes. Procedures that involve writes are handled differently (two phase commit, etc).

You could run this DELETE statement as an Ad Hoc query from the client interface.

Another option, if there are a small number of tables you want this procedure to handle would be to declare separate SQLStmt objects for each table. This would be fine as far as making the SQLStmt's compile-time constants:

final String sql1 = "DELETE FROM ";
final String sql2 = " WHERE col1 <= ?;";
final SQLStmt qry1 = new SQLStmt(sql1 + "EMPLOYEE" + sql2);
final SQLStmt qry2 = new SQLStmt(sql1 + "DEPT" + sql2);

I should also caution you about potentially deleting many records at once. I wrote a blog post that goes over that topic.

BenjaminBallard
  • 1,482
  • 12
  • 11
  • I have tried to make a final SQL Stmt object , and call voltQueueSQL method to delete the records but it is failing giving this error(org.voltdb.ProcedureRunner.getCleanParams(ProcedureRunner.java:816)). Can't we make SQLStmt bypassing the tableName as input to procedure? I was able to delete records from client interface,that's works for me . – Arvind Kumar Dwivedi Mar 19 '15 at 05:01
  • Hi Arvind, I should have been more clear. I'll edit my answer to address this. – BenjaminBallard Mar 19 '15 at 13:46