#1274 null pointer exception while executing stored procedure

closed-fixed
None
1
2013-12-30
2012-11-30
No

Hi,

the next example gives a null pointer exception (HSQLDB 2.2.8, server version):
create table test1 (
test_id integer primary key,
test_text VarChar(20)
);

insert into test1 values (1, '1');
insert into test1 values (2, '2');

-- drop PROCEDURE test_bind_var_is_null;

CREATE PROCEDURE test_bind_var_is_null()
MODIFIES SQL DATA
BEGIN ATOMIC
DECLARE v_test_id INTEGER;
declare i, j integer;
set i = 0;
in_i: while i < 2
do
set i = i + 1;
set j = select test_id from test1 where test_id = i;
do_update: begin atomic
--declare undo handler for SQLEXCEPTION leave do_update;
update test1
set test_text = concat(test_id, test_text)
where test_id = j
;
end do_update;
end while in_i;
end;

call test_bind_var_is_null();

this occurrs only when the do_update block is run inside a loop.

This error occurred inside a more complex procedure and was really hard to find. It would be much easier, if there was a print option as requested (by me) in 3584054. Is there already an internal / unofficial mechanism that helps debugging stored procedures?

Discussion

  • Fred Toussi

    Fred Toussi - 2012-11-30

    Thanks for reporting;

    In org.hsqldb.StatementCompound modify the class variable declarations around line 66

    HashMappedList scopeVariables = new HashMappedList();

    As for debugging, currently the best option is to write your own Java function to dump the data and call this function wherever you want in your procedure.

    public static dump(String s) {
    System.out.println(s);
    }

    In your procedure

    do_update: begin atomic
    --declare undo handler for SQLEXCEPTION leave do_update;
    update test1 set test_text = concat(test_id, test_text) where test_id = j;
    dump(String.valueof(i);
    end do_update;

    Please note there have been quite a few enhancements to routines in the latest snapshot jars. Use the latest snapshot and report any issues or requests for enhancement.

     
  • Fred Toussi

    Fred Toussi - 2012-11-30

    I mixed up Java and SQL in the example. The correct example is:

    do_update: begin atomic
    --declare undo handler for SQLEXCEPTION leave do_update;
    update test1 set test_text = concat(test_id, test_text) where test_id = j;
    dump(cast(i to VARCHAR(10)));
    end do_update;

     
  • Fred Toussi

    Fred Toussi - 2012-11-30
    • assigned_to: nobody --> fredt
     
  • Fred Toussi

    Fred Toussi - 2012-11-30
    • priority: 5 --> 1
    • status: open --> open-fixed
     
  • Stephan Zodrow

    Stephan Zodrow - 2012-12-01

    Hi Fred,

    I checked with latest snapshot from this morning. The error still occurrs.

    I had additional findings. When you modify the procedure as follows, execution hangs and you cannot cancel the SQL (using squirrel frontend):

    CREATE PROCEDURE test_bind_var_is_null()
    MODIFIES SQL DATA
    BEGIN ATOMIC
    DECLARE v_test_id INTEGER;
    declare i, j integer;
    set i = 0;
    -- in_i: while i < 2
    -- do
    set i = i + 1;
    set j = select test_id from test1 where test_id = i;
    do_update: begin atomic
    --declare undo handler for SQLEXCEPTION leave do_update;
    update test1
    set test_text = concat(test_id, test_text)
    where test_id = j
    ;
    end do_update;
    -- end while in_i;
    end;

    When you then comment out the set j = select.. statement
    -- set j = select test_id from test1 where test_id = i;
    and recompile, then everything is fine. Maybe set = (select ...) is the problem.

    Thanks for the dump procedure

     
  • Fred Toussi

    Fred Toussi - 2012-12-01

    You tried the snapshot but this particular issue was fixed and committed to SVN, after the last snapshot.

    The new routine works with or without the commenting.

     
  • Fred Toussi

    Fred Toussi - 2013-12-30
    • status: open-fixed --> closed-fixed
     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks