Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Query works in DB, but not with JDBC (MS version)

 
Joe Vahabzadeh
Ranch Hand
Posts: 140
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey all,

I stumbled across this thread regarding problems with a simple query.

In my case, it works when I use the Query Analyzer with Microsoft SQL Server, but it does NOT work with my java code. However, since I'm using (I think) Microsoft's JDBC, as was suggested in the thread I mentioned, I don't see why I'm getting this error.


The query is:

SELECT MAX(EntryNum) FROM Individual

I get the following message when the exception is thrown:

[Microsoft]{SQLServer 2000 Driver for JDBC}{SQL Server}Column 'Individual.EntryNum' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.


What am I doing wrong here?
 
Joe Vahabzadeh
Ranch Hand
Posts: 140
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ugh, gotta lay off the SHIFT key.. those should all be square brackets, not curly braces, but you get the idea.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34178
340
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Joe,
That is a valid SQL statement. Are you by any chance doing rs.get("EntryNum") ? If so, that won't work without creating an alias for the column. Alternatives are to use "as name" or retrieve the result by column number.

If this isn't the problem, can you show your JDBC code? Also, check which line throws the exception. Is it when you execute the statement, get the result, ...

Also for the future, note that we have an edit button. It is the second button from the left near the top of all your posts. The button that looks like a pencil and paper.

And most importantly, welcome to Javaranch!
 
Joe Vahabzadeh
Ranch Hand
Posts: 140
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks! It didn't occur to me to look at the little icons, I was looking for a link labeled "edit"...

As to the error I'm getting, here's the code:


I've tried commenting out the entire if-else block, and the error still happens, so I know it's the rs=stmt.executeQuery line and not at the rs.last or the rs.getLong lines.

Also, the "AS EntryNum" wasn't originally there. I did that hoping that the AS clause would solve my problems, but no luck.

The connection's definitely open at the time (I know, bad practice, I use a static object-level variable for it!), because different querys put into the rs=stmt.executeQuery line will work, just not the particular query I want.

Any help would be greatly appreciated!
[ January 06, 2005: Message edited by: Joe Vahabzadeh ]
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34178
340
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Joe,
The query looks fine. You said it works in query analyzer. Does it work at the SQL Server command line. Some databases (like db2) are actually less strict for the query analyzer. Your query is standard SQL though. If it works at the command line, it is definitely a driver problem.

This isn't the problem, but I just want to comment on the resultset part. The else statement will never get executed. Aggregate functions always return exactly one value. I forget if it is 0 or null here. Also, getLong returns a long so the case it redundant. (I'm a big fan of avoiding unnecessary code.) So it could be:



[edited because i deleted part of the code tag]
[ January 06, 2005: Message edited by: Jeanne Boyarsky ]
 
Joe Vahabzadeh
Ranch Hand
Posts: 140
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ah, thanks for the shortcut on the if-else bit!

Also, I finally figured out what I was doing wrong . . I had a feeling it was something simple and stupid, but finally found it.

It wasn't the problem with the query itself, it was how I set up the original statement object (which I did in a separate method).

To wit, I was using:

stmt = con.createStatement(ResultSet.TYPE_SCROLL INSENSITIVE, ResultSet.CONCUR_UPDATEABLE)

when I *should* be using

stmt = con.createStatement(ResultSet.TYPE_SCROLL INSENSITIVE, ResultSet.CONCUR_READ_ONLY)


D'oh!
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34178
340
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Joe,
I'm glad you got it working. Thanks for sharing the solution.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic