Caused by: org.hsqldb.HsqlException: cardinality violation

Question

I am using HSQLDB as a backend for my java application, when i am trying to call procedure in java with single record, i am able to get the resultset, but for multiple records, i am getting following exception "Caused by: org.hsqldb.HsqlException: cardinality violation"

CREATE PROCEDURE get_Street(OUT c_str_col VARCHAR(50), OUT c_color VARCHAR(50)) READS SQL DATA BEGIN ATOMIC SELECT str_col, color INTO c_str_col, c_color FROM sample;END

String proc = ("{call get_Street(?,?)}");
CallableStatement cs = connection.prepareCall(proc);            
cs.registerOutParameter(1, Types.VARCHAR);
cs.registerOutParameter(2, Types.VARCHAR);  
cs.execute();

Answer 1

The two OUT variable are scalar and accept only a single value. If you want to return multiple rows of data, you should open a CURSOR in the procedure body and read a ResultSet that is returned by procedure call.

Details are in the guide:

http://hsqldb.org/doc/2.0/guide/sqlroutines-chapt.html#src_returning_data