File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

select works in db, but not across jdbc...

 
Matt Horton
Ranch Hand
Posts: 107
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I take it this is a driver error?
SELECT dbo.ORDER.ID, CONVERT(CHAR(12), dbo.ORDER.ORDERDATE, 1) AS
ORDERDATE, dbo.USERS.NAME, dbo.USERS.EMAIL, dbo.USERS.PHONE,
dbo.TCOMPLETE.COMPLETE AS STATUS, dbo.ORDER.NOTES FROM dbo.ORDER
INNER JOIN dbo.TCOMPLETE ON dbo.ORDER.COMPLETEID = dbo.TCOMPLETE.ID
LEFT OUTER JOIN dbo.USERS ON dbo.ORDER.CLIENTID = dbo.USERS.ID
WHERE (dbo.ORDER.LABID IN (SELECT dbo.USERLABINFO.LABID FROM dbo.USERS
INNER JOIN dbo.USERLABINFO ON dbo.USERS.ID = dbo.USERLABINFO.USERID
WHERE dbo.USERS.ID = 10))

This works great in SQL Server... but across jdbc, it throws the error:

java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Column 'DBO.ORDER.ID' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6031)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:6188)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(JdbcOdbc.java:2494)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(JdbcOdbcStatement.java:334)
at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(JdbcOdbcStatement.java:249)
at sun.jdbc.odbc.JdbcOdbcResultSet.calculateRowCount(JdbcOdbcResultSet.java:6063)
at sun.jdbc.odbc.JdbcOdbcResultSet.initialize(JdbcOdbcResultSet.java:150)
at sun.jdbc.odbc.JdbcOdbcStatement.getResultSet(JdbcOdbcStatement.java:420)
at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(JdbcOdbcStatement.java:250)
-on and on...

Is there a way to get jdbc to handle IN (SOME SELECT STATEMENT)? This seems to be a perpetual problem for me, and I have to come up w/ hokey sql workarounds to avoid the problem (oftentimes the exception is only thrown when there is no data that fulfills the query).
edit: silly end tags.
[ August 25, 2003: Message edited by: Matt Horton ]
 
Dana Hanna
Ranch Hand
Posts: 227
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Its that wacky ODBC driver that you are using. You are using "sun.jdbc.odbc.JdbcOdbcDriver" for your Jdbc Driver (and jdbc dbc:<db_name> for your url...). Use the Microsoft SQL Server jdbc driver.
The sun odbc driver doesn't conform to a lot of stuff... Example - the executeQuery() method throws an exception when no rows are returned, even though it should return an empty resultset.
The microsoft driver is available for free on thier site, and is a high performance JDBC Type-4 driver.
[ August 25, 2003: Message edited by: Dana Hanna ]
 
Matt Horton
Ranch Hand
Posts: 107
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Dana, you are my hero!
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic