aspose file tools*
The moose likes JDBC and the fly likes Query works in DB, but not with JDBC (MS version) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Query works in DB, but not with JDBC (MS version)" Watch "Query works in DB, but not with JDBC (MS version)" New topic
Author

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

Joe Vahabzadeh
Ranch Hand

Joined: Jan 05, 2005
Posts: 140
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

Joined: Jan 05, 2005
Posts: 140
Ugh, gotta lay off the SHIFT key.. those should all be square brackets, not curly braces, but you get the idea.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30506
    
150

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!


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Joe Vahabzadeh
Ranch Hand

Joined: Jan 05, 2005
Posts: 140
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30506
    
150

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

Joined: Jan 05, 2005
Posts: 140
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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30506
    
150

Joe,
I'm glad you got it working. Thanks for sharing the solution.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Query works in DB, but not with JDBC (MS version)