• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Query works with Oracle but not with java/jdbc

 
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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);
}
 
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
studing!
 
Jan Cumps
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Welcome to JavaRanch, Wang.
 
wang lei
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
You ought to ventilate your mind and let the cobwebs out of it. Use this cup to catch the tiny ads:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic