aspose file tools*
The moose likes JDBC and the fly likes Query works with Oracle but not with java/jdbc Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Query works with Oracle but not with java/jdbc" Watch "Query works with Oracle but not with java/jdbc" New topic
Author

Query works with Oracle but not with java/jdbc

Steve Dambrosio
Greenhorn

Joined: Apr 02, 2009
Posts: 28
Hello, has anyone seen a problem similar to this? I'm executing the following procedure with a PreparedStatement (no ? parameters). The section with 'VA' and current_date [or (stat_cd = 'VA' and updt_dts < current_date - 30/1440))] doesn't work - however the query works perfectly if I run it using SQLDeveloper or Toad for Oracle! Any ideas? Thanks.

SELECT crte_dts, crte_usr_id, dm_3pty_ref_num, dm_id, dm_ref_id, err_cd_list, image_id, note_descr, source_id, spf_staging_id, splr_nam, stat_cd, updt_dts, updt_usr_id, wrtoff_amt FROM s_spf_staging_hdr WHERE ROWNUM <= 501 and (stat_cd in ('ER') or (stat_cd = 'VA' and updt_dts < current_date - 30/1440)) order by spf_staging_id ASC
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

Hi Steve,

Please explain 'doesn't work'. Do you get an error? If yes, please share it with us.
Regards, Jan


OCUP UML fundamental and ITIL foundation
youtube channel
Steve Dambrosio
Greenhorn

Joined: Apr 02, 2009
Posts: 28
Thanks for the reply! I get no errors. The "or (stat_cd = 'VA' and updt_dts < current_date - 30/1440)" part of the statement is working differently that it would when run in Toad because it selects rows with stat_cd = 'VA' regardless of the date. When I run the query in Toad, it properly selects rows of stat_cd = 'VA' using the date. I've tried it with a PreparedStatement and a "regular" Statement but get the same results. I even tried changing the 30/1440 to .0208 but that was just on a crazy whim. I need to play around some more but it seems quite irregular that *any* query would work differently, unless jdbc is not passing the *text* to oracle and letting oracle interpret it but rather interpreting it first and then passing it to oracle. In case you're interested, here's the log entry that shows the value of the SQL statement string and a snippet of code:

RADAR:2010.03.10.09.37.48:INFO:com.mckesson.radar.spf.SpfDAO:searchHeader:User Id=xxxxxxx, SQL=SELECT crte_dts, crte_usr_id, dm_3pty_ref_num, dm_id, dm_ref_id, err_cd_list, image_id, note_descr, source_id, spf_staging_id, splr_nam, stat_cd, updt_dts, updt_usr_id, wrtoff_amt FROM s_spf_staging_hdr WHERE ROWNUM <= 501 and (stat_cd in ('ER') or (stat_cd = 'VA' and updt_dts < current_date - 30/1440)) order by spf_staging_id ASC


String sqlStmt = sb.toString();
radarCommonLogger.log(Level.INFO, "User Id=" + userId + ", SQL=" + sqlStmt);

Connection conn = null;
ResultSet rs = null;
PreparedStatement ps = null;

try {
DataSource ds = RadarUtil.getDataSource();
conn = ds.getConnection();
ps = conn.prepareStatement(sqlStmt);
rs = ps.executeQuery();
while (rs.next()) {
spfListForm.addToSpfHeaderBeanList(new SpfHeaderBean(rs));
}

} finally {
RadarUtil.closeJdbcObj(conn, ps, rs);
}
wang lei
Greenhorn

Joined: Mar 09, 2010
Posts: 12
studing!
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

Welcome to JavaRanch, Wang.
wang lei
Greenhorn

Joined: Mar 09, 2010
Posts: 12
thank you,Jan.

i am new to the english language,my english is very poor,later,i wish to learn from all of you
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Query works with Oracle but not with java/jdbc