CODEWITHSUNDEEP
javasqlsql-server-2008scalajdbc
djsumdog
djsumdog

Reputation: 2710

Parameters in odd locations in Prepared Statements in Microsoft SQL and JDBC

So I have the following statement I'm executing in Microsoft SQL 2008:

  SELECT
  'UTC' AS timezone,
  rel.unique_id AS relay,sns.unique_id AS sensor,
  dateadd(MINUTE, datediff(MINUTE, 0, pak.rtime) / ? * ?, 0) AS time,
  SUM(CONVERT(FLOAT,dat.data)) AS total
  FROM sensor_data dat
   LEFT OUTER JOIN data_package pak ON dat.package_id = pak.id
   LEFT OUTER JOIN relays rel ON pak.relay_id = rel.id
   LEFT OUTER JOIN sensors sns ON dat.sensor_id = sns.id
   LEFT OUTER JOIN sensor_types typ ON sns.sensor_type = typ.id
   WHERE typ.name = 'Volume' AND dateadd(MINUTE, datediff(MINUTE, 0, pak.rtime) / ? * ?, 0) > ? AND dateadd(MINUTE, datediff(MINUTE, 0, pak.rtime) / ? * ?, 0) < ?
   GROUP BY rel.unique_id,sns.unique_id, dateadd(MINUTE, datediff(MINUTE, 0, pak.rtime) / ? * ?, 0)
   ORDER BY time,relay,sensor

If I set the parameters using the jTDS/JDBC driver like so:

Parameter 1: 15
Parameter 2: 15
Parameter 3: 15
Parameter 4: 15
Parameter 5: 2011-10-31 20:00:00
Parameter 6: 15
Parameter 7: 15
Parameter 8: 2011-12-29 19:00:00
Parameter 9: 15
Parameter 10: 15

I get the error: 

Caused by: java.sql.SQLException: Column 'data_package.rtime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

If I manually put in 15s in all those ? spaces, the query works perfect with just the dates as parameters.

1) Is there anyway to parametrize the interval value (15 in this case) or do I just have to escape it and search and replace that before it becomes a prepared statement (and if that's true, what's the best way to escape that parameter in Scala/Java)

2) Can I keep from repeating the dateadd(datediff()) section three times? I know I can't reference "time" in the WHERE clause, but is there some other way to specify that somewhere to make it cleaner?

Upvotes: 4

Views: 1016

Answers (2)

Michael Lorton
Michael Lorton

Reputation: 44406

Andomar is right that the problem is use of parameters in your GROUP BY, but I believe his solution might be over-complicated. Easier, I would think, to write:

SELECT
  'UTC' AS timezone,
  rel.unique_id AS relay,sns.unique_id AS sensor,
  dateadd(MINUTE, datediff(MINUTE, 0, pak.rtime) / ? * ?, 0) AS time,
  SUM(CONVERT(FLOAT,dat.data)) AS total
  FROM sensor_data dat
   LEFT OUTER JOIN data_package pak ON dat.package_id = pak.id
   LEFT OUTER JOIN relays rel ON pak.relay_id = rel.id
   LEFT OUTER JOIN sensors sns ON dat.sensor_id = sns.id
   LEFT OUTER JOIN sensor_types typ ON sns.sensor_type = typ.id
   WHERE typ.name = 'Volume' AND dateadd(MINUTE, datediff(MINUTE, 0, pak.rtime) / ? * ?, 0) > ? AND dateadd(MINUTE, datediff(MINUTE, 0, pak.rtime) / ? * ?, 0) < ?
   GROUP BY rel.unique_id,sns.unique_id, time
   ORDER BY time,relay,sensor

(On the other hand, since in your particular case, you are using the exact same expression in the WHERE, maybe the subquery would be better.)

Upvotes: 0

Andomar
Andomar

Reputation: 238176

Your select looks like:

SELECT dateadd(MINUTE, datediff(MINUTE, 0, pak.rtime) / ? * ?, 0) AS time,

And your group by:

GROUP BY dateadd(MINUTE, datediff(MINUTE, 0, pak.rtime) / ? * ?, 0)

With the ? replaced by constants, these two are identical.

But using unnamed parameters introduces a problem. The select version uses parameter 1 and 2, and the group by version uses parameter 9 and 10. And SQL Server does not now that those parameters are always equal. So it throws an error.

You can avoid this situation by computing the field in a subquery:

left join
        (
        select  *
        ,       dateadd(MINUTE, datediff(MINUTE, 0, pak.rtime) / ? * ?, 0) as X
        from    data_package
        ) as pak
on      dat.package_id = pak.id

You can now refer to pak.X in other parts of your query, like:

group by
        pak.X

Upvotes: 5

Related Questions