• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Query works with Oracle but not with java/jdbc

 
Steve Dambrosio
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2608
14
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Steve,

Please explain 'doesn't work'. Do you get an error? If yes, please share it with us.
Regards, Jan
 
Steve Dambrosio
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
studing!
 
Jan Cumps
Bartender
Posts: 2608
14
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to JavaRanch, Wang.
 
wang lei
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thank you,Jan.

i am new to the english language,my english is very poor,later,i wish to learn from all of you
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic