• 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

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

 
Ranch Hand
Posts: 107
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
Ranch Hand
Posts: 227
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Dana, you are my hero!
reply
    Bookmark Topic Watch Topic
  • New Topic