Re: Unnecessary increment of sequence

[Rick Hillegas]

On 1/3/14 4:49 AM, Tim Dudgeon wrote:
VALUES SYSCS_UTIL.SYSCS_PEEK_AT_SEQUENCE('APP', 'SEQ_CPD_CODE');
VALUES COALESCE(99, NEXT VALUE FOR seq_cpd_code);
VALUES SYSCS_UTIL.SYSCS_PEEK_AT_SEQUENCE('APP', 'SEQ_CPD_CODE');
Hi Tim,
You could write a function to issue the "next value for" only when 
necessary. Something like this:

import java.sql.*;

public class w
{
    public  static  Integer coalesce( Integer arg1, String sequenceName )
        throws SQLException
    {
        if ( arg1 != null ) { return arg1; }

        Connection  conn = DriverManager.getConnection( 
"jdbc:default:connection" );
        ResultSet   rs = conn.prepareStatement( "values next value for 
" + sequenceName ).executeQuery();

        rs.next();
        int     retval = rs.getInt( 1 );
        rs.close();

        return retval;
    }
}

...and then use the function like this:

connect 'jdbc:derby:memory:db;create=true';

create sequence FOO;

create function myCoalesce( arg1 int, sequenceName varchar( 32672 ) ) 
returns int
language java parameter style java reads sql data
external name 'w.coalesce';

values syscs_util.syscs_peek_at_sequence( 'APP', 'FOO' );
values myCoalesce( 99, 'FOO' );
values syscs_util.syscs_peek_at_sequence( 'APP', 'FOO' );
values myCoalesce( null, 'FOO' );
values syscs_util.syscs_peek_at_sequence( 'APP', 'FOO' );


Hope this helps,
-Rick