debugging prepared statements - Mailing list pgsql-jdbc

ok, i've got something going on that i really don't get, and i'm uncertain
how to proceed in debugging it. i'm probably missing something really dumb
here, but i just don't see it.

i have code which builds a pair of prepared statements when the java object
corresponding to a row in a db is created, one for insert (used if it's a
new object) and one for update (if it's been inserted before.) fields are
in the same order so a mostly-common set of set methods can be used to set
values in the prepared statement.

the insert is working fine. the set statements for the update are blowing
up, complaining about an out of range index. i can't for the life of me see
the difference.

the text for the creation of the prepared statements is (this what is
output by the program, with newlines and indentation supplied by me):

INSERT INTO county_gat ( county_gat_id, inactive,
     created, updated, updated_by, county_id, year,
     target_total, legacy, target_percentages,
     current_monthly_totals, monthly_targets,
     current_total, last_computed)
VALUES ( ?, ?, now(), now(), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

UPDATE county_gat SET inactive = ?, updated = now(),
     updated_by = ?, county_id = ?, year = ?,
     target_total = ?,legacy = ?, target_percentages = ?,
     current_monthly_totals = ?, monthly_targets = ?,
     current_total = ?, last_computed = ?
    WHERE county_gat_id = ?

the save method with the mutual code is as follows. the path for inserts
(when boolean new_county_gat is true) works as intended. the path for
updates blows up on index 9, per the debugging output shown after the code.
this is at the first set method after the method prints "done with all
arrays ..."

    public void save(){
        int field_index;
        PreparedStatement statement;
        Debug.println( "Setting up statements");
        if( new_county_gat){
            Debug.println( "Insert");
            field_index = insert_core_variables;
            statement = insert_statement;
        } else {
            Debug.println( "Update");
            field_index = update_core_variables;
            statement = update_statement;
        }
        Debug.println( "field_index: " + field_index);
        Debug.println( "statement: " + statement.toString());
        try {
            if( new_county_gat){
                statement.setLong( 1, county_gat_id);
            }
            statement.setLong( ++field_index, county_id);
            statement.setInt( ++field_index, year);
            statement.setInt( ++field_index, target_total);
            statement.setBoolean( ++field_index, legacy);
            statement.setString( ++field_index,
                                 DBInstance.ArrayToString( target_percentages));
            statement.setString( ++field_index,
                                 DBInstance.ArrayToString( current_monthly_totals));
            statement.setString( ++field_index,
                                 DBInstance.ArrayToString( monthly_targets));
            Debug.println( "done with all arrays, field_index: " + field_index);
            statement.setInt( ++field_index, current_total);
            Debug.println( "field_index: " + field_index);
            statement.setTimestamp( ++field_index, last_computed);
            if( ! new_county_gat){
                statement.setLong( ++field_index, county_gat_id);
            }
            Debug.println( "Final field_index value: " + field_index);
            Debug.println( "done with specific vars, now set Core vars");
            setCommonCoreVars( true);
            Debug.println( "Saving with Prepared Statement: " + statement.toString());
            statement.executeUpdate();
            if( new_county_gat){
                new_county_gat = false;
            }
        } catch( SQLException ex){
            System.err.println( "SQLException: " + ex.getMessage());
        }
    }

and the debug output. note that the toString() method on the prepared
statement just produces the string of "?null" values. what is interesting
is that there are 9 of them, and it is the ninth set which blows up.

Setting up statements
Update
field_index: 2
statement: ?null?null?null?null?null?null?null?null?null
done with all arrays, field_index: 9
SQLException: Parameter index out of range.

any help/suggestions on debugging tactics would be appreciated.

richard
--
Richard Welty                                         rwelty@averillpark.net
Averill Park Networking                                         518-573-7592
              Unix, Linux, IP Network Engineering, Security