aspose file tools*
The moose likes JDBC and the fly likes updating rows in table error Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "updating rows in table error" Watch "updating rows in table error" New topic
Author

updating rows in table error

dom torrez
Greenhorn

Joined: Apr 29, 2011
Posts: 5
Hello all,
I'm currently working on a program to query the database, massage the returned data (ResultSet) through our business logic, then save this data back to a table (different from qry table) in the db.
I'm close, but things just aren't working right. I have a couple of questions, hopefully someone can help.
1) Is there an easy way to insert nulls into a table when the field is null, but insert the value when it isn't null.
For example, if I knew the field would always be null I could use:
## updateTableInDb.setNull(9, java.sql.Types.Date);

or if the field is not null then the code I want to use is...

## updateTableInDb.setDate(dto.getMyFieldDate);

But this field is not always null.
I'm trying to avoid coding the logic to check if this field is null and THEN generate the appropriate line of code (see above). There should be an easier way, shouldn't there?

2) I'm getting a weird error: 'Unable to complete Query: org.postgresql.util.PSQLException: The column index is out of range: 31, number of columns: 30.'
I've verified several times over, that my parameters match my table fields numbers. all are 33. so in my prepared statement query I have 33 ?'s, and all correspond to my 33 updateTableInDb statements.
anyone have a clue what may be going on? Is there a limit to the # of fields I can update in a table through the prepared statement?

I appreciate your time.

--Dom
dom torrez
Greenhorn

Joined: Apr 29, 2011
Posts: 5
In debugger I did see that my lines in the update_qry which are attempting to run a DB function on the input parameter are being skipped.
For example:
update myTable
set ...
archive_age = age( timestamp '?') ,
...

with the parameter input code as follows:

updateMyTable.setDate(15, dto.get_update()); --This value is a date retrieved from my dto via my getter method.

I can watch through the debugger, all my values being populated. ie. the ? parameter place holders changing to the appropriate values. But when I gets to the placeholders for my functions it skips right over these.

Below is my update statement, Notice that all the fields are populated except the following: dataflow_age, archive_age, netrssoh_age. Those still have the '?' placeholders. The code just skipped right over these. any ideas??

Pooled statement wrapping physical statement UPDATE myTable set pnum = ABCD, project = PBO, idlocale = 123, idsite = 456, stationid = 789,
siteshortname = Nikolski__AK2007, region = AK, comms_update = NULL, eqpunavcoid = NULL, manualdownload = 0, dataflow_update = 2009-10-27 -06:00:00, dataflow_age = age( timestamp '?'), dataflow_latest_file = 2009-10-27 -06:00:00,
archive_update = AB02200910260000a.T00, archive_age = age( timestamp '?'), archive_latest_file = 2009-10-26 -06:00:00, netrssoh_update = 2009-10-26 -06:00:00, netrssoh_age = age( timestamp '?'), netrs_soh_status_label = AB02200910260000a.T00, comms_soh_status_label = NULL,
dataflow_soh_status_label = NULL, archive_soh_status_label = soh_label_unknown, comms_detail = NULL, dataflow_detail = soh_label_bad, archive_detail = soh_label_bad, netrs_detail = NULL, netrs_voltage = NULL,
netrs_temp = NULL, comms_url = NULL, comments = 0.0, status = 0.0, status_label = NULL
WHERE station_soh_snapshot.pnum = ?
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18563
    
    8

dom torrez wrote:I can watch through the debugger, all my values being populated. ie. the ? parameter place holders changing to the appropriate values. But when I gets to the placeholders for my functions it skips right over these.


You didn't find it suspicious that '?' was treated differently than ? was? The former is a string containing a question mark, whereas the second is a placeholder for PreparedStatement.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18563
    
    8

dom torrez wrote:Is there an easy way to insert nulls into a table when the field is null, but insert the value when it isn't null.
For example, if I knew the field would always be null I could use:
## updateTableInDb.setNull(9, java.sql.Types.Date);

or if the field is not null then the code I want to use is...

## updateTableInDb.setDate(dto.getMyFieldDate);

