wood burning stoves 2.0*
The moose likes JDBC and the fly likes select works in db, but not across jdbc... Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "select works in db, but not across jdbc..." Watch "select works in db, but not across jdbc..." New topic
Author

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

Matt Horton
Ranch Hand

Joined: Feb 06, 2002
Posts: 107
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

Joined: Feb 28, 2003
Posts: 227
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

Joined: Feb 06, 2002
Posts: 107
Thanks Dana, you are my hero!
 
 
subject: select works in db, but not across jdbc...