*
The moose likes JDBC and the fly likes Getting java.sql.SQLException. Why? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Getting java.sql.SQLException. Why?" Watch "Getting java.sql.SQLException. Why?" New topic
Author

Getting java.sql.SQLException. Why?

shukla raghav
Ranch Hand

Joined: Aug 03, 2008
Posts: 200
in my application , i wish to fetch exactly one Question from each chapter to generate a final Exam question paper. Now since every chapter has several questions and i wish to randomly extract one question from each chapter from the Questions_Master table. Because it is a practice application i am using MS Access as database. i used following query to fetch data




This is the Exception that i am getting. I had to search the MS Access forum to know the function that is used by MS Acess to return random values. I am using it in the jdbc expression and it did not return unidentified function error so it means Rnd() method is recognized but i did not understand why is it returning Data Type misMatch





Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

Have you read the description of Rnd, and it's caveats?
http://office.microsoft.com/en-us/access/HA012289011033.aspx

What is the data type of t1.QID?
Does the query work when you execute it in MsAccess SQL window?


OCUP UML fundamental and ITIL foundation
youtube channel
shukla raghav
Ranch Hand

Joined: Aug 03, 2008
Posts: 200
here i am pasting an Excerpt from the following site

http://www.carlj.ca/2007/12/16/selecting-random-records-with-sql/

To select a random record or records from MS Access, the method isn’t much different.

Instead of doing an ORDER BY NewID() like in SQL Server, you would use ORDER BY Rnd(Field1), where Field1 is the name of your primary auto-numbered key field.



Although the SQL statements are very similar, the results however, are not.

I ran the following SQL statement through my C# sharp code three times, each time looping to select 5000 random records. Each time however, the results were exactly the same.

I closed down the web app, and restarted it, and what do you know, the results were identical again.


now i am trying to use the same query and fire it using the following statement from my java program

Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

And did you concider this suggestion:
Does the query work when you execute it in MsAccess SQL window?
shukla raghav
Ranch Hand

Joined: Aug 03, 2008
Posts: 200
i have understood my mistake; the Rnd() expects an argument that is field name of the colums that is of type AutoNumber. Now i need to change my design and insert a column with auto number as the data type in my Database. ooooooh no..!!! isnt there any other way or i need to fetch ordered data and then write a code on the application end to do the same. But that will complicate my program logic.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

shukla raghav wrote:... the Rnd() expects an argument that is field name of the colums that is of type AutoNumber...
No.

From the specs: "Rnd[(number)]: The optional number argument is a Single or any valid numeric expression."
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30085
    
149

Reposting from a new thread on this exact topic.

shukla raghav wrote:here is the problem

in my application i have a table that looks like the following


---------------------------------------------------------
RNO QID QDESC CHID
----------------------------------------------------------
1 A //some data// 1
2 B //some data// 2
3 C //some data// 1
4 D //some data// 2
5 E //some data// 1
6 F //some data// 3



now i need to randomly select exactly one record from each chapter, like my result set must look like this



---------------------------------------------
QID QDESC CHID
---------------------------------------------
A //some data// 1
B //some data// 2
F //some data// 3



you can see i am selecting one record from each chapter i.e. 1,2 and 3

beacuse i am connecting my application with an MS Access database here are some of the facts that i have already tested. May be they are useful.

in order to select N random values from a table in ms access we can use the following query



SELECT TOP N column
FROM table
ORDER BY Rnd(t1.RNO);



please suggest some solutions for how should i write the query. I have been trying hard bbut uselessly


.


[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
shukla raghav
Ranch Hand

Joined: Aug 03, 2008
Posts: 200
still looking for solution
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

The difficulty is that top 1 will return only one record, and not a record per chapter.

I have tried your rnd() example though, and that works: select * from Questions_Master ORDER BY Rnd(rno)

And my rno is of type Long Integer (Number)

You can solve your problem using two queries:

First get a list of allchapters:
select distinct Chid from Questions_Master order by chid

Then loop through all returned chapters, and for each Chid:
select top 1 * from Questions_Master WHERE Chid = ? ORDER BY Rnd(rno)
shukla raghav
Ranch Hand

Joined: Aug 03, 2008
Posts: 200
well that is certainly a solution but is there someway we could do it using a single query. I am not a database expert rather a layman. But i wanted to know cant we use group by clause in any way. what i know is that the group by works on a group of data why cant we make the Rnd() function work on each data block grouped by chid.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

But i wanted to know cant we use group by clause in any way
I don't think so.
Whatever you put in your query, the top 1 will return the first result, not the first result per chapter. And group by is a mechanism to aggregate values. It won't serve your goal.

 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Getting java.sql.SQLException. Why?
 
Similar Threads
Joining 3 Tables
Simple JDBC question
Error when Inserting record
Problems in inserting data to MS Access Database
Data source name not found and no default driver specified