• 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

Getting java.sql.SQLException. Why?

 
Ranch Hand
Posts: 208
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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





 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
shukla raghav
Ranch Hand
Posts: 208
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
And did you concider this suggestion:
Does the query work when you execute it in MsAccess SQL window?
 
shukla raghav
Ranch Hand
Posts: 208
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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."
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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


.

 
shukla raghav
Ranch Hand
Posts: 208
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
still looking for solution
 
Jan Cumps
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 208
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.

 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic