Thread: debugging prepared statements
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
On Fri, 7 Feb 2003 08:32:51 -0500 (EST) Richard Welty <rwelty@averillpark.net> wrote:
> 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.
> statement: ?null?null?null?null?null?null?null?null?null
what is interesting is that the insert, which is succeeding (or at least
was until i broke it 5 minutes ago), has the following .toString() output:
statement: ?null?null?null?null?null?null?null?null?null?null?null?null)
which is the expected number of ? characters (12). the processing of the
prepared statements for the update and the insert does indeed seem to be
behaving differently.
i don't see any material difference in the way that i construct the Insert
prepared statement and the Update prepared statement except for the things
required by SQL syntax.
i left the environment information out of the previous posting:
RedHat 7.3
postgresql 7.2.1-5 from the rpm
pgjdbc2.jar from the website late october of last year
jdk 1.4.1_01
thanks in advance for any help/advice,
richard
--
Richard Welty rwelty@averillpark.net
Averill Park Networking 518-573-7592
Unix, Linux, IP Network Engineering, Security
From your original message it appears that either update_core_variables should be set to one less, or you should use field_index++ instead of ++field_index. You might also consider updating the JDBC driver at some point. The Statement.toString method was recently corrected to display the actual statement you're working on. This hasn't made it into any of the prebuilt jar files on jdbc.postgresql.org, but perhaps soon. Kris Jurka On Fri, 7 Feb 2003, Richard Welty wrote: > On Fri, 7 Feb 2003 08:32:51 -0500 (EST) Richard Welty <rwelty@averillpark.net> wrote: > > 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. > > > statement: ?null?null?null?null?null?null?null?null?null > > what is interesting is that the insert, which is succeeding (or at least > was until i broke it 5 minutes ago), has the following .toString() output: > > statement: ?null?null?null?null?null?null?null?null?null?null?null?null) > > which is the expected number of ? characters (12). the processing of the > prepared statements for the update and the insert does indeed seem to be > behaving differently. > > i don't see any material difference in the way that i construct the Insert > prepared statement and the Update prepared statement except for the things > required by SQL syntax. > > i left the environment information out of the previous posting: > > RedHat 7.3 > postgresql 7.2.1-5 from the rpm > pgjdbc2.jar from the website late october of last year > jdk 1.4.1_01 > > thanks in advance for any help/advice, > richard > -- > Richard Welty rwelty@averillpark.net > Averill Park Networking 518-573-7592 > Unix, Linux, IP Network Engineering, Security > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
On Sun, 9 Feb 2003 04:31:19 -0500 (EST) Kris Jurka <books@ejurka.com> wrote:
>
> >>From your original message it appears that either update_core_variables
> should be set to one less, or you should use field_index++ instead of
> ++field_index.
i investigated that. the discrepancy is by more than one, and the parallel,
generally similar insert code is working properly. if both were broken, i'd
agree, but in this case...
> You might also consider updating the JDBC driver at some point. The
> Statement.toString method was recently corrected to display the actual
> statement you're working on. This hasn't made it into any of the
> prebuilt jar files on jdbc.postgresql.org, but perhaps soon.
lacking time to pursue this right now, i've retained the working prepared
statement for select (that being the only way to bring arrays in), but have
bagged the prepared statemens for output. i'll try again when the prebuilt
jars are updated.
richard
--
Richard Welty rwelty@averillpark.net
Averill Park Networking 518-573-7592
Unix, Linux, IP Network Engineering, Security
Richard, I can't see anything in the code that would lead to the behavior you are seeing. Can you put together a simple test program that I could compile and run that demonstrates these problems you are seeing? thanks, --Barry Richard Welty wrote: > On Fri, 7 Feb 2003 08:32:51 -0500 (EST) Richard Welty <rwelty@averillpark.net> wrote: > >>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. > > >>statement: ?null?null?null?null?null?null?null?null?null > > > what is interesting is that the insert, which is succeeding (or at least > was until i broke it 5 minutes ago), has the following .toString() output: > > statement: ?null?null?null?null?null?null?null?null?null?null?null?null) > > which is the expected number of ? characters (12). the processing of the > prepared statements for the update and the insert does indeed seem to be > behaving differently. > > i don't see any material difference in the way that i construct the Insert > prepared statement and the Update prepared statement except for the things > required by SQL syntax. > > i left the environment information out of the previous posting: > > RedHat 7.3 > postgresql 7.2.1-5 from the rpm > pgjdbc2.jar from the website late october of last year > jdk 1.4.1_01 > > thanks in advance for any help/advice, > richard > -- > Richard Welty rwelty@averillpark.net > Averill Park Networking 518-573-7592 > Unix, Linux, IP Network Engineering, Security > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >