JDBC query returning no rows, but interactive query does?

Question

Hi all,

I'm attempting to retrieve data from a SolarWinds network performance database (MS SQL 2005) and a query that works perfectly fine interactively (in the Orion database manager) returns no rows when run via JDBC. Any ideas?

The query itself is a shocker (I dislike MS-SQL date/time handling which I believe forces queries like this for joins by date/hour). I can cut and paste the query output by println and it works fine, yet in my program it returns no rows (but throws no exceptions).

I assume that query complexity doesn't matter, on the basis that JDBC will not attempt to parse the query - it will just pass it through to the back end.

String qtext = new String("select rd.nodeid, rd.hr, rd.response, rd.loss, cd.cpu, cd.mem, bd.nomem, bd.smmiss, bd.mdmiss, bd.bgmiss, bd.lgmiss, bd.hgmiss" + " from" +
    " (select nodeid,  DATEPART(hh, DateTime) as hr, round(avg(AvgResponseTime), 0) as response, round(avg(PercentLoss), 0) as loss" +
    "    from ResponseTime_Detail" +
    "    where DateTime >= " + today + " and DateTime < " + tomorrow +
    "    group by nodeid, DATEPART(hh, DateTime)" +
    " ) as rd" +
    " left outer join" +
    " (select nodeid,  DATEPART(hh, DateTime) as hr, round(avg(AvgLoad), 0) as cpu, bound(avg(AvgPercentMemoryUsed), 0) as mem" +
    "      from CPULoad_Detail" +
    "    where DateTime >= " + today + " and DateTime < " + tomorrow  +
    "      group by nodeid, DATEPART(hh, DateTime)" +
    " ) as cd" +
    " on rd.nodeid = cd.nodeid and rd.hr = cd.hr" +
    " left outer join" +
    "  (select nodeid,  DATEPART(hh, DateTime) as hr, round(avg(BufferNoMem), 0) as nomem, round(avg(BufferSmMiss), 0) as smmiss, round(avg(BufferSmMiss), 0) as mdmiss," +
    "          round(avg(BufferBgMiss), 0) as bgmiss, round(avg(BufferLgMiss), 0) as lgmiss, round(avg(BufferHgMiss), 0) as hgmiss" +
    "      from CiscoBuffers_Detail" +
    "    where DateTime >= " + today + " and DateTime < " + tomorrow +
    "      group by nodeid, DATEPART(hh, DateTime)" +
    " ) as bd" +
    " on rd.nodeid = bd.nodeid and rd.hr = bd.hr" +
    " order by rd.nodeid, rd.hr;");
 System.out.println("Query from hell = [" + qtext + "]");
 st = sol.db.createStatement();
 System.out.println("Created statement");
 rs = st.executeQuery(qtext);
 System.out.println("Executed statement");
 while (rs.next()) {
   ....
 }

Thanks all for your suggestions. I believe the issue was with interpretation of dates/times. I used a PreparedStatement as suggested, and the query then worked.

Answer 1

As to your query, dates/timestamps are to be set using PreparedStatement#setDate()/setTimestamp(). Not only to avoid SQL injections but also to prevent from formatting mistakes in the String representation of the Date.

Answer 2

JDBC syntax does not terminate the SQL statement with a semi-colon.

The line adding the last clause to the SQL string should read:

" order by rd.nodeid, rd.hr");