db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: Unnecessary increment of sequence
Date Fri, 03 Jan 2014 21:16:21 GMT
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

Mime
View raw message