But this field is not always null.
I'm trying to avoid coding the logic to check if this field is null and THEN generate the appropriate line of code (see above). There should be an easier way, shouldn't there?


I've found this works perfectly well:
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38729
    
  23
Welcome to the Ranch

I think this thread would sit better on our databases forum. Moving.
dom torrez
Greenhorn

Joined: Apr 29, 2011
Posts: 5
Paul Clapham wrote:
dom torrez wrote:I can watch through the debugger, all my values being populated. ie. the ? parameter place holders changing to the appropriate values. But when I gets to the placeholders for my functions it skips right over these.


You didn't find it suspicious that '?' was treated differently than ? was? The former is a string containing a question mark, whereas the second is a placeholder for PreparedStatement.


Paul, Yes I did. but the db function being called requires the values to be passed wrapped in ' '. If I remove the ' ', then I get the following:
Unable to complete Query: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$12"

The function requires that the timestamp be wrapped in 'timestamp'.
Essentially what i'm attempting to do is calculate the age of a date. Date1 = today(), date2 = <passed in date> -- for example 12/21/2000.
In postgres I just run... age(timestamp '12/21/2000') and the result is an interval output of (today - '12/21/2000')

Here's what I get at the command line from my db:
select age(timestamp '12/21/2000');
age
-------------------------
10 years 4 mons 12 days
(1 row)

So i'm trying to do the equivalent in java, where I pass in the date ie. 12/21/2000, subtract it from today then output a fancy interval like result (10 years 4 mns 12 days).
From my research, java is very lousy at this. If I could just get my date parameter passed into the query, it'll be MUCH simpler to let the db handle the age calculation.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18563
    
    8

dom torrez wrote:Paul, Yes I did. but the db function being called requires the values to be passed wrapped in ' '.


Okay, so then wrap the values in quotes. You're just doing it in the wrong place; it should be clear that you can't put the quotes into the PreparedStatement directly. So they would have to be part of the setString() parameter.
dom torrez
Greenhorn

Joined: Apr 29, 2011
Posts: 5
Thanks for the help Paul. So what i've done is removed the "calculation" part of my code to my front end query and save this as a String in my dto.
what I'm doing is running a query (qry1), I manipulate the data from qry1. I then save this data to my dto, and use my dto to populate parameters in my update query(qry2).

This "moving" of my calculations from qry2 to qry1, has essentially made my qry2 and parameters simpler.
But...
I'm still getting errors:
org.postgresql.util.PSQLException: ERROR: column "dataflow_age" is of type interval but expression is of type character varying

The problem (I believe) is that I'm working with database intervals. Java is handling the input of an interval into my program as a String. For example qry1 returns the value '48 years 7 mons 1 day 03:57:59.876544' as my dataflow_age. I save this in my dto as a String value.
This side works fine, no errors in DB or in Java. But when I goto save this value back to the db, ie. through my update query. I'm using: updateMyTable.setString(12, dto.getDataflow_age());
But in the database, the field type for dataflow_age is Interval.

Now the funny thing is that I can insert into the table at the db commandline a string value of '48 years 7 mons 1 day 03:57:59.876544', and it works with no complaints. But attempting to do this very same thing through java code produces an type error (see above).
I know that there's probably some type casting being done on the DB side when I do the update on the command line. But I would think it shouldn't matter as long as the string format is similar to intervals, in this case they are exact.
Here's the DB output:

my_db=# update my_table set dataflow_age = '48 years 7 mons 1 day 03:57:59.876544' where pnum = 'FAKE';
UPDATE 1

It seems like the PreparedStatement is forcing some type matching which is failing between my String value and the db field of Interval type. I'm looking into using updateMyTable.setObject(12, dto.getDataflow_age()); instead of updateMyTable.setString(12, dto.getDataflow_age()); perhaps this will work?

again, thanks for the help.
--Dom

dom torrez
Greenhorn

Joined: Apr 29, 2011
Posts: 5
OK, finally figured this out. I had to use the following in my update query for any field which I was attempting to enter a java.String in the interval format, into a DB field type of INTERVAL.

CAST (? AS INTERVAL)

this worked great.

thanks for all the help.
--Dom
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: updating rows in table